Is there a way to execute VBA code contained in a cell?

Question

I'm building a macro in Excelto run rules against a set of data and output whether each row passes or fails the rules. I want to be able to add, remove, or alter the rules without altering the macro. As such I have a DATA worksheet and a RULES worksheet and the macro generates the OUTPUT worksheet and then populates it.

RULES is set up so that each different rule is enumerated on a different row. For this to work I need to be able to enter the actual VBA code relevant to the rule in on RULES, then I need to have the macro look at that column on RULES and execute the code in the cell.

Simplified example of my setup-

DATA has : ID, Dividend1, Dividend2, Divisor. There are n rows on DATA.
An example of a row on DATA might be ID="123", Dividend1=5, Dividend2=7, Divisor=35.
RULES has : Name, Formula, Threshold. For simplicity's sake there is only .
Let's set the as Name="Example", Formula=[see below], Threshold="0.15" (Threshold is used for conditional formatting in the macro, in this example it is unused.)

I'm going to use pseudocode for Formula just to eliminate the need to explain some of the irrelevant particulars of my macro so far. RULES.Formula should contain a line of VBA code that carries out-

If CurrentDATARow.Dividend1 = Empty Then
    CurrentDATARow.Dividend2 / CurrentDATARow.Divisor
Else
    CurrentDATARow.Dividend1 / CurrentDATARow.Divisor
End If

So, all of this explanation just to give context to this question: What can I do in the VBA of the macro to make it read the contents of RULES.Formula and make it execute that code inline with the rest of the macro?

Seems like you are going about this the wrong way... Why not establish each `Rule` as a boolean `Function` in VBA, then just iterate over your range of rows and test each against all whatever functions. You can then easily modify one or multiple Functions -- just as easily as you could manipulate the text in the worksheet cells. — David Zemens, May 23 '13 at 15:09
I can see what you are trying to do, but I cant understand your pseudocode. Whats it supposed to do? — , May 23 '13 at 15:10
I would store the tests as Excel worksheet formulas, with placeholders for the particular values to be plugged in. Then replace the placeholders for each row with the cell addresses: use Evaluate to run the formula and return the result. — Tim Williams, May 23 '13 at 15:15
@DavidZemens - Part of the issue is that this isn't just for me it's also going to be used by my coworkers. Having to write up a short explanation about how to create/modify existing rules is going to be a lot simpler than teaching them how to deal with the entirety of the macro. — newuser, May 23 '13 at 15:15
@TimWilliams - Can you link me to a resource that explains what you're suggesting in greater detail? I'm use to programming in C# and have never really attempted something this in-depth with VBA in Excel so I'm effectively a VBA noob. — newuser, May 23 '13 at 15:16
@JonnyP I favor my method, too :) but if it's an application for non-VBA users, then Tim's method would be better. — David Zemens, May 23 '13 at 15:17
As long as you can rely on the users **not** messing up the Worksheet formulas. In my experience, that's probably unreasonable. Give people the means to screw up an input, and they'll screw up the input :) — David Zemens, May 23 '13 at 15:19
lets [chat](http://chat.stackoverflow.com/rooms/30506/explain-to-me) — , May 23 '13 at 15:36
@David: the OP could protect and [hide the worksheet formulas](http://stackoverflow.com/questions/16684297/hiding-formulas-in-formula-bar/16686868#16686868) — Our Man in Bananas, May 23 '13 at 15:39
What is "best" really depends on what the real point of this is: if it's to distribute to "power user" types who may not be able to write VBA but are comfortable with formulas then I prefer my idea: I've used this method in projects and it provides a lot of flexibility. If it's for your own personal use then David's suggestion make much more sense. — Tim Williams, May 23 '13 at 16:43
@TimWilliams - It's for me and a couple of other power users. There's one standard user who won't even care that we can alter the rules on the fly so I'm not concerned about him. Can you elaborate on what you meant by "use Evaluate"? Is that a VBA method? — newuser, May 23 '13 at 17:07
Ha. Okay, so I asked before I searched which is a no no. So Evaluate() accepts a string formatted as an Excel funciton and returns the result. This is kind of exactly what I need because rather than having RULES.Formula be a line of VBA it can just be an Excel function with placeholders for the variables, like you said above, @TimWilliams. I think I've got my solution. — newuser, May 23 '13 at 17:11
If you have (say) `IF({dividend1}="",{dividend2}/{divisor},{dividend1}/{divisor})` stored in a cell (note no "="), you can use `Replace()` to replace the placeholders with the relevant cell addresses for each cell in the row you're checking. Then use something like `val=Evaluate(yourformulastring)` If the evaluation results in an error you can test with `IsError(val)`, otherwise it will return the result of the formula, which you can test against your "threshold" value cell. `Evaluate` will calculate the formula based on the ActiveSheet, so make sure the right sheet is active when this runs. — Tim Williams, May 23 '13 at 17:15
@TimWilliams - Please post that as an answer so I can make it as accepted. Thank you! — newuser, May 23 '13 at 17:19

score 3 · Accepted Answer · answered May 23 '13 at 17:28

If you have (say)

IF({dividend1}="",{dividend2}/{divisor},{dividend1}/{divisor})

stored in a "rule" cell (note do not include the "="), you can use Replace() to replace the placeholders with the relevant cell addresses for each cell in the row you're checking.

Then use something like

Dim val

val=Sheet1.Evaluate(yourformulastring) 'evaluate vs. specific worksheet

If Not IsError(val) Then
    'check against thresholds etc
End If

If the evaluation results in an error you can test with IsError(val) as shown, otherwise it will return the result of the formula, which you can test against your "threshold" value cell. If you set background colors on your threshold cells you can color each row according to which threshold was exceeded.

NOTE without a worksheet qualifier, Evaluate will calculate the formula based on the ActiveSheet, so make sure the right sheet is active when this runs if you don't use the qualifier.

score 1 · Answer 2 · answered May 23 '13 at 15:48

1

you could store your Tests/Rules as Excel worksheet formulas in Named ranges. Then you just call them from the cells.

see Ozgrid: Named Formulas

If you give us some example data and the type of calculations or rules I can give you a couple of examples.

answered May 23 '13 at 15:48

Our Man in Bananas

5,809
21
91
148

Is there a way to execute VBA code contained in a cell?

2 Answers2