Scalar Multiplying VBA Array

J

Jacob JKW

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
 
B

Bob Phillips

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)
 
J

Jacob JKW

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.
 
A

Alan Beban

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

Alan Beban
 
J

Jacob JKW

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

Alan Beban

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("myRawProbs /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
 
A

Alan Beban

Jacob said:
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("myRawProbs /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
 
A

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
 
J

Jacob JKW

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.
 
A

Alan Beban

You're welcome; thanks for the feedback.

Alan

Jacob said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top