5 Replies Latest reply on Nov 5, 2015 2:33 PM by BenJ

    VBA command to turn 'Show % Good' on/off?


      Using the Call PIDLdialogs command, is there one to turn on/off the Show % Good value for a tag?  I have a macro pulling a large amount of data, but I only need that % good value for one of the tags.  I'd rather save the time not pulling it for the other tags due to the amount I'm pulling.  My plan currently if I can turn that off with code, is to pull the first tag with % good values, paste it to another sheet, then go back and pull the remaining 7 tags without that % good value.


      If there's another way to automate that other than what I'm using as my starting point, I'd be interested in those answers as well, of course.




        • Re: VBA command to turn 'Show % Good' on/off?
          Kenji Hashimoto

          Hi Ben,

          Do you want to turn ON/OFF "Calculated Data" > "show percent good" check box?

          If so, cell's syntax contains on/off settings.The screenshot last part's 4 is show percent good. If you change it to 0, it does not show percent good.

          So I think you can put the syntax (contains 4) to cells by VBA and could get the percent good.

            • Re: VBA command to turn 'Show % Good' on/off?

              Thanks, that's helpful to know, though to use that, I would need to just manually type in the formulas in my macro, correct?  Ideally I was looking for something like the resize/recalculate command (Call PIDLdialogs.DLResize) , but that may just not be available.

                • Re: VBA command to turn 'Show % Good' on/off?
                  Kenji Hashimoto

                  If you want to show %good by one call, I think it is not possible.

                  How about read the existing cell's syntax and replace the specific words?

                  For Example,

                  If cell A6 contains =PIAdvCalcVal(Sheet1!$I$3,Sheet1!$B$2,Sheet1!$B$3,"count","event-weighted",0,1,0,"")

                  Dim CellFormula As String
                  Dim tmpstring As String
                  CellFormula = Replace(Range("A6").Formula, ",0,1,0", ",0,1,4")
                  Selection.FormulaArray = CellFormula
                  1 of 1 people found this helpful
                    • Re: VBA command to turn 'Show % Good' on/off?
                      Marcos Vainer Loeff

                      Using Kenji's code snippet, if you want to resize automatically after showing or hiding the % Good, please refer to the code snippet below:


                      Sub ShowPercGood_Click()
                          Call UpdateCell(True)
                      End Sub
                      Sub HidePercGood_Click()
                          Call UpdateCell(False)
                      End Sub
                      Sub UpdateCell(ShowPercGood As Boolean)
                          Dim addIn As COMAddIn
                          Dim automationObject As Object
                          Set addIn = Application.COMAddIns("PI DataLink")
                          Set automationObject = addIn.Object
                          Dim MyRange As Range
                          Set MyRange = Range("B6")
                          If ShowPercGood = True Then
                               MyRange.FormulaArray = "=PIAdvCalcDat(Sheet1!$A$1,Sheet1!$A$2,Sheet1!$A$3,""1h"",""count"",""time-weighted"",0,1,4,"""")"
                              MyRange.FormulaArray = "=PIAdvCalcDat(Sheet1!$A$1,Sheet1!$A$2,Sheet1!$A$3,""1h"",""count"",""time-weighted"",0,1,0,"""")"
                          End If
                      End Sub
                      2 of 2 people found this helpful
                      • Re: VBA command to turn 'Show % Good' on/off?

                        Ah, great thought, thanks!  That should be an easy enough way to solve what I'm trying to do via a macro.


                        Thanks Marcos for the next bit of code I'd need to finish things up too.