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"

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.