Finding Smallest and Largest Value with VBA. Using ParamArray to find Minimum Value.
Related Links:
———————————————————————————————–
Contents:
Determine smallest value in range
Determine largest value in range
Determine smallest value in each non-blank row and display message mentioning row no. and the value
Determine smallest value in range, highlight and return its address
Determine Minimum Value from a List
Determine Maximum Value from a List
Determine Minimum Value from a Parameter Array
———————————————————————————————–
Determine smallest value in range
Sub Smallest()
‘Cells with dates also return a value, and get covered for determining smallest value. Percentages will convert and return numerics.
Dim rng As Range
Dim dblMin As Double
‘Set range from which to determine smallest value
Set rng = Sheet1.Range(“A1:Z100”)
‘Worksheet function MIN returns the smallest value in a range
dblMin = Application.WorksheetFunction.Min(rng)
‘Displays smallest value
MsgBox dblMin
End Sub
Determine largest value in range
Sub Largest()
‘Cells with dates also return a value, and get covered for determining largest value. Percentages will convert and return numerics.
Dim rng As Range
Dim dblMax As Double
‘Set range from which to determine largest value
Set rng = Sheet1.Range(“A1:Z100”)
‘Worksheet function MAX returns the largest value in a range
dblMax = Application.WorksheetFunction.Max(rng)
‘Displays largest value
MsgBox dblMax
End Sub
Determine smallest value in each non-blank row and display message mentioning row no. and the value.
Sub rowSmallest()
‘Cells with dates also return a value, and get covered for determining smallest value. Percentages will convert and return numerics.
Dim rng As Range
Dim currentRow As Long
Dim dblMin As Double
Dim lastRow As Long
‘Determines the last used row number in worksheet
lastRow = Sheet1.UsedRange.Row – 1 + Sheet1.UsedRange.Rows.Count
For currentRow = 1 To lastRow
Set rng = Sheet1.Rows(currentRow)
‘Checks for empty rows provided there are no formulas (including =”” ) or spaces present in any of the cells
If WorksheetFunction.CountA(rng) = 0 Then
MsgBox “Row ” & currentRow & ” is blank.”
Else
dblMin = Application.WorksheetFunction.Min(rng)
MsgBox “The smallest value in row ” & currentRow & ” is ” & dblMin
End If
Next currentRow
End Sub
Determine smallest value in range, highlight and return its address
Sub Smallest_Value_Highlight_Address()
‘Determines smallest value in range, highlights it and returns its address
‘Cells with dates also return a value, and get covered for determining smallest value. Percentages will convert and return numerics.
‘Determines values from the active worksheet
Dim strData As String
Dim rng As Range
Dim vValue As Variant
Dim rngCol As Range
Dim lngRow As Long
Dim rngAdd As Range
‘Enter desired range in which to find the smallest value
strData = “A1:Z100“
Set rng = Range(strData)
‘Determines smallest value in range
vValue = Application.WorksheetFunction.Min(rng)
For Each rngCol In rng.Columns
‘Determines in case the smallest value exists in a particular column
If Application.WorksheetFunction.CountIf(rngCol, vValue) > 0 Then
‘Returns row number of the smallest value, in the column which has the same
lngRow = Application.WorksheetFunction.Match(vValue, rngCol, 0)
‘Returns cell address of the smallest value
Set rngAdd = rngCol.cells(lngRow, 1)
‘Selects smallest value to highlight with color
rngAdd.Select
With Selection
.Interior.Color = RGB(255, 255, 0)
End With
‘Message displays the searched range, smallest value, and its address
MsgBox “Smallest Value in Range(“”” & strData & “””) is ” & vValue & “, in Cell ” & rngAdd.Address & “.”
Exit Sub
End If
Next
End Sub
————————————————————————————————————————————————————-
‘ParamArray (Parameter Array): It is not possible to call a procedure with more arguments than the procedure declaration specifies. VBA allows use of optional parameters but you have to know the number of elements in the array ahead of time, when you define the procedure. The ParamArray keyword lets you pass in any number of values. The function receives them as an array. The ParamArray argument makes it possible for a procedure (a function or a subroutine) to accept an arbitrary number of arguments, each of a possibly different type (by using a Variant).
————————————————————————————————————————————————————-
Determine Minimum Value from a List
Function MinInList(ParamArray ArrayList() As Variant)
‘Function will return the minimum value from a list of values
Dim n As Integer
Dim iValue As Variant
‘Set the variable iValue – initialize to the first item or value in list.
iValue = ArrayList(0)
‘Checks each item or value in the list to find the smallest.
‘The UBound function is used with the LBound function to determine the size of an array. Use the LBound function to find the lower limit of an array dimension. Since array subscripts start at 0, the length of a dimension is greater by one than the highest available subscript for that dimension. The largest available subscript for the indicated dimension of an array can be obtained by using the Ubound function.
For n = 0 To UBound(ArrayList)
‘Determines the smallest value.
If ArrayList(n) < iValue Then
iValue = ArrayList(n)
End If
Next n
MinInList = iValue
End Function
Sub SmallestValueInList()
‘Returns minimum value from a List – Calls Function MinInList.
‘Cells(16, 5) contains -308, Range(“B13”) contains -400 and Range(“D19”) contains the date “2/1/2011”. Value returned is -400, being the smallest.
MsgBox MinInList(1, -5, 3, -8, -9, hello, 10 * -1, cells(16, 5), Range(“B13”), Range(“D19”))
‘Range(“D19”) contains the date “2/1/2011”, Range(“H8”) contains the date “3/5/2010” and Range(“I10”) contains the date “3/5/2009”. Date returned is “3/5/2009”, being the smallest.
MsgBox MinInList(Range(“D19”), Range(“H8”), Range(“I10”))
End Sub
Determine Maximum Value from a List
Function MaxInList(ParamArray ArrayList() As Variant)
‘Function will return the maximum value from a list of values
Dim n As Integer
Dim iValue As Variant
‘Set the variable iValue – initialize to the first item or value in list.
iValue = ArrayList(0)
‘Checks each item or value in the list to find the largest.
For n = 0 To UBound(ArrayList)
‘Determines the largest value.
If ArrayList(n) > iValue Then
iValue = ArrayList(n)
End If
Next n
MaxInList = iValue
End Function
Sub LargestValueInList()
‘Returns maximum value from a List- Calls Function MaxInList.
‘Range(“K7”) contains 3000. Value returned and displayed in message box is 3000, being the largest.
MsgBox MaxInList(1, -5, 3, -8, -9, hello, 10 * -1, Range(“K7”))
‘Range(“D19”) contains the date “2/1/2011”, Range(“H8”) contains the date “3/5/2010” and Range(“I10”) contains the date “3/5/2009”. Date returned is “2/1/2011”, being the largest.
MsgBox MaxInList(Range(“D19”), Range(“H8”), Range(“I10”))
End Sub
Determine Minimum Value from a Parameter Array (also works for nested array or a multiple column range)
Function minimum(ParamArray Values() As Variant)
‘Returns minimum value from a Parameter Array (also works for nested array or a multiple column range).
Dim Item As Variant
Dim Part As Variant
For Each Item In Values
‘Checks if an item in the array of Values is itself an array (viz. nested array) and determines minimum value therein
If IsArray(Item) Then
For Each Part In Item
minimum = minimum(Part, minimum)
Next
‘If an item in the array of Values is not an array
Else
If Not IsEmpty(minimum) Then
If Item < minimum And Not IsEmpty(Item) Then
minimum = Item
End If
Else
minimum = Item
End If
End If
Next
End Function
Sub SmallestValue()
‘Returns minimum value from a Parameter Array (also works for nested array or a multiple column range).
‘Returns -25.
MsgBox minimum(Array(11, 20, -16), -14, hello, -18.5, Array(1, Array(1 * -25, -21, -1), -11))
‘Returns 11.
MsgBox minimum(16.5, 11, 20)
‘Returns -7700, smallest value in range which is in cell “B27”.
MsgBox minimum(Range(“A1:Z100”))
‘Cells(16, 5) contains -308, Range(“B13”) contains -400 and Range(“D19”) contains the date “2/1/2011”. Value returned is -400, being the smallest.
MsgBox minimum(1, -5, 3, -8, -9, hello, 10 * -1, cells(16, 5), Range(“B13”), Range(“D19”))
‘Range(“D19”) contains the date “2/1/2011”, Range(“H8”) contains the date “3/5/2010” and Range(“I10”) contains the date “3/5/2009”. Date returned is “3/5/2009”, being the smallest.
MsgBox minimum(Range(“D19”), Range(“H8”), Range(“I10”))
End Sub