There has been many an occasion when I have wanted programmatic access to the maximum or minimum or smallest value of a data type. Some programming languages have built-in support through names like MaxInt. VBA, unfortunately, is not one of them.
I decided to “translate” the documentation defining the different data types into code. The functions Max{datatype}, Min{datatype}, and Smallest{datatype} return the appropriate value. The Max and Min functions should be self-evident. The Smallest function returns the smallest non-zero value supported by the data type. Since this is simply 1 for all data types associated with integers (Byte, Integer, Long, LongPtr, LongLong, and Currency), Smallest is defined only for data types associated with real numbers (Single, Double, and Decimal).
In cases where the documentation is incorrect, the code implements the correct limits (as in the case of the Double datatype). In cases where the software fails to return accurate results, the code makes the necessary correction (as in the case of the Decimal datatype).
The use of compile time constants ensures that the code will work correctly on versions of Office from Office 97 to Office 2010. In the case of Office 2010, it will work on both 32-bit and 64-bit installations.
Option Explicit
Function MaxByte() As Byte: MaxByte = 255: End Function
Function MinByte() As Byte: MinByte = 0: End Function
Function MaxInteger() As Integer: MaxInteger = 32767: End Function
Function MinInteger() As Integer: MinInteger = -32768: End Function
Function MaxLong() As Long: MaxLong = 2147483647: End Function
Function MinLong() As Long: MinLong = -2147483648#: End Function
#If Win64 Then
Function MaxLongLong() As LongLong
MaxLongLong = CLngLng("9,223,372,036,854,775,807")
End Function
Function MinLongLong() As LongLong
MinLongLong = CLngLng("-9,223,372,036,854,775,808")
End Function
#End If
#If VBA7 Then
Function MaxLongPtr() As LongPtr
MaxLongPtr = CLngPtr("&H7F" & String((Len(MaxLongPtr) - 1) * 2, "F"))
End Function
Function MinLongPtr() As LongPtr
MinLongPtr = CLngPtr("&H80" & String((Len(MinLongPtr) - 1) * 2, "0"))
End Function
#End If
Function MinSingle() As Single: MinSingle = -3.402823E+38: End Function
Function MaxSingle() As Single: MaxSingle = 3.402823E+38: End Function
Function SmallestSingle() As Single
SmallestSingle = 1.401298E-45
End Function
Function MinDouble() As Double
MinDouble = -1.79769313486231E+308
End Function
Function MaxDouble() As Double
MaxDouble = 1.79769313486231E+308
End Function
'The documentation incorrectly indicates the largest double _
is 1.79769313486232E308
Function SmallestDouble() As Double
SmallestDouble = 4.94065645841247E-324
End Function
Function MinCurrency() As Currency
MinCurrency = CCur("-922,337,203,685,477.5808 ")
End Function
Function MaxCurrency() As Currency
MaxCurrency = CCur("922,337,203,685,477.5807")
End Function
Function MaxDecimal(ByVal NbrDecimals As Byte) As Variant
'there is a bug in the code that handles decimal division so that _
even when the resulting value fits into a decimal data type, the _
software rounds the last digit. Hence, the correction below.
MaxDecimal = CDec("79,228,162,514,264,337,593,543,950,335")
If NbrDecimals > 0 Then
MaxDecimal = MaxDecimal / CDec(10) ^ CDec(NbrDecimals)
Dim Correction
Correction = CDec(0.5) / CDec(10) ^ (CDec(NbrDecimals) - 1)
MaxDecimal = MaxDecimal - Correction
End If
End Function
Function MinDecimal(ByVal NbrDecimals As Byte) As Variant
MinDecimal = -MaxDecimal(NbrDecimals)
End Function
Function SmallestDecimal()
SmallestDecimal = CDec("0.0000000000000000000000000001")
End Function
Sub testMinMax()
Debug.Print "Byte: " & MinByte & ", " & MaxByte
Debug.Print "Integer: " & MinInteger & ", " & MaxInteger
Debug.Print "Long: " & MinLong & ", " & MaxLong
#If VBA7 Then
Debug.Print "LongPtr: " & MinLongPtr & ", " & MaxLongPtr
#End If
#If Win64 Then
Debug.Print "LongLong: " & MinLongLong & ", " & MaxLongLong
#End If
Debug.Print "Single: " & MinSingle & ", " & MaxSingle _
& ", " & SmallestSingle()
Debug.Print "Double: " & MinDouble & " " & MaxDouble _
& ", " & SmallestDouble()
Debug.Print "Currency: " & MinCurrency & " " & MaxCurrency
Debug.Print "Decimal:" & SmallestDecimal
Dim I As Byte
For I = 0 To 28
Debug.Print Format(I, "00") & ", " & CStr(MinDecimal(I)) _
& ", " & CStr(MaxDecimal(I))
Next I
End Sub