Excel VBA pass one field of a matrix array to a UDF

Asked Jun 17 '20 at 19:18

Active Jun 17 '20 at 19:22

Viewed 14 times

I have written some code that builds up a matrix of values (strings and numbers). The matrix is always 3 rows and unknown columns and is sized like this:

ReDim MyMatrix(1 To 3, 1 To SelectedPivot.RowFields.Count)

I then populate the matrix with three loops.

I have written a UDF that requires 1st and 3rd row of the matrix, as follows:

Public Function MyUDF(SourceTable As ListObject, Arr1() As Variant, Arr3() As Variant)
...
End Function

...where Arr1() is the first row of 'MyMatrix' and Arr3() is third row of 'MyMatrix'

Back in the main sub, I am trying to use the UDF like this:

Result = MyUDF(SourceTable, MyMatrix(1), MyMatrix(3))

And I get the error 'Type mismatch: array or user-defined type expected'

Any suggestions on how to get this working? I thought 'MyMatrix(n)' in a two-dimensional array gives the nth row

edited Jun 17 '20 at 19:22

BigBen

38,994
6
24
37

asked Jun 17 '20 at 19:18

JakeyG

1

Loop the array and populate two 1d arrays with the necessary data. – Scott Craner Jun 17 '20 at 19:20
It's possible as a work around, but not ideal as then creating two additional arrays purely as middle-men. Hoping for a cleaner approach. – JakeyG Jun 17 '20 at 19:23
1

https://stackoverflow.com/questions/175170/how-do-i-slice-an-array-in-excel-vba – BigBen Jun 17 '20 at 19:25
1

You can always use `Application.Index` but that is really slow. The quickest method is to create two other arrays – Scott Craner Jun 17 '20 at 19:25
^^^^^ I second Scott. `Application.Index` as noted has issues. – BigBen Jun 17 '20 at 19:28
Thanks @BigBen, answer is here: https://stackoverflow.com/questions/175170/how-do-i-slice-an-array-in-excel-vba – JakeyG Jun 17 '20 at 19:38

Excel VBA pass one field of a matrix array to a UDF

0 Answers0