Use a cell value (text) as a part of a formula in Excel VBA

Question

I have a problem while doing a macro in Excel VBA which looks simple but I was not able to find an answer.

What I want is to change a formula depending on a value of a concrete cell; the cell is situated in C7 and can have the text OR or the text AND. The part of the formula is (being CritEUs and CritSKUs String variables):

If CritEUs = "NO" (OR/AND) CritSKUs = "NO" Then .... (whatever)

So I want to change the OR/AND depending on the value in C7, I tried to use INDIRECT but I think it works only with numbers, and also the following (being W the Worksheet variable):

Dim Pattern As String

Pattern = W.Range("C7").Value

If CritEUs = "NO" " & Pattern & " CritSKUs = "NO" Then 

But the Excel don't accept me this option.

Could it be that this is not possible?

I would really appreciate any help!

Answer 1

I'd look to handle this in another if statement and then nest the next if statement within like so:

Sub Example()

Dim Pattern As String

Pattern = W.Range("C7").Value

If Pattern = "AND" Then
    If CritEUs = "NO" And CritSKUs = "NO" Then
        'Do Something'
    End If
ElseIf Pattern = "OR" Then
    If CritEUs = "NO" Or CritSKUs = "NO" Then
        'Do Something'
    End If
End If

End Sub

Answer 2

Even if I strongly prefer Gareth's solution, there is a trick for doing what you want (i.e. for evaluating the condition) through the usage of the Application.Evaluate() method. It would be:

If Application.Evaluate(Pattern & "(" & Chr(34) & CritEUs & Chr(34) & "=" & Chr(34) & "NO" & Chr(34) & "," & Chr(34) & CritSKUs & Chr(34) & "=" & Chr(34) & "NO" & Chr(34) & ")") Then

... where the string being an expression such as =AND(whatever = "NO", whateverelse = "NO") or =OR(whatever = "NO", whateverelse = "NO") (depending on the value of the variable Pattern) that can be evaluated by the MS Excel application no matter what the system language is.

But as I said, I would personally prefer a nested if block as Gareth suggested because it's clearer what you are doing and it cannot crash if the user inserts an invalid logic operator or makes just a spelling mistake; you should consider this option if you don't want / cannot slightly re-design your code.

FORMULA EXPLANATION - required from the asker

The Evaluate() is a method of the Application object, which means of the object MS Excel. This method is very straightforward:

input: string
output: evaluation of the string

It is in fact used to "evaluate" a string inserted by the user exactly as it does when you type a formula into a cell. If you type into a cell "=3+4", you are basically typing Application.Evaluate("3+4"). This will return you 7, because it's the result of the string evaluation you provided.

This built-in is very very powerful, because it uses a very consolidated system (the one of MS Excel) to parse and evaluate any string that Excel can evaluate. Moreover, the evaluation is always in English (you can use the English function IF but not the Italian SE, nor the German WENN or the French SI because the method evaluates as if your Excel was in English to be system independent.

On the other hand, the Chr(34) is just returning the character ". This character is hard to use in VBA because it's usually need to separate strings (e.g. a = "first" & "second". However, you need this character inside the string to be evaluated so I'm just calling it with Chr(34) to avoid confusion of the compiler.

SUMMARY:

The string is being built up like this:

Pattern & "(" & Chr(34) & CritEUs & Chr(34) & "=" & Chr(34) & "NO" & Chr(34) & "," & Chr(34) & CritSKUs & Chr(34) & "=" & Chr(34) & "NO" & Chr(34) & ")"

Being...

Pattern = AND or OR
Chr(34) = "

... the string that we are building will be of this kind (just a possible outcome):

"AND("NO"="NO","YES"="NO")"

So, once we have built-up this string, we pass it into the Evaluate method: it's like if we were writing =AND("NO"="NO","YES"="NO") into an Excel cell. What would the outcome be? Clearly it depends on your variable, but in this case it would be FALSE, so the If - Then block will not be entered because the return value is false. Otherwise, it would be entered.

This is not a "wrong" method, but as I was saying it has only two possible downsides:

1) It needs data validation, because if you pass crap into the variable Pattern the Evaluate method will fail; in Gareth's solution, instead, only AND and OR will be evaluated, otherwise the code will skip --> more stability;

2) It's not 100% intuitive: while Gareth's solution could be explained to a 10 year old child (because it's very very straight-forward to understand), this one needs (as we just did) a deeper analysis to understand properly what it does. I.E.: you need one line of code to write it, but you/someone else that will have to work on it in the future will need 5-10 minutes and a cup of coffee to understand what the statement wants to check.