Converting "text formated as string" using CDbl() fails

Question

I am using this function to convert "numbers formatted as strings" to numbers.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' **Occurence:**
' This function is used in multiple handlers.
'
' **Summary:**
' This function iterates over all the (a) rows & columns in the 1st table
' on the sheet. It checks non-empty values. If value is not formatted as
' to the table while *"numbers formated as text"* are converted to
' `Double` and added to the array. At the end this array is written back
' to the table while *"numbers formated as text"* are converted to `Double`.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function remove_numbers_formated_as_text(ByVal sh As Worksheet)

    Dim r As Range
    Dim arr As Variant
    Dim i As Long '''' arr's rows
    Dim j As Long '''' arr's columns
    
    Dim s As String
    Dim b As Boolean
    
    Set r = sh.ListObjects(1).DataBodyRange
    arr = r.Formula2

    '''' Iterate over a whole row and then proceed to next column
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            If IsEmpty(arr(i, j)) = False Then
                
                '''' Check whether array mamber stores formula
                b = r(i, j).HasFormula
                If b = True Then GoTo A
                
                '''' If cell doesn't treat numbers as text and is a numeric value,
                '''' then this is definitely a measurement that somebody entered and
                '''' it is therefore converted to double.
                s = r(i, j).NumberFormat
                If IsNumeric(arr(i, j)) And s <> "@" Then
                    arr(i, j) = CDbl(arr(i, j))
                End If
            
            End If
A:
        Next
    Next

    r.Formula2 = arr
    
End Function

I thought that this works, but it does not. Original array stores everything as Variant/Strings, e.g. "0.544502556324005" but after these values are modified by CDbl(), they become a Variant/Double but they loose decimal separator, becoming e.g. 544502556324005. So data get's corrupted...

Why are decimal separators removed? Any idea how to solve this? Or if anyone has some idea on how to convert "numbers formated as strings" better...

I can't get the whole purpose of your function and asked whether you can give an example of input string to see how it looks like — Vitaliy Prushak, Sep 25 '22 at 21:10
Or you may try using `CDbl(replace(arr(i, j),",","."))` in case you have different seprators — Vitaliy Prushak, Sep 25 '22 at 21:40
The solution: https://stackoverflow.com/questions/14979333/vba-converts-string-12-00-to-1200-instead-of-12-00-this-happens-only-on-eu-re — 71GA, Sep 25 '22 at 21:43

score 2 · Accepted Answer · answered Sep 26 '22 at 05:48

2

Original array stores everything as Variant/Strings, e.g. "0.544502556324005" ...

If so, don't use CDbl but Val which operates independently of the local settings:

If IsNumeric(arr(i, j)) And s <> "@" Then
    arr(i, j) = Val(arr(i, j))
End If

answered Sep 26 '22 at 05:48

Gustav

53,498
7
29
55

This was the solution. `CDbl()` id s real testament to Microsoft's sloppiness. – 71GA Sep 26 '22 at 07:10
1

_CDbl() id s real testament to Microsoft's sloppiness._ No, but it is intended for another context - where you convert _localised_ strings, for example as returned by `Format(SomeDecimalValue, "0.00")` where "." is not a dot but the localised decimal separator. `Val`, however, matches `Str` which always returns a dot as the decimal separator. – Gustav Sep 26 '22 at 07:18

Converting "text formated as string" using CDbl() fails

1 Answers1