4 Replies Latest reply on Jan 11, 2018 2:08 AM by msj_

    Store XYPlot stats as numbers (Processbook)

    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

          Else

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

       

      Thanks,

       

      Shiji

        • Re: Store XYPlot stats as numbers (Processbook)
          rschmitz

          Hi Shiji,

           

          I was under the impression that the slope and intercept lines are inaccessible in VBA for Processbook. Could you post some sample code so we could get a better idea of how you're retrieving them?

           

          Cheers,

          Rob

            • Re: Store XYPlot stats as numbers (Processbook)
              msj_

              Thanks for the response. I have updated the post to include some sample codes.

                • Re: Store XYPlot stats as numbers (Processbook)
                  rschmitz

                  Thanks for adding the code to your post.

                   

                  I wasn't able to reproduce this behavior of type mismatch on the first pass through the VBA code. I agree with you on both points, that these do need to be cast in as they are stored as strings and that CSng is the way to go for this. Are you able to replicate this issue on machines other than your own? Correct me if I'm wrong here, but it gives you the type mismatch the first time you run the script after opening Processbook, or after making changes and saving it? What version of Processbook are you using?

                   

                  On a side note, I would recommend using "If curSymbol.Type = pbSymbolXYPlot" over "If Symbols.Item(i).Type = 29" for clarity's sake.

                   

                  --Rob

                    • Re: Store XYPlot stats as numbers (Processbook)
                      msj_

                      Hi Rob,

                       

                      I'm using Processbook 2014 Version 3.4.0.251 on a remote desktop connection (because PI server is not accessible from local machine)

                      This only applies after opening the Processbook Display. But not after changing and saving.

                       

                      I have done some more step into runs and I realised that on the first run, the XYPlot stats are not generated, and all stats show as "NA", which causes the problem when CSng("NA").

                      Every run after that shows normal XYPlot stats and code works fine.

                       

                      Is there any reason why the stats do not show in the first run after opening the Display? I also tried to add a 0.05sec pause ProcessBook VBA Short Pause  just in case it needed a delay to process but it does not fix the problem.

                       

                      Edit: I realised that the XYPlot.stats method is returning the previous stats. For example, if I change the XYPlot time range from "*-2d","*-1d"  to "*-1d","*", the XYPlot will update to the new time range, but the XYPlot.stats will still maintain the slope and coefficients of the old time range.

                      Because my old time range did not have any data on first run, it was showing "N/A" as old time range. Therefore, even though my script updated the XYPlot, the stats remained "N/A" until the second run, where the stats belongs to the first run.

                       

                      Thanks,

                       

                      Shiji

                      1 of 1 people found this helpful