CODEWITHSUNDEEP
excelvba
jmaz
jmaz

Reputation: 527

Pass Array to Function That Returns the Minimum Value in the Array

Note: 1) I don't want to use Application.Worksheetfunction.Min; 2) I want to reject array elements that are not vbDouble; 3) I do not know ahead of time how many elements are in the passed array

Public Function getArrayMin(ByRef passedArray() As Double) As Double
'return min of valid numbers in an array

Dim element As Variant
Dim i As Integer

getArrayMin = 1E+25

i = 0
For Each element In passedArray
    If VarType(element) = vbDouble Then
        i = i + 1
        If element < passedArray(i) Then
            getArrayMin = passedArray(i)
        End If
    End If
Next

End Function

JNevill's edited version, which still does not work:

Function getArrayMin(passedArray() As Double) As Double
Dim minVal As Double
Dim element As Variant

minVal = 1E+25

For Each element In passedArray
    If element < minVal Or element = passedArray(0) Then
        minVal = element
    End If
Next element

getArrayMin = minVal
End Function

Upvotes: 0

Views: 252

Answers (2)

jmaz
jmaz

Reputation: 527

Figured it out:

Public Function getArrayMin(passedRange As range) As Double
'return min of valid numbers in an array

Dim myarray() As Variant
Dim cell As Variant
Dim i As Integer

myarray = passedRange.value

getArrayMin = 1E+25

For Each cell In passedRange
    If VarType(cell) = vbDouble Then
        If getArrayMin > cell Then
            getArrayMin = cell
        End If
    End If
Next

End Function

Upvotes: 0

JNevill
JNevill

Reputation: 50034

i = 0
For Each element In passedArray
    If VarType(element) = vbDouble Then
        i = i + 1
        If cell < passedArray(i) Then
            passedArray(i) = element
        End If
    End If
Next

This seems odd. You are looping through elements in the array, testing it to see if the next element is larger, and if the next element is larger you set the next element to the current element. Then you do it again.

So... after this runs, the "min" should theoretically be in passedArray(ubound(passedArray)).

I guess that will work, but it feels odd because it blows away your array and fills it with intermediate garbage except for the first and last elements.

The gotcha is that you don't have a next element once you hit the uBound of your array. That last element is going to cause problems, but you don't really need to analyze it since it will contain the min already. To get around the error this will cause you could change that if statement to be:

 If VarType(element) = vbDouble and i < uBound(passedArray) Then

At the end of your function just stick in the following so that your function returns a value. 

 getArrayMin=passedArray(uBound(passedArray)).

You could also not have it return anything since you used byRef, but it means after you call this function, you would need to get the last element, so it would look like:

getArrayMin(myArray)
minOFArray=myArray(Ubound(myArray))

Just keep in mind that you are using byRef and that your original array that you pass to the function is going to be blown away by the function rewriting all the elements. Personally, I would use byVal and insure that the function returns a min.

As a total rewrite, I would do something like this instead:

Function getArrayMin(passedArray AS Double) as Double
    Dim minVal as double
    Dim element as variant

    For each element in passedArray
        if element < minVal or element = passedArray(0) Then minVal = element
    Next element

    getArrayMin = minVal
End Function

It's more concise and there is no monkeying around with your array. I didn't check for a double since the passedarray parameter is already declared as a double. It shouldn't allow a string or some other element through.

Lastly, the rewrite above will give you a jumping off point if your logic needs to get more complicated. But the easiest way to do what your doing would be to just use Application.Min():

getArrayMin = Application.Min(passedArray)

Upvotes: 1

Related Questions