Max/Min Functions

[Greg Maxey]

I put together the following little snippet of code to determine Max/Min
values with two arguments. I think it was JGM that posted the min function
a while back and I just reversed the equality signs to make the max function
work:

Sub Test()
Dim i As Long
i = min(1, 1000)
MsgBox i
i = max(5, 500)
MsgBox i
End Sub
Private Function min(a As Long, b As Long)
min = -((a < b) * a + (a >= b) * b)
End Function
Private Function max(a As Long, b As Long)
max = -((a > b) * a + (a <= b) * b)
End Function

Does anyone know if someone has figure out how to determine max or min
values in an array using Word VBA? Thanks.

 

[Jezebel]

You can use code along these lines --

pMax = srcArray(lbound(srcArray))

For pIndex = lbound(srcArray) To ubound(srcArray) - 1
pValue = Max(srcArray(pIndex), srcArray(pIndex + 1))
If pValue > pMax Then
pMax = pValue
End If
Next


As a separate issue (although relevant if you're testing large arrays) your
min/max functions, notwithstanding their one-line elegance, are actually
rather inefficient because they involve two comparisons and three arithmetic
operations. Quicker (use GetTickCount to measure it) is the simple --

If a > b Then
Max = a
Else
Max = b
End If

 

[Jonathan West]

Hi Greg,

Ii go along with Jezebel's comment regarding the efficiency of your
comparison. Getting stuff onto a single line is not necessarily going to
make things run faster.

As for getting a max from an array, something like this should work

Function MaxOfArray(vArray() as Variant) As Variant
Dim iStart as Long
Dim iEnd as Long
Dim vMax as Variant
Dim i As Long

iStart = LBound(vArray)
iEnd = UBound(vArray)
vMax = vArray(iStart)
For i = iStart + 1 to iEnd
If vArray(i) > vMax Then vMax = vArray(i)
Next i
MaxOfArray = vMax
End Function

You can adapt the code as needed depending on what kind of items you are
comparing. If they are all integers for instance, you can speed things up a
bit by using Long instead of variant everywhere.

You should also have no problem working out the Min equivalent of the
function

 

[Greg Maxey]

Jonathan,

I really tried, but I admit that I have little experience and lots of
problems with "arrays" in general and passing things between macros and
subroutines in particular. I am stuck. Here is a simple example of what I
am trying to do. While the problem has moved around as I tried to find a
solution, I am stuck now on creating the array on numbers to pass. I think
if I could get passed the line

myArry = ... that I might be home free. Please advise.

Sub CallMacro()
Dim myArray() As Long
Dim oMaxValue As Long
myArray = Split("3 7 13")
oMaxValue = MaxOfArray(myArray)
MsgBox oMaxValue
End Sub

Function MaxOfArray(vArray() As Long) As Long
Dim iStart As Long
Dim iEnd As Long
Dim vMax As Long
Dim i As Long
iStart = LBound(vArray)
iEnd = UBound(vArray)
vMax = vArray(iStart)
For i = iStart + 1 To iEnd
If vArray(i) > vMax Then vMax = vArray(i)
Next i
MaxOfArray = vMax
End Function

 

[Greg Maxey]

Jezebel,

Thanks. Yes I see the simplicity of the multi-line approach. As you might
have read my reply to Jonathan you are aware that I am very inexperienced,
practically clueless, with arrays and passing things. I am sure that your
code snippet would work, I just don't have the skills to plug it in ;-).

 

[Greg Maxey]

Jonathan,

OK, I know that I have created an array because I tested it with the below.
Still I am getting an error on the oMaxValue line. "Type Mismatch - Array
or user defined type expected" Please help me clear the fog!

Sub CallMacro()
Dim myArray
Dim oMaxValue As Long
Dim i As Long
myArray = Array(3, 7, 13)
For i = 0 To UBound(myArray)
MsgBox myArray(i)
Next
oMaxValue = MaxOfArray(myArray)
MsgBox oMaxValue
End Sub

 

[Greg Maxey]

Jonathan,

I solved my immediate problem with the below code. I was searching Google
and found the following tip that Jezebel had posted in response to a
similiar question:

"Put your arguments into an array, and pass that as a single argument. "

I knew I had the array and so I removed the "()" following vArray in the
Function you provided


This only confirms my near complete lack of understanding of 1) Arrays, 2)
Passing arguments. I really don't understand the basic concept and usually
simply stumble on a workable solution. If you (or any other benevolent
soul) have time, I would certainly appreciate it if you could provide a
sample of how you intended your code to be employed with an explanation of
how it works. Of all the thing is VBA, it is those empty parens "()" that
confound me the most. I feel that I am close to an epiphany but I could use
divine intervention. Thanks for everything

