Print

User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

 

Finding Smallest and Largest Value with VBA. Using ParamArray to find Minimum Value.

 

Related Links:

Remove Duplicates in a range, using "Find 'Smallest', 'Largest', 'K-th Smallest' and 'K-th Largest' Numbers in a Range, with Excel Functions.

 

-----------------------------------------------------------------------------------------------  

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