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

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...

CodePudding user response：

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