Sub CallMacro()
Dim myArray
Dim oMaxValue As Long
Dim oMinValue As Long
Dim i As Long
myArray = Array(3, 1, 13)
oMaxValue = MaxOfArray(myArray)
oMinValue = MinOfArray(myArray)
MsgBox oMaxValue
MsgBox oMinValue
End Sub

Function MaxOfArray(vArray As Variant) As Long
Dim iStart As Long
Dim iEnd As Long
Dim vMax As Long
Dim i As Long
iStart = LBound(vArray)
iEnd = UBound(vArray)
vMax = vArray(iStart)
For i = iStart + 1 To iEnd
If vArray(i) > vMax Then vMax = vArray(i)
Next i
MaxOfArray = vMax
End Function

Function MinOfArray(vArray As Variant) As Long
Dim iStart As Long
Dim iEnd As Long
Dim vMin As Long
Dim i As Long
iStart = LBound(vArray)
iEnd = UBound(vArray)
vMin = vArray(iStart)
For i = iStart + 1 To iEnd
If vArray(i) < vMin Then vMin = vArray(i)
Next i
MinOfArray = vMin
End Function

 

[Jonathan West]

Ok. You are getting mixed up between three different things. Arrays,
Variants containing arrays, and arrays of Variants.

An array is something defined like this

Dim x(10) as Long

A variant is defined like this

Dim x as Variant

I'll come to variants containing arrays in a moment

More inline...

Jonathan,

I solved my immediate problem with the below code. I was searching Google
and found the following tip that Jezebel had posted in response to a
similiar question:

"Put your arguments into an array, and pass that as a single argument. "

I knew I had the array and so I removed the "()" following vArray in the
Function you provided

What you were trying to pass was a Variant containing an array, whereas I
had defined an array of variants...

This only confirms my near complete lack of understanding of 1) Arrays, 2)
Passing arguments. I really don't understand the basic concept and
usually simply stumble on a workable solution. If you (or any other
benevolent soul) have time, I would certainly appreciate it if you could
provide a sample of how you intended your code to be employed with an
explanation of how it works. Of all the thing is VBA, it is those empty
parens "()" that confound me the most. I feel that I am close to an
epiphany but I could use divine intervention. Thanks for everything

Sub CallMacro()
Dim myArray

That defines a Variant. If you don't specify a datatype in a Dim statement,
your variable is created as a Variant. A variant is a kind of container
datatype which can be just about anything - an integer, a floating point
number, a string, or even an array of something.

Dim oMaxValue As Long
Dim oMinValue As Long
Dim i As Long
myArray = Array(3, 1, 13)

The Array function returns a Variant containing an array. What you have here
is almost identical to the code example in the VBA Help.

oMaxValue = MaxOfArray(myArray)
oMinValue = MinOfArray(myArray)

In both these lines, you are passing that Variant, which just so happens at
the moment to be containing an array.

MsgBox oMaxValue
MsgBox oMinValue
End Sub

Function MaxOfArray(vArray As Variant) As Long

Because you are passing a Variant to this function, your were getting a type
mismatch when you used my original code, because I has specificed the
paremater as follows

Function MaxOfArray(vArray() As Variant) As Variant

There are two key differences here

