The previous chart post (XY Chart problems – 1) looked at problems plotting charts with very short data ranges. An associated problem is plotting XY charts with data with gaps (see When is an XY chart not an XY chart …? and Plotting Charts With Gaps), using array data returned from VBA or an array function. In the latest release of Excel there is now a simpler solution to this problem, illustrated in the screen-shots below.
The graphs on the left show plots of the XY data in Columns A and B. The graphs on the right plot the same data, as returned by a VBA user defined function (UDF), with various options for the data format of the returned data.
The original data plots a continuous line, which displays the same in both graphs:
If the Y data includes a blank cell the graph on the left displays with a gap in the line, but if the data is returned from VBA as a variant array (or a range) the blank cell is converted to a value of 0:
If the empty cell is converted to a #N/A value (using VBA CVErr(xlErrNA)), the cell now plots as a gap:
If the VBA array is declared as a Double array the #N/A value will not be accepted, and blank cells are returned as 0:
If any cell in the Y range contains a space, or any other text, that cell will plot as a zero value, both directly and when returned as a variant array from VBA:
Converting the text to an error value in VBA, the graph displays with a gap:
A blank cell in the X column displays the same as in the Y column; as a gap when ploted directly on the spreadsheet and as 0 when returned as a Variant array from VBA:
Converting the blank to a #N/A value in VBA now displays as a gap:
A space (or other text) in the X data results in the XY chart displaying as a Line chart:
Converting the text to #N/A in VBA returns the graph to XY format, with a gap:
If the data is converted to a double array in VBA, with the text cell converted to a zero, the chart will display in correct XY format, but with a zero X value, rather than a gap:
The correct display can be returned to both graphs by entering the #N/A value on the spreadsheet, and returning the data from VBA as a variant array:
As previously, using a Double array in VBA will not allow the #N/A value to be returned from VBA.
A spreadsheet including the chart and the VBA function (as well as the charts from the previous XY graph post) can be downloaded from: XY chart bug.xlsb.