Using arrays inside functions in VBA

Question

I can't seem to find an answer to this question: can I create a user-defined function in Excel VBA that uses an array from some sub-procedure, and then in Excel use that function to return a value?

From this site (http://www.cpearson.com/excel/passingandreturningarrays.htm) I found this example:

Sub AAATest()
    Dim StaticArray(1 To 3) As Long
    Dim Result As Long
    StaticArray(1) = 10
    StaticArray(2) = 20
    StaticArray(3) = 30
    Result = SumArray(Arr:=StaticArray)
    Debug.Print Result
End Sub

Function SumArray(Arr() As Long) As Long
    '''''''''''''''''''''''''''''''''''''''''''
    ' SumArray
    ' This sums the elements of Arr and returns
    ' the total.
    '''''''''''''''''''''''''''''''''''''''''''
    Dim N As Long
    Dim Total As Long
    For N = LBound(Arr) To UBound(Arr)
        Total = Total + Arr(N)
    Next N
    SumArray = Total
End Function

How should I use this function (SumArray) in an Excel cell?

What are the arguments required?

I think I've tried every possible combination of things, but can't seem to get it working. I want the cell with this UDF to return a value (which is Total).

Thanks in advance!

---

Added more info

Thanks for the answers! However I'll try to rephrase my question from a different angle. What I ultimately want to achieve is a user-defined function which I can use in any Excel cell, for example "=MYOWNFUNCTION(N,M)" which takes two parameters as input: value N and value M. Using these values the function MYOWNFUNCTION finds the closest "combination" of values N and M from a 3D array specified and generated in a separate Sub() and returns the corresponding value from the 3rd row of third axis of the 3D array. The first and the second row of the 3D array are N and M respectively.

So to make my task as simple as possible, I want this to happen?

1. Generate a 3D array with dimensions of 100*100*3 in some Subprocedure and fill it with values using functions not relevant to the problem here.
2. Generate a function (MYOWNFUNCTION) which uses the data in this previously generated 3D array and finds the closest combination or pair of values N and M, which are user-given inputs.
3. I can insert =MYOWNFUNCTION(N,M) with N and M of my choice in an Excel cell and get the corresponding result. By correspondence I mean that for example 3Darray(14,14,1) represents the value of N with parameters (14,14) and 3Darray(14,14,2) represents the value of M with parameters (14,14) and the value of (14,14,3) is the one I want to be returned. The function MYOWNFUNCTION goes through the 3Darray values and finds the nearest match of user-given input values.

The reason why I don't include the creation of the 3D array in the same function is that it's quite large and takes a few seconds to generate. I just want to use the data of the generated 3Darray so I only have to calculate it once.

I hope this makes sense.

Answer 1

You can, but you need to specify what kind of array :

If you are talking about a VBA array, like Arr() As Long in your example, you already have the syntax with your example but you won't be able to input a VBA array directly into your function in an Excel's cell.

If you are talking about an array in Excel (a range with multiple cells), just change the function to input a Range and then convert it to an array, take a look at this :

Function SumArray(RgArr As Range) As Long
    Dim N As Long, _
        Arr() As Long, _
        Total As Long
    '''''''''''''''''''''''''''''''''''''''
    ' Convert Range to an Array
    '''''''''''''''''''''''''''''''''''''''
    Arr = RgArr.Value
    'or
    'Arr = RgArr.Value2

    For N = LBound(Arr) To UBound(Arr)
        Total = Total + Arr(N)
    Next N
    SumArray = Total
End Function

---

The main difference between .Value and .Value2, is :

1. .Value2 gives you the underlying value of the cell (unformatted data)
2. .Value gives you the formatted value of the cell

For more details, take a look at Charles William's blog here.

Answer 2

Excel doesn't know how to translate a range into an array of values without explicit instructions. You can pass a Range as a parameter and then loop through the cells in the range.

1. Insert a new module in your VBA project

2. Paste in this code:

   Public Function SumArray(Target As Range) As Long
       '''''''''''''''''''''''''''''''''''''''''''
       ' SumArray
       ' This sums the values of the cells in Target and returns
       ' the total.
       '''''''''''''''''''''''''''''''''''''''''''
       Dim cell As Range
       Dim Total As Long
       For Each cell In Target.Cells
           Total = Total + cell.Value ' You should have some code that checks that cell.Value is a number
       Next cell
       SumArray = Total
   End Function
   

3. Use the function as shown in the picture.

Answer 3

Like an USA president said: Yes you can!

• Create a module and put your SumArray in the module (it don't work if the Function is not in a module)
• You can't pass an array as parameter in it because Excel won't understand

You have some choices:

if it's always the same subprocedure: retrieve directly your array in the SumArray function

if datas are from an excel sheet: pass a range in your function Function SumArray(byval rng as Range) As Long and use this range as array

Other cases: precise how you decide which array you have to use, and we will look how to do it