Hello,
I have been trying to write a macro in Excel VB for creating scatterplots using sorted data in a worksheet. When I read in this data in the form of x and y arrays and then try to create a plot from this data using the Xvalues/Values properties of the series on a new scatterplot, it gives me error. Funny thing is that for a particular column only this error is present. For other columns, there is no error. I have read past two threads on this problem and have ensured that both x and y arrays contain only plottable data, meaning that there is no element of x/y array which is empty or not defined. Here is the part of the code that causes this problem.
dknt = 0
For l = 0 To d_rows - 1
If dta(i, m, 0, j, l) <> "" Then
dknt = dknt + 1
End If
Next
Above loop counts the valid data points. After this, we set the dimensions of dynamics arrays x and y, and get the data in those arrays from the following loop.
ReDim x(dknt), y(dknt)
For l = 1 To d_rows
If dta(i, m, 0, j, l) <> "" Then
x(l) = dta(i, m, 0, j, l)
End If
Next
ActiveChart.SeriesCollection(scount).XValues = x
This is the final step which gives me the error I described.
Any clue on this matter will be really helpful.
Swanand.
Unable to set Xvalues property error
Alan Robbins
CLng converts to a long integer. CDbl converts to a double, which retains all the possible digits. CSng converts to a single which may or may not reduce digits. CSng will not change your situation much. You need to truncate after a certain number of significant digits, as described in this web page:
http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html
This will still not help a whole lot unless you have fewer than a few dozen or so values. Put the values into the worksheet and plot them from there. Excel charts are designed to plot data from worksheet ranges, up to 32000 points per series without regard to significant digits. You will not lose precious storage space by adding data to the worksheet, and look at the effort it's cost to try to get around doing so.
Andrew_Shough
You don't say what dta() does. But the hint that it's a ratio with too many digits indicates to me that the array is exceeding the limit on number of characters allowed in the literal array strings passed to .XValues and .Values. You are passing an array, but Excel builds the series formula and the .values and .xvalues like such:
"={1.2345678901234,2.3456789012345,3.4567890123456}"
If you only have a small number of array elements, or if they have few significant digits, no problem. But you are limited to roughly 250 characters in the string shown above. By taking percentages, you may be changing the number of digits used per value. The formula above uses 50 characters for only three values.
I would advise putting these calculated values into the worksheet; in fact, put formulas into the worksheet to do the calculations. Then plot based on the calculated cells.
divya mittal
Hi,
Just a few minutes back I had created this thread. Although I had gone through previous two posts regarding this problem, I was not able to solve the problem I was facing. However, I found a solution for my problem and wanted to share what was happening.
In my case, the data was completely valid and plottable, but it was still giving me this error. I found that the reason was that it was considering too many digits after decimal point. So instead of having a ratio, I represented it in percentage and that solved the problem. I am not sure if this is exactly the cause of the problem but for my case it worked.
Swanand.
efm01
Hi Jon, thanks for your comment and suggestion. What you have said is correct, the limitation of around 250 characters is the deciding factor. I am trying to implement your suggestion, but it reduces some flexibility in my manipulations.
Can you suggest me a method to reduce the number of significant digits I tried using CLng or CDbl, but CLng returns integer (maybe I am using it incorrectly) and CDbl does not help at all(although I expected this!). Really appreciate your help!!
Swanand.