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