VBA Issue with Number formatted as text when numbers include comma

Question

I use a vba script to open another workbook. This workbook is in format .XLS and the content from a DB was stored like:

"Name1"  "0"  "7,98"   
"Name2"  "5"  "1"  
"Name3"  "2"  "7,1"  

When opening the workbook with a VBA script, every cell that includes a comma, is interpreted as text and shows this warning:
The number in this cell is formatted as text or is preceded by an apostrophe

Strangely, if I open the file by double clicking, the numbers are just formatted as text and don't show any error. I guess, Excel is doing some interpretting, which doesn't work.
My code is:

Dim WorkBookImport As Workbook
Name = Application.GetOpenFilename()
If Name <> False Then
   Set WorkBookImport = Workbooks.Open(Filename:=Name)
End If

I tried everything from:

Range(mycolumn).Value = Range(mycolumn).Value 

For loop with CDbl(.Value)
Range(mycolumn).TextToColumns ...

Nothing works ;( Please help!

Answer 1

Option Explicit

Sub edksg()
    Dim c As Range
    
    Set c = Sheet1.Range("D3")
    
    c.Value2 = CDbl(c.Value2)
    
End Sub

Works fine for me when cell D3 of the worksheet is formatted as text.

Of course, my location uses the comma as decimal separator, so if your location uses a different standard, the issue might be there. In that case, doing something like CDbl(Replace(c.Value2, ",", Application.DecimalSeparator, 1, -1, vbBinaryCompare)) might solve that part of the issue.

If you want to loop through the entirety of some column, the end result might look something like this, assuming the values you want to convert to numbers are in column C.

Option Explicit

Sub edksg()
    Dim c As Range
    Dim r As Range
    
    Set r = Sheet1.Range("C1:C" & Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Row)
    
    For Each c In r
        If Len(c) > 0 Then
            c.Value2 = CDbl(Replace(c.Value2, ",", Application.DecimalSeparator, 1, -1, vbBinaryCompare))
        End If
    Next c
    
End Sub