Copy Bot Killer Notes tests. ByVal ByRef Application.Run.OnTime Multiple Variable Arguments ByRef ByVal

Slightly extended explanations
ByVal ByRef, and variables
Part 1 Simple variable Long type]

For more detailed background, See also here
https://www.excelfox.com/forum/showt...age3#post11882
https://www.excelfox.com/forum/showt...ll=1#post17881
We give here the simple explanation for ByRef / ByVal issues in VBA. but slightly different in that we are less concerned in considering resulting variable values, but rather the values of all pointer associated with the variables concerned in the example coding
Consider the 12 labelled code lines, those with the code line numbers) associated with the following coding. Those lines are done sequentially when we run the first main coding, Sub ByValByRefWithPointersLong()
(If anything is unclear about how the pointer values discussed are obtained, you should refer to the two references above )

Code:

---

Sub ByValByRefWithPointersLong()
1 Dim Lng As Long
2 Debug.Print VarPtr(Lng), VarPtr(ByVal Lng) '  '    1831204      0
3 Call ByRefLong(Lng) ' Do a  ByRef  Call --#
7 Debug.Print VarPtr(Lng), VarPtr(ByVal Lng)    '    1831204      2

8 Let Lng = 0

9 Call ByValLong(Lng) ' Do a  ByVal  Call --**
13 Debug.Print VarPtr(Lng), VarPtr(ByVal Lng)  '    1831204      0
End Sub
Sub ByRefLong(ByRef Lnge As Long) ' #-- Do a  ByRef  Call
4 Debug.Print VarPtr(Lnge), VarPtr(ByVal Lnge)  '    1831204      0
5 Let Lnge = 2
6 Debug.Print VarPtr(Lnge), VarPtr(ByVal Lnge)  '  1831204      2
End Sub
Sub ByValLong(ByVal Lnge As Long) ' **-- Do a  ByVal  Call
10 Debug.Print VarPtr(Lnge), VarPtr(ByVal Lnge)  '  1831192      0
11 Let Lnge = 2
12 Debug.Print VarPtr(Lnge), VarPtr(ByVal Lnge)  '  1831192      2
End Sub

---

Code line 1: Lng is strictly speaking the symbol for the pointer held in a stack of active variable that we don’t have easy access to, going by the name of Common Object File Format ( COFF ) symbol table, and could perhaps be by a layman be regarded as some sort of stack or shelf arrangement populated by some complex rules allowing software to access as necessary. Our interest starts at what is "in" this, in other words the number held at this stack/shelf location . It is a number which is made with 4 bytes, (32 bits). It, or the mechanisms associated with it, is/are called a Pointer, often.
The actual number refers to the first memory address ( the first memory address at the left hand side) of 4 Bytes (32 bits) set aside in memory to hold the final value which I later assign to the variable. In other words, where the first 0/1 value Bit is in the binary representation of a number is. This address is obtained by the first half of code line 2
I use for this explanation the numbers I got , you will of course get similar sized but different numbers. – Your computer will find an appropriate suitable place at the time you run the coding
This next sketch is a pictorial representation of that number in that mysterious shelf/stack (COFF symbol Table)
https://i.postimg.cc/RCXmK1wN/First-...able-value.jpg
https://i.postimg.cc/0QwkZTfZ/First-...able-value.jpg
We cannot easily know "where that sketch is".
The second value at line 2 tells us what number is held at that address. At this stage it will be 0 as the memory location and following 31 bits all have the value 0, representing the final decimal value of 0. At this stage those 32 bits have just been set aside for us to use.
This next sketch is similar to the last, as it is representing a similar sized memory location, ( 32 consecutive bits ). But this is a different "”place" / memory location , the one being "pointed to" by that number, 1831204 , in that shelf/stack (COFF symbol Table) . This place is being used currently to represent a value of zero, which it will be set to by default when it is set aside for us to use. We know "where this is". It starts at memory location 1831204, and ends 31 bits (4 bytes), further along at 1831204 + 3 = 1831207
https://i.postimg.cc/J0mxTCLL/Memeoy...ng-value-0.jpg
https://i.postimg.cc/J0mxTCLL/Memeoy...ng-value-0.jpg

Code Line 4 in the first Called procedure returns the same values as code line 2. This is because the code lines are the same, and, importantly for our discussions, they are referring to the same variable, the same "place" depicted above. This is arranged by the use of ByRef in the Called Sub ByRefLong(ByRef Lnge As Long) We could think in simple layman terms, that in the first main procedure, Sub ByValByRefWithPointersLong() and the first Called procedure , the variables Lng and Lnge are in effect the same variable.
Code line 5 in the first Called procedure, then changes the value in the 32 bits there to a binary set of 0/1 digits representing the decimal value of 2. So not much has changed there: This is what it looks like now:
https://i.postimg.cc/6q00gbWG/Memeoy...ng-value-2.jpg
https://i.postimg.cc/6q00gbWG/Memeoy...ng-value-2.jpg

Just to make that all clear again, the story so far:
VarPtr(Lng) has returned us the number held in some place we do not have easy access to. That number , 1831204 , is the memory address of the first bit on the left of 32 bits reserved for us to hold the actual final value that we want to "have in the variable"
VarPtr(ByVal Lng) gives us the decimal value held in those 32 bits at any time. Originally it was set by default to zero, and at some point we changed it to 2

As we now move on, back in the main procedure. at code line 7, the two values returned remain at the values obtained / last set, within the first Called procedure: These are now the values as they stand in the main procedure: the address location of our original variable will never change as long as the coding runs, but we changed the value effectively at that address within the first Called procedure, - we were able to do that change to the value of 2 as effectively, in simple terms, Lng and Lnge were the same variable.

(Code line 8 takes the value of our variable,( that is to say the value represented by the 32 bits starting at memory location 1831204), back to 0 so that we can now repeat the same experiment using the second Called procedure, Sub ByValLong(ByVal Lnge As Long) )