1. vArray vs vArray()
vArray means a variant is being passed. Remember that a variant can contain
anything including an array. vArray() means that an array of variants is
being passed. (and just to make live interesting, since each item in that
array is itself a Variant, it could be anything, including another array!

2. As Long vs As Variant
What comes after As determines the data type of the value returned by the
function. You have specified this as Long. This means you would have had
some strange results had you tried to call the function as follows. Try it
and see what happens!

myArray = Array(3.5, 1.9, 13.34)
oMaxValue = MaxOfArray(myArray)

 

[Jezebel]

Greg, you might be confusing yourself by thinking about this too much. There
really isn't a lot to explain.

VBA is strict about argument types and checks them at compile time (ie
before running). So if you have a variable declared as a variant, you can't
pass it to a function that expects a long array, even if at run time you
have put an array of longs into that variant.
(Incidentally, Split() returns an array of strings.)

To make your first version work, you would need something like ---

Dim pArray() as long
Dim pValues() as string
Dim pIndex as long

pValues = Split("3 7 13")
redim pArray(lbound(pValues) to ubound(pvalues))
for pindex = lbound(pvalues) to ubound(pvalues)
pValues(pindex) = clng(pArray(pindex))
Next

pMax = MaxOfArray(pArray)


Empty parentheses mean that the variable is an array of unknown dimensions.
Within a procedure, it means you are going to use Redim() at some point.
With a procedure argument, it means you are going to pass an array. You can
also use it to indicate that a function returns an array ---

Function ReturnAnArray() as string()

Dim pData(1 to 10) as string
:
ReturnAnArray = pData

End function

 

[Jay Freedman]

Hi Greg,

I know how you feel about this stuff. It's almost deliberately
confusing. :-b

The root of the problem is the Variant data type, which is a
chameleon. You can stuff a value of any other data type into a Variant
variable, including String, Long, Double, etc. You can also declare an
array of Variants with the syntax

Dim myArray(3) As Variant

This creates four separate memory locations numbered 0 through 3, each
of which holds a Variant value.

The confusing part is that, unlike any other data type, you can stuff
a whole array into a single Variant variable, which is what the
Split() and Array() functions do:

Dim myVar As Variant
myVar = Split("3 7 13")
or
myVar = Array(3, 1, 13)

In this case, the single variable named myVar contains a whole array
of three integers or strings, respectively.

This paragraph is buried in the help topic about the Array function,
although the topic on the Variant data type doesn't mention it:

"Note - A Variant that is not declared as an array can still contain
an array. A Variant variable can contain an array of any type, except
fixed-length strings and user-defined types. Although a Variant
containing an array is conceptually different from an array whose
elements are of type Variant, the array elements are accessed in the
same way."

Not nice, not nice at all.

Now, on to the idea of passing an array as an argument...

If the function is declared as Jonathan's was:

Function MaxOfArray(vArray() as Variant) As Variant

it says that the argument coming from the main routine will be an
array of Variant variables, each containing one value. (Well,
technically each could itself contain an array, but we won't go
there.) The empty parentheses after vArray in this syntax mean "the
argument will be an array of Variant elements, but I don't know how
many elements it will have; that information will come from the call
in the main routine at run time".

When the function starts executing, the interpreter knows how many
elements were passed in the argument; that's why you can use the
LBound() and UBound() functions on it.

Your function below, in contrast, defines the argument as a single
Variant variable that (presumably) contains an array:

Function MaxOfArray(vArray As Variant) As Long

Inside the function, the LBound() and UBound() functions give you the
bounds of the array that's in that single variable.

****

Here's how you would have to call Jonathan's function. Notice the
different manner of assigning values to the array in the main routine;
the Array() function won't work here.

Sub CallMacro()
Dim myArray(2) As Variant
Dim oMaxValue As Variant

myArray(0) = 3
myArray(1) = 7
myArray(2) = 13
oMaxValue = MaxOfArray(myArray)
MsgBox oMaxValue
End Sub

Function MaxOfArray(vArray() As Variant) As Variant
Dim iStart As Long
Dim iEnd As Long
Dim vMax As Variant
Dim i As Long

iStart = LBound(vArray)
iEnd = UBound(vArray)
vMax = vArray(iStart)
For i = iStart + 1 To iEnd
If vArray(i) > vMax Then vMax = vArray(i)
Next i
MaxOfArray = vMax
End Function

****

As a separate issue, you should change your functions so that the
return value (the last As clause in the Function line) and the vMax
variable are Variants, as in Jonathan's code. That way, the functions
can be called from other main routines that assign data types other
than Long to the argument array. For example, Jonathan's function will
return the correct maximum value when called by this routine:

Sub CallMacro2()
Dim myArray(2) As Variant
Dim oMaxValue As Variant

myArray(0) = 3.14159
myArray(1) = 0.75
myArray(2) = 1.3333
oMaxValue = MaxOfArray(myArray)
MsgBox oMaxValue
End Sub

Your function, though, assigns a value such as 3.14159 to vMax as
Long, which truncates it to just 3 and returns that. Your function
operates correctly only if the main routine passes it an array of Long
values to begin with.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Greg Maxey]

Jezebel,

Thanks for the reply. I am learning slowly, but sometimes I think the
confusion goes from bad to worse.

I got Jonathan's code to work with:

Sub CallMacro()
Dim myArray() As Variant
myArray = Array(1, 99.99, -34.3, 50)
MsgBox MaxOfArray(myArray)
End Sub

You said:

Empty parentheses mean that the variable is an array of unknown
dimensions. Within a procedure, it means you are going to use Redim()
at some point.

