CODEWITHSUNDEEP
exceluser-defined-functionsvba
user2255731
user2255731

Reputation: 21

How do I call a UDF that returns an array within another UDF?

I am having some trouble figuring out how to return an array within a UDF from another UDF. The one here is a simple exponential moving average UDF and I am trying to return the array into another UDF but I am getting #value error. I feel there is a simple solution that I am not seeing. All help is greatly appreciated, thanks.

Function ema(arg1 As Variant, ByVal lngth As Long) As Variant
    x = arg1
    dim avg As Double

    avg = 1

    Dim arrema As Variant
    arrema = Array()
    ReDim arrema(1 To UBound(x, 1), 1 To 1)

    For j = 1 To (UBound(x, 1) - lngth)
        For i = (1 + j - 1) To (lngth + j - 1)
            avg = (WorksheetFunction.Index(x, i, 1) + 1) * avg
        Next i
        arrema(j, 1) = avg ^ (1 / lngth)
        avg = 1
    Next j
    'ema = avg ^ (1 / lngth)
    ema = arrema
End Function

Function test(arg2 As Variant, xlength As Long)
    Dim arra As Variant
    'Call ema(arg2, xlength)
    Dim arr As Variant
    arr = Array()
    ReDim arr(1 To UBound(arg2, 1), 1 To 1)

    arra = ema(arg2, xlength)

    For i = 1 To UBound(arg2, 1) - xlength
        arr(i, 1) = arra(i, 1)
    Next i

    test = arr
End Function

Upvotes: 2

Views: 300

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

If you are calling test from a formula with a range as the arg1 parameter, then your problem is you are treating a Range as if it were an Array by calling UBound(arg2,1)

Change that to UBound(arg2.Value,1) and it will work.

Further explanation:

By declaring the arg# parameters as Variant allows the UDFs to be called with either Range's or Array's. It may be better to be specific by using either As Range or As Variant().

In Function ema this issue is avoided by the line x = arg1: If arg1 is a Range then this copies the default property of the Range which is the Value property to x, making x an array. If arg1 is an Array then it just copies that array into x.

Net result is Function ema can handle either Ranges or Arrays. There is another issue there though: WorksheetFunction.Index(x, i, 1) will fail with one dimensional Arrays. Change it to WorksheetFunction.Index(x, i) or better still Application.Index(x, i) to avoid this issue too.

Upvotes: 1

Related Questions