Code line 10 tells us we "have a new variable” , with the name Lnge , which might sometimes be referred to as a local variable, - in this case it is local to the second Called procedure: The use of ByVal in Sub ByValLong(ByVal Lnge As Long) means that we take a value of, in this example 0, and , as in any Long value, we know it must be held somewhere. It cannot be held in 32 bits starting from memory location 1831204, since we are already using this for Lng in the main procedure. The computer has arranged another address for us, and as expected. It is not too far away as I was doing not much else on my computer that might need such a memory space. It has given me 1831192 and the value represented in the 32 bits starting there I currently have the decimal value of 0
Code line 11 sets the decimal value there to 2, as confirmed by code line 12

Finally we are back in our main procedure and do a last check with code line 13 of the situation to our original variable Lng. It has not been effected in any way by the second Called procedure, and remains in the state we left it in, when we went to the second
Called procedure. (Line 8 set it to 0 )

---

The next sketch shows the situation as it would be in at typical situation in a coding with a long variable declared, for example after
Dim Lng As Long
Let Lng = 2
https://i.postimg.cc/rpVP5Dm5/Final-...th-value-2.jpg
https://i.postimg.cc/zDjP7FJt/Final-...th-value-2.jpg




For comparison, here is the situation just before our coding ends, where we have our variable, but have "cleared" it so to speak, with = 0 , that is to say got it down to its "minimum" computer storage.
https://i.postimg.cc/G2DbWSp1/Final-...th-value-0.jpg
https://i.postimg.cc/G2DbWSp1/Final-...th-value-0.jpg

We note finally that for the case of a Long type, the computer storage used is identical, as the sketches perhaps suggest. In other words, from the computers point of view, an assignment of Let Lng = x, ( where x could be a small or a very large number ) , makes no significant difference to its memory state, and no difference at all to its memory allocation


_.___

One last short interesting experiment. It may not always work for you, but if you run the following short coding immediately after running the last coding, then it may work. We note that in the last experiments I mostly got a result of1831204 for the memory location of the start of the 32 bits that were made available to hold our Long variable number. The exception was when I ran Sub ByValLong(ByVal Lnge As Long) , and we got a different number, 1831192
We noted that this may have been because the number 1831204 was in use already. The following small coding only Calls that Sub ByValLong(ByVal Lnge As Long)
You may often find as I did , that it returns the same memory location as first obtained and mostly obtained in the last coding. This goes half way to supporting our ideas, as this makes sense as we do not make any attempt to get such a number/memory location reserved, until in the Sub ByValLong(ByVal Lnge As Long)

Code:

---

Sub OnlyByValCall()
Call ByValLong(0)
End Sub
' 1831204      0
' 1831204      2


---

Extended explanations
ByVal ByRef, and variables
Part 2 less Simple variable String type

Characters are more complicated for a computer to store, ( https://www.excelfox.com/forum/showt...ll=1#post17877 ) and a lot of text can be extremely long, so desirably we would like this large amount to be available. Currently I think we are talking of sizes around a Giga Bytes or two. But we may not always need this. Unlike in the case of a Long variable type, it would be impractical to routinely set aside a very large amount of memory after/ with a Declaration. More commonly, the address gets assigned once a value is given, ( when the variable gets "filled" ). It is slightly more complicated in the case of VBA as the VBA variable will then contain the address of another pointer which is a specific pointer type, one originating from Visual Basic, sometime referred to as BSTR, (Binary String or Basic String, or a pointer of some sort . Immediately you have opened a can of worms as there is not generical agreement on what a BSTR is). This BSTR (pointer) "goes to" (has the address ("value in it") of), not the start of the memory location as was the case with the Long, but rather to the start of the section containing the representatio0n of the characters of the string. Unlike in the case of the Long, things other than the final value we are interested in ,
_ the a length of the string, at the "left" as it were
and
_ a zero is added at the end

The test coding below used in my explanations is very similar and gives similar results to the previous for the Long case, but there are some subtle things going on that are different: It is very wise therefore to try to understand the Long case from the last post before moving on to here

StrPtr
This is as mysterious as the VarPtr, and was introduced around d the VB4 - VB5 time apparently wired a bit differently to the VarPtr(ByVal ) so as to make sure it went to the correct place, which may somehow help make UNICODE API calls more efficient, since in that use there may be more difficulty in getting the VarPtr(ByVal ) to work as we wish. We expect it to work as the VarPtr(ByVal ) in our experiments

Code:

---

Sub ByValByRefWithPointersString() ' https://www.excelfox.com/forum/showthread.php/2404-Notes-tests-ByVal-ByRef-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11889&viewfull=1#post11889
1 Dim strOb As String
2 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb)      ' 1831204      0            0            0
3 Call ByRefString(strOb) ' Do a  ByRef  Call --#
7 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb)      ' 1831204      86412692      86412692      86412692

8 Let strOb = vbNullString

9 Call ByValString(strOb) ' Do a  ByVal  Call --**
13 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb)    ' 1831204      0            0            0
End Sub
Sub ByRefString(ByRef strObs As String) ' #-- Do a  ByRef  Call
4 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs)  ' 1831204      0            0            0            0            0
5 Let strObs = ""
6 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs)  ' 1831204      86412692      86412692      86412692
End Sub
Sub ByValString(ByVal strObs As String) ' **-- Do a  ByVal  Call
10 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831036      0            0            0
11 Let strObs = ""
12 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831036      86412092      86412092      86412092
End Sub

---

