I have a number of arrays that are populated with database values. I
need to determine which array has the highest ubound out of all the
arrays. The array size will always change based on the database
record. Therefore, I need to be able to throw the arrays into a
function that will automatically determine the highest ubound array.
I was wondering if anyone might have a clean function that can do
this. I have created a function that works but there must be a simple
and more intuitive way to do this.
Any help would be greatly appreciated.
My example
Private Sub Command1_Click()
'these will always vary
Dim array1(15)
Dim array2(20)
Dim array3(27)
Dim array4(37)
Dim array5(7)
Dim myfinalstring
myfinalstring = checkupper(array1, array2, array3, array4, array5)
MsgBox myfinalstring
End Sub
Public Function checkupper(ByRef array1() As Variant, ByRef array2()
As Variant, ByRef array3() As Variant, ByRef array4() As Variant,
ByRef array5() As Variant) As String
If UBound(array1) > UBound(array2) And UBound(array1) > UBound(array3)
And UBound(array1) > UBound(array4) And UBound(array1) >
UBound(array5) Then
checkupper = "array1 is the highest"
Exit Function
End If
If UBound(array2) > UBound(array1) And UBound(array2) > UBound(array3)
And UBound(array2) > UBound(array4) And UBound(array2) >
UBound(array5) Then
checkupper = "array2 is the highest"
Exit Function
End If
If UBound(array3) > UBound(array1) And UBound(array3) > UBound(array2)
And UBound(array3) > UBound(array4) And UBound(array3) >
UBound(array5) Then
checkupper = "array3 is the highest"
Exit Function
End If
If UBound(array4) > UBound(array1) And UBound(array4) > UBound(array2)
And UBound(array4) > UBound(array3) And UBound(array4) >
UBound(array5) Then
checkupper = "array4 is the highest"
Exit Function
End If
If UBound(array5) > UBound(array1) And UBound(array5) > UBound(array2)
And UBound(array5) > UBound(array3) And UBound(array5) >
UBound(array4) Then
checkupper = "array5 is the highest"
Exit Function
End If
End Function