Find smallest/largest values

Summary

Loop across some records. Put an If in the loop, comparing the current value with the smallest/largest so far.

Situation

You have some records in a Range, or a set of arrays.

Needs

You want the smallest and/or largest value(s) for a field in the records.

Provides

Smallest/largest values.

Action

Here's an example for the smallest, from array data:

  • Dim sMinimumWeight as Single
  • ...
  • sMinimumWeight = 3.4028235E+38
  • For sCurrentRecord = 1 To sNumberRecords
  •     If asGoatWeights(sCurrentRecord) < sMinimumWeight Then
  •         minimumWeight = asGoatWeights(sCurrentRecord)
  •     End If
  • Next sCurrentRecord
  • MsgBox "Minimum weight: " & sMinimumWeight

sMinimumWeight is initialized to a very large value. Every value in the array will be smaller than that.

For a Range, do the same, except that the data comes from a cell in the Range. Here's code that gets the minimum and maximum from a Range:

  • sMinimumWeight = 3.4028235E+38
  • sMaximumWeight = -3.4028235E+38
  • ...
  • For sCurrentRecord = 1 to sNumberRecords
  •     sWeight = rGoatDataTable.Cells(sCurrentRecord, FIELD_WEIGHT)
  •     If sWeight < sMinimumWeight Then
  •         sMinimumWeight = sWeight
  •     End If
  •     If sWeight > sMaximumWeight Then
  •         sMaximumWeight = sWeight
  •     End If
  • Next sCurrentRecord

You could get other fields, like a goat name, as well:

  • sMinimumWeight = 3.4028235E+38
  • tMinimumWeightGoatName = ""
  • sMaximumWeight = -3.4028235E+38
  • tMaximumWeightGoatName = ""
  • ...
  • For sCurrentRecord = 1 to sNumberRecords
  •     tName = rGoatDataTable.Cells(sCurrentRecord, FIELD_NAME)
  •     sWeight = rGoatDataTable.Cells(sCurrentRecord, FIELD_WEIGHT)
  •     If sWeight < sMinimumWeight Then
  •         sMinimumWeight = sWeight
  •         tMinimumWeightGoatName = tName
  •     End If
  •     If sWeight > sMaximumWeight Then
  •         sMaximumWeight = sWeight
  •         tMaximumWeightGoatName = tName
  •     End If
  • Next sCurrentRecord
Where referenced