The first output lines of 2 and 4 give us very similar results to the Long case. The fact that the returned value in both lines are the same demonstrate again that both in the main routine and the Called we are referring to the same variable, which is what the ByRef in the Called Sub ByRefString(ByRef strObs As String) has arranged.
Furthermore, if you run the main coding Sub ByValByRefWithPointersString() on the same computer after running the previous Long case coding from the last post, then , as I did you may get exactly the same results. This is perhaps not too surprising. At this stage we are doing exactly as in the previous coding for the corresponding code lines: At some location not easily known to us, a 32 bit, 4Byte is created holding a number, that number being sometimes regarded as the pointer: it tells us the first "left" of another 4 Bytes of memory location, set aside for us, and they will be initialised to 0 initially, just as previously, as the lines 2 and 4 suggest. (But we are not finished with memory allocation here, as we were in the Ling case!!)
https://i.postimg.cc/2y7gccT9/vb-Null-String-state.jpg
https://i.postimg.cc/2y7gccT9/vb-Null-String-state.jpg
(In passing, note, that this might be considered a "vbNullString situation" , but this is not directly because of the value 0 being held in the above chunk of memory. This 0 value is a consequence of, and an indication of, a vbNullString, but it is not directly depicting in any way a "nothing string" or "not string" or "null string". The specific direct representation is given by the fact that we see nothing else substantial in the picture yet, as we soon will)
Zero length string

In line 5 I deliberately chose the string "" , rather than something like "Alan" , as initially might reasonably have been expected. This is to emphasise that this “„ is not such a direct equivalent to the long of 0. If anything it comes closer to assigning a long value of a moderately large number, similar to the "pointer" values we have discussed in the COFF. In my example I might say that it does something like what Let Lng = 86412692 might have done in the previous coding. But now something else also happens: We get something new, extra, even in the very smallest simplest case of this string "" , which can be considered a string of zero length. A laymen instinct might be to consider 0 and "" as the equivalent things for the appropriate type. But that is more false than true, even if it is not simple black and white.
The following attempts to show this situation, when we "have" , "" , and even in this very smallest simplest case of this string, "" , it is difficult to accurately show the situation in a small simple diagram, because another chunk of memory place is made available, in more typical cases this is much larger than those considered so far. Even for "" , it is larger than those considered so far: For this simplest case it is 6 Bytes, (48 Bits)
Unlike in the case of the Long, the value, in my example here , it is 86412692, (and was 2 for the Long case), is not the final value of interest to me, which in this example is "" , but it is doing a very similar thing to the value of 1831204 in the COFF: It is pointing to this new extra chunk of memory. This "" situation is more commonly called the zero Length string situations
https://i.postimg.cc/8CSsbvrK/Zero-Length-Situation.jpg
https://i.postimg.cc/8CSsbvrK/Zero-Length-Situation.jpg


An important observation / concussion here is that the use of = "" does not return us to the state after the Dim, in the same or similar way that the = 0 did/ does in the long case

Lines 6 and 7 are giving the same results, indicating again that we are looking at the same variable in the main routine or the first Called routine, Sub ByRefString(ByRef strObs As String)

_.___________________

Before we take a break, consider what would happen if we simply replaced Let strOb = "" with Let strOb = "A"
Lets zoom in on what the area which would most likely be the only** area changed:
https://i.postimg.cc/BnDm793c/Area-l...assignment.jpg
https://i.postimg.cc/BnDm793c/Area-l...assignment.jpg



This would most likely be the (only** ) change after replaced Let strOb = "" with Let strOb = "A"

https://i.postimg.cc/8Pxybk2z/Let-str-Ob-A.jpg
https://i.postimg.cc/8Pxybk2z/Let-str-Ob-A.jpg

So two extra Bytes are added to represent the character A

_.__

** If you make a substantial change to the length of your character string you may see another change. Consider the following short coding.

Code:

---


Sub BigStringLength()
Dim strOb As String
 Let strOb = ""
Debug.Print VarPtr(strOb), VarPtr(ByVal strOb)
 Let strOb = "A"
Debug.Print VarPtr(strOb), VarPtr(ByVal strOb)
 Let strOb = "Alan"
Debug.Print VarPtr(strOb), VarPtr(ByVal strOb)
 Let strOb = String(99999999, "x")
Debug.Print VarPtr(strOb), VarPtr(ByVal strOb)
End Sub

---

The first number in all the output pairs should never change as that is the number held in the COFF symbol table.
In the first 3 character cases the second number will either never change or just change slightly
For the last output line, the second number will very likely change, and possibly a lot. This is because the extremely large string may require VBA / your computer to find some place perhaps less often used for such things: Given that strings can be up to a GByte or two, some flexibility is required in organising their storage. (As ever the actual numbers obtained will vary depending on when / where the coding is done)

_._______________________________________---





A lot was covered in this post, and an important concept, the zero length string was. We are about half way in the total coding and are at a convenient break point as the next thing dealt with will be more detail to the briefly mentioned vbNullString….

….continued from last post



So in the coding we are now in the main coding just before code line 8.
In the corresponding line in the Long case, we did Let Lng = 0 here, the purpose of which was to get us back to the situation of the variable at the point after the Dim

Code:

---

Sub ByValByRefWithPointersString() ' https://www.excelfox.com/forum/showthread.php/2404-Notes-tests-ByVal-ByRef-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11889&viewfull=1#post11889
1 Dim strOb As String
2 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb)      ' 1831204      0            0            0
3 Call ByRefString(strOb) ' Do a  ByRef  Call --#
7 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb)      ' 1831204      86412692      86412692      86412692

8 Let strOb = vbNullString

9 Call ByValString(strOb) ' Do a  ByVal  Call --**
13 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb)    ' 1831204      0            0            0
End Sub
Sub ByRefString(ByRef strObs As String) ' #-- Do a  ByRef  Call
4 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs)  ' 1831204      0            0            0            0            0
5 Let strObs = ""
6 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs)  ' 1831204      86412692      86412692      86412692
End Sub
Sub ByValString(ByVal strObs As String) ' **-- Do a  ByVal  Call
10 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831036      0            0            0
11 Let strObs = ""
12 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831036      86412092      86412092      86412092
End Sub

---

The last thing we discussed in the last post was the code line Let strObs = "" , and one conclusions was that the use of = "" does not return us to the state after the Dim, in the same or similar way that the = 0 did/ does in the long case.

