Max/Min Functions

[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

 

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

 

[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

 

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

 

[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

 

[Klaus Linke]

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]

Thanks Klause, I will look into this further.

 

[Greg Maxey]

Ok, Thanks.

 

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

 

[Jean-Guy Marcil]

Tony Jollans was telling us:
Tony Jollans nous racontait que :

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!)

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org