How to pass arrays of integers or doubles to VBA function in Excel
I think Jim was rushing his answer:
Also ranges give 2D arrays, but if you
transpose it you get a 1D array...
If your range is multiple rows with a single column in width, then this is
true.
If it is multiple columns and multiple rows, it is isn't
If it is multiple columns and a single row, it isn't true To account for
the other situation that can be converted, you could use this to illustrate.
Sub testRevised()
Dim rng As Range
Dim ary As Variant
Set rng = Selection
If rng.Count = 1 Then
MsgBox "Single cell"
Exit Sub
End If
rw = rng.Rows.Count: col = rng.Columns.Count
If rw 1 Then
If col = 1 Then
ary = Application.Transpose(rng.Value)
Call test2(ary)
Else
MsgBox "Multiple rows, multiple columns"
End If
Else
If rw = 1 Then
ary = Application.Transpose( _
Application.Transpose(rng.Value))
Call test2(ary)
End If
End If
End Sub
' no change
Sub test2(ByRef ary As Variant)
Dim i As Integer
For i = LBound(ary) To UBound(ary)
MsgBox ary(i)
Next i
End Sub
So if multiple columns and a single row, you need to do the tranpose twice.
--
regards,
Tom Ogilvy
"Jim Thomlinson" wrote in message
...
A couple of things to note. You need to leave the variables as type variant
but that should not be a big deal. Also ranges give 2D arrays, but if you
transpose it you get a 1D array... so something like this...
Sub test()
Dim rng As Range
Dim ary As Variant
Set rng = Selection
ary = Application.Transpose(rng.Value)
Call test2(ary)
End Sub
Sub test2(ByRef ary As Variant)
Dim i As Integer
For i = LBound(ary) To UBound(ary)
MsgBox ary(i)
Next i
End Sub
--
HTH...
Jim Thomlinson
"Bull Earwig" wrote:
I have written a function in Excel's Visual Basic editor. It accepts two
arrays, one of Integer's and one of Double's.
The function seems to compile just fine and other functions are working
well
for me, so I semi know what I am doing.
In the spreadsheet, I would like to select a range of cells and pass them
as
the integer array. I would like to do the same for another contiguous
range
of cells to serve as the Double array.
I tried simply doing just that but got errors in the spreadsheet.
How do I do this? Is there some function in the spreadsheet that
converts a
range of cells into an array of numerical values?
Or do I need to adapt the function to work in the spreadsheet's way of
doing
things and make it receive cell references from which it extracts the
numerical values?
TIA,
BE
|