vbNullString
This introduced for API things around the VB4 - VB5 time, and in VB6 and VBA it is "hidden". Quite a bit of debate and different opinions seem to be about it, perhaps as often as few people, if any, really know for sure. For our current discussions it seems to put us back to the state after the Dim
To quickly demonstrate that pictorially, here some sketch pairs, on the left some we had before, and on the right the change caused if we applied vbNullString to the variable
https://i.postimg.cc/k2XMKD4q/Areas-...assignment.jpg https://i.postimg.cc/ZCm5Ywz9/Areas-...ull-String.jpg



https://i.postimg.cc/Z9WTsgpQ/Let-str-Ob-A.jpg https://i.postimg.cc/Js6rqmDJ/Let-st...ull-String.jpg



Attachment 6161 Attachment 6162



Attachment 6163 Attachment 6164


The sketches on the right represent the exact situation after the Dim

_.____-

So, after code line 8, Let strOb = vbNullString , we are back to the initial start position so as to move on to the ByRef ByVal issues. Having covered the ByRef case we move on to the ByVal

The signature line , ByVal strObs As String , in the second Called routine, Sub ByValString(ByVal strObs As String) effectively gives us a new string type variable, strObs, as demonstrated by the next code line 10 which is giving us the number in the COFF, which for the first time we see has changed from that previously. In my ran example, I had until now always got 1831204 but now I have got 1831036. We expect this as our computer still has the address 1831204 in use, as it were, in the main coding, Sub ByValByRefWithPointersString()
We see perhaps a more noticeable difference in the two numbers, compared to the long case. This is perhaps understandable as there is more uncertainty in the final memory space that might be needed. (In the long case it was known that we would need no more than another 4Bytes, 32 Bits, so the next variable could be given a value not far from the last.)
We use again the example string "" ain the variable assignment in code line11, Let strObs = ""
Code line 12 the gives expected results of the previous COFF number and now also a new, the other "pointer" value, a unique one not yet used
Finally back in the main routine, we confirm in code line 13 that nothing done in this second Called routine, Sub ByValString(ByVal strObs As String), had any effect on the strOb variable of the main routine




So the conclusion is that both Long and String type variables behave similarly in terms of the ByRef ByVal issues in Called procedures. However we have seen that we need to have a good understanding to the memory location handling, and difference in the two types in order to interoperate the results correctly.

lfjladskjf

Code solution for this Thread
http://www.excelfox.com/forum/showth...e-page-numbers
https://www.excelforum.com/excel-pro...d-numbers.html

Code:

---

Option Explicit
Sub Moshe() ' http://www.excelfox.com/forum/showthread.php/2229-complete-page-numbers
Rem 1 Make array for holding inoput data and output data -  ' Input data can be handled as simple text so Array work is satisfactory
Dim arrIn() As Variant ' We know the data type can be taken as string, but I want to get the data quickly in a spreadsheet "capture" type way, using the .Value Property applied to a range object which returns a field of values for more than 1 cell returns a field of values held in Variant types, - so the type must be variant or a type mismatch runtime error will occcur
 Let arrIn() = Range("A1:A" & Range("A1").CurrentRegion.Rows.Count & "").Value
Dim arrOut() As String: ReDim arrOut(1 To UBound(arrIn())) ' I can use string type to suit my final data. I also know the array size, but I must make the array a dynamic ( unknown size ) type as the Dim declare statement will only take actual numbers, but I determine my size from the size of the input array by UBound(arrIn()) : the ReDim method will accept the UBound(arrIn()) , wheras the Dim declaration syntax will not accept this, as the Dim is done at complie and not runtime
Rem 2 Effectively looping for each data row
Dim Cnt As Long ' For going through each "row"
    For Cnt = 1 To UBound(arrIn()) ' Going through each element in arrIn()
    '2a) split the data in a cell into an array of data. The VBA strings collection split function will return a 1 dimentsional array of string types starting at indicie 0
    Dim spltEnt() As String ' For the string row split into each number entry, in other words an array of the data in a cell
        If InStr(1, arrIn(Cnt, 1), ", ", vbBinaryCompare) <> 0 Then ' case more than 1 entry in cell. starting at the first character  ,  in the current Cnt array element  , I look for  ", "  , stipulating an excact computer match search type        This Function will return eitheer the position counting from the left that it finds the first ", " or it will return 0 if it does not find at least one occurance of the ", "
        Let spltEnt() = VBA.Strings.Split(arrIn(Cnt, 1), ", ", -1, vbBinaryCompare) ' we now have a number or number pair
        Else ' case a single entry I cannot split by a ", " as i don't have any, ...
        ReDim spltEnt(0): Let spltEnt(0) = arrIn(Cnt, 1) ' ... so i just make a single element array and put the single element in it
        End If
    '2b) working through each data part in a cell
    Dim strOut As String 'String in each "row" '_-"Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular "Value", or ("Values" for Objects).  There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
    Dim CntX As Long '                        '_-Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )      https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
        For CntX = 0 To UBound(spltEnt()) ' for going through each entry in a row in other words for going through each piece of data in a cell
        '2c)(i) case just data for a single page
            If InStr(1, spltEnt(CntX), "-", vbBinaryCompare) = 0 Then ' case of no "-"
            Let strOut = strOut & spltEnt(CntX) & ", " ' just the single number goes in the output string, strOut
            Else ' we have a "-"
            Dim NmbrPear() As String ' this will be am Array of 2 elements for each number pair
            Let NmbrPear() = VBA.Strings.Split(spltEnt(CntX), "-", -1, vbBinaryCompare)
            '2c)(ii) case no correction needed in the data
                If Len(NmbrPear(0)) = Len(NmbrPear(1)) Then ' the numbers are the same
                Let strOut = strOut & spltEnt(CntX) & ", "  ' the same number pair goes in the output string
                Else ' from here on, we need to do some adjustment before adding to the output string
            '2c)(iii) cases data correction needed
                Select Case Len(NmbrPear(0)) - Len(NmbrPear(1)) ' selecting the case of the difference in length of the two parts of the data "FirstNumberPart-SecondNumberPart"
                  Case 1 ' Like 123-24 or 12345-2345
                  Let NmbrPear(1) = VBA.Strings.Mid$(NmbrPear(0), 1, 1) & NmbrPear(1) ' like 1 & 24 or 1 & 2345 ' VBA strings collection Mid Function: This returns the part of  (  NmbrPear(0)    ,  the starts at character 1    ,    and has the length of 1 character    )
                  Case 2 ' like 123-4
                  Let NmbrPear(1) = VBA.Strings.Mid$(NmbrPear(0), 1, 2) & NmbrPear(1) ' like 12 & 4
                  Case 3 ' like 1234-6
                  Let NmbrPear(1) = VBA.Strings.Mid$(NmbrPear(0), 1, 3) & NmbrPear(1) ' like 123 & 6
                  Case 3 ' like 12345-8
                  Let NmbrPear(1) = VBA.Strings.Mid$(NmbrPear(0), 1, 4) & NmbrPear(1) ' like 1234 & 8
                End Select ' at this point we have corrected our second number part from the pair
                Let strOut = strOut & VBA.Strings.Join(NmbrPear(), "-") & ", " ' The number pair is rejoined with the corrected second number part before adding the number parts pair to the output string
                End If
            End If
        Next CntX
    '2d) The string of corrected data can now be added to the array for output
    Let strOut = VBA.Strings.Left$(strOut, Len(strOut) - 2) ' This removes the last unwanted ", "  ' 'VBA Strings collection Left function returns a Variant- initially tries to coerces the first parameter into Variant, Left$ does not, that's why Left$ is preferable over Left, it's theoretically slightly more efficient, as it avoids the overhead/inefficieny associated with the Variant. It allows a Null to be returned if a Null is given. https://www.excelforum.com/excel-new...ml#post4084816 .. it is all to do with ya .."Null propagation".. maties ;) '_-.. http://allenbrowne.com/casu-12.html Null is a special "I do not know, / answer unknown" - handy to hav... propogetion wonks - math things like = 1+2+Null returns you null. Or string manipulation stuff like, left(Null returns you Null. Count things like Cnt (x,y,Null) will return 2 - there are two known things there..Hmm - bit iffy although you could argue that Null has not been entered yet.. may never
    Let arrOut(Cnt) = strOut ' Finally the string is aded to the current "row" in the  outout array
    Let strOut = "" ' Empty variable holding a row string for use ijn next loop
    Next Cnt
