How do I pass a column to a function in Excel to be used as an array?-CodePudding

I need to pass a column to a function in Excel to perform some calculations over its values. Basically I would like to convert a column of values inside my Excel file into an array to be used inside a VBA function.

A simple example would be writing a function to sum the values of some cells like shown in the image. The function I was thinking of looks like this:

Function sum_items(A)
    Total = 0
    
    For i = 0 To UBound(A)
        Total = Total   A(i)
    Next
    
    sum_items = Total
End Function

Of course, once I recall the function in my Excel file it doesn't work. How can I pass a range of cells to my function?

Thank you for your help. Yuri

CodePudding user response：

You can pass a Range as a Variant argument in your function like this.

Sub test()
  Dim v As Boolean
  v = passRangeAsArray(Application.Transpose(Range("A1:A5")))

End Sub

Function passRangeAsArray(r As Variant) As Boolean
  
  Dim i As Long
  
  For i = LBound(r) To UBound(r)
    Debug.Print r(i)
  Next
  passRangeAsArray = True
End Function

Take note that for simplicity, I transposed the range before passing it to the funtion. The reason is when you pass or assign a Range to a Variant, it produces a 2D array and you will have to make some more test to iterate to the array correctly. HTH.