More Efficient If function

[will12985]

I need some help improving the efficiency of an if statement in VBA. I can
do a nested if statement in excel and the formula takes no time to change
when an input is changed. I need to add more conditions so I am at the
extent of my nested if skills. The function is:

Function MertonA(IsCall, IsAmerican, Price, Exercise, u, d, n, RowCount,
ColumnCount)

If IsCall = True And IsAmerican = True Then
Formula 1
ElseIf IsCall = False And IsAmerican = True Then
Formula2
ElseIf IsCall = True And IsAmerican = False Then
Formula3
ElseIf IsCall = False And IsAmerican = False Then
Formula4
End If

End Function

Each formula is about 2 lines so I omitted it for brevity. The function is
pasted in a 100 x 100 range and takes about 20 seconds to update completely.

Help would be appreciated.

 

[Jim Cone]

Following is the form your function might take.

Note that all of the variables are declared;
as there were no declarations they were all variants.
Also the function has been given a data type (String)
A Select Case statement is used to replace the If/End If structure.
You can use as many case conditions as necessary.
In addition, the result is assigned to the function itself ...

Function MertonA(ByRef IsCall As Boolean, ByRef IsAmerican As Boolean, _
ByRef Price As Double, ByRef Exercise As Boolean, ByRef u As Double, _
ByRef d As Double, ByRef n As Double, ByRef RowCount As Long, _
ByRef ColumnCount As Long) As String

Select Case True
Case IsCall = True And IsAmerican = True
MertonA = Formula1
Case IsCall = False And IsAmerican = True
MertonA = Formula2
Case IsCall = True And IsAmerican = False
MertonA = Formula3
Case IsCall = False And IsAmerican = False
MertonA = Formula4
Case IsCall
MertonA = moreformulas
Case Else
MertonA = alternate
End Select

End Function

--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"will12985"
<[email protected]>
wrote in message
I need some help improving the efficiency of an if statement in VBA. I can
do a nested if statement in excel and the formula takes no time to change
when an input is changed. I need to add more conditions so I am at the
extent of my nested if skills. The function is:

Function MertonA(IsCall, IsAmerican, Price, Exercise, u, d, n, RowCount,
ColumnCount)

If IsCall = True And IsAmerican = True Then
Formula 1
ElseIf IsCall = False And IsAmerican = True Then
Formula2
ElseIf IsCall = True And IsAmerican = False Then
Formula3
ElseIf IsCall = False And IsAmerican = False Then
Formula4
End If

End Function

Each formula is about 2 lines so I omitted it for brevity. The function is
pasted in a 100 x 100 range and takes about 20 seconds to update completely.

Help would be appreciated.

 

[NickHK]

I wouldn't say this is more efficient, but it more easy to see where you're
going:

Function MertonA(IsCall, IsAmerican, Price, Exercise, u, d, n, RowCount,
ColumnCount)
If IsCall = True Then
If IsAmerican = True Then
Formula1
Else
Formula3
End If
Else
If IsAmerican = True Then
Formula2
Else
Formula4
End If
End If
End Function

Depending on what other conditions you need to add, "Select Case" may be
useful.

NickHK

 

[Dana DeLouis]

... I need to add more conditions so I am at the

extent of my nested if skills.

Hi. One technique when deciding what to do based on a group of Boolean
variables is to treat the group as a Binary number.

n = Abs(4 * IsOther + 2 * IsCall + IsAmerican)
Run "Formula" & n


This will generate the unique numbers 0,1,2,3,4...etc
If you can keep to your formula names like "Formula0",
"Formula1","Formula2", etc then this could be another option.
I included a third Binary variable called "IsOther."
Note that True is -1 in Vba, so we use Abs at the end.

'//Note:
'0=All False
'1=Only IsAmerican is True
'2=Only IsCall is True
'3=Both IsCall and IsAmerican are True
'...etc

As a side note in your original formula, some like to drop the comparison
operator (=True/False)
This is because your variables are Boolean to begin with. Maybe something
like this:
I've changed the formula names to match the above idea.

If IsCall And IsAmerican Then
Formula3
ElseIf Not IsCall And IsAmerican Then
Formula1
ElseIf IsCall And Not IsAmerican Then
Formula2
ElseIf Not IsCall And Not IsAmerican Then
Formula0
End If