On Jan 3, 4:17 am, "Bob Phillips" wrote:
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?

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

The problem here is that it requires expressing joining my array into
a string. That's not fast operation. The array itself is a calculated
variable -- I just expressed it as a constant to simplify the example
code.

Thanks,
Jacob.