6 Replies Latest reply: Apr 22, 2010 6:11 PM by Huub Letzer RSS

    Macro to clear variables

      I have a QVW like a financial statement and i have added a formula which links to a variable where i enter for example 25.00 and the financial statement will automatically adjust one line item according to the 25.00 i entered in a varaible. The 25.00 is linked to the Rate. The current rate is 20 but i have added a formula where if i change the varaible to 25.00 then the revenue will change accordingly.

      What i need to be able to do is run a macro which will clear the variable from 25.00 to 0.00 when needed. I have multiple rates for various line items so i need to be able to clear them rather than having to go back and enter 0.00 in each variable.

      Revenue = 100

      Membership = 5

      Rate = 20.00

      New After 25.00

      Revenue = 125

      Membership = 5

      Rate = 25.00

      Thanks,

      Krishan

        • Macro to clear variables
          Huub Letzer

          Hi Krishan,

          This macro will reset all variables to 0.0

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           



           

           

           

           



           

           

           



           

           

           



           

           

           



           

           

          sub reset_vars

          set

           

          doc=ActiveDocument

           

          set

           

           

          for

           

           

          set

           

           

           

           

           

           

          v = vars.Item(i) "0.0" , true

          next

          set

           

          v = nothing

          set

           

          vars = nothing

          set

           

          doc = nothing

          end

           

          sub Kind regards i = 0 to vars.Count - 1 vars = doc.GetVariableDescriptions

          Huub

           

            • Macro to clear variables

              What if i only want to reset selected variables? I have other variables which should not be touched because they feed other formulas.

                • Macro to clear variables
                  Huub Letzer

                  When the variables you want to reset start with the same prefix. like PAR_rate, PAR_index etc.. You can add a selection around the reset-part within the for/next loop.

                  if mid(v.Name,1,3)= "PAR" then

                   

                   

                  doc.Variables(v.Name).SetContent

                  "0.0", true

                  end if

                   

                  Huub





                    • Macro to clear variables

                      Ok this might work because the variables which i would like to change to 0.00 all begin with v_NEW. I am not familiar with Macros and how to set them up or where to paste this formula you have created?

                      Thanks again,

                      Really appreciated it!!!!

                      Krishan

                        • Macro to clear variables

                          I was able to use this formula:

                           

                           

                           

                           



                           

                           

                          SUB

                           

                           

                          "v_NewCommRate"

                           

                          ).SetContent "0.00", true

                          "v_NewPRate").SetContent "0.00", true

                          "v_NewHRate"

                           

                          ).SetContent "0.00", true

                          "v_NewMRate"

                           

                          ).SetContent "0.00", true

                          END SUB







                          Macro1

                          • Macro to clear variables
                            Huub Letzer

                             

                            Ok,
                            Open your document.
                            open macro-editor (Menu Tools, edit Module).
                            Copy/past the code below.
                            Press Ok.
                            In your document insert a button. (New sheet-object, button).
                            Give it a text and ten press tab actions.
                            press add, action type = external, click run-macro , press Ok and fill in the macro-name: reset_vars
                            Now you have a button, that will run the macro
                            Huub

                            sub reset_vars
                            set doc=ActiveDocument
                            set vars = doc.GetVariableDescriptions
                            for i = 0 to vars.Count - 1
                            set v = vars.Item(i)
                            if mid(v.Name,1,5) = "v_NEW" then
                            doc.Variables(v.Name).SetContent "0.0", true
                            end if
                            next
                            set v = nothing
                            set vars = nothing
                            set doc = nothing
                            end sub