CODEWITHSUNDEEP
excelvbaformula
user3338172
user3338172

Reputation: 3

How to return calculated array in array formula

I'm creating a user defined function and input values from a range of cells (like =sk_test(A1:a5)), do calculations with those values, return an array with values in cells. To use it, I highlight the cells that will hold return values, enter function call (like =sk_test(A1:a5)) and CTRL+SHIFT+RETURN. The following code is giving me errors. Thanks.

Regards, Steve

Function sk_test(data1 As Range) As Variant 
    Dim c As Range 

    Dim aa() As Double 
    Dim i, npt As Integer 
    Dim data2 As Variant 
    data2 = data1 

    npt = UBound(data2) 

    Redim aa(1 To npt) 

    i = 1 
    For Each c In data1 
        aa(i) = c.Value * 10 + 99 ' example of calcuations
        i = i + 1 
    Next c 

    i = 1 
    For Each c In data2 
        c.Value = aa(i) 
        i = i + 1 
    Next c 
    sk_test = data2 
End Function

Upvotes: 0

Views: 84

Answers (2)

John Alexiou
John Alexiou

Reputation: 29254

This is how I have always created a function than returned an array

Public Function Sequence(ByVal N As Long, ByVal M As Long) As Variant()
    Dim res() As Variant

    Dim i As Long, j As Long

    ' Set return shape
    ReDim res(1 To N, 1 To M)
    ' Sample calculation
    For i = 1 To N
        For j = 1 To M
            res(i, j) = (i - 1) * M + j
        Next j
    Next i
    ' Return array
    Sequence = res
End Function

Result

Upvotes: 0

CRondao
CRondao

Reputation: 1903

Just replace 

i = 1 
For Each c In data2 
    c.Value = aa(i) 
    i = i + 1 
Next c 
sk_test = data2

By

sk_test = Application.Transpose(aa)

Upvotes: 1

Related Questions