How to get vba function array to return in excel

Question

I'm working on this project and I need to create a function in VBA in order to store a formula and return the result in excel using the index function. My code is as below and i created just a simple dummy function for testing purposes. In excel I believe the function should be =INDEX(NewArray,,1)? Any help on this would be greatly appreciated Thanks

Public Function NewArray(a As Integer, b As Integer) As Long()
Dim arr() As Long
ReDim arr(1 To 10, 1 To 5) As Long
Dim row As Integer
Dim col As Integer

For row = 1 To 5
arr(row, 1) = (row + 1)
 Next row

NewArray = arr(a, b)
End Function

Why are you returning a Long()? Should be a Long, if you're returning a single element from the array and not the array itself. — BigBen
– BigBen, Commented Feb 1, 2022 at 19:29
NewArray = arr and =INDEX(NewArray(1,1),5,1) for example. It's not clear why your function has unused parameters, but you must supply them... — Tim Williams
– Tim Williams, Commented Feb 1, 2022 at 19:32

VBasic2008 · Accepted Answer · 2022-02-01 20:19:04Z

Using a 'UDF Array' with INDEX

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Creates a 2D one-based array with 'a' rows and 'b' columns.
'               populated by the product of rows and columns.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function NewArray(ByVal a As Long, ByVal b As Long) As Long()
    
    Dim arr() As Long: ReDim arr(1 To a, 1 To b)
    
    Dim r As Long
    Dim c As Long
    
    For r = 1 To a ' loop (iterate) through rows
        For c = 1 To b ' loop (iterate) through columns
            arr(r, c) = r * c ' e.g. product of the current row and column
        Next c
    Next r
    
    NewArray = arr ' don't forget to return the result

End Function

5 rows, 7 columns

In VBA, return the elements of the array in the Immediate window (Ctrl+G).

Sub NewArrayTEST()

    Dim arr() As Long: arr = NewArray(5, 7)
    
    Dim r As Long
    Dim c As Long
    
    For r = 1 To UBound(arr, 1)
        For c = 1 To UBound(arr, 2)
            Debug.Print r, c, arr(r, c)
        Next c
    Next r

End Sub

In Excel, you can return the element at the intersection of the 2nd row and the 3rd column using the INDEX function:
```
=INDEX(NewArray(5,7),2,3)
```
The result is 6 since we added the product 2*3.

Collectives™ on Stack Overflow

How to get vba function array to return in excel

1 Answer 1

Using a 'UDF Array' with INDEX

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Using a 'UDF Array' with INDEX

Comments

Your Answer

Sign up or log in

Post as a guest

Related