Rem 3 I have the final data array, and so umst now paste it out where I want it.
Dim arrClmOut() As String: ReDim arrClmOut(1 To UBound(arrOut), 1 To 1) ' This is for a 1 column 2 Dimensional array which I need for the orientation of my final output
'3(i) a simple loop to fill the transposed array
Dim rCnt As Long '
    For rCnt = 1 To UBound(arrOut())
    Let arrClmOut(rCnt, 1) = arrOut(rCnt)
    Next rCnt
'3(ii) Output to worksheet
 Let Range("B1").Resize(UBound(arrOut())).Value = arrClmOut() ' The cell Top left of where the output should go is resized to the required row size, and 1 column. The .Value Property of that range object may have the values in an Array assigned to it in a simpla one line assignment
End Sub
'
'
'
'  http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA?p=10192#post10192
'  https://www.excelforum.com/word-programming-vba-macros/1175184-vba-word-repeat-character-in-string-a-number-of-times.html#post4591171

---

Code for this Thread:
http://www.excelfox.com/forum/showth...0503#post10503
http://www.excelfox.com/forum/forumd...ips-and-Tricks


Function CStrSepDbl

Code:

---

'10  '  http://www.eileenslounge.com/viewtopic.php?f=27&t=22850#p208624
Function CStrSepDbl(Optional ByVal 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. )
20  Rem 0        At the Dim stage  a  '_-String  is "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects).  There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks, But http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
30    If StrPtr(strNumber) = 0 Then Let CStrSepDbl = "9999999999": Exit Function '_- StrPtr(MyVaraibleNotYetUsed)=0 ..  http://www.excelfox.com/forum/showthread.php/1828-How-To-React-To-The-Cancel-Button-in-a-VB-(not-Application)-InputBox?p=10463#post10463      https://www.mrexcel.com/forum/excel-questions/35206-test-inputbox-cancel-2.html?highlight=strptr#post2845398    https://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4412382
40  Rem 1  'Adding a leading zero if no number before a comma or point, change all seperators to comma  ,
50    If VBA.Strings.Left$(strNumber, 1) = "," Or VBA.Strings.Left$(strNumber, 1) = "." Then Let strNumber = "0" & strNumber  ' case for like .12  or ,7  etc 'VBA Strings collection Left function returns a Variant- initially tries to coerces the first parameter into Variant, Left$ does not, that's why Left$ is preferable over Left, it's theoretically slightly more efficient, as it avoids the overhead/inefficieny associated with the Variant. It allows a Null to be returned if a Null is given. https://www.excelforum.com/excel-new...ml#post4084816 .. it is all to do with ya .."Null propagation".. maties ;) '_-.. http://allenbrowne.com/casu-12.html Null is a special "I do not know, / answer unknown" - handy to hav... propogetion wonks - math things like = 1+2+Null returns you null. Or string manipulation stuff like, left(Null returns you Null. Count things like Cnt (x,y,Null) will return 2 - there are two known things there..Hmm -bit iffy although you could argue that Null has not been entered yet..may never
60    If VBA.Strings.Left$(strNumber, 2) = "-," Or VBA.Strings.Left$(strNumber, 2) = "-." Then Let strNumber = Application.WorksheetFunction.Replace(strNumber, 1, 1, "-0") ' case for like  -.12  or -,274  etc
70    Let strNumber = Replace(strNumber, ".", ",", 1, -1, vbBinaryCompare) 'Replace at start any . to a ,  After this point there should be either no or any amount of ,
80    'Check If a Seperator is present, then MAIN CODE is done
90    If InStr(1, strNumber, ",") > 0 Then 'Check we have at least one seperator, case we have, then..
100  Rem 2 'MAIN CODE part ====
110    'Length of String:  Position of last ( Decimal ) Seperator
120    Dim LenstrNumber As Long: Let LenstrNumber = Len(strNumber): Dim posDecSep As Long: Let posDecSep = VBA.Strings.InStrRev(strNumber, ",", LenstrNumber) '  from right the positom "along" from left  (  (in strNumber)  ,  for a  (",")    ,  starting at the ( Last character ) which BTW. is the default
130    'Whole Number Part
140    Dim strHlNumber As String: Let strHlNumber = VBA.Strings.Left$(strNumber, (posDecSep - 1))
150    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 ).
160    Dim HlNumber As Long: Let HlNumber = CLng(strHlNumber) 'Long Number is a Whole Number, no fractional Part
170    'Fraction Part of Number
180    Dim strFrction As String: Let strFrction = VBA.Strings.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 )
190    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?.
200    Dim Frction As Double: Let Frction = CDbl(strFrction) 'This will convert to a Whole Double Number. Double Number can have  Fractional part
210    Let Frction = Frction * 1 / (10 ^ (LenstrFrction)) 'Use 1/___, rather than a x 0.1 or 0,1 so as not to add another , . uncertainty!!
220    'Re join, using Maths to hopefully get correct Final Value
230    Dim DblReturn As Double 'Double Number to be returned in required Format after maniplulation.
240        If Left(strHlNumber, 1) <> "-" Then 'Case positive number
250          Let DblReturn = CDbl(HlNumber) + Frction 'Hopefully a simple Mathematics + will give the correct Double Number back
260        Else 'Case -ve Number
270          Let strHlNumber = Replace(strHlNumber, "-", "", 1, 1, vbBinaryCompare) ' strHlNumber * (-1) ' "Remove" -ve sign
280          Let DblReturn = (-1) * (CDbl(strHlNumber) + Frction) 'having constructed the value of the final Number we multiply by -1 to put the Minus sign back
290        End If 'End checking polarity.
300    'Final Code Line(s)  At this point we have what we want. We need to place this in the "Double Type variable" , CStrSepDbl , so that an assinment like    = CStrSepDbl( ) will return this final value
310    Let CStrSepDbl = DblReturn 'Final Double value to be returned by Function
320    Else 'End MAIN CODE. === We came here if we have a Whole Number with no seperator, case no seperator
330    'Simple conversion of a string "Number" with no Decimal Seperator to Double Format
340    Let CStrSepDbl = CDbl(strNumber) 'String to be returned by Function is here just a simple convert to Double ' I guess this will convert a zero length string "" to 0 also
350    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-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

