Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

6 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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





Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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