What is the VBA function to return the greater of two integers?

[baobob]

I'm still a VBA newbie, and I have two questions:

1. As an Object Pascal programmer, it never ceases to amaze me how
difficult it is, on occasion, to find out how to do the simplest of
things in Excel VBA.

So how do I return the greater of two integers? In Pascal, it's Max(x,
y). I mean, don't all languages have that simple construction?

But my VBA compiler doesn't recognize "Max". Not only that, but "max"
and "maximum" don't even appear in the Contents and Index. Nor does
VBA Help's list of math functions even mention that functionality. I
find that to be unbelievable.

2. I should probably ask this in a different thread, but: I routinely
search microsoft.public.excel.* for answers to these basic syntax
questions, and I often find absolutely no hits whatsoever on even the
commonest questions. For example, I just searched
microsoft.public.excel.programming for various combinations of "max",
"maximum" "greater of", "integers", etc. and all I get are threads on
much more specialized topics.

Now, I use Google Groups' interface into Newsnet. Is that my problem?
Does Google present only a crippled subset of Newsnet to the world? If
so, how crippled? Pathetically so? Should I be using a true news
reader instead?

Thanks much.

***

 

[Gary Keramidas]

try something like this:

number = worksheetfunction.Max(range("A1"),range("B1"))

 

[Gary Keramidas]

forgot to add that this would also work

number = application.Max(range("A1"),range("B1"))

 

[JMB]

to my knowledge, vba does not have its own max/min functions, but it can
access excels max/min worksheet functions

application.worksheetfunction.max
or
worksheetfunction.max

although I prefer
application.max

when using excel's worksheet functions (there is a difference between these
two methods in how errors are handled w/some of the lookup functions). Also,
worksheetfunction apparently does not like omitted arguments when using Index
(Application.WorksheetFunction.Index(someRange, , 2)) as described by Alan
Beban in an earlier post.

If you type in worksheetfunction. - the intellisense should bring up a list
of XL functions that can be accessed by VBA.

Never used google's interface, so I can't be of any help there.

 

[joeu2004]

to my knowledge, vba does not have its own max/min functions,
but it can access excels max/min worksheet functions
[....]
worksheetfunction.max

although I prefer
application.max

How does the performance of either of those compare to writing my own
VBA function? The OP is only interested in the max of two integers.

 

[TWR]

I suppose you could write a function like this:
Private Function mGetMax(i1 as integer, i2 as integer) as integer
If i1>i2 then
mGetMax = i1
Else
mGetMax=i2
Endif
End Function

 

[Bob Phillips]

It probably balances out. Using a worksheet function means that you call
back into Excel, and it returns a result, which incurs an overhead. ON the
other hand, if you write code, that is inherently less efficient than Excel
functions, but without the overhead of the call. The worksheet function will
probably; be a bit slower, but it will be marginal.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

to my knowledge, vba does not have its own max/min functions,
but it can access excels max/min worksheet functions
[....]
worksheetfunction.max

although I prefer
application.max

How does the performance of either of those compare to writing my own
VBA function? The OP is only interested in the max of two integers.

 

[Ron Rosenfeld]

I'm still a VBA newbie, and I have two questions:

1. As an Object Pascal programmer, it never ceases to amaze me how
difficult it is, on occasion, to find out how to do the simplest of
things in Excel VBA.

So how do I return the greater of two integers? In Pascal, it's Max(x,
y). I mean, don't all languages have that simple construction?

But my VBA compiler doesn't recognize "Max". Not only that, but "max"
and "maximum" don't even appear in the Contents and Index. Nor does
VBA Help's list of math functions even mention that functionality. I
find that to be unbelievable.

2. I should probably ask this in a different thread, but: I routinely
search microsoft.public.excel.* for answers to these basic syntax
questions, and I often find absolutely no hits whatsoever on even the
commonest questions. For example, I just searched
microsoft.public.excel.programming for various combinations of "max",
"maximum" "greater of", "integers", etc. and all I get are threads on
much more specialized topics.

Now, I use Google Groups' interface into Newsnet. Is that my problem?
Does Google present only a crippled subset of Newsnet to the world? If
so, how crippled? Pathetically so? Should I be using a true news
reader instead?

Thanks much.

***

VBA does not have a built-in Max function.

You can use the Excel function as in:

=application.worksheetfunction.max(n1,n2)

Or you can write your own. For example:

Function vbMax(n1, n2) As Double
vbMax = IIf(n1 > n2, n1, n2)
End Function

You can also, by using the ParamArray argument, write a function that will take
any number of arguments.
--ron

 

[baobob]

Yow! Thanks to all y'all who answered by dumb Q: Bob, Gary, JMB,
joeu2004, and Ron.

***

 

[JMB]

I wasn't taking the OP literally when he/she asked about finding the max of
two integers.

I've learned that posters often try to ask a simple question to learn the
mechanics of how something is done and then try to apply it to what they are
actually doing (which is usually more complex). I'm sure the OP knows how to
find the larger of only two numbers so I assumed that is not what he/she is
ultimately trying to accomplish.

The VBA function was faster by 0.000008 seconds, but that's only when
passing it two integers to evaluate.

to my knowledge, vba does not have its own max/min functions,
but it can access excels max/min worksheet functions
[....]
worksheetfunction.max

although I prefer
application.max

How does the performance of either of those compare to writing my own
VBA function? The OP is only interested in the max of two integers.