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

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[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/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by 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

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[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
--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[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/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by 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
--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[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/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by 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

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[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
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by 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

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[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

Post by 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
"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
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by 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

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[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

Post by Jezebel
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?
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by 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

Post by 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
"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
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by 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

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[Jezebel]

Post by Greg Maxey
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/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by Jezebel

Post by Greg Maxey
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

[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

Post by 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/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by Jezebel

Post by Greg Maxey
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]

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

Post by 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

Post by 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/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by Jezebel

Post by Greg Maxey
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

[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 FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

On Sat, 3 Dec 2005 10:57:19 -0500, "Greg Maxey"

Post by 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
"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

[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/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by Jonathan West
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
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,
"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...
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
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
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.

Post by 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
"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

[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?
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by Jonathan West
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
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,
"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...
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
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
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.

Post by 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
"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

[Jay Freedman]

On Sat, 3 Dec 2005 21:28:14 -0500, "Greg Maxey"

Post by 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
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 FAQ: http://word.mvps.org
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.
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by Jay Freedman
On Sat, 3 Dec 2005 21:28:14 -0500, "Greg Maxey"

Post by 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
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
If (IsNumeric(vArray(i))) And _
(vArray(i) > vMax) Then vMax = vArray(i)
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.

[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...

Post by 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
"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...

Post by Greg Maxey
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.

Post by Greg Maxey
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.

Post by Greg Maxey
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.

Post by Greg Maxey
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)

[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.
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by 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...

Post by 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
"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...

Post by Greg Maxey
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.

Post by Greg Maxey
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.

Post by Greg Maxey
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.

Post by Greg Maxey
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)
--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

[Klaus Linke]

Hi Greg,

If you need to access the maximum and minimum values in an array often, you
might also think about using a sorted collection instead:
http://www.vb-helper.com/howto_sorted_collection.html

Regards,
Klaus

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[Greg Maxey]

Klaus,

Thanks for the link and information. I am not sure I know how to use it.
Would you happen to have sample of code that shows how a list of numbers is
fed into this process and the how to get the max and min value?

Where would the various pieces of code be put in the Project?

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

Post by Jonathan West
Hi Greg,
If you need to access the maximum and minimum values in an array
http://www.vb-helper.com/howto_sorted_collection.html
Regards,
Klaus

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[Klaus Linke]

You'd download the class module, add it to your project from the VBA file
menu (SortedCollection.CLS).

The class module is designed to work with strings. I'd prefer variants, so
that you can use it for numbers as well:
Just open the class module in the project explorer, and replace "String"
with "Variant" (two times).

(If you mix numbers and strings, the sorting will be "funny" though, so use
with care...)

Then you can use the class in some macro:

Dim mySC As New SortedCollection
Dim myVar As Variant
mySC.AddItem (17)
mySC.AddItem (12)
mySC.AddItem (123)
mySC.AddItem (5)
' Min item = first item:
Debug.Print mySC.Item(1) ' prints 5
' Max item = last item:
Debug.Print mySC.Item(mySC.Count) ' prints 123
mySC.Remove (1) ' removes the 5
For Each myVar In mySC
Debug.Print myVar
Next myVar
Set mySC = Nothing

Regards,
Klaus

Post by Greg Maxey
Klaus,
Thanks for the link and information. I am not sure I know how to use it.
Would you happen to have sample of code that shows how a list of numbers
is fed into this process and the how to get the max and min value?
Where would the various pieces of code be put in the Project?
Thanks.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by Jonathan West
Hi Greg,
If you need to access the maximum and minimum values in an array
http://www.vb-helper.com/howto_sorted_collection.html
Regards,
Klaus

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[Greg Maxey]

Thanks Klause, I will look into this further.
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by Klaus Linke
You'd download the class module, add it to your project from the VBA
file menu (SortedCollection.CLS).
The class module is designed to work with strings. I'd prefer
Just open the class module in the project explorer, and replace
"String" with "Variant" (two times).
(If you mix numbers and strings, the sorting will be "funny" though,
so use with care...)
Dim mySC As New SortedCollection
Dim myVar As Variant
mySC.AddItem (17)
mySC.AddItem (12)
mySC.AddItem (123)
mySC.AddItem (5)
Debug.Print mySC.Item(1) ' prints 5
Debug.Print mySC.Item(mySC.Count) ' prints 123
mySC.Remove (1) ' removes the 5
For Each myVar In mySC
Debug.Print myVar
Next myVar
Set mySC = Nothing
Regards,
Klaus

Post by Greg Maxey
Klaus,
Thanks for the link and information. I am not sure I know how to
use it. Would you happen to have sample of code that shows how a
list of numbers is fed into this process and the how to get the max
and min value? Where would the various pieces of code be put in the
Project?
Thanks.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by Jonathan West
Hi Greg,
If you need to access the maximum and minimum values in an array
http://www.vb-helper.com/howto_sorted_collection.html
Regards,
Klaus

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[Tony Jollans]

If you want a whole array sorted you can use the old WordBasic sort ... but
it requires 'proper' arrays, it doesn't work on Variants ...

Sub SortedArray()
Dim myArray(2)
myArray(0) = 3
myArray(1) = 1
myArray(2) = 13
WordBasic.SortArray myArray
MsgBox "Min = " & myArray(LBound(myArray))
MsgBox "Max = " & myArray(UBound(myArray))
End Sub

--
Enjoy,
Tony

Post by Jonathan West
Hi Greg,
If you need to access the maximum and minimum values in an array often, you
http://www.vb-helper.com/howto_sorted_collection.html
Regards,
Klaus

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[Greg Maxey]

Tony,

Here I go again getting confused.

What do you mean by a "proper" array as compared to a variant?
Since it works just as well with either:

Sub SortedArray1()
Dim myArray()
myArray = Array(12, 45, 0.678, 3456)
WordBasic.SortArray myArray
MsgBox "Min = " & myArray(LBound(myArray))
MsgBox "Max = " & myArray(UBound(myArray))
End Sub

or

Sub SortedArray2()
Dim myArray
myArray = Array(12, 45, 0.678, 3456)
WordBasic.SortArray myArray
MsgBox "Min = " & myArray(LBound(myArray))
MsgBox "Max = " & myArray(UBound(myArray))
End Sub

I take it myArray() and myArray both represent "proper" arrays. ???
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by Tony Jollans
If you want a whole array sorted you can use the old WordBasic sort
... but it requires 'proper' arrays, it doesn't work on Variants ...
Sub SortedArray()
Dim myArray(2)
myArray(0) = 3
myArray(1) = 1
myArray(2) = 13
WordBasic.SortArray myArray
MsgBox "Min = " & myArray(LBound(myArray))
MsgBox "Max = " & myArray(UBound(myArray))
End Sub

Post by Jonathan West
Hi Greg,
If you need to access the maximum and minimum values in an array
http://www.vb-helper.com/howto_sorted_collection.html
Regards,
Klaus

Post by 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
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.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

[Klaus Linke]

Post by Greg Maxey
What do you mean by a "proper" array as compared to a variant?

Something like this:

Dim myArray As Variant
Dim i As Long
myArray = Split("12/45/0.678/3456", "/")
For i = LBound(myArray) To UBound(myArray)
myArray(i) = Val(myArray(i))
Next i
WordBasic.SortArray myArray
MsgBox "Min = " & myArray(LBound(myArray))
MsgBox "Max = " & myArray(UBound(myArray))

:-) Klaus

[Greg Maxey]

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

Post by Klaus Linke

Post by Greg Maxey
What do you mean by a "proper" array as compared to a variant?

Dim myArray As Variant
Dim i As Long
myArray = Split("12/45/0.678/3456", "/")
For i = LBound(myArray) To UBound(myArray)
myArray(i) = Val(myArray(i))
Next i
WordBasic.SortArray myArray
MsgBox "Min = " & myArray(LBound(myArray))
MsgBox "Max = " & myArray(UBound(myArray))
:-) Klaus

[Tony Jollans]

Hi Greg,

What I meant by a 'proper' array was something explicitly declared as an
array - and not a variant which later happened to be holding an array.

Of the two routines you posted and the one Klaus posted, the only one to
actually sort the array and give the right result is your first one - with
the "Dim vArray()" declaration - displaying 0 as the minimum - the other two
display 12 (the first element in the unsorted array).

--
Enjoy,
Tony

Post by Klaus Linke

Post by Greg Maxey
What do you mean by a "proper" array as compared to a variant?

Dim myArray As Variant
Dim i As Long
myArray = Split("12/45/0.678/3456", "/")
For i = LBound(myArray) To UBound(myArray)
myArray(i) = Val(myArray(i))
Next i
WordBasic.SortArray myArray
MsgBox "Min = " & myArray(LBound(myArray))
MsgBox "Max = " & myArray(UBound(myArray))
:-) Klaus

[Jean-Guy Marcil]

Post by Jonathan West
Hi Greg,
What I meant by a 'proper' array was something explicitly declared as
an array - and not a variant which later happened to be holding an
array.

The "confusing" part is that the explicitely declared array as to be of the
Variant type... ;-)

(or, it could be a string - if you do not use Array to build the array - but
then you would get unexpected results!)