Well, I don't know about the serious thinking part
of it, but here's where I was headed...
Function BiggestThing(a As Variant, b As Variant, Optional c As
Variant, Optional d As Variant, _
Optional e As Variant, Optional f As Variant, Optional g As Variant,
Optional h As Variant, _
Optional i As Variant, Optional j As Variant, Optional k As Variant,
Optional l As Variant, _
Optional m As Variant, Optional n As Variant, Optional o As Variant,
Optional p As Variant, _
Optional q As Variant, Optional r As Variant, Optional s As Variant,
Optional t As Variant) As Variant
'************************************************* ****************************************
' Idea here is to accept up to 20 values and return the biggest one,
' using the default sorting method set up in Access.
'************************************************* ****************************************
On Error GoTo Err_BiggestThing
100 Dim arr1 As Variant
120 Dim i2 As Integer, j2 As Integer, k2 As Integer
140 arr1 = Array(a, b, c, d, e, f, g, h, i, j, l, m, n, o, p, q, r, s,
t)
'160 i2 = arr1(0)
180 j2 = arr1(0)
200 For k2 = 1 To UBound(arr1)
220 If IsNull(arr1(k2)) Then Exit For
240 If arr1(k2) j2 Then j2 = arr1(k2)
260 Next
280 Debug.Print j2
It was kind-a-workin' ... But in the loop, when I
encountered the first Null array value - for some
reason the Exit For is not catching it. Can you
figure why?
I'm gonna have a look now at both your suggestion
and Allens. Thx 4 the feedback.
On Thu, 17 Jul 2008 07:10:33 -0700, JvC <jo******@earthlink.net>
wrote:
>Put the values in an array, and bubble sort it. I found the following
BubbleSort routine online. ToSort is your array of values. You may find
one you like more!
Sub BubbleSort(ToSort As Variant, Optional SortAscending As Boolean =
True)
' Chris Rae's VBA Code Archive - http://chrisrae.com/vba
' By Chris Rae, 19/5/99. My thanks to
' Will Rickards and Roemer Lievaart
' for some fixes.
Dim AnyChanges As Boolean
Dim BubbleSort As Long
Dim SwapFH As Variant
Do
AnyChanges = False
For BubbleSort = LBound(ToSort) To UBound(ToSort) - 1
If (ToSort(BubbleSort) ToSort(BubbleSort + 1) And
SortAscending) _
Or (ToSort(BubbleSort) < ToSort(BubbleSort + 1) And Not
SortAscending) Then
' These two need to be swapped
SwapFH = ToSort(BubbleSort)
ToSort(BubbleSort) = ToSort(BubbleSort + 1)
ToSort(BubbleSort + 1) = SwapFH
AnyChanges = True
End If
Next BubbleSort
Loop Until Not AnyChanges
End Sub
After serious thinking MLH wrote :
>Is there any built-in FN for returning largest value
in a group of say 2 - 10 values? Lets say I want
a FN something like MaxVal(5, 7, 9, 3, 12) or
perhaps MaxVal(79,34). I would wanna see
return value of 12 and 79 respectively.
What's simplest way to do that in A97?
