To recap from the last Post…

This is how it works
Full Description
I am doing this with a Function code. Here is the Full version:
Function CStrSepDbl : http://www.excelfox.com/forum/showth...0502#post10502
https://pastebin.com/1kq6h9Bn

I go on to explain that now in detail, so it is probably best to copy that to a code module and follow through it as you read the explanations.

Pseudo Declaration Function signature line
The first line of our Function code says a lot about what we are actually doing. This will be explained again later. But briefly for now: This is approximately our Signature line: It pseudo “Declares” what I am doing:
Function CStrSepDbl(strNumber As String) As Double
This is very similar to a simple variable declaration , Diming . So pseudo I have:
_ Dim __ CStrSepDbl( _¬¬¬ ) ______ As Double
or
_ Dim VariableFunctionCStrSepDbl( _ ) As Double
In fact VBA is holding the CStrSepDbl as just that: variable CStrSepDbl is a Double type variable, held in memory somewhere, god knows where, probably, I don’t know.
The only difference is that in code lines like_..
_Dim Retun As Double
___ Retun = CStrSepDbl()
_.. what will usually happen in the last code line above is that the Function code will be carried out before that code line is completed. The completion of the code line then does pseudo
Retun = current value held by the VBA variable CStrSepDbl
If the Function code does not do anything to the VBA variable CStrSepDbl , then Retun will become 0. ( 0 is the default value for a non filled Double variable ).
But we want the function to function such as to put the final number in the correct format into Retun, using a code line such as
__ Retun = CStrSepDbl(HereTheStringNumber)
The purposed of the Function code will be to arrange for that to happen….. somehow…… So we want it to function in such a way.

2 things a lot of people probably know, but maybe did not realise
The key to how to do this is probably in a couple of things that Factually are, but that I have never seen clearly documented or explained, .. that being ..
_1: Once I have syntaxly correctly written a Function code (named in this case CStrSepDbl), then VBA recognises CStrSepDbl as a Function in any other code. Because of that we can do a code line of _...
__ Retun = CStrSepDbl(HereTheStringNumberInAnyOfTheIndicatedFormats)
_... in any code ( even in the function CStrSepDbl, in which case another copy of the code is made available to run. This is called recursion )
In passing that code line, the Function code is completed before the code line is completed. In other words the main code in which the code line _ Retun = CStrSepDbl(HereTheStringNumberInAnyOfTheIndicatedFormats) _ is in will pause in the middle of this code line whilst the Function code Function CStrSepDbl is carried out.

_2: However, importantly in addition, within the Function CStrSepDbl, the use of a code line like _..
__ CStrSepDbl = 9.3
_.. will result in assigning the value 9.3 into the VBA variable CStrSepDbl
( Such a code line will error in any other code ). This would have the effect that in completion of this Main code code line _...
_ Retun = CStrSepDbl(HereTheStringNumberInAnyOfTheIndicatedFormats) ,
_.... the value of 9.3 would be copied from the VBA variable CStrSepDbl into Retun. In other words Retun would become 9.3 at the completion of that main code code line.
This is because, the pseudo code of that line, as mentioned is
_ Retun = the VBA variable CStrSepDbl


Function code strategy
So the strategy is to write a code which “takes in” at the first ( signature ) line the string variable (HereTheStringNumberInAnyOfTheIndicatedFormats) and turns this into a number of the required magnitude, and then , for example , puts that in a variable, _ NumberInCorrectMagnitude _ , and then finally just before the End of the Function CStrSepDbl places this in the VBA variable CStrSepDbl . The latter can be done, for example , using within the function code and towards the End of the code such a code line
_ CStrSepDbl = NumberInCorrectMagnitude





So a walk through the code, Function CStrSepDbl


