Excel VBA Decimal and Thousand Separator Problem

[Doc.AElstein]

Excel VBA Decimal and Thousand Separator Problem , . Comer Point

I am posting my current answer to this problem, to share it, and also to ask if anyone has a better ( that is to say simpler answer….)

I keep thinking I have got this problem solved. (_...... The problem that is trying to control predictably how the Separators are chosen, changed and manipulated in Excel in different Excel land versions –
In Excel it is a nightmare IMO.
In VBA the conventions is generally along the USA conventions, but depending on exactly what you are doing you may get to “see” ( for example in the watch window or Debug.Print window or hovering over a variable in Debug F8 mode ) one convention or the other. ….)

Just today the problem hit me again. A code that had served me faithfully suddenly got a “Spanner in the works” due to the Excel VBA Decimal and Thousand Separator Problem.

In frustration I wrote a Function. It works for me now, but seems too complicated. ( !!! and is not perfect )

So..I am just asking here for any:….
comments on the Function !!!, comments generally on the Excel VBA Decimal and Thousand Separator Problem, or if anyone knows of a code that might be better, or could give a better code version of mine that might be quicker.
I have spent some time recently speeding up codes, and this extra Function I expect could unnecessarily slow things down. I lack the computer experience and knowledge to know of the most efficient code for doing this sort of thing.
Or anyway…..
I present my code in case it could help anyone else coming here on a search on the theme Excel VBA Decimal and Thousand Separator Problem.


!!!EDIT: I do / see one problem with my code. That is to say if I have one or more Thousand Separators, but no decimal separator, then I obtain the wrong answer!
For example, If I give the Function a string of 100,000 ( One Hundred Thousand ) , my code will return 100. With no prior knowledge of my separator convention, I see no way to get over this.
( In my current applications, such a number would be represented by 100K or 100.0K so this is not a problem: first my Function works on the Number part to give the correct USA convention Number, and then in another code section the Text part is examined and a K, for example, would result in the final Number being multiplied by one Thousand )
_.......................................

This is what I am doing in my Function:

_ ) Initially I will try to have ( and the Function assumes I have ) my “Numbers” held in a “String” Variable, strNumber . This is necessary for me to be able to manipulate it initially as Text to get length info, positioning of separators, etc. (The code will work for Leading and Trailing zeros, but will not return them. )
I assume the Number “looks” like a number, so it only has Numbers and possibly Decimal and Thousand Separators and possibly a –ve sign. ( I assume if a separator is given then there is at least one character after it, something like 123. Is not acceptable and will cause an error )

The Code Description:
In Brief:
The String is Divided into two parts consisting of the Whole and Fractional Part. The Basic assumption here being that the last ( if any ) separator will be the Decimal separator , regardless of whether a . or a ,

Mathematics is then used to rejoin them into a Double variable.
The Hope is then that what I finally have is “seen” by VBA to be the correct Double number that I want
My thinking is that if Excel ( VBA ) constructs my Numbers then I hope after that point it will not decide to mix up separators later. Well, one has to live in hope!!!

In Detail:

The Function should be given things like
“String” Value~~~~~~:~~~~~~~~In “Spoken” English
“1,20”~~~~~~~~~~~~~~~~:~~~~~~~~One Point Two
~~“1,20”~~~~~~~~~~~~~~~:~~~~~~~~One Point Two
“1,2000000”~~~~~~~:~~~~~~~~One Point Two
“01,20”~~~~~~~~~~~~~~:~~~~~~~~One Point Two
“100,000.0”~~~~~~~~:~~~~~~~~One Hundred Thousand Point Naught
“100.000,2”~~~~~~~~:~~~~~~~~One Hundred Thousand Point Two



_20 I replace any “.” with a “,” So I should then only have “,” ‘s , if any, as separators

_
_40 I check if I do have any “,” s ….. ( Else*** ( 250 ) I just do a simple Convert to Double later in the code on the string and assign that to the value returned by the function. ) …….

_ ……So if I do have one or more “,” s in the String Then……..

_60 Length and Separator Positional Information is obtained from the Full String, strNumber

_..... 70 'Whole Number Part
_80 The whole part of the number is taken from the left up to just before the Decimal

_ 90 Any remaining Separators (presumably Thousand separators ) are removed from the Whole part

_100 At this stage a simple conversion is done to a Long type on this Whole number ( which may or may not have a “-“ at the start representing a –ve Number )
_...................................