---

Demo Code to call Function

Code:

---

Sub TestieCStrSepDbl() ' 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 = CStrSepDbl(Stear)
    Dim TabulatorSyncranartor As String: Let TabulatorSyncranartor = "                        "
    LSet TabulatorSyncranartor = Stear
    Let MyStringsOut = MyStringsOut & TabulatorSyncranartor & Retn & vbCrLf
    Debug.Print Stear; Tab(15); Retn
    Next Stear
 MsgBox MyStringsOut
End Sub

---

Code also Here:
https://pastebin.com/1kq6h9Bn

Code for this Thread:
http://www.excelfox.com/forum/showth...0503#post10503
http://www.excelfox.com/forum/forumd...ips-and-Tricks


Function CStrSepDbl

Code:

---

'10  '  http://www.eileenslounge.com/viewtopic.php?f=27&t=22850#p208624
Function CStrSepDbl(Optional ByVal 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. )
20  Rem 0        At the Dim stage  a  '_-String  is "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects).  There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks, But http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
30    If StrPtr(strNumber) = 0 Then Let CStrSepDbl = "9999999999": Exit Function '_- StrPtr(MyVaraibleNotYetUsed)=0 ..  http://www.excelfox.com/forum/showthread.php/1828-How-To-React-To-The-Cancel-Button-in-a-VB-(not-Application)-InputBox?p=10463#post10463      https://www.mrexcel.com/forum/excel-questions/35206-test-inputbox-cancel-2.html?highlight=strptr#post2845398    https://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4412382
40  Rem 1  'Adding a leading zero if no number before a comma or point, change all seperators to comma  ,
50    If VBA.Strings.Left$(strNumber, 1) = "," Or VBA.Strings.Left$(strNumber, 1) = "." Then Let strNumber = "0" & strNumber  ' case for like .12  or ,7  etc 'VBA Strings collection Left function returns a Variant- initially tries to coerces the first parameter into Variant, Left$ does not, that's why Left$ is preferable over Left, it's theoretically slightly more efficient, as it avoids the overhead/inefficieny associated with the Variant. It allows a Null to be returned if a Null is given. https://www.excelforum.com/excel-new...ml#post4084816 .. it is all to do with ya .."Null propagation".. maties ;) '_-.. http://allenbrowne.com/casu-12.html Null is a special "I do not know, / answer unknown" - handy to hav... propogetion wonks - math things like = 1+2+Null returns you null. Or string manipulation stuff like, left(Null returns you Null. Count things like Cnt (x,y,Null) will return 2 - there are two known things there..Hmm -bit iffy although you could argue that Null has not been entered yet..may never
60    If VBA.Strings.Left$(strNumber, 2) = "-," Or VBA.Strings.Left$(strNumber, 2) = "-." Then Let strNumber = Application.WorksheetFunction.Replace(strNumber, 1, 1, "-0") ' case for like  -.12  or -,274  etc
70    Let strNumber = Replace(strNumber, ".", ",", 1, -1, vbBinaryCompare) 'Replace at start any . to a ,  After this point there should be either no or any amount of ,
80    'Check If a Seperator is present, then MAIN CODE is done
90    If InStr(1, strNumber, ",") > 0 Then 'Check we have at least one seperator, case we have, then..
100  Rem 2 'MAIN CODE part ====
110    'Length of String:  Position of last ( Decimal ) Seperator
120    Dim LenstrNumber As Long: Let LenstrNumber = Len(strNumber): Dim posDecSep As Long: Let posDecSep = VBA.Strings.InStrRev(strNumber, ",", LenstrNumber) '  from right the positom "along" from left  (  (in strNumber)  ,  for a  (",")    ,  starting at the ( Last character ) which BTW. is the default
130    'Whole Number Part
140    Dim strHlNumber As String: Let strHlNumber = VBA.Strings.Left$(strNumber, (posDecSep - 1))
150    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 ).
160    Dim HlNumber As Long: Let HlNumber = CLng(strHlNumber) 'Long Number is a Whole Number, no fractional Part
170    'Fraction Part of Number
180    Dim strFrction As String: Let strFrction = VBA.Strings.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 )
190    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?.
200    Dim Frction As Double: Let Frction = CDbl(strFrction) 'This will convert to a Whole Double Number. Double Number can have  Fractional part
210    Let Frction = Frction * 1 / (10 ^ (LenstrFrction)) 'Use 1/___, rather than a x 0.1 or 0,1 so as not to add another , . uncertainty!!
220    'Re join, using Maths to hopefully get correct Final Value
230    Dim DblReturn As Double 'Double Number to be returned in required Format after maniplulation.
240        If Left(strHlNumber, 1) <> "-" Then 'Case positive number
250          Let DblReturn = CDbl(HlNumber) + Frction 'Hopefully a simple Mathematics + will give the correct Double Number back
260        Else 'Case -ve Number
270          Let strHlNumber = Replace(strHlNumber, "-", "", 1, 1, vbBinaryCompare) ' strHlNumber * (-1) ' "Remove" -ve sign
280          Let DblReturn = (-1) * (CDbl(strHlNumber) + Frction) 'having constructed the value of the final Number we multiply by -1 to put the Minus sign back
290        End If 'End checking polarity.
300    'Final Code Line(s)  At this point we have what we want. We need to place this in the "Double Type variable" , CStrSepDbl , so that an assinment like    = CStrSepDbl( ) will return this final value
310    Let CStrSepDbl = DblReturn 'Final Double value to be returned by Function
320    Else 'End MAIN CODE. === We came here if we have a Whole Number with no seperator, case no seperator
330    'Simple conversion of a string "Number" with no Decimal Seperator to Double Format
340    Let CStrSepDbl = CDbl(strNumber) 'String to be returned by Function is here just a simple convert to Double ' I guess this will convert a zero length string "" to 0 also
350    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-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

