Try this in the immediate pane: ? Val("11D11")

[MarkK]

I expected to get 11.

 

[arnelgp]

CDEC() AND CDBL() also give same result.

 

[Edgar_]

The speed of light:
?Val("3D8")

The electron charge
?Val("1.602D-18")

The D and the E are interchangeable there. Didn't know, but it's good to know.

In Val("11D11") it's doing 11*10^11
Allows calculations
?Val("11D11")*2
2200000000000

 

[gemma-the-husky]

The idiosyncrasies of Val don't help if you are trying to actually get a value normally from a string that might include such "formatting" characters though.

I don't suppose there is a way to force it to select numbers only.

 

[DickyP]

Basically you need to validate the input string, before applying to Val().

The only simple way I can think of is to iterate through the string using IsNumeric for each character and remove the char if it's not. This, of course, won't work for Octal or Hex values.

Simple example attached:

Public Function StripForVal(pstrIn As String) As String
    Dim strOutput As String
    Dim lngChar As Long
    Dim strChar As String * 1
    
    strOutput = vbNullString
    
    lngChar = 1
    
    Do While lngChar < Len(pstrIn) + 1
        strChar = Mid(pstrIn, lngChar, 1)
        If IsNumeric(strChar) Then
            strOutput = strOutput & strChar
        End If
        lngChar = lngChar + 1
    Loop
    StripForVal = strOutput
End Function

 

[Pat Hartman]

e and d are valid characters using scientific notation.

 

[DickyP]

I recommended the following:

Basically you need to validate the input string, before applying to Val().

The only simple way I can think of is to iterate through the string using IsNumeric for each character and remove the char if it's not. This, of course, won't work for Octal or Hex values.

Simple example attached:

Public Function StripForVal(pstrIn As String) As String
    Dim strOutput As String
    Dim lngChar As Long
    Dim strChar As String * 1
 
    strOutput = vbNullString
  
    lngChar = 1
  
    Do While lngChar < Len(pstrIn) + 1
        strChar = Mid(pstrIn, lngChar, 1)
        If IsNumeric(strChar) Then
            strOutput = strOutput & strChar
        End If
        lngChar = lngChar + 1
    Loop
    StripForVal = strOutput
End Function

.. but actually, on reflection, a better and simpler way is to use RegEx

Public Function StripCharsForVal(pstrIn As String) As String
  
    Static RE As New VBScript_RegExp_55.RegExp
   
    RE.Pattern = "[^0-9]{1,}"
    RE.Global = True

    StripCharsForVal = RE.Replace(pstrIn, vbNullString)

End Function