
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Krishan,
This macro will reset all variables to 0.0
sub reset_vars
set
doc=ActiveDocumentset
for
set
v = vars.Item(i) "0.0" , truenext
set
v = nothingset
vars = nothingset
doc = nothingend
sub Kind regards i = 0 to vars.Count - 1 vars = doc.GetVariableDescriptionsHuub

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What if i only want to reset selected variables? I have other variables which should not be touched because they feed other formulas.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
