Defining formula/String with Variable

[Imbizile]

Hi all

I have a problem, that I hope someone has a solution to

The short version:
Is there a way to "store" a formula/string in VBA where it keeps variables as variables, and not as the value of the variable at the point I define the formula?

Im trying to define a formula/string in VBA that contains variables and use that formula several times in later steps in the script. So I need to store a formula like

Please Login or Register  to view this content.

and avoid it converting the CurrentRow variable to the actual value of CurrentRow when I store it.

The long version:
The contents of the formula in my setup depends on a lot of different things and for several reasons, I need to define it early in my script and use it many many times afterwards. The way I define it, is based on a lot of different criterias and I run a lot of code to build the correct formula, and need to avoid running all the pre-building code again every time I want to use that formula. Some criterias might also have changed it the meantime after I build the right formula, so running the pre-building again, would give a wrong formula.

A bit tricky to explain, but I tried to make a simple example below.

Depending on if cell C2 is A or B the FormulaText will respectively be either

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

where the CurrentRow is a variable that is not known at this point (or it could be something different later when I use the FormulaText again). So CurrentRow it not static and the result of FormulaText should therefore also differ depending on the CurrentRow number, when I use the formula again.

I tried to set the scenario up in this code with comments alone the way:

Please Login or Register  to view this content.

Really hope there is a way to do this and thanks in advance

[daboho]

still Confiuse but maybe like this

Please Login or Register  to view this content.

[Imbizile]

Thanks for the reply

It is hard to explain, but what im looking for is a general solution to storing/defining a formula at a certain point, that contains variables, and using that formula afterwards, with the updated values of the variables. Its not a specific rewrite of my actual example, that will give the outcome im looking for in this particular example. Its a way to store a formula containing variables at one point, and using that formula dynamically afterwards with the variables still beign variables.

To refer to my example, I need to store the content of FormulaText as

Please Login or Register  to view this content.

and not as

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

In my example I only had 2 different cases, but in my actual setup (which is huge and I cant post that here), that number differs based on different things, and the formulas could also differ. I have like 100+ different cases and 200+ different formulas.

So when constructing the formulas based on cases and other things, the cases in my actual setup could look like below, which makes and a specific rewrite for each case out of the question (FormulaText called FormulaShell in my actual setup). Difficult not to cludder my original question, without giving examples of my actual setup, thats why I tried a simple setup that would illustrate the predicament. But to give a few examples this is some of the cases in my code, where those formulas hold variables and I need to "store" those formulas at a certain point and use them later, with the updated values of the included variables. The below is just bits and parts of my actual setup, so no need to go into details in this code

Please Login or Register  to view this content.

[daboho]

Thanks for the reply

It is hard to explain, but what im looking for is a general solution to storing/defining a formula at a certain point, that contains variables, and using that formula afterwards, with the updated values of the variables. Its not a specific rewrite of my actual example, that will give the outcome im looking for in this particular example. Its a way to store a formula containing variables at one point, and using that formula dynamically afterwards with the variables still beign variables.

To refer to my example, I need to store the content of FormulaText as

Please Login or Register  to view this content.

and not as

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

In my example I only had 2 different cases, but in my actual setup (which is huge and I cant post that here), that number differs based on different things, and the formulas could also differ. I have like 100+ different cases and 200+ different formulas.

So when constructing the formulas based on cases and other things, the cases in my actual setup could look like below, which makes and a specific rewrite for each case out of the question (FormulaText called FormulaShell in my actual setup). Difficult not to cludder my original question, without giving examples of my actual setup, thats why I tried a simple setup that would illustrate the predicament. But to give a few examples this is some of the cases in my code, where those formulas hold variables and I need to "store" those formulas at a certain point and use them later, with the updated values of the included variables. The below is just bits and parts of my actual setup, so no need to go into details in this code

Please Login or Register  to view this content.

If you have any formula
You can store to array

Please Login or Register  to view this content.

[rorya]

You can’t do what you are trying. You just have to change the point at which you use the variable so that it has the correct value.

[Imbizile]

Ok, thats sad. If no one else has any suggestions, I will try to solve this another way.

@daboho: thanks for the effort, but I could not get this to work. Even if I export the formulas, as they are, I cant seem to reuse them, treating the variables in the formulas as variables.