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?

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.
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.
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.

Your edit was a major change to your original question. I suggest you ask it as a new question so that it gets seen. Your first question already has 3 answers so it is unlikely that many people will have seen your edit. — ChipsLetten
– ChipsLetten, Commented Aug 27, 2015 at 12:25

Maxime Porté · Accepted Answer · 2015-08-03 09:10:12Z

1

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

answered Aug 3, 2015 at 9:10

Maxime Porté

1,0948 silver badges13 bronze badges

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

1 Comment

Hammaskivilammas Over a year ago

My choice is "Other cases". Please see the added info on the opening post.

ChipsLetten · Accepted Answer · 2015-08-03 09:22:51Z

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.

Insert a new module in your VBA project

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

Use the function as shown in the picture.

R3uK · Accepted Answer · 2015-08-03 09:24:20Z

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 :

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

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

Collectives™ on Stack Overflow

Using arrays inside functions in VBA

Added more info

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Added more info

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related