VBA - Convert range to variant

809 views Asked by At

I am stuck in the conversion from range to variant.

For the function of percentageAbove, I want to remove elements that is 0 and then use the inputs in percentageAboveHelper. For example, if

xInput is 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

yInput is 5, 0, 0, 2, 3, 4, 0, 4, 5, 0

I want the inputs passed to percentageAboveHelper would be

xInput: 1, 4, 5, 6, 8, 9

yInput: 5, 2, 3, 4, 4, 5

For the function of percentageAboveHelper, it works fine itself. But, if I passed the variants from percentageAbove, I get #value!

I tried to check which line cause #value!. So, I write msgbox "1" and msgbox "2" in percentageAboveHelper. I see that if I only use percentageAboveHelper itself, I can see message 1 and 2. But, if I use percentageAbove, I only can see message 1.

From this post, Switching from Range to Array and Back in a VBA Function I see the conversion can be simply done by variant = range.value. But it doesn't work in my case. Any suggesions?

Function percentageAbove(above As Double, x As Double, xInput As Excel.Range, yInput As Excel.Range)
    Dim xRange As Excel.Range, yRange As Excel.Range
    For index = 1 To xInput.count
        If Not yInput.Item(index) = 0 Then
            If Not yRange Is Nothing Then
                Set xRange = Union(xRange, xInput.Item(index))
                Set yRange = Union(yRange, yInput.Item(index))
            Else
                Set xRange = xInput.Item(index)
                Set yRange = yInput.Item(index)
            End If
        End If
    Next index
    ' I do check the xRange and yRange. Both contain elements
    Dim xVariant As Variant, yVariant As Variant
    xVariant = xRange.Value
    yVariant = yRange.Value
    percentageAbove = percentageAboveHelper(above, x, xVariant, yVariant)
End Function

Function percentageAboveHelper(above As Double, x As Double, xVariant As Variant, yVariant As Variant)
    Dim n As Integer, df As Integer, meanOfX As Double, expectedY As Double, sste As Double, ssx As Double, se As Double
    n = Application.count(xVariant)
    df = n - 2
    meanOfX = Application.Average(xVariant)
    MsgBox "1"
    expectedY = Application.Forecast(x, yVariant, xVariant)
    MsgBox "2"
    sste = Application.StEyx(yVariant, xVariant)
    ssx = Application.DevSq(xVariant)
    se = sste * Sqr(1 / n + (x - meanOfX) ^ 2 / ssx)
    Dim tValue As Double, oneTailConf As Double
    tValue = (expectedY - above) / se
    oneTailConf = Application.TDist(Abs(tValue), df, 1)
    If tValue > 0 Then
        percentageAboveHelper = 1 - oneTailConf
    Else
        percentageAboveHelper = oneTailConf
    End If
End Function
0

There are 0 answers