Scalar Multiplying VBA Array

  • Thread starter Thread starter Jacob JKW
  • Start date Start date
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
 
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)
 
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
Click to expand...
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("myRawProbs /19"))
End Function

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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...
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 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.
Click to expand...
 
Back
Top