_..... 110 'Fraction Part of Number
_120 The fractional part of the number is determined by the part in strNumber starting from just after Decimal Separator and extending to a length of
= ( the length of the whole strNumber minus the position of the separator )

_130 We determine the order of magnitude that we need to divide this fractional “as Whole Number” to get the correct final number from the length of the Number in its current “as Whole Number” form
That is to say , for example if
strNumber was “12,345”
then the fractional “as Whole Number” is
345
So we need to divide it by
10 x 10 x 10 = 1000
So as to get
345 / (10 ^ 3) = 345/1000 = ( USA convention ) .345

_ 140 150 The basic Long number is now obtained. But this may be –ve. So:….
_.......................................

_.... 160 'Re join, using Maths to hopefully get correct Final Value
_170 180 ..... …If it is not negative, a simple addition of the two parts of the Number thus far is made

_.200 210 ....Else the “-“ is removed, the two parts of the Number are added, and then the “-“ is replaced by multiplying by -1
_.............................................

_ 230 The final re constructed and converted to Double number is given to the Function for Return on leaving the Function.
_.....................................

(_....270 ***Else part where a whole Number was given to Function )
_

_.............................................


Alan
_..........................................................

Code SHimpfGlified

Code: Select all

'
Function CStrSepDblshg(strNumber As String) As Double
20   Let strNumber = Replace(strNumber, ".", ",", 1, -1)
40     If InStr(1, strNumber, ",") > 0 Then
170         If Left(Replace(Left(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty, 1, 1), 1) <> "-" Then
180          Let CStrSepDblshg = CDbl(CLng(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)))))))
190         Else
210          Let CStrSepDblshg = (-1) * (CDbl(Replace(Left(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty, 1, 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))))))))
220         End If
250    Else
270     Let CStrSepDblshg = CDbl(strNumber)
280    End If
End Function

Code Full:

Code: Select all

Function CStrSepDbl(strNumber As String) As Double 'Return a Double based on a String Input which is asssumed to "Look" like a Number. The code will work for Leading and Trailing zeros, but will not return them. )
10   Dim DblReturn As Double 'Double Number to be returned in required Format after maniplulation.
20   Let strNumber = Replace(strNumber, ".", ",", 1, -1) 'Replace at start any . to a ,  After this point there should be either no or any amount of ,
30     'Check If a Seperator is present
40     If InStr(1, strNumber, ",") > 0 Then 'Check we have at least one seperator, case we have, then..
50      'Length of String Position of last ( Decimal ) Seperator
60      Dim LenstrNumber As Long: Let LenstrNumber = Len(strNumber): Dim posDecSep As Long: Let posDecSep = InStrRev(strNumber, ",", LenstrNumber) '  from right the positom "along" from left  (   (in strNumber)  ,  for a  (",")    ,   starting at the ( Last character ) which BTW. is the default
70      'Whole Number Part
80      Dim strHlNumber As String: Let strHlNumber = Left(strNumber, (posDecSep - 1))
90      Let strHlNumber = Replace(strHlNumber, ",", Empty, 1, 1) 'In (strHlNumber)   ,   I look for a (",")   ,    and replace it with "VBA Nothing there"    ,     considering and returning the strNumber from  the start of the string    ,     and replace all occurances ( -1 ).
100     Dim HlNumber As Long: Let HlNumber = CLng(strHlNumber) 'Long Number is a Whole Number, no fractional Part
110     'Fraction Part of Number
120     Dim strFrction As String: Let strFrction = Mid(strNumber, (posDecSep + 1), (LenstrNumber - posDecSep)) 'Part of string (strNumber )  ,  starting from just after Decimal separator  ,    and extending to a length of = ( the length  of the whole strNumber minus  the position of the separator )
130     Dim LenstrFrction As Long: Let LenstrFrction = Len(strFrction) 'Digits after Seperator. This must be done at the String Stage, as length of Long, Double etc will allways be 8, I think?.
140     Dim Frction As Double: Let Frction = CDbl(strFrction) 'This will convert to a Whole Double Number. Double Number can have  Fractional part
150     Let Frction = Frction * 1 / (10 ^ (LenstrFrction)) 'Use 1/___, rather than a x 0.1 or 0,1 so as not to add another , . uncertainty!!
160     'Re join, using Maths to hopefully get correct Final Value
170         If Left(strHlNumber, 1) <> "-" Then 'Case positive number
180          Let DblReturn = CDbl(HlNumber) + Frction 'Hopefully a simple Mathematics + will give the correct Double Number back
190         Else 'Case -ve Number
200          Let strHlNumber = strHlNumber * (-1) ' "Remove" -ve sign
210          Let DblReturn = (-1) * (CDbl(strHlNumber) + Frction) 'having constructed the value of the final Number we multiply by -1 to put the Minus sign back
220         End If 'End checking polarity.
230     'Simple Conversion of manipulated Long Number to Double, after all manipulatin finished
240     Let CStrSepDbl = DblReturn 'Final Double value to be returned by Function
250    Else 'We have a Whole Number with no seperator, case no seperator
260     'Simple conversion of a string "Number" with no Decimal Seperator to Double Format
270     Let CStrSepDbl = CDbl(strNumber) 'String to be returned by Function is just a simple convert to Double
280    End If 'End checking for if a Separator is present.
End Function

