Pass array from worksheet 1

[JTBorton]

Excel 2007

Is it possible to pass an array to a function parameter from the worksheet? I tried using braces but it's not working.

"=AddMyArray( {1,3,4,5,6} )" gives me an error

Option Explicit
Function AddMyArray(Numbers() As Single) As Single
    Dim J As Integer
    Dim K As Integer
    Dim L As Integer
    On Error Resume Next
    J = UBound(Numbers)
    L = LBound(Numbers)
    On Error GoTo 0
    For K = L To J
        AddMyArray = AddMyArray + Numbers(K)
    Next K
End Function

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[PHV]

Why not passing a Range instead ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

You can use one of the following, with Variant arguments:

Function AddX(ParamArray X())
Dim v
On Error Resume Next
For Each v In X
    AddX = AddX + v
Next v
End Function

Function AddY(X)
Dim v
On Error Resume Next
For Each v In X
    AddY = AddY + v
Next v
End Function

In the worksheet:
[tt]=AddX(1,3,4,5,6)[/tt]
or
[tt]=AddY({1,3,4,5,6})[/tt]


combo

 

[JTBorton]

Thanks Combo, that's exactly what I was looking for.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[electricpete]

Why not use:
=SUM( {1,3,4,5,6} )

 

[JTBorton]

electricpete
If getting the sum of numbers was my real intention, that would be the way to go. But in reality I was only using this function to learn how to pass parameter arrays. Either way, thanks for the suggestion.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[electricpete]

That makes sense. Then Combo gave you a good solution.

Another question that might be asked is: what's wrong with your original code. If you take the type declaration off of numbers, vba will treat it as a variant data type and in this case everything works out fine:

Option Explicit

Function AddMyArray([b]Numbers[/b]) As Single
    Dim J As Integer
    Dim K As Integer
    Dim L As Integer
    On Error Resume Next
    J = UBound(Numbers)
    L = LBound(Numbers)
    On Error GoTo 0
    For K = L To J
        AddMyArray = AddMyArray + Numbers(K)
    Next K
End Function

It may not be the fastest or most elegant and won't pass muster for a professional product that needs to be peer-reviewed, but it gets the job done and easier to figure out how to write it imo.

 

[JTBorton]

electricpete
I've started to notice that it is better to use a regular variant variable to recieve items from the spreadsheet. So let me ask you this: What then is the real advantage of using the ParamArray statement? It doesn't seem to offer any advantage in recieving info from the spreadsheet, and if I am going to use it in a child function then I would probably use a specified array data type anyway.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[electricpete]

The main advantage of declaring your variables a something other than variant is in error checking..... If the wrong datatype gets passed to a declared variable (other than variant type), you get an error. So it is good form. (I'm sure others here could put it more eloquently).

Also there is more computational overhead to work with a variant variable than other types because (to my simple way of thinking) vba has to think more about how to treat the variable.

 

[JTBorton]

hmmmm... I understand what your saying, and agree. But doesn't the ParamArray statement require a variant data type?

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[MajP]

Maybe this would help. I would only use a Paramarray if I am passing in individual arguments not an array

Public Sub passToFunction()
  Dim myArray() As String
  myArray = Split("A,B,C,D,E", ",")
  'Test passing an array
  MsgBox getItem(myArray, 3)
  'Test passing individual arguments into a paramarray
  MsgBox getItemParam(3, "A", "B", "C", "D")
End Sub

Public Function getItem(myArray() As String, myIndex As Integer) As String
  getItem = myArray(myIndex)
End Function


Public Function getItemParam(myIndex As Integer, ParamArray myValues()) As String
  getItemParam = myValues(myIndex)
End Function