---

Demo Code to call Function

Code:

---

Sub TestieCStrSepDbl() ' 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 = CStrSepDbl(Stear)
    Dim TabulatorSyncranartor As String: Let TabulatorSyncranartor = "                        "
    LSet TabulatorSyncranartor = Stear
    Let MyStringsOut = MyStringsOut & TabulatorSyncranartor & Retn & vbCrLf
    Debug.Print Stear; Tab(15); Retn
    Next Stear
 MsgBox MyStringsOut
End Sub

---

Code also Here:
https://pastebin.com/1kq6h9Bn

_1 ) Way 1) Use the CDO (Collaboration Data Objects ) object library available in VBA
Main Code , Sub PetrasDailyProWay1_COM_Way() ,
and
Function Code for solution to this Thread and Post
http://www.excelfox.com/forum/showth...kbooks-at-once
http://www.excelfox.com/forum/showth...0518#post10518

Code:

---

Option Explicit ' Daily Diet plan, Sending of Notes and an Excel File
Sub PetrasDailyProWay1_COM_Way() '  Allow access to deep down cods wollops from Microsoft to collaborating in particular in the form of messaging. An available library of ddl library functions and associated things is available on request, the  Microsoft CDO for Windows 2000. We require some of these '  CDO is an object library that exposes the interfaces of the Messaging Application Programming Interface (MAPI). API: interfaces that are fairly easy to use from a fairly higher level from within a higher level programming language. In other words this allows you to get at and use some of the stuff to do with the COM OLE Bollocks from within a programming language such as VBA  API is often referring loosely to do with using certain shipped with Windows software in Folders often having the extension dll. This extension , or rather the dll stands for direct link libraries. These are special sort of executable files of functions shared by many other (Windows based usually) software’s.
' Rem1 The deep down fundamental stuff , which includes stuff been there the longest goes by the name of Component Object Model. Stuff which is often, but not always, later stuff, or at a slightly higher level of the computer workings, or slightly more to a specific application ( an actual running "runtime" usage / at an instance in time , "instance of" ) orientated goes to the name of Object Linking and Embedding. At this lower level, there are protocols for communicating between things, and things relate are grouped into the  to Office  application available Library, CDO. An important object there goes by the name of Message.
'Rem 1) Library made available            ====================#
  With CreateObject("CDO.Message") '  Folders mostly but not always are in some way referenced using dll, either as noted with the extension or maybe refered to as dll Files or dll API files.
