Excel VBA comma point thousand decimal separator number problem.

[DocAElstein]

Shortened Code version of Function described in last Post, and demo calling code

'    http://www.excelfox.com/forum/showthread.php/2232-Excel-VBA-comma-point-thousand-decimal-separator-number-problem?p=10503#post10503
Sub TestieCStrSepDblSHimpfGlified() ' using adeptly named  TabulatorSyncranartor ' / Introducing LSet TabulatorSyncranartor Statement :   http://www.excelfox.com/forum/showthread.php/2230-Built-in-VBA-methods-and-functions-to-alter-the-contents-of-existing-character-strings
Dim LooksLikeANumber(1 To 17) As String
 Let LooksLikeANumber(1) = "001,456"
 Let LooksLikeANumber(2) = "1.0007"
 Let LooksLikeANumber(3) = "123,456.2"
 Let LooksLikeANumber(4) = "0023.345,0"
 Let LooksLikeANumber(5) = "-0023.345,0"
 Let LooksLikeANumber(6) = "1.007"
 Let LooksLikeANumber(7) = "1.3456"
 Let LooksLikeANumber(8) = "1,2345"
 Let LooksLikeANumber(9) = "01,0700000"
 Let LooksLikeANumber(10) = "1.3456"
 Let LooksLikeANumber(11) = "1,2345"
 Let LooksLikeANumber(12) = ".2345"
 Let LooksLikeANumber(13) = ",4567"
 Let LooksLikeANumber(14) = "-,340"
 Let LooksLikeANumber(15) = "00.04"
 Let LooksLikeANumber(16) = "-0,56000000"
 Let LooksLikeANumber(17) = "-,56000001"
Dim Stear As Variant, MyStringsOut As String
    For Each Stear In LooksLikeANumber()
    Dim Retn As Double
     Let Retn = CStrSepDblSHimpfGlified(Stear)
    Dim TabulatorSyncranartor As String: Let TabulatorSyncranartor = "                         "
     LSet TabulatorSyncranartor = Stear
     Let MyStringsOut = MyStringsOut & TabulatorSyncranartor & Retn & vbCrLf
     Debug.Print Stear; Tab(15); Retn ' When in VB Editor, Hit Ctrl+g to reveal Immediate window
    Next Stear
 MsgBox MyStringsOut
End Sub
'



' http://www.excelfox.com/forum/showthread.php/2232-Excel-VBA-comma-point-thousand-decimal-separator-number-problem?p=10503#post10503
Function CStrSepDblSHimpfGlified(ByVal strNumber As String) As Double
50     If Left$(strNumber, 1) = "," Or Left$(strNumber, 1) = "." Then strNumber = "0" & strNumber
60     If Left$(strNumber, 2) = "-," Or Left$(strNumber, 2) = "-." Then strNumber = Application.WorksheetFunction.Replace(strNumber, 1, 1, "-0")
70    strNumber = Replace(strNumber, ".", ",")
90     If InStr(1, strNumber, ",") > 0 Then
240         If Left(Replace(Left$(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty), 1) <> "-" Then
250          CStrSepDblSHimpfGlified = CDbl(CLng(Replace(Left$(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty))) + CDbl(Mid$(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber))))) * 1 / (10 ^ (Len(Mid$(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber)))))))
260         Else
280          CStrSepDblSHimpfGlified = (-1) * (CDbl(Replace(Replace(Left$(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty), "-", "", 1, 1)) + CDbl(Mid$(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber))))) * 1 / (10 ^ (Len(Mid$(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber))))))))
290         End If
320    Else
340     CStrSepDblSHimpfGlified = CDbl(strNumber)
350    End If
End Function

Typical demo Output:
( In Immediate Window )

001,456        1.456 
1.0007         1.0007 
123,456.2      123456.2 
0023.345,0     23345 
-0023.345,0   -23345 
1.007          1.007 
1.3456         1.3456 
1,2345         1.2345 
01,0700000     1.07 
1.3456         1.3456 
1,2345         1.2345 
.2345          0.2345 
,4567          0.4567 
-,340         -0.34 
00.04          0.04 
-0,56000000   -0.56 
-,56000001    -0.56000001

( MsgBlx Displayed)
MsgBolox.JPG https://imgur.com/MtDObYA
MsgBolox.JPG

---

Refs
https://excelribbon.tips.net/T013675...h_Periods.html
https://excelribbon.tips.net/T007563...ed_by_100.html