title: how to create a excel function that returns an array of variable size
body: <p>I am trying to create a excel function that returns the result of a sql query. 
  I can do it if the result has size 1x1. However if the result is larger than that, I could not make the code work. Is there a solution for that? I am especially struggling with the fact that in all examples I have found, it is required to select exactly the right number of cells, whereas the result of my sql query could be of any size... and changing through time.</p>
  
  <p>here is what I use so far:</p>
  
  <pre><code>Public Function getTS(field As String) As String
          Set oConnection = New ADODB.Connection
          Dim oRecordset As ADOR.Recordset
          oConnection.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=AISDB;Data Source=mydomainlocation\mydatabase;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;"
          Set oRecordset = oConnection.Execute("select " &amp; field &amp; " from mytable where country = 'China'")
          If oRecordset.EOF Then
              getTS = "n/a"
          Else
              getTS = oRecordset(field)
          End If
      End Function
  </code></pre>