vba initialize or detect NaN, +infinity, -infinity, special values code example

Example: excel vba initialize or detect NaN, +infinity, -infinity, special values

'VBA function to create and detect IEEE 754 Floating-Point Arithmetic
'special values in the Double data type.

'To create a special value:
MsgBox SpecialVal("PosQNaN")		 'displays: 1.#QNAN
MsgBox SpecialVal("NegMin")			 'displays: -1.79769313486232E+308

'To detect if a Double variable is a special value:
MsgBox SpecialVal("NegINaN", dblVal) 'displays: True or False

'--------------------------------------------------------------------
'List of supported special value types:
'PosINFI, NegINFI, PosQNaN, NegQNaN, NegINaN
'NegMin, NegMax, PosMin, PosMax
'--------------------------------------------------------------------

'Place the following into a new standard code module:

Option Explicit

Type i4
  a%(1 To 4)
End Type

Type d1
  d As Double
End Type

Function SpecialVal(typ, Optional n)
    Dim a As d1, b As i4, s
    s = SpecialsMap(typ)
    If IsEmpty(s) Then SpecialVal = "Unrecognized type: '" & typ & "'.": Exit Function
    If Not IsMissing(n) And IsNumeric(n) = 0 Then SpecialVal = "Unrecognized type: '" & n & "'.": Exit Function
    SpecialVal = False
    If IsMissing(n) Then
        b.a(4) = s(1): b.a(1) = s(3)
        If UBound(s) > 3 Then
            b.a(3) = s(2)
            b.a(2) = s(3)
            b.a(1) = s(4)
        End If
        LSet a = b
        SpecialVal = a.d
    Else
        a.d = n: LSet b = a
        If b.a(4) >= s(1) And b.a(4) <= s(2) Then
            If UBound(s) > 3 Then If b.a(3) >= s(2) And b.a(2) <= s(3) And b.a(1) <= s(4) Then SpecialVal = True: Exit Function
            SpecialVal = True
        End If
    End If
End Function
  
Function SpecialsMap(typ)
    Dim e, t, v
    Const MAP1 = "PosINFI:7FF0:7FF0:0|NegINFI:FFF0:FFF0:0|PosQNaN:7FF8:7FFF:0|NegQNaN:FFF8:FFFF:1|NegINaN:FFF8:FFF8:0|"
    Const MAP2 = "NegMin:FFEF:FFFF:FFFF:FFFF|NegMax:8000:8000:1|PosMin:0:0:1|PosMax:7FEF:FFFF:FFFF:FFFF"
    t = LCase(typ)
    For Each e In Split(LCase(MAP1 & MAP2), "|")
    If InStr(e, t) Then
            v = Split(e, ":"): v(1) = val("&H" & v(1)): v(2) = val("&H" & v(2))
            If UBound(v) > 3 Then
                v(3) = val("&H" & v(3)): v(4) = val("&H" & v(4))
            End If
            Exit For
        End If
    Next
    SpecialsMap = v
End Function

'--------------------------------------------------------------------

'Reference:
'    https://devblogs.microsoft.com/oldnewthing/20130221-00/?p=5183    
'    https://babbage.cs.qc.cuny.edu/IEEE-754.old/IEEE-754references.html
    
'Notes:	VBA cannot create or detect the following special values:	
'		Signaling NaN, either pos or neg. Causes Overflow error.
'		Positive Indefinite NaN. VBA returns a '1.#QNAN'. 
'		Negative Zero. VBA instantly changes this to a normal zero.