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

c = minimum of a and b

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

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

Thank you sir.

Ed English




On 20 Nov 2006 06:14:47 -0800, "Zone" wrote:

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

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

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

Somewhat strangely, the following sub takes about twice as long as
min2(). I was expecting it to be quicker (or at least no slower). IIf
is a VBA function (which I have never used before) that evaluates its
first argument and, if true, returns the second argument otherwise it
returns the third argument. Thus it is the equivalen to C's ? operator.
I though that the compiler might be able to do some optimization with
it:

Sub Min3()
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 = IIf(a < b, a, b)
Next i
elapsed = Timer - start
MsgBox elapsed
End Sub


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

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