Evaluate formula in macro

[#1]

Hi all,

I have got this kind of formula in a lot of textboxes. To avoid copy mistakes and maybe do a quick change on the formula in every box, I want to evaluate the value by a macro and just give parameters to it. Is this possible?

My formula:

only({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},rowid={`$(=min({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},KPI_Category_Name={"$(=FieldValue('KPI_Category_Name',1))"}>}rowid,1))`}>} Statuscolor)

Can someone tell me how to evaluate this in a macro? I never implemented a macro but tried it with

sub value()
    value = activedocument.Evaluate("formula");
end sub

but the " " dont work because there are also some inside the formula.

Thanks for any kind of help!

Kind regards

[Gysbert_Wassenaar]

Try square brackets instead of the double quotes:

only({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},rowid={`$(=min({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},KPI_Category_Name={[$(=FieldValue('KPI_Category_Name',1))]}>}rowid,1))`}>} Statuscolor)

[#3]

Thank you, this totally works fine with a macro giving an msgbox.

What I expected to do is :

function value_ret()
    value_ret =  activedocument.Evaluate("only({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},rowid={`$(=min({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},KPI_Category_Name={[$(=FieldValue('KPI_Category_Name',1))]}>}rowid,1))`}>} Statuscolor)")
end function

and then say in a textbox

=value_ret()

to set the value in the textbox (later on with variables). Is this even possible because I dont get any values.

Thank you!

[Gysbert_Wassenaar · Accepted solution]

Not with a macro. But you can simply use a variable instead. Put the expression in a variable and put $1, $2 etc in the expression as placeholders for any parameters you want to pass.

[#5]

and if I call the variable in a textbox with =$(myvariable)  ... how can I pass the real value for the placeholders?

thank you got it !

[#6]

Wow, this is damn powerful, I feel like I knew nothing. Thanks a lot!