[Solved] How to use arrays in VBA functions? [closed]

by


Jeeped’s comment is a very probable reason of your code malfunctioning, unless you have Option Base 1 at the top of your code pane. so either you have/place that statement or you change the ReDim statements to ReDim B(1 To nrows, 1 To ncols) As Double

moreover you must invert Next I and Next j

finally I’d suggest you to:

  • use Long variable type instead of integer for nrows and i variables, should you ever deal with a datarange with more than 32767 rows. and assume the same type for ncols and j for simplicity

  • use a “simple” (not array) double variable for A since you only need as a temporary “x” value for calculating the corresponding “y” value and store this latter only in B() array, to be eventually returned by your function.

so it could be:

Function functionvalue(datarange)

Dim nrows As Long, ncols As Long
Dim i As Long, j As Long

Dim A As Double, B() As Double

nrows = datarange.Rows.Count
ncols = datarange.Columns.Count

ReDim B(1 To nrows, 1 To ncols) As Double

For i = 1 To nrows
    For j = 1 To ncols
        A = datarange.Cells(i, j)
        B(i, j) = A ^ 3 + A * 1 / 5 + 2
    Next j
Next i

functionvalue = B

End Function

solved How to use arrays in VBA functions? [closed]