20 30 ( **The first couple of lines are something that I added later just to make the function a bit more flexible to return a value if used without giving it any text. It is not particularly relevant to a real life requirement , and I do not include it in the shortened working code in the second ( next ) post )
40 60 Possible adjustments to the left hand side
The basic idea is to consider the string text in two parts. This would not work as intended if the number starts with just a comma , or point . _ So to overcome that problem a 0 is added If the first character is either a comma , or point .
As leading 0s can be present then just adding a 0 in any case would have been another possibility but we also need to consider cases like -.38746 or -,32876 . This problem is overcome by If the first two cahracters are -, or .- Then
Application.WorksheetFunction.Replace( InTheText , AtFirstCharacter , ForLengthOneChharacter , SubstituteIn”-0”
70
We choose arbitrarily the comma , as separating symbol ( “ , is “the separator” “ ) , so just in case we have a point as separator, a
__Replace is made of ( InTheText , APoint , IsReplacedByAComma ,
___________________________LookingFromAndreturningFromFirstCharacter ,
________________________________LookingFromTheLeftForTheFirstOccuranceIsSufficientButReplacingAllHereSimplifiesTheCodeLater ,
_________________________________________LookingForAnExcactMatch _ )
( VBA Replace Function: http://www.excelfox.com/forum/showth...0499#post10499 )
It is sufficient to look for one occurrence, as that would catch and replace the situation when looking from the right a point . is used as the decimal separator, but later in the code any thousand separators will be removed and it simplifies the code to only look for commas at that point
100 – 320 MAIN CODE ===
90 A main check is done: If we have a presence of a comma then the MAIN CODE begins.
________ ( 320 – 340 Else a simple conversion of the original Text to a Double number is done towards the end of the code )
120 The position of the decimal separator is determined as that for the last comma ,

Determination of Whole part to left of separator:
So the position of the first comma “seen Looking in the String from the right” was obtained, which allows a
140 simple truncation of the Text to reveal the “whole Number” part, ( that to the left of the decimal separator )
150 Any other separators (which will be any thousand separators) will be removed.
Determination of fractional part to right of separator (170-210)
180 The fractional string part is obtained.
190-210 Logic for the fractional part determination as number
_ Consider with an example, 1.00100
The string fractional part, strFrction, is determined to be “00100”
The character length of this string, LenstrFrction, is 5
Conversion of strFrction to a Double number gives 100 which is placed in variable Frction
If one inspect these Numbers , one can see that the required fraction of ( in this case 0.001 ) is obtained from a formula of the form
100 / (10^5), or in general _ TheDoubleNumber / (10^CharacterLength)
____________ = strFrction / (10^ LenstrFrction)
____ strFrction = strFrction / (10^ LenstrFrction)

Reconstruction using Maths (220-290)
240-250 For a positive original number the Mathematical addition is straight forward.
260-280 For a negative number the Number must first be constructed with the Mathematical addition to get the correct magnitude without the –ve sign , followed finally by conversion mathematically to a –ve number by multiplying by -1


We are almost finished… we have what we want in a variable DblReturn
A recap of the Pseudo Declaration Idea is useful here:
Final construction / pseudo assignment of the Function variable for return
The Function signature line _..
Function CStrSepDbl(strNumber As String) As Double
_.. was considered as pseudo a Declaring ( Diming ) code line for a variable .
The corresponding pseudo assignment of the variable to a value is realised by passing a value to strNumber , pseudo
Let FunctionCStrSepDbl = strNumber ‘ Where strNumber is a string that “looks like a number”, noting that Excel will accept a Number type into a String variable and convert it to a string automatically.
The actual code line required has two possible syntaxes
_ Call CStrSepDbl(strNumber)
_ CStrSepDbl strNumber
Those lines do not actually “fill” the variable “FunctionCStrSepDbl”
The signature line has defined a Double type to be returned held in the Pseudo variable CStrSepDbl
Effectively CStrSepDbl is a variable of type Double
I can assign a variable to that variable, that is to say place in some variable the value in CStrSepDbl just as I can any variable
Dim Retn As Double
_ Let Retn = CStrSepDbl
( ** In fact in the actual code that I have done, I used in the signature line Optional strNumber As String . Because of this I can use exactly that code line, without any syntax error.)
Final code line(s) 310 and 340 ( and 30 ) __ CStrSepDbl = xxxxx
So the discussion so far have got our function doing what we want. This is what happens when we Call the Function, in the variable assignment type line Let Retn = CStrSepDbl(“123.45”), that is to say pseudo code lines likje the following are done:

Retn ________ , IsEqualTo , ___ ”variable” FunctionCStrSepDbl ______ - _ ButOnlyAfterTheFunctionCodeIsRun
InVariableRetn , Is put in it , AfterRunnigTheCStrSepDbl(Using”123.45”) - TheContentsOf”variable” FunctionCStrSepDbl

So if a call of the Function is made, such as with Retn = CStrSepDbl(“123.45”) , then the function is run and the contents of ”variable” FunctionCStrSepDbl is put in Retn.
So Retn becomes ….. the value of an unassigned variable of the Double type …. _..
_.. It becomes 0 . That is not very useful!!!
As noted at the outset, VBA is holding CStrSepDbl as variable with Double type.
We have the final thing we want: the number in variable DblReturn. We must now put this in the ”variable” FunctionCStrSepDbl before the function Ends
This final required steps are done either of the code lines 310 or 340 ( or 30 )
That final code line is of the form like
__ CStrSepDbl = Retun


The full code is here:

http://www.excelfox.com/forum/showth...0502#post10502
https://pastebin.com/1kq6h9Bn


A shortened Function code is given in the next post, along with a calling code to help demo

Ref:
http://www.eileenslounge.com/viewtop...=22850#p208624
http://www.excelfox.com/forum/showth...0192#post10192
http://www.excelfox.com/forum/showth...0463#post10463
https://www.excelforum.com/developme...ml#post4630570
https://www.mrexcel.com/forum/excel-...tr#post2845398