Scalar Multiplying VBA Array
Jacob,
This works
Public Function MyReturnArrayFunction() As Variant()
Dim myRawProbs As String
myRawProbs = "{7,9,3}"
With Application
MyReturnArrayFunction = .Transpose(ActiveSheet.Evaluate(myRawProbs &
" / 19"))
End With
End Function
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Jacob JKW" wrote in message
...
I have a VBA function which returns a Variant(). This function is
called as an array formula within Excel.
Within the function VBA assigns an array to the function return value.
Somewhere in there I'd like to be able scalar multiply the array
without having to manually loop through each element.
Here's sample code illustrating what I'd *like* to be able do:
Public Function MyReturnArrayFunction() as Variant()
myRawProbs = Array(7, 9, 3)
' do more stuff here
MyReturnArrayFunction = Application.Transpose(myRawProbs)/19 '
This does not work!
End Function
I do realize I could simply loop through thr array and divide through
by the constant, but ostensibly when the VBA array is assigned to the
Excel range it's already looping through each element anyway -- so why
should I have to do that twice?
Thanks in advance,
Jacob
|