Setting SeriesCollection.Values using variant array

Question

I am creating a dashboard of charts in Excel. There is no need for the data to be visible to end users, so I am attempting to use arrays to set SeriesCollection.Values. Here is the offending code block:

'test sub to declare and pass header_arr and series_arr
Sub test()

Dim arr1(), arr2() As Variant
With ThisWorkbook.ActiveSheet
    arr1 = .Range(.Range("I2"), .Range("AI2")).Value
    arr2 = .Range(.Range("I25"), .Range("AI28")).Value
End With

Call CreateWaterfall(arr1, arr2, , , , Range("I30"))

Erase arr1, arr2

End Sub

'sub to create chart
Sub CreateWaterfall(header_arr As Variant, series_arr As Variant, Optional colors As Boolean = True, Optional target_wb = Nothing, Optional target_ws = Nothing, Optional target As Range = Nothing)

'whole bunch of parameter checks that work fine

'trim header_arr
Dim temp() As Variant
ReDim temp(LBound(header_arr, 1) To LBound(header_arr, 1), LBound(header_arr, 2) To UBound(header_arr, 2))
For i = LBound(header_arr, 2) To UBound(header_arr, 2)
    temp(LBound(header_arr, 1), i) = header_arr(1, i)
Next i
ReDim header_arr(LBound(temp, 1) To LBound(temp, 1), LBound(temp, 2) To UBound(temp, 2))
For i = LBound(temp, 2) To UBound(temp, 2)
    header_arr(LBound(temp, 1), i) = temp(LBound(temp, 1), i)
Next i

'declare chart object, start with block...
    '...stuff that works...
For i = 1 To 4
    With .SeriesCollection(i)
        If LBound(series_arr, 1) = 0 Then i = i - 1 'handle base 0 case-operation is reversed at end of loop to avoid incorrect steps
        ReDim temp(LBound(series_arr, 1) To LBound(series_arr, 1), LBound(series_arr, 2) To UBound(series_arr, 2))
        For j = LBound(series_arr, 2) To UBound(series_arr, 2)
            temp(LBound(series_arr, 1), j) = series_arr(i, j)
        Next j
        .Values = temp '<--problem with this line
        .XValues = header_arr
    End With
    If LBound(series_arr, 1) = 0 Then i = i + 1
Next i
'...other stuff that works...
'end with block

End Sub

This is within a With block that creates the chart and sets its properties. All of the rest of the sub is working properly, even the line here that sets .XValues, but none of the .Values are correct. When I check them on the spreadsheet itself, they are a series of 0s and #N/As. Checking revealed that this is not because of empty cells in the ranges the arrays are populated form. For the debugging phase I'm in, series_arr and header_arr are populated using a spreadsheet range; that will not be the case in the final draft. Here is a small example of the kind of data I want the sub to handle (I'm sorry; I don't know how to make it prettier than this...):

215.56 empty empty empty 432.73
empty 43.184 empty 56.442 empty
empty 136.65 186.8 345.67 empty
empty empty 87.653 empty empty

All the arrays contain the correct data as ascertained by stepping through the code.

I've looked here already and am stuck; as far as I can tell, the .Values line is exactly analogous. I've also used exactly analogous code for other charts with the exception that .Values is set using a spreadsheet range, so I know the problem isn't there.

Any ideas?

are you using a 2-dimensional array ? can you share your data ? to simulate the errors you are getting — Shai Rado
– Shai Rado, Commented Jan 8, 2017 at 6:48
@Shai Rado series_arr and header_arr are both two-dimensional, though there is a fail-safe built into the sub in case a user tries to pass an array with more than one row into header_arr. temp, however, is one-dimensional. I can't share my data, but it is a range of doubles and empty cells. @Robin Mackenzie It's a stacked column chart; .ChartType is set right after the snippet I posted. — Theaetetos
– Theaetetos, Commented Jan 9, 2017 at 21:59
Could you provide a data range with values something like you're using? How are you representing the empty cells? How is temp declared? — Jon Peltier
– Jon Peltier, Commented Jan 11, 2017 at 1:39

Theaetetos · Accepted Answer · 2017-01-12 15:15:49Z

I was able to figure it out myself: it was a variant array vs. array of variants problem. I added some code to force the data within temp when it is used to set SeriesCollection.Values into type double. The final block in the snippet I originally posted is now:

Dim dbl As Double '<--var to hold contents of series_arr in loop
Dim j As Integer
For i = 1 To 4
    With .SeriesCollection(i)
        If LBound(series_arr, 1) = 0 Then i = i - 1 'handle base 0 case-operation is reversed at end of loop to avoid incorrect steps
        ReDim temp(LBound(series_arr, 2) To UBound(series_arr, 2))
        For j = LBound(series_arr, 2) To UBound(series_arr, 2)
            If series_arr(i, j) = "" Then '<--convert series_arr recs
                temp(j) = 0
            Else
                dbl = series_arr(i, j)
                temp(j) = dbl
            End If
        Next j
        .Values = temp
        .XValues = header_arr
    End With
    If LBound(series_arr, 1) = 0 Then i = i + 1
Next i

And it works! Here is my (relatively) educated guess as to why; I would appreciate someone who knows better giving a better answer, though.

Variables of type Variant are in principle implicitly convertible to type Double, but they are also implicitly convertible to type String (among others). This means that Empty records are ambiguous; when Excel has to decide to convert it to "" or 0, it needs some other criteria to fall back on.

I thought that the fact that temp, when being used to set .Values, contained some numbers would cause Excel to convert the Empty records to 0, but I guess this isn't the case. My conclusion is that Excel will only convert array elements of type Variant to the correct type if all of them are convertible to only one other type. Thus header_arr was correctly converted to type String, even when I tested this hypothesis by removing one of the elements of header_arr. series_arr, on the other hand, since it contained elements that were convertible to type Double only but also elements that were convertible to several number types, did not convert properly.

Source

Collectives™ on Stack Overflow

Setting SeriesCollection.Values using variant array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related