I didn't use a Redim() statement. So is myArray = Array(1, 99.99, -34.3,
50)
the same as Redim?

 

[Jezebel]

I didn't use a Redim() statement. So is myArray = Array(1, 99.99, -34.3,
50)
the same as Redim?

Yes. What you've now got is an array of variants. You code works in the
example you give, but it's risky because a variant array can contain
*anything* -- while your MaxOfArray function assumes that the array elements
can be evaluated numerically.

MyArray = Array(1, ThisDocument, "XXX")

etc

 

[Greg Maxey]

Jezebel,

OK, I understand that. Would you this one time just give this village idiot
the fish? Can you show me using my example how you would change it using
the Redim method to make it less risky. Thank you ever so much.

 

[Greg Maxey]

Jay,

Studying this now. Thanks for your time. I hope that by asking enough
questions that I will finally get it.

 

[Greg Maxey]

Jay,

You said the Array function won't work here. I agree that with the a value
in the () in the Dim statement it won't. However, if leave that blank I can
use the Array function like:

Sub CallMacro()
Dim myArray() As Variant
myArray = Array(3, 1.5, -13.32, 5000.34)
MsgBox MaxOfArray(myArray)
MsgBox MinOfArray(myArray)
End Sub

Is there hidden dangers here?

 

[Jay Freedman]

Jay,

You said the Array function won't work here. I agree that with the a value
in the () in the Dim statement it won't. However, if leave that blank I can
use the Array function like:

Sub CallMacro()
Dim myArray() As Variant
myArray = Array(3, 1.5, -13.32, 5000.34)
MsgBox MaxOfArray(myArray)
MsgBox MinOfArray(myArray)
End Sub

Is there hidden dangers here?

Yes, that works. And it has no more or less dangerous possibilities
than the code I showed you before.

The danger that Jezebel warned about is in both versions: a Variant
can contain *anything*. For example, replace your Array statement with

myArray = Array(3, "fred", -13.32, 5000.34)

and watch what happens. It runs, it doesn't throw any errors, and it
tells you that the maximum value is "fred". Now, I'm not sure
exactly how the greater-than operator decides whether a non-numeric
string is or isn't greater than a number, but clearly this isn't the
behavior you really want.

You'd see the same behavior if you took my code and changed one of the
assignments to something like

myArray(1) = "fred"

The solution is not necessarily to change the assignments in the main
routine, but to check each value in the Min and Max subroutines to
verify that it's numeric before using it:

If (IsNumeric(vArray(i))) And _
(vArray(i) > vMax) Then vMax = vArray(i)

This is a general rule for helping to create crash-proof software:
Never assume anything about the input data; always check everything.
For example, you may have heard about "buffer overruns" and how they
can be exploited by hackers. The cause of this problem is that a lot
of software simply assumed that the input will fit into the buffer
(memory area) that was allocated for it, but hackers figured out that
they could cause "unanticipated behavior" by feeding in strings that
are too long to fit. The fix is to check the size of every input
before accepting it. This business of Variant values is similar: when
your data can be of any type, you need to verify that it actually has
the type you want.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Greg Maxey]

Thanks Jay.

As long as you , Jonathan, Jezebel, and Doug and Tony (if he is a hanger)
and others stay around, I think I might get it someday. In the meantime it
is nice that you folks are around to give directions.

 

[Jezebel]

It's not the redim vs. array that affects the risk, but using an array of
variants where you really want an array of numbers

Dim myArray() as double

:
redim myArray(1 to 3)
myArray(1) = 1
myArray(2) = 99.99
myArray(3) = -34.3

:
pMax = MaxOfArray(myArray)

-------

Function MaxOfArray(vArray() as double) as double

:

 

[Greg Maxey]

Ok, got that. Thanks

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

It's not the redim vs. array that affects the risk, but using an
array of variants where you really want an array of numbers

Dim myArray() as double

redim myArray(1 to 3)
myArray(1) = 1
myArray(2) = 99.99
myArray(3) = -34.3

pMax = MaxOfArray(myArray)

Jezebel,

OK, I understand that. Would you this one time just give this
village idiot the fish? Can you show me using my example how you
would change it using the Redim method to make it less risky. Thank
you ever so much. --
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[/QUOTE]

 

[Greg Maxey]

Jonathan (Jezebel/Tony/Jay),

Thanks for all your time. I can't say that it is crystal clear, but at
least I have all of this material to reference should I brave to enter these
waters again in the future.

Thanks again.