How to return calculated array in array formula

Question

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

data2 is Variant (it's just array of values), while c is range. And you tries to iterates through cells in plain array. Btw, c.Value = aa(i) - you can't modify cells in UDF (in normal way) — Dmitry Pavliv
– Dmitry Pavliv, Commented Feb 21, 2014 at 17:00

CRondao · Accepted Answer · 2014-02-21 17:33:39Z

1

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)

answered Feb 21, 2014 at 17:33

CRondao

1,9012 gold badges12 silver badges10 bronze badges

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

Comments

John Alexiou · Accepted Answer · 2014-02-21 20:06:53Z

0

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

answered Feb 21, 2014 at 20:06

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

Collectives™ on Stack Overflow

How to return calculated array in array formula

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related