I am using the slope and correlation coefficient figures from XYPlots to do some calculations. I have noticed that they are generated as strings (e.g. "0.97") so I tried to convert them into numbers (using VBA.CDbl or VBA.CSng) before I do my adding and multiplying.
However, the first time I run the script, there will always be a Type Mismatch error. After the first run with error, every other run after (with same script) works fine. What is the problem?
For more info, the array is already defined as a Variant, and I'm using a for loop to store slope and correl coeff numbers from multiple XYPlots.
edit: the codes look like this right now
Dim sctr_plots As XYPlot
If Symbols.Item(i).Type = 29 Then 'for scatter plots (type = 29)
Set sctr_plots = Symbols.Item(i)
If sctr_plots.Stats.Item(2).CorrelationCoefficient = "N/A" Then
scatter_stats(num, 5) = sctr_plots.Stats.Item(1).CorrelationCoefficient 'update correlation coefficient and slope stats
scatter_stats(num, 2) = sctr_plots.Stats.Item(1).Slope
scatter_stats(num, 5) = sctr_plots.Stats.Item(2).CorrelationCoefficient
scatter_stats(num, 2) = sctr_plots.Stats.Item(2).Slope
There's more repetitions in the loop to give me a matrix of these stats as Strings. I then convert them to numbers before calculations. for this purpose I'll just show adding them.
scatter_stats(num, 7) = VBA.CSng(scatter_stats(num, 2)) + VBA.CSng(scatter_stats(num, 5))