Assigned arrays of doubles to XLScatter chart series' .XValues & .Values properties; however .XValues get converted to strings somehow. Why?

Question

I have functional VBA code that creates a XYScatter chart of data provided to it in the form of an "X" array and a "Y" array (as opposed to data that exists inside a range on a worksheet). Both arrays are declared as doubles (i.e. Locals window lists the arrays as type "Double(# to #)" and each array element as type "Double"). They are assigned respectively to the .XValues and .Values properties of a series in the chart, and everything appears to be displayed as expected.

A separate sub attempts to adjust the .MinimumScale & .MaximumScale properties of the chart's axes scales based on the extent of the data it finds in .XValues and .Values, and it does so successfully for the Y-axis (the .Values). However, it fails for the X-axis. Somehow, although I've provided the same data types to those two properties, doubles passed to .XValues are getting converted into strings while those within .Values remain doubles. To be precise, they're listed respectively in the Locals window as arrays of "Variant/String" and "Variant/Doubles".

Thus, when I try to retrieve the min and max X values in order to set the X-axis scale Application.Max(.XValues) all I ever get back is 0, presumably because Excel's Max function doesn't recognize the strings as numbers.

It's worth noting that if I attempt the same axis scaling on a chart with XY data from a range in a worksheet I don't encounter this issue, and when I use the Locals window to peer into such a chart it lists both the .XValues and .Values properties as arrays of Variant/Doubles.

Below I've pulled together the relevant pieces of my originally separate subs to troubleshoot and/or illustrate the issue. Please keep in mind that while it would appear the simple solution is to use the max of xArray to set the X-axis scale, my original code has creation of charts occurring in one sub and rescaling of charts occurring in another; xArray is only accessible by the first sub, and I'd like to keep it that way.

Sub test()

    Dim MaxX As Double, MaxY As Double, MinX As Double, MinY As Double
    Dim xArray(1 To 5) As Double, yArray(1 To 5) As Double
    Dim CHRT As Chart, SER As Series

    xArray(1) = 0: xArray(2) = 0.000001: xArray(3) = 9.99999
    xArray(4) = 10: xArray(5) = 20

    yArray(1) = 0: yArray(2) = 0.000001: yArray(3) = 9.99999
    yArray(4) = 10: yArray(5) = 20

    Set CHRT = ActiveSheet.Shapes.AddChart2(-1, xlXYScatter).Chart

    With CHRT
        .SeriesCollection.NewSeries
        Set SER = .SeriesCollection(1)
        With SER
            .XValues = xArray
            .Values = yArray
        End With
    End With

    With Application
        MaxX = .Max(SER.XValues)
        MinX = .Min(SER.XValues)
        MaxY = .Max(SER.Values)
        MinY = .Min(SER.Values)
    End With

    Stop '...to check Locals window; we expect MaxX = MaxY

End Sub

You could wrap the values CDbl( expression ) to convert and value to `Double'.... Also +1 for neat and tidy code — alowflyingpig
Sounds like your scatter plot is somehow getting converted to a line chart, or some other type with a categorical x-axis — Tim Williams
Thanks @alowflyingpig, that'll likely be my workaround: looping through the contents of .XValues and Cdbl()ing them into some new array which doesn't mess with Application.Max(). — dmorchard
@TimWilliams - yeah, I wondered about that too, but according to Locals ChartType = xlXYScatter for both objects, Chart and Series. The Series has a second property, Type = -4169 (which is the Enum for "xlXYScatter"). — dmorchard
Your posted code runs fine for me - no problems with the min/max values. — Tim Williams

dmorchard dmorchard · Accepted Answer · 2019-08-25T15:16:22

I've been unable to determine why I can't get the results I expect so I've implemented a work-around which, in the context of my original troubleshooting code would require declaring two new variables...

Dim tempX() As Double, ii As Integer

and then replacing...

With Application
    MaxX = .Max(SER.XValues)
    MinX = .Min(SER.XValues)
    MaxY = .Max(SER.Values)
    MinY = .Min(SER.Values)
End With

...with...

ReDim tempX(LBound(SER.XValues) To UBound(SER.XValues))
For ii = LBound(SER.XValues) To UBound(SER.XValues)
    tempX(ii) = CDbl(SER.XValues(ii))
Next ii
With Application
    MaxX = .Max(tempX)
    MinX = .Min(tempX)
    MaxY = .Max(SER.Values)
    MinY = .Min(SER.Values)
End With

Now MaxX is being correctly reported as 20 rather than 0.

Assigned arrays of doubles to XLScatter chart series' .XValues & .Values properties; however .XValues get converted to strings somehow. Why?

1 Answers