I was using the if else, but below is easier.

Thanks again,

Ed English

c = Application.WorksheetFunction.Min(a, b)


On 20 Nov 2006 08:10:11 -0800, "John Coleman"
wrote:

Another possibilty is:

If a < b Then
c = a
Else
c = b
End If

Another thread today (on VLOOKUPS) disscussed the overhead in calling
worksheet functions from VBA. This made me curious about which method
of computing a min would be quicker. If you run the following 2 subs:

Sub Min1()
Randomize
Dim a As Double, b As Double, c As Double
Dim start As Double, elapsed As Double
Dim i As Long

start = Timer
For i = 1 To 1000000
a = Rnd()
b = Rnd()
c = Application.WorksheetFunction.Min(a, b)
Next i
elapsed = Timer - start
MsgBox elapsed
End Sub

Sub Min2()
Randomize
Dim a As Double, b As Double, c As Double
Dim start As Double, elapsed As Double
Dim i As Long

start = Timer
For i = 1 To 1000000
a = Rnd()
b = Rnd()
If a < b Then
c = a
Else
c = b
End If
Next i
elapsed = Timer - start
MsgBox elapsed
End Sub

You should find the second noticably faster than the first. On my
machine Min2() takes about half a second and min1() takes about 5
seconds. So - if your min calculation is in the body of a loop you
might want to use a straight VBA approach. On the other hand, if you
are using it only a few times in your code then the readability of the
worksheetfunction.min approach might win out. YMMV.

HTH

-John Coleman

Ed wrote:
What is the code to obtain the variable c as the minimum of variables
a and b?

c = minimum of a and b