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

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

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.

Public Function MyReturnArrayFunction() As Variant()
myRawProbs = Array(19, 38, 57)
ActiveSheet.Names.Add Name:="myRawProbs", RefersTo:=myRawProbs
MyReturnArrayFunction = _
Application.Transpose(ActiveSheet.Evaluate("myRawP robs /19"))
End Function

Alan Beban

Jacob JKW wrote:
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.

On Jan 4, 12:31 am, Alan Beban wrote:
Public Function MyReturnArrayFunction() As Variant()
myRawProbs = Array(19, 38, 57)
ActiveSheet.Names.Add Name:="myRawProbs", RefersTo:=myRawProbs
MyReturnArrayFunction = _
Application.Transpose(ActiveSheet.Evaluate("myRawP robs /19"))
End Function

Alan Beban

Jacob JKW wrote:
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.

Thanks for the reply, Alan. Calling this function from Excel:
Public Function MyReturnArrayFunction() As Variant()
On Local Error GoTo ErrHandler
Dim myRawProbs() As Variant
myRawProbs = Array(19, 38, 57)
ActiveSheet.Names.Add Name:="myRawProbs", RefersTo:=myRawProbs
MyReturnArrayFunction = _
Application.Transpose(ActiveSheet.Evaluate("myRawP robs /19"))
Exit Function
ErrHandler:
Debug.Print Err.Description & " (" & Err.Number & ")"
End Function

I get "Application-defined or object-defined error (1004)" as a debug
message. The error is caused by the statement:
ActiveSheet.Names.Add Name:="myRawProbs", RefersTo:=myRawProbs

Thanks,
Jacob

Jacob JKW wrote:
Thanks for the reply, Alan. Calling this function from Excel:
Public Function MyReturnArrayFunction() As Variant()
On Local Error GoTo ErrHandler
Dim myRawProbs() As Variant
myRawProbs = Array(19, 38, 57)
ActiveSheet.Names.Add Name:="myRawProbs", RefersTo:=myRawProbs
MyReturnArrayFunction = _
Application.Transpose(ActiveSheet.Evaluate("myRawP robs /19"))
Exit Function
ErrHandler:
Debug.Print Err.Description & " (" & Err.Number & ")"
End Function

I get "Application-defined or object-defined error (1004)" as a debug
message. The error is caused by the statement:
ActiveSheet.Names.Add Name:="myRawProbs", RefersTo:=myRawProbs

Thanks,
Jacob

Yes; called from a worksheet, a Function can't change the Excel
environment, i.e., can't add a worksheet name. I didn't appreciate that
that's what was happening.

I'm not sure I understand the flow, but

Public Function MyReturnArrayFunction() As Variant()
On Local Error GoTo ErrHandler
Dim myRawProbs() As Variant
myRawProbs = Array(19, 38, 57)
MyReturnArrayFunction = _
Application.Transpose(myRawProbs)
Exit Function
ErrHandler:
Debug.Print Err.Description & " (" & Err.Number & ")"
End Function

Can be called from the worksheet with

=MyReturnArrayFunction()/19, array entered, to return the desired result.

Alan Beban

Or, if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook,

Public Function MyReturnArrayFunction(iScalar As Single) As Variant()
On Local Error GoTo ErrHandler
Dim myRawProbs() As Variant
myRawProbs = Array(19, 38, 57)
MyReturnArrayFunction = _
ScalarMult(Application.Transpose(myRawProbs), iScalar, False)
Exit Function
ErrHandler:
Debug.Print Err.Description & " (" & Err.Number & ")"
End Function

called as

=MyReturnArrayFunction(19)

will do so.

Alan Beban

On Jan 4, 12:44 pm, Alan Beban wrote:
Or, if the functions in the freely downloadable file at http://home.pacbell.net/bebanare available to your workbook,

Public Function MyReturnArrayFunction(iScalar As Single) As Variant()
On Local Error GoTo ErrHandler
Dim myRawProbs() As Variant
myRawProbs = Array(19, 38, 57)
MyReturnArrayFunction = _
ScalarMult(Application.Transpose(myRawProbs), iScalar, False)
Exit Function
ErrHandler:
Debug.Print Err.Description & " (" & Err.Number & ")"
End Function

called as

=MyReturnArrayFunction(19)

will do so.

Alan Beban
I haven't tried your suggestion yet, but there does look to be some
pretty cool functions in your spreadsheet Thanks much for the link,
Alan.

You're welcome; thanks for the feedback.

Alan

Jacob JKW wrote:
On Jan 4, 12:44 pm, Alan Beban wrote:

Or, if the functions in the freely downloadable file at http://home.pacbell.net/bebanare available to your workbook,

Public Function MyReturnArrayFunction(iScalar As Single) As Variant()
On Local Error GoTo ErrHandler
Dim myRawProbs() As Variant
myRawProbs = Array(19, 38, 57)
MyReturnArrayFunction = _
ScalarMult(Application.Transpose(myRawProbs), iScalar, False)
Exit Function
ErrHandler:
Debug.Print Err.Description & " (" & Err.Number & ")"
End Function

called as

=MyReturnArrayFunction(19)

will do so.

Alan Beban

I haven't tried your suggestion yet, but there does look to be some
pretty cool functions in your spreadsheet Thanks much for the link,
Alan.