CODEWITHSUNDEEP
arraysexcelvbauser-defined-functions
nicolas
nicolas

Reputation: 31

Excel VBA UDF - Fail to pass a variant array resulting from UDFas an input in second UDF

I am struggling with using the array resulting from an UDF I have written as the argument to another UDF.

The function returns the #value error.

I can not figure where the problem lies.

Below is the code.

Any help / advice would be greatly appreciated ;)

This one works..

Function fTA_GetSMA(ByRef varData As Variant, ByRef lPeriod As Long) As Variant

' This function computes a simple moving average over a defined period.

Dim l As Long
Dim dSum As Double
Dim var() As Variant

    Application.Volatile
    varData = varData.Value2
    ReDim var(LBound(varData, 1) To UBound(varData, 1), 1)
    For l = LBound(varData, 1) To UBound(varData, 1)
        If l < lPeriod Then
            dSum = dSum + varData(l, 1)
        ElseIf l = lPeriod Then
            dSum = dSum + varData(l, 1)
            var(l, 1) = dSum / lPeriod
        ElseIf l > lPeriod Then
            dSum = dSum + varData(l, 1) - varData(l - lPeriod, 1)
            var(l, 1) = dSum / lPeriod
        End If
    Next l
    fTA_GetSMA = var

End Function

This one works also.

Function fTA_GetTR(ByRef varData As Variant) As Variant ' This function computes the true range of a financial time series. ' The input data must be a matrix containing O, H, L, C.

Dim var() As Variant
Dim l As Long
Dim dMaxTR As Double
Dim dMinTR As Double

    Application.Volatile
    varData = varData.Value2
    ReDim var(LBound(varData, 1) To UBound(varData, 1), 1)
    For l = LBound(varData, 1) To UBound(varData, 1)
        If l = 1 Then
            dMinTR = varData(l, 3)
            dMaxTR = varData(l, 2)
        ElseIf l > 1 Then
            dMaxTR = Application.WorksheetFunction.Max(varData(l, 2), varData(l - 1, 4))
            dMinTR = Application.WorksheetFunction.Min(varData(l, 3), varData(l - 1, 4))
        End If
        var(l, 1) = dMaxTR - dMinTR
    Next l

    fTA_GetTR = var

End Function

THIS IS THE ONE which is not working..

Function fTA_GetATR(ByRef varData As Variant, ByRef lPeriod As Long) As Variant ' This function computes the average true range of a financial time series over a given number of periods. ' the input data must be a matrix containing O, H, L, C. ' the average formula is a SMA

Dim var() As Variant
Dim varATR() As Variant

    ReDim var(LBound(varData, 1) To UBound(varData, 1), 1)
    ReDim varATR(LBound(varData, 1) To UBound(varData, 1), 1)
    var = fTA_GetTR(varData)
    varATR = fTA_GetSMA(var, lPeriod)
    Debug.Print varATR
    fTA_GetATR = varATR

End Function

Upvotes: 1

Views: 1082

Answers (1)

Charles Williams
Charles Williams

Reputation: 23520

You can only use VarData.Value2 if if Var contains a range reference: it won't work if VarData is a variant array.
Your UDFs expect Range arguments as input (because you use .Value2) but return a variant containing an array. So when you call one of them using the output from a different UDF it fails because the input is not a range.
You can detect this by setting a breakpoint and showing the Locals window to inspect what the Var variable contains.
The way to handle this is to do something like this

if IsObject(VarData) then VarData=VarData.Value2

Upvotes: 1

Related Questions