Scalar Multiplying VBA Array

[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

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

"Jacob JKW" <jaco...@yahoo.com> wrote in message
news:7c79aa65-4c7c-4857...@n20g2000hsh.googlegroups.com...

[Jacob JKW]

On Jan 3, 4:17 am, "Bob Phillips" <bob....@somewhere.com> 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.

[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

[Jacob JKW]

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

[Alan Beban]

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("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

[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

[Jacob JKW]

On Jan 4, 12:44 pm, Alan Beban <unavaila...@no.com> 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.

[Alan Beban]

You're welcome; thanks for the feedback.

Alan