find the minimum value out of a list or array excluding certain elements

Question

• I have a list of variables that I need to find the minimum value of and evaluate as candidates to increase a different, related value by 1.
• However variables that are already equal to 100 (as in percent) should not be considered as they cannot be increased beyond 100.

(determination variables are a's, increment values are b's)

a1 = 0.5,
a2 = 0.6,
a3 = 0.2,
myArray(0) = a1
myArray(1) = a2
myArray(2) = a3
b1 = 0,
b2 = 10,
b3 = 100

My current code

If b1 = 100 Then
myArray(0) = null (empty, something to remove it from consideration in min function)
End IF
If b2 = 100 Then
myArray(1) = null
End IF
If b3 = 100 Then
myArray(2) = null
End IF
minvalue = Application.WorksheetFunction.Min(myArray(0), myArray(1), myArray(2))
'also tried: minvalue = Application.WorksheetFunction.Min(myArray)
If minvalue = a1 Then
b1 = b1 +1
Else If minvalue = a2 Then
b2 = b2 +1
Else If minvalue = a3 Then
b3 = b3 +1
End IF

I want the code to note that even though a3 is the smallest value, b3 cannot be increased anymore, so the next lowest value is a1, and as such b1 is increased by 1.

This functionality does work in the regular excel spreadsheet. I can make a list of values and then in the cell below them type: =Min(A1,B1,C1) where A1 = a1 in my example, and B2 = a2 in my example above, etc. and if I type Null in C1, the min function equates to a1 without problem.

However, when I try to do the same thing in vba, minvalue just keeps equating to 0 whenever an element in myArray is Null.

I have also looked into attempting to manually determine the minimum value with loops, but it is very ugly and I would like to keep the code as clean as possible.

Thanks in advance for your help.

Answer 1

You can use Filter to remove values from your array.

In this case Filter is used to remove the 100 values from the array, then EVALUATE is used to take the miniumum of the remaining array

Sub Test2()
Dim MyArray()
MyArray = Array(900, 112, 100, 122, 196)
MsgBox Evaluate("Min(" & Join(Filter(MyArray, 100, False), ",") & ")")
End Sub

Answer 2

This uses loops but I would consider this fairly clean code.

Sub test()

    Dim a(1 To 3) As Double
    Dim b(1 To 3) As Double
    Dim check(1 To 3) As Boolean
    Dim lowestIndex As Integer
    Dim lowestValue As Double

    Dim i As Integer
    Dim j As Integer

    a(1) = 0.5
    a(2) = 0.6
    a(3) = 0.2

    b(1) = 0
    b(2) = 10
    b(3) = 100


    'initialize to  everything initially
    For i = 1 To UBound(a)
        If (b(i) >= 100) Then
            check(i) = False
        Else
            check(i) = True
        End If
    Next i


    Dim numbTimesToDoStuff As Integer
    numbTimesToDoStuff = 100

    'go through this process however many times you need
    For i = 1 To numbTimesToDoStuff
        'reset each iteration
        lowestValue = 99999

        'find minimum value and index each time
        For j = 1 To UBound(a)
            If (check(j) = True) Then
                If (a(i) < lowestValue) Then
                    lowestValue = a(i)
                    lowestIndex = i
                End If
            End If

        Next j

        'check if no values were found to be lowest and valid
        If (lowestValue = 99999) Then
            MsgBox ("Error: not checking any values!")
        End If

        'update appropriate "b"
        b(lowestIndex) = b(lowestIndex) + 1
        'check if you've reached 100 and stop future checks
        If (b(lowestIndex >= 100)) Then
            check(lowestIndex) = False
        End If


    Next i



End Sub