Passing array to a function

  • Thread starter Thread starter GB
  • Start date Start date
G

GB

AAA=ExactMatch ( Guess(1,) )

The above does not work, needless to say.

ExactMatch is a function expecting to receive a single dimensional array

Guess is a 2-dimensional array. What I am attempting to do is pass the 1st
row of Guess to the function.

What is the correct syntax to do that, please? (Obviously, I can be more
explicit about what I am passing across, but I thought VBA would take care
of this for me.)

Thanks

Geoff
 
There is no automatic way in VBA to do this.

Since you are using VBA in Excel, you can use the Excel Index function

Sub Tester2()
Dim myArray(1 To 6, 1 To 2)
For i = 1 To 6
For j = 1 To 2
myArray(i, j) = Chr(i * j + 64)
Next j
Next i
varr = Application.Index(myArray, 0, 2)
For i = LBound(varr) To UBound(varr)
Debug.Print i, varr(i, 1), myArray(i, 1), myArray(i, 2)
Next
End Sub


This returns the second column of a 2D array - but it returns it as 1 x
Number of rows, 1 x 1
 
Did you really mean first Row?

Sub testme()

Dim myArray As Variant
Dim myArray2 As Variant

'just some test data in a 9x2 array
myArray = ActiveSheet.Range("a1:b9").Value
myArray2 = Application.Index(myArray, 1, 0)

End Sub

Or did you mean the first column?

Sub testme()

Dim myArray As Variant
Dim myArray2 As Variant

'just some test data in a 9x2 array
myArray = ActiveSheet.Range("a1:b9").Value

myArray2 = Application.Transpose(Application.Index(myArray, 0, 1))

End Sub

Be aware that application.transpose and application.index fail for some versions
of excel when the number of elements exceeds 5461. (xl2002 has been changed to
support lots more.)
 
Thanks Tom and Dave. I guess I was looking for something that does not exist
in VBA.

I had not thought of using Application.Index, but I can see that that would
shorten my code.

Thanks again.

Geoff
 
Back
Top