Is there a NaN in VBA?

bilokon · February 15th, 2008, 07:40 AM

In fact, there is a way to use the Excel formula #N/A value in VBA.

You can achieve this by using the Variant type and the following function:

Function NaN() As Variant
NaN = CVErr(xlErrNA)
End Function

For more information, and also to find out how to check for these #N/A values, please refer to:

http://www.bilokon.co.uk/finance/ind..._values_in_VBA

Hope this helps.

Regards,
Paul

Nickledeg · March 6th, 2016, 08:40 AM

Here is code to create all special value types for a double

Code:

Option Explicit

Public Declare Sub CopyMemory Lib "kernel32" Alias _
    "RtlMoveMemory" (destination As Any, source As Any, _
    ByVal length As Long)


Public Function PosQNaN() As Double
    'returns positive qnan
    Dim Oput As Double
    Dim l(1 To 2) As Long
    l(1) = 0
    l(2) = &H7FF80000
    CopyMemory Oput, l(1), 8
    PosQNaN = Oput
End Function

Public Function NegQNaN() As Double
    'returns negative qnan
    Dim Oput As Double
    Dim l(1 To 2) As Long
    l(1) = 1
    l(2) = &HFFF80000
    CopyMemory Oput, l(1), 8
    NegQNaN = Oput
End Function

Public Function Indeterminate() As Double
    'returns an indeterminate
    Dim Oput As Double
    Dim l(1 To 2) As Long
    l(1) = 0
    l(2) = &HFFF80000
    CopyMemory Oput, l(1), 8
    Indeterminate = Oput
End Function

Public Function PosInfinity() As Double
    'returns a positive infinity
    Dim Oput As Double
    Dim l(1 To 2) As Long
    l(1) = 0
    l(2) = &H7FF00000
    CopyMemory Oput, l(1), 8
    PosInfinity = Oput
End Function

Public Function NegInfinity() As Double
    'returns a negative infinity
    Dim Oput As Double
    Dim l(1 To 2) As Long
    l(1) = 0
    l(2) = &HFFF00000
    CopyMemory Oput, l(1), 8
    NegInfinity = Oput
End Function