0

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
YowE3K
  • 23,852
  • 7
  • 26
  • 40
Pak Ho Cheung
  • 1,382
  • 6
  • 22
  • 52
  • Where (at what line of code) does the error occur? What error is it (exactly)? – Daniel Dušek Sep 02 '17 at 09:44
  • (a) "But it doesn't work in my case" is not very informative. (b) Your `xRange` and `yRange` are potentially not contiguous ranges. (I assume that is why you are using `Union`?) If so, that will mean that `xVariant` and `yVariant` are only being set to the values of the first `Area` in each of those non-contiguous ranges. – YowE3K Sep 02 '17 at 09:46
  • Hi @dee I mean #value! not error. Please see my edit. I added some information. Thanks. – Pak Ho Cheung Sep 02 '17 at 10:53
  • HI @YowE3K Yes, you are right. I added some information. Please see my edit. Thanks. – Pak Ho Cheung Sep 02 '17 at 10:55
  • Try to use [this function](https://stackoverflow.com/questions/18993915/get-values-from-union-of-non-contiguous-ranges-into-array-with-vba-with-a-simple) which works with areas and gets values of all of the areas a range has. – Daniel Dušek Sep 02 '17 at 13:36
  • Note: I just added a [excel-udf] tag, because the reason for the `#VALUE` is simply that a UDF returns that as soon as it crashes. (Due to the 5-tag limit, I removed [type-conversion] as it was the least applicable tag, as you aren't actually converting types, just assigning `Variant` values from a `Range` to a `Variant` array.) – YowE3K Sep 02 '17 at 18:27
  • When debugging UDFs, it is usually best to go to the Immediate Window of the VBE and run them from there - e.g. perhaps use `?percentageAbove(2, 4, Range("A1:J1"), Range("A2:J2"))` (not sure what values you would normally be passing, so I just made some up). When not run as a UDF, functions will actually crash, rather than just jumping out without really telling you why. – YowE3K Sep 02 '17 at 18:31
  • Thanks guys. I solved the question by creating one more xArray and yArray, so that I can put the arrays into Application's functions – Pak Ho Cheung Sep 03 '17 at 13:50

0 Answers0