AnsweredAssumed Answered

Store XYPlot stats as numbers (Processbook)

Question asked by msj_ on Jan 8, 2018
Latest reply on Jan 11, 2018 by msj_

Hi all,


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

    End If

End If


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))