'Rem 2 ' Intraction protocols are given requird infomation and then set
    '2a) 'With --------------------* my Created LCDCW Library, (LCD 1.0 Library ) (Linking Configuration Data_Cods Wollups) which are used and items configured for the Exchange at Microsoft’s protocol thereof;  http://schemas.microsoft.com/cdo/configuration/ ......This section provides the configuration information for the remote SMTP server
    Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/" ' Linking Configuration Data : defines the majority of fields used to set configurations for various Linking Collaboration (LCD) Objects Cods Wollops: These configuration fields are set using an implementation of the IConfiguration.Fields collection.  https://msdn.microsoft.com/en-us/library/ms872853(v=exchg.65).aspx
    .Configuration(LCD_CW & "smtpusessl") = True ' ' ' HTTPS (Hyper Text Transfer Protocol Secure) appears in the URL when a website is secured by an SSL certificate. The details of the certificate, including the issuing authority and the corporate name of the website owner, can be viewed by clicking on the lock symbol on the browser bar. in short, it's the standard technology for keeping an internet connection secure and safeguarding any sensitive data that is being sent between two systems, preventing criminals from reading and modifying any information transferred, including potential personal details.  ' SSL protocol has always been used to encrypt and secure transmitted data
    .Configuration(LCD_CW & "smtpauthenticate") = 1  ' ... possibly this also needed ..  When you also get the Authentication Required Error you can add this three lines.
    '  ' Sever info
    .Configuration(LCD_CW & "smtpserver") = "smtp.gmail.com" ' "securesmtp.t-online.de"                '"smtp.gmail.com" "smtp.mail.yahoo.com" "smtp.live.com" "pod51017.outlook.com"  "smtp-mail.outlook.com" "smtp.live.com"  "securesmtp.t-online.de"  465        SMTP is just used to mean the common stuff.....  Simple Mail Transport Protocol (SMTP) server is used to send outgoing e-mails. The SMTP server receives emails from your Mail program and sends them over the Internet to their destination.
    '  The mechanism to use to send messages.
    .Configuration(LCD_CW & "sendusing") = 2  '  Based on the LCD_OLE Data Base of type DBTYPE_I4
    .Configuration(LCD_CW & "smtpserverport") = 25 ' 465or25fort-online ' 465 'or 587 'or 25  ' The port of type somehow refered to by the last line
    '
    .Configuration(LCD_CW & "sendusername") = "excelvbaexp@gmail.com" ' "Doc.AElstein@t-online.de" ' .... "server rejected your response".  AFAIK : This will happen if you haven't setup an account in Outlook Express or Windows Mail .... Runtime error '-2147220975 (800440211)': The message could not be sent to the SMTP server. The transport error code is 0x80040217. The server response is not available
    .Configuration(LCD_CW & "sendpassword") = "Bollocks" '              "Bollox"
    ' Optional - How long to try    ( End remote SMTP server configuration section )
    .Configuration(LCD_CW & "smtpconnectiontimeout") = 30 '    Or there Abouts ;) :)
    ' Intraction protocol is Set/ Updated
    .Configuration.Fields.Update ' 'Not all infomation is given, some will have defaults. - possibly this might be needed initially ..    .Configuration.Load -1 ' CDO Source Defaults
    'End With ' -------------------* my Created  LCDCW Library ( Linking Configuration Data Cods Wollups)  which are  used and items configured for the Exchange at Microsoft's protocol therof;
  '2b) ' Data to be sent
  '.To = "Doc.AElstein@t-online.de"
  .To = "excelvbaexp@gmail.com"
  .CC = ""
  .BCC = ""
  .from = """Alan"" <Doc.AElstein@t-online.de>"
  .Subject = "Bollox"
  '.TextBody = "Hi" & vbNewLine & vbNewLine & "Please find the Excel workbook attached."
  .HTMLBody = MyLengthyStreaming
  .AddAttachment "G:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan2016\Übersicht aktuell.xlsx" ' ' Full File path and name. File must be closed
 Rem 3 Do it
  .Send
 End With ' CreateObject("CDO.Message") (Rem 1 Library End =======#
End Sub
Public Function MyLengthyStreaming() As String
Rem 1 Make a long string from a Microsoft Word doc
'1(i) makes available the Library of stuff, objects, Methods etc.
Dim Fso As Object: Set Fso = CreateObject("Scripting.FileSystemObject")
'1(ii) makes the big File Object                      " Full path and file name of Word doc saved as .htm      "
Dim FileObject As Object: Set FileObject = Fso.GetFile("G:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan2016\ProMessage.htm"): Debug.Print FileObject
'1(iii) sets up the data  "stream highway"
Dim Textreme As Object:  Set Textreme = FileObject.OpenAsTextStream(iomode:=1, Format:=-2)        '  reading only, Opens using system default            https://msdn.microsoft.com/en-us/library/aa265341(v=vs.60).aspx
'1(iv) pulls in the data, in our case into a simple string variable
 Let MyLengthyStreaming = Textreme.ReadAll        '        Let MyLengthyStreaming = Replace(MyLengthyStreaming, "align=center x:publishsource=", "align=left x:publishsource=")
 Textreme.Close
 Set Textreme = Nothing
 Set Fso = Nothing
Rem 2 possible additions to MyLengthyStreaming

---

Last bit of Function ( must go here in the excelfox Test Sub Forum in HTML Tags as there are HTML Tags in the final text string string and this makes a mess in normal BB code tags, because in excelfox Test Forum HTML is activated ) :

HTML Code:

---

Rem 2
 Let MyLengthyStreaming = "<p><span style=""color: #ff00ff;"">Start=========== " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ------------------------------------</span></p>" & MyLengthyStreaming & "<p><span style=""color: #ff00ff;"">-- " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ==End, Sent from Doc.AElstein Mail ======</span></p>"
End Function

---

later

An answer I had difficulty posting here: http://www.vbaexpress.com/forum/show...141#post426141

---

Hi
Another possibility.

Download both files, - put them anywhere as long as they are in the same place.
Just open FileToRunMacroInOtherFile.xls ( leave OtherFileWithSubMakeFinalVendorsInIt.xls closed )

Now run this macro which is in FileToRunMacroInOtherFile.xls

Code:

---

Sub AppRunSubMakeFinalVendorsInOtherFile() ' http://www.vbaexpress.com/forum/showthread.php?72087-Error-while-trying-to-run-a-macro-using-a-selected-workbook
 Application.Run macro:="'" & ThisWorkbook.Path & "\" & "OtherFileWithSubMakeFinalVendorsInIt.xls" & "'" & "!'Modul1.MakeFinalVendors'"
End Sub

---

That should cause the other file to open, and the macro in it , Sub MakeFinalVendors() , should run

( If you had both files open, the effect would be the same – running Sub AppRunSubMakeFinalVendorsInOtherFile() should make Sub MakeFinalVendors() run )

Alan