How to use an If/then condition on a Variant/double array value?

I am trying to look at each value of the array generated by my function, and place it in a cell if the value is <> 0, but I always get a type mismatch error at that stage. The code will place a value inside the cells as it is written, but I want to scrub the 0 values from the set and replace with "-".

Vals0(1 to 30)

Do While (Now - 1) > fdate0
    looop0 = 1
    Start = DateAdd("n", 450, PT.Range("P2").Value)
    Endd = DateAdd("d", 1, Start)
    E0.Cells((counter0), 1).Value = fdate0

    For looop0 = 1 To 30

        vals0(looop0) = Application.Run("PIAdvCalcVal", E0Tags(looop0), Start, Endd, "average (time-weighted)", "time-weighted", "0", "1", "0")

       ' If Vals0(looop0) <> 0 Then
            E0.Cells(counter0, (looop0 + 1)).Value = vals0(looop0)
        'Else
         '   E0.Cells(counter0, (looop0 + 1)).Value = "-"
        'End If

    Next looop0


    counter0 = counter0 + 1
    PT.Range("P2").Value = (PT.Range("P2").Value + 1)
    fdate0 = PT.Range("P2").Value

Loop

Here is the locals window for a few iterations of the for loop

Solution

You wrote "It Says Variant/Double, then when I go into the array in the local window, the double value is listed as vals0(1)(1) =18.2738746824382". So, obviously vals0(1) is not a double, it is an array of double.

Unfortunately you don't tell us anything about PIAdvCalcVal. vals0 is declared as (one-dimensional) array with 30(?) element of type Variant. That means you have 30 variants. Every variant can hold anything, a double, a string, an object, or also an Array of anything. The latter seems to be the case, PIAdvCalcVal returned an array of double values (even if the array holds maybe only one value).

So first things first: Put a statement Debug.Print looop0, VarType(vals0(looop0)) after your Run-statement. Have a look to https://learn.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/vartype-function to get an idea about the numbers representing a vartype. If the vartype is something > 8192, you have an array of something, and that would explain the Type mismatch error - you cannot compare an array with a number.

If PIAdvCalcVal always returns an array, the solution could be to simply write If Vals0(looop0)(1) <> 0 (note that Vals0(looop0, 1) will not work as Vals0 is not a 2-dimensional array).

A more robust solution could be

Var x
If IsArray(Vals0(looop0)) Then
    x = Vals0(looop0)(1)
Else
    x = Vals0(looop0)
End If
If x <> 0 Then
...

Finally, you have to check how large the returned array is (check LBound and UBound). What would you do if it contains more than one value?