Variant as matrix

[curious]

Some relatively easy question:
Suppose I pass a matrix from spreadsheet to VBA as Variant


Code
-------------------

Public Function foo(matrix As Variant)

End Function

-------------------


How do I get the dimensions of the matrix?
(i.e. what is the equivalent of LBound,UBound for this case)

How do I address the elements of this matrix
(i.e. what is the equivalent of matrix(i,j) for this case)

Thanks a lot

 

[Guest]

The easiest way to address every element without knowing its rank (how many dimensions it has) of any matrix is

For each Element in MyMatri
... your cod
Nex

This would not be suitable if you want to reassign to MyMatrix

LBound(MyMatrix) gives the starting index of a 1-dimensional matrix; UBouund the last
LBound(mymatrix,1) dives the starting index on the first dimension etc. Any reference to a non-existent dimension is an error

There is no way to query the number of dimensions in a matrix

Depending on how the variant matrix is created, its starting index on any dimension is 0 when created using Split or Array, irrespective of Option Base

 

[Tom Ogilvy]

Sub Main()
Dim varr as Variant
varr = Range("A1:A4").Value

foo varr
End Sub

Public Function foo(matrix As Variant)
Dim lb1 as Long, lb2 as Long, ub1 as Long, ub2 as Long
Dim numDim as Long, i as long, j as long
If IsArray(matrix) Then
numDim = 1
lb1 = LBound(matrix, 1)
ub1 = UBound(matrix, 1)
On Error Resume Next
lb2 = LBound(matrix, 2)
ub2 = UBound(matrix, 2)
If Err.Number = 0 Then
Err.Clear
numDim = 2
End If
On Error GoTo 0
If numDim = 1 Then
For i = lb1 To ub1
Debug.Print "i= " & matrix(i)
Next
Else
For i = lb1 To ub1
For j = lb2 To ub2
Debug.Print "Matrix(" & i & ", " & j & ")= " & matrix(i, j)
Next
Next
End If
End If
End Function

 

[Paul Robinson]

Hi
Essentially the same way.
Your Variant matrix will be 1 based, so if you are looping through its
elements start at 1 not 0. Note that a 1 dimensional Variant (a piece
of row or column) will still need two indices e.g. (1, j) if it is a
row

cheers
Paul

 

[Paul Robinson]

Hi
Just one extra point; a Variant coming from a single cell value e.g.
MyVariant = Cell.Value cannot be accessed as MyVariant(1, 1). This is
a potential source of pain if you are creating variant arrays inside a
loop for example. If your code can potentially create such a Variant,
you will have to test for it and simply use MyVariant rather than
MyVariant(1,1).
cheers
Paul

 

[ross]

Hi,
could you not use a 2d array?

Ross