[HansV]

Application.International(xlDecimalSeparator) will return the currently used decimal separator (as a string), usually either "." or ",".

Application.International(xlThousandsSeparator) will return the currently used thousands separator (as a string), usually either "," or ".".

[Doc.AElstein]

Application.International(xlDecimalSeparator) will return .........".

Thanks Hans

That could be worth a try in code line of the form

If Application.International(xlDecimalSeparator) = _____ Then

I will bear that one in mind.

Alan

[Doc.AElstein]

Hi

I just referenced this thread in answering another Thread, and noticed an interesting phenomena actually error in my code !! ;) )
So I thought I would be responsible for my code here and bring the thing up , for anyone using this Thread in the future.

What I noticed is that the code as I posted here will error in a certain condition. That condition is if I feed my function a Variable that is in Element in an Array. In such a case I get a compile error of “Argument ByRef incompatible “ , and highlighted is arrNumbers in the calling line

Code: Select all

 70        Let arrOut(Cnt) = CStrSepDbl(arrNumbers(Cnt))

of the demo code below.

The problem is solved if I include ( or rather add ) a ByVal in the signature line of the function, such that
Public Function CStrSepDbl(strNumber As String) As Double
Is changed to
Public Function CStrSepDbl(ByVal strNumber As String) As Double

I confess I actually missed putttin ByVal in, which was my intention originally, - I forgot that the default implicit was ByRef )

I have not quite figured out why this error comes up in the ByRef case. I do know and understand that passing an Array to a function ByVal is not straight forward , and that one must “house” it in an Variant to do so,
http://www.excelforum.com/showthread.ph ... ost4381420" onclick="window.open(this.href);return false;

I would be interested if anyone can explain why the code errors in the case of
(ByRef strNumber As String)
The difference between ByRef and ByVal calls with Arrays comes up quite a lot..
But it is only of academic and passing interest, so not important.
If I come up with any ideas I will post a further follow up

Alan




_.................

Function Code and Calling Demo Code:

Code: Select all

Sub TestieCStrSepDbl() ' http://www.excelforum.com/showthread.php?t=1152109&p=4471278&highlight=#post4471278
10   Dim arrNumbers() As Variant, arrOut(0 To 4) As Variant
20   Dim Number3 As Variant: Let Number3 = "1200" & ".001"
30   Dim number4 As String: Let number4 = "000000000001,200.00100000"
40   Let arrNumbers = Array("1,200.001", 1200 + 1 / 1000, Number3, number4, "-000001200.001")
50   Dim Cnt As Long
60       For Cnt = 0 To 4
70        Let arrOut(Cnt) = CStrSepDbl(arrNumbers(Cnt))
80       Next Cnt
90   MsgBox prompt:=arrOut(0) & vbCr & arrOut(1) & vbCr & arrOut(2) & vbCr & arrOut(3) & vbCr & arrOut(4)
End Sub

'
Public Function CStrSepDbl(strNumber As String) As Double   'Return a Double based on a String Input which is asssumed to "Look" like a Number. The code will work for Leading and Trailing zeros, but will not return them. ) ' Note it is importent to have ByVal, otherwise there would be problems when passing an Array Element.
10   Dim DblReturn As Double 'Double Number to be returned in required Format after maniplulation.
20   Let strNumber = Replace(strNumber, ".", ",", 1, -1) 'Replace in strNumber or part thereof**  ,  any point    ,  with commer  ,  starting lookind at and returning string from** character 1  ,  doing it for all occurances        After this point there should be either no or some amount of cpmmers
30     'Check If a Seperator is present
40     If InStr(1, strNumber, ",") > 0 Then 'Check we have at least one seperator, case we have, then..  --|
50      'Length of String Position of last ( Decimal ) Seperator ( which will be a comer now )  This is the character position counting from the left of where a commer is
60      Dim LenstrNumber As Long: Let LenstrNumber = Len(strNumber): Dim posDecSep As Long: Let posDecSep = InStrRev(strNumber, ",", LenstrNumber) '  from right the positom "along" from left  (   (in strNumber)  ,  for a  (",")    ,   starting at the ( Last character ) which BTW. is the default
70      'Whole Number Part
80      Dim strHlNumber As String: Let strHlNumber = Left(strNumber, (posDecSep - 1)) ' One less than the comer ( decimal seperator ) will give whole number length
90      Let strHlNumber = Replace(strHlNumber, ",", Empty, 1, 1) 'In (strHlNumber)   ,   I look for a (",")   ,    and replace it with "VBA Nothing there"    ,     considering and returning the strNumber from  the start of the string    ,     and replace all occurances ( -1 ).
100     Dim HlNumber As Long: Let HlNumber = CLng(strHlNumber) 'Long Number is a Whole Number, no fractional Part
110     'Fraction Part of Number
120     Dim strFrction As String: Let strFrction = Mid(strNumber, (posDecSep + 1), (LenstrNumber - posDecSep)) 'Part of string (strNumber )  ,  starting from just after Decimal separator  ,    and extending to a length of = ( the length  of the whole strNumber minus  the position of the separator )
130     Dim LenstrFrction As Long: Let LenstrFrction = Len(strFrction) 'Digits after Seperator. This must be done at the String Stage, as length of Long, Double etc will allways be 8, I think?.
140     Dim Frction As Double: Let Frction = CDbl(strFrction) 'This will convert to a Whole Double Number. Double Number can have  Fractional part
150     Let Frction = Frction * 1 / (10 ^ (LenstrFrction)) 'Use 1/___, rather than a x 0.1 or 0,1 so as not to add another , . uncertainty!!
160     'Re join, using Maths to hopefully get correct Final Value
170         If Left(strHlNumber, 1) <> "-" Then 'Case positive number
180          Let DblReturn = CDbl(HlNumber) + Frction 'Hopefully a simple Mathematics + will give the correct Double Number back
190         Else 'Case -ve Number
200          Let strHlNumber = strHlNumber * (-1) ' "Remove" -ve sign
210          Let DblReturn = (-1) * (CDbl(strHlNumber) + Frction) 'having constructed the value of the final Number we multiply by -1 to put the Minus sign back
220         End If 'End checking polarity.
230     'Simple Conversion of manipulated Long Number to Double, after all manipulatin finished
240     Let CStrSepDbl = DblReturn 'Final Double value to be returned by Function
250    Else '  InStr(1, strNumber, ",") = 0  We have a Whole Number with no seperator, case no seperator --|
260     'Simple conversion of a string "Number" with no Decimal Seperator to Double Format
270     Let CStrSepDbl = CDbl(strNumber) 'String to be returned by Function is just a simple convert to Double
280    End If 'End checking for if a Seperator is present   -----------------------------------------------|
End Function

[rory]

1. You're not passing an array, you are passing one member of the array.
2. You declared the array as Variant - therefore every item in it is a Variant.
3. If passing ByRef, the type must match, but your function requires a String not a Variant.

[Doc.AElstein]

Hi Rory,
Long time no hear, How are you?

_1 ) I realise I am not passing an array... I said ...”.... if I feed my function a Variable that is in Element in an Array....”...but.. If you so wish , I pass a “Member” ...

_2 ) and _ 3 ) Exactly! Bingo ! ;) , Thanks.
What a twat I am not to have noticed that. Of course. I am “passing” or rather arranging that in the course of the function running what I reference to is the “pointing “Pointer” thing “ of the variable itself, ( or the local copy of it - no one knows for sure** ;) ) – but in any case it does make sense that the “thing” going through the Function, as it were, is going to have to be an exact match , if not "being" the variable itself**.. That is necaerssary so that changes can be reflected exactly as they would and will be or are to the original variable ...
All makes sense, .. sort of

Now that I think of it, I had been “capturing” Arrays from Ranges ( hence necessarily my Arrays were of Variant types to suit the .Value Property on ranges of greater than 1 cell chucking back a Field ( a load of “Members” ) of Variant types... so that’s why I got the compile error ). I was not thorough enough in my_...
Sub TestieCStrSepDbl() calling demo code
_... and should have included Arrays with String “Members” as this does not error, as expected, 60 ( Or even just variables of Variant types 120 to catch the error again). – I got mixed up as the ByVal call took Variant types, which makes sense)

(_............ Of course I could do the shg “params Wonk”.... “.... parens around an argument that doesn't require them, the argument is evaluated, and the result of the evaluation is passed to the procedure. It means the argument is passed by value regardless of how the procedure requested it.....”
http://www.excelforum.com/excel-program ... ost4226271" onclick="window.open(this.href);return false;
http://www.mrexcel.com/forum/excel-ques ... ost4336751" onclick="window.open(this.href);return false;_ .. Line 80 in new Demo Code , Sub TestieMember()
_.......... ..I cannot think immediately why i would want to do this, but another possibility )

_.....

SOLVED

Thanks
:)

Alan

_...

Code: Select all

Sub TestieMember()  '    http://www.eileenslounge.com/viewtopic.php?f=27&t=22850#p190682
10   Rem 1 Array Members
20   Dim MyStringyMembers(1) As String: Let MyStringyMembers(1) = "12.34"
30   Dim MyVeryMembers(1) As Variant: Let MyVeryMembers(1) = "12.34"
40   Dim GetTheNumberBackToYou As Double
50 '
60   Let GetTheNumberBackToYou = CStrSepDbl(MyStringyMembers(1)) ' Works
70   'Let GetTheNumberBackToYou = CStrSepDbl(MyVeryMembers(1)) ' will error on compile for signature line in Function of CStrSepDbl(ByRef__   --- "I am "passing" or rather arranging that  in the course of the function running what I reference to is the "pointing "Pointer" thing " of the variable itself, ( or the local copy of it  - no one knows for sure** ;) ) - but in any case it does make sense that "thing" going through the Function as it were is going to have to be an exact match , if not being the variable itself** so that changes can be reflected exactly as they would and will be to the original variable ..."  Rory  http://www.eileenslounge.com/viewtopic.php?f=27&t=22850#p190682
80   Let GetTheNumberBackToYou = CStrSepDbl((MyVeryMembers(1))) ' shg "Params" wonk :     http://www.excelforum.com/excel-programming-vba-macros/1110756-has-iif-changed.html#post4226271
90 '_...
100  Rem Not members _
110  Dim BanishedMember As Variant ' Not a member of an Array, but a Variant ...so
120  ' Let GetTheNumberBackToYou = CStrSepDbl(BanishedMember) '   ........    ...will also error on compile for signature line in Function of CStrSepDbl(ByRef__   
End Sub

[rory]

Hi Alan.

Re 1, yes, but your question said (at some length ;)):

I do know and understand that passing an Array to a function ByVal is not straight forward , and that one must “house” it in an Variant to do so,
http://www.excelforum.com/showthread.ph" onclick="window.open(this.href);return false; ... ost4381420

I would be interested if anyone can explain why the code errors in the case of
(ByRef strNumber As String)
The difference between ByRef and ByVal calls with Arrays comes up quite a lot..

which seemed to imply you thought you were passing an array.

The rest I couldn't read for fear of migraine. ;)

[Doc.AElstein]

Just a - p 'arsin Member
;)
Thanks again

[rory]

Any time.

[Doc.AElstein]

Hi
Just adding a slightly improved version of my codes, ( I could not edit due to the post size limit )
Alan

Code: Select all

'   http://www.eileenslounge.com/viewtopic.php?f=27&t=22850&p=177528#p177528
Function CStrSepDbl(strNumber As String) As Double 'Return a Double based on a String Input which is asssumed to "Look" like a Number. The code will work for Leading and Trailing zeros, but will not return them. )
5      If Left(strNumber, 1) = "," Or Left(strNumber, 1) = "." Then Let strNumber = "0" & strNumber ' case for .12  or ,7   etc
10   Dim DblReturn As Double 'Double Number to be returned in required Format after maniplulation.
20   Let strNumber = Replace(strNumber, ".", ",", 1, -1) 'Replace at start any . to a ,  After this point there should be either no or any amount of ,
30     'Check If a Seperator is present
40     If InStr(1, strNumber, ",") > 0 Then 'Check we have at least one seperator, case we have, then..
50      'Length of String Position of last ( Decimal ) Seperator
60      Dim LenstrNumber As Long: Let LenstrNumber = Len(strNumber): Dim posDecSep As Long: Let posDecSep = InStrRev(strNumber, ",", LenstrNumber) '  from right the positom "along" from left  (   (in strNumber)  ,  for a  (",")    ,   starting at the ( Last character ) which BTW. is the default
70      'Whole Number Part
80      Dim strHlNumber As String: Let strHlNumber = Left(strNumber, (posDecSep - 1))
90      Let strHlNumber = Replace(strHlNumber, ",", Empty, 1, 1) 'In (strHlNumber)   ,   I look for a (",")   ,    and replace it with "VBA Nothing there"    ,     considering and returning the strNumber from  the start of the string    ,     and replace all occurances ( -1 ).
100     Dim HlNumber As Long: Let HlNumber = CLng(strHlNumber) 'Long Number is a Whole Number, no fractional Part
110     'Fraction Part of Number
120     Dim strFrction As String: Let strFrction = Mid(strNumber, (posDecSep + 1), (LenstrNumber - posDecSep)) 'Part of string (strNumber )  ,  starting from just after Decimal separator  ,    and extending to a length of = ( the length  of the whole strNumber minus  the position of the separator )
130     Dim LenstrFrction As Long: Let LenstrFrction = Len(strFrction) 'Digits after Seperator. This must be done at the String Stage, as length of Long, Double etc will allways be 8, I think?.
140     Dim Frction As Double: Let Frction = CDbl(strFrction) 'This will convert to a Whole Double Number. Double Number can have  Fractional part
150     Let Frction = Frction * 1 / (10 ^ (LenstrFrction)) 'Use 1/___, rather than a x 0.1 or 0,1 so as not to add another , . uncertainty!!
160     'Re join, using Maths to hopefully get correct Final Value
170         If Left(strHlNumber, 1) <> "-" Then 'Case positive number
180          Let DblReturn = CDbl(HlNumber) + Frction 'Hopefully a simple Mathematics + will give the correct Double Number back
190         Else 'Case -ve Number
200          Let strHlNumber = strHlNumber * (-1) ' "Remove" -ve sign
210          Let DblReturn = (-1) * (CDbl(strHlNumber) + Frction) 'having constructed the value of the final Number we multiply by -1 to put the Minus sign back
220         End If 'End checking polarity.
230     'Simple Conversion of manipulated Long Number to Double, after all manipulatin finished
240     Let CStrSepDbl = DblReturn 'Final Double value to be returned by Function
250    Else 'We have a Whole Number with no seperator, case no seperator
260     'Simple conversion of a string "Number" with no Decimal Seperator to Double Format
270     Let CStrSepDbl = CDbl(strNumber) 'String to be returned by Function is just a simple convert to Double ' I guess this will convert a zero length string "" to 0 also
280    End If 'End checking for if a Seperator is present.
End Function
'Long code lines:  Referrences    http://www.mrexcel.com/forum/about-board/830361-board-wish-list-2.html          http://www.mrexcel.com/forum/test-here/928092-testing-if-code-window-now-scrolls-horizontally.html                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    http://www.eileenslounge.com/viewtopic.php?f=27&t=22850
Function CStrSepDblshg(strNumber As String) As Double '          http://excelxor.com/2014/09/05/index-returning-an-array-of-values/      http://www.techonthenet.com/excel/formulas/split.php
5      If Left(strNumber, 1) = "," Or Left(strNumber, 1) = "." Then Let strNumber = "0" & strNumber
20   Let strNumber = Replace(strNumber, ".", ",", 1, -1)
40     If InStr(1, strNumber, ",") > 0 Then
170         If Left(Replace(Left(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty, 1, 1), 1) <> "-" Then
180          Let CStrSepDblshg = CDbl(CLng(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)))))))
190         Else
210          Let CStrSepDblshg = (-1) * (CDbl(Replace(Left(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty, 1, 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))))))))
220         End If
250    Else
270     Let CStrSepDblshg = CDbl(strNumber)
280    End If
End Function

Edit Feb 2018
I am keeping this code updated from now on in this Thread:
http://www.excelfox.com/forum/showthrea ... er-problem" onclick="window.open(this.href);return false;