Dimension array

Posted by David on July 06, 2001 12:53 PM

I am having trouble when I define an array
If i do not declare it everything works fine but when I do it all fall apart.

I would like to define everything.

Dim vntArray(100,100) As Variant

'SUBMIT QUERY
vntArray = modMain.PerformQuery(strQuery, 1)

now the performquery redims the array but if I declare it then it won't let me all the perform query.

Any clues

Public Function PerformQuery(ByVal strQuery As String, _
Optional ByVal intFieldCount As Integer) As Variant

error is can't assign to array

Posted by Damon Ostrander on July 06, 2001 4:09 PM

Hi David,

This is something that is not very clearly explained in the VBA documentation. The problem is, a function cannot return an array. It can only return a variant that contains an array. Consequently, the function result must be assigned to a single variant variable, NOT an array. Your function should have worked if you had simply declared

Dim vntArray As Variant

without the array dimensions. The dimension of the array that is assigned to vntArray by the PerformQuery function is determined by that function, i.e., it will only be 100 by 100 if PerformQuery so declares it internally.

You will still be able to access the elements of vntArray as if it were itself the array. For example, to assign the 50,50 element to the variable X would be

X = vntArray(50,50)

This distinction between an array and a variant that contains an array is a very fine one, but very important. This is what makes it possible for VBA functions to return Excel arrays so that a single VBA function can fill an entire range of cells rather than just a single cell.

Happy computing.

Damon