Converting input range to array in function

Question

I have been learning VBA and thought I was getting the hang of it, but for some reason this basic task is eluding me. I am trying to create a function where I select an input region of numbers (really only one row or column), and then output the summation of the numbers in a cell. Here is my code:

Function CashFlow(CashArray As Excel.Range)

Dim cashflows() As Variant
cashflows = CashArray.Value

amount = CashArray.Rows.Count

dim y()
redim y(amount)
Sum = 0

For i = 1 To amount
    y(i) = cashflows(i)
    Sum = Sum + y(i)
Next i

CashFlow = Sum

End Function

Despite me knowing how to do essentially this in a subroutine, the fact that I'm getting my data from the function's input is throwing me off. How do I accomplish this task?

What that means is: y(i) = cashflows(i,1) for vertical and y(i) = cashflows(1,i) for horizontal. — Scott Craner
– Scott Craner, Commented Nov 14, 2017 at 1:54

halfer · Accepted Answer · 2020-04-24 13:41:14Z

1

Just to summarize the above comments, please give the following code a try:

Option Explicit

Function CashFlow(CashArray As Excel.Range)

Dim sum As Double
Dim x As Long, y As Long
Dim cashflows() As Variant

cashflows = CashArray.Value
sum = 0

For y = LBound(cashflows, 1) To UBound(cashflows, 1)
    For x = LBound(cashflows, 2) To UBound(cashflows, 2)
        sum = sum + cashflows(y, x)
    Next x
Next y

CashFlow = sum

End Function

Note, that this code summarizes all cells in the given range (even if there are multiple rows and columns in that range). Basically, the range is stored in the array cashflows. As mentioned in the comments, this will yield a two-dimensional array. The function LBound(cashflows) will give you the Lower Boundary of that array.

By specifying LBound(cashflows, 1) I am specifically asking for the lower boundary of the first dimension (in this case the rows). UBound returns the Upper Boundary of that array for the specified dimension. Similarily UBound(cashflows, 2) returns the last column (second dimension) of the given array.

_{Remark: I am always using LBound() and UBound() in my code instead of starting with 0 or 1 to avoid coding errors. Some people prefer Option Base 0 and some prefer Option Base 1. While neither will have an impact on the above (as ranges always yield an array starting with 1) it is just good coding practice to prevent bugs in the future.}

edited Apr 24, 2020 at 13:41

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Nov 14, 2017 at 10:31

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Coolio2654 Over a year ago

So, what you (and the comments as well) taught me is that ranges can be created automatically and intuitively, but they have multiple dimensions that must be specified (even if it's just 1 column or 1 row for one of the dims.). Thank you for this insight, and the additional tips on UBound and LBound! P.S. I found that declaring your inputs "as excel.range" is not necessary in the first place, fyi.

Collectives™ on Stack Overflow

Converting input range to array in function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related