CODEWITHSUNDEEP
vbaexcel
Marc L
Marc L

Reputation: 877

Dynamic IF statement

Is there a way to build a dynamic if statement in Excel VBA? Basically I am trying to create a parametised calculation where the user will be able to enter the different variables i.e.

variable 1 "variable 2" "variable 3"

In this case variable 1 is a cell reference variable 2 would be = or < or > etc variable 2 would be a cell reference

Thus my If statement statement would be If variable1 varaible2 variable3 then "y" else "n"

When I try it in code 

 If FinalvaluesArray(ArrayRow, 5) & SRCBook.Sheets("Filter Parameters").Cells(2, 4).Value & SRCBook.Sheets("Filter Parameters").Cells(2, 3).Value Then wbNew.Sheets("Output Table").Range(ResultString) = "Y" Else wbNew.Sheets("Output Table").Range(ResultString) = N"

It does not seem to evaluate the expression properly but always seems to return a true value. is there anyway to buld such a function?

I tried bulding the expression up as a string first, and had the code like this:

EvaluateFunction = FinalvaluesArray(ArrayRow, 5) & SRCBook.Sheets("Filter Parameters").Cells(2, 4).Value & SRCBook.Sheets("Filter Parameters").Cells(2, 3).Value

If EvaluateFunction= true Then "Y" Else "N"

But that also did not work

Upvotes: 1

Views: 2246

Answers (1)

GSerg
GSerg

Reputation: 78155

If you trust the source from which your variables come, then you can simply do

If Application.Evaluate(FinalvaluesArray(ArrayRow, 5) & SRCBook.Sheets("Filter Parameters").Cells(2, 4).Value & SRCBook.Sheets("Filter Parameters").Cells(2, 3).Value) Then
    ....
End If

Otherwise this may run arbitrary code, so you might want to check that the values are numbers and/or operators before doing it.

Upvotes: 3

Related Questions