DocAElstein
Banned user
- Joined
- May 24, 2014
- Messages
- 1,336
Hi,
This caught me out, in a nasty way just now, so I thought I might just mention it in Passing.
So what am I talking about:......
Many of us are aware that the VBA Split Function written so
Dim arr() as String
Let Arr() = Split(strTest)
Is identical to including an optional second argument thus
Let arr() = Split(strTest, " ")
These both Split the string, strTest, by as many times as there are single spaces in the String strTest, returning a 1 Dimensional Array of String Types. ( Although the Array has technically no orientation, the Excel convention seems to be to accept this into a Spreadsheet as a Horizontal Array**
Similarly if
strTest = "1|2|3" then
Split(strTest, "|")
returns
Arr(0)=”1”
Arr(1)=”2”
Arr(2)=”3”
**Just a note for some syntax and notation convention consistency: We can use VBA ( or Excel ) to put strings “1” “2” and “3” somewhere in a 3 column single row. We can, for example use a code line thus:
Range("G1:I1").FormulaArray = "={""1"",""2"",""3""}"
This gives us in the Spreadsheet this:
The code line is equivalent to a CSE Type 2 Formula insertion across a selected 3 column, 1 row Range of this form for English USA Excel ={"1","2","3"}
( BTW for European Convention to insert you use this formula ={"1"\"2"\"3"} )
Once inserted, Selecting this formula in the Formula bar and Hitting F9 ( to do an instant evaluation of it ) shows the representation conventionally for English Excel {"1","2","3"}**
( BTW for European Excel you see this {"1"\"2"\"3"} )
So far so good.
_.....................................................................................
The Optional Third argument is a bit more subtle, IMO. I got caught out thinking it referred to the number of separators used, such that
Arr()= Split(strTest, “|“ , 1 )
Would give me
{ “1” , “2|3” }
or
Arr(0)=”1”
Arr(1)=”2|3”
It Doesn’t. The third argument has two subtle points. It refers
_a) to the maximum number of outputs you get,
AND
_b) VBA stops splitting after this number has been reached
Such that
Arr()= Split(strTest, “|“ , 1 )
Would not give me
{ “1” }
or
Arr(0)=”1”
But noting point b) I obtain rather
{ “1|2|3” }
or
Arr(0)=”1|2|3”
In this first case, may be we can think of it as if VBA “pseudo stopped splitting before it started” as from the outset it had 1 Part.
Similarly
Arr()= Split(strTest, “|“ , 2 )
Would not give me
{ “1” , “2” }
or
Arr(0)=”1”
Arr(1)=”2”
It would in fact give me
{ “1” , “2|3” }
or
Arr(0)=”1”
Arr(1)=”2|3”
The documentation does clearly state point _a), but I think the instinct can be to get the wrong idea as I did originally.
The documentation does not clearly state IMO point _b). One could easily interpret incorrectly as I did that the full Split is “done” , but that the third argument determines how many of the Split Elements are returned.
_....................
One way to explain Split better may be would be:
~~~~“If you have no Third argument you Split the String in the First argument
~~~~~as many times as you can using the~~separator
~~~~~( of type~~stipulated in the second argument~~( or by a space if no second argument ) )”
~~~~“Else”
~~~~~~“While you do not have as many parts as stipulated in the Third argument
~~~~~~~~AND you have not reached~~the maximum possible Elements,
~~~~~~you Do Split the String in the First argument by the next separator
~~~~~~~~( of type~~stipulated in the second argument~~( or by a space if no second argument ) )”
~~~~“End of it”
I think that covers most situations, including an interesting outcome that if you have no or any second argument and a third argument of “1” then the Split Function can be used to convert a string to a 1 Dimension 1 Element Array with the String Value of the first Split argument , as a String Type, in it.
Arr() = Split(strTest, , 1)
Giving
Arr(0)= "1|2|3"
Or
{ “1|2|3” }
_...................
Here a demo code I did to remind me. It runs through a few more examples and displays the results in a Message Box:
Alan.
Ref
MS Excel: How to use the SPLIT Function (VBA)
https://msdn.microsoft.com/de-de/library/6x627e5f(v=vs.90).aspx
INDEX: Returning an array of values « EXCELXOR
http://www.mrexcel.com/forum/excel-...ions-evaluate-range-vlookup.html?#post3944863
This caught me out, in a nasty way just now, so I thought I might just mention it in Passing.
So what am I talking about:......
Many of us are aware that the VBA Split Function written so
Dim arr() as String
Let Arr() = Split(strTest)
Is identical to including an optional second argument thus
Let arr() = Split(strTest, " ")
These both Split the string, strTest, by as many times as there are single spaces in the String strTest, returning a 1 Dimensional Array of String Types. ( Although the Array has technically no orientation, the Excel convention seems to be to accept this into a Spreadsheet as a Horizontal Array**
Similarly if
strTest = "1|2|3" then
Split(strTest, "|")
returns
Arr(0)=”1”
Arr(1)=”2”
Arr(2)=”3”
**Just a note for some syntax and notation convention consistency: We can use VBA ( or Excel ) to put strings “1” “2” and “3” somewhere in a 3 column single row. We can, for example use a code line thus:
Range("G1:I1").FormulaArray = "={""1"",""2"",""3""}"
This gives us in the Spreadsheet this:
| Row\Col | G | H | I |
1 | 1 | 2 | 3 |
The code line is equivalent to a CSE Type 2 Formula insertion across a selected 3 column, 1 row Range of this form for English USA Excel ={"1","2","3"}
( BTW for European Convention to insert you use this formula ={"1"\"2"\"3"} )
| Row\Col | G | H | I |
1 | {={"1","2","3"}} | {={"1","2","3"}} | {={"1","2","3"}} |
Once inserted, Selecting this formula in the Formula bar and Hitting F9 ( to do an instant evaluation of it ) shows the representation conventionally for English Excel {"1","2","3"}**
( BTW for European Excel you see this {"1"\"2"\"3"} )
So far so good.
_.....................................................................................
The Optional Third argument is a bit more subtle, IMO. I got caught out thinking it referred to the number of separators used, such that
Arr()= Split(strTest, “|“ , 1 )
Would give me
{ “1” , “2|3” }
or
Arr(0)=”1”
Arr(1)=”2|3”
It Doesn’t. The third argument has two subtle points. It refers
_a) to the maximum number of outputs you get,
AND
_b) VBA stops splitting after this number has been reached
Such that
Arr()= Split(strTest, “|“ , 1 )
Would not give me
{ “1” }
or
Arr(0)=”1”
But noting point b) I obtain rather
{ “1|2|3” }
or
Arr(0)=”1|2|3”
In this first case, may be we can think of it as if VBA “pseudo stopped splitting before it started” as from the outset it had 1 Part.
Similarly
Arr()= Split(strTest, “|“ , 2 )
Would not give me
{ “1” , “2” }
or
Arr(0)=”1”
Arr(1)=”2”
It would in fact give me
{ “1” , “2|3” }
or
Arr(0)=”1”
Arr(1)=”2|3”
The documentation does clearly state point _a), but I think the instinct can be to get the wrong idea as I did originally.
The documentation does not clearly state IMO point _b). One could easily interpret incorrectly as I did that the full Split is “done” , but that the third argument determines how many of the Split Elements are returned.
_....................
One way to explain Split better may be would be:
~~~~“If you have no Third argument you Split the String in the First argument
~~~~~as many times as you can using the~~separator
~~~~~( of type~~stipulated in the second argument~~( or by a space if no second argument ) )”
~~~~“Else”
~~~~~~“While you do not have as many parts as stipulated in the Third argument
~~~~~~~~AND you have not reached~~the maximum possible Elements,
~~~~~~you Do Split the String in the First argument by the next separator
~~~~~~~~( of type~~stipulated in the second argument~~( or by a space if no second argument ) )”
~~~~“End of it”
I think that covers most situations, including an interesting outcome that if you have no or any second argument and a third argument of “1” then the Split Function can be used to convert a string to a 1 Dimension 1 Element Array with the String Value of the first Split argument , as a String Type, in it.
Arr() = Split(strTest, , 1)
Giving
Arr(0)= "1|2|3"
Or
{ “1|2|3” }
_...................
Here a demo code I did to remind me. It runs through a few more examples and displays the results in a Message Box:
Code:
[color=blue]Sub[/color] SplitMyTeststr() [color=darkgreen]'Split a Test String using VBA [color=blue]String[/color]s collection Split Function.[/color]
Rem 1) Variable Declarations
[color=darkgreen]'1a) Main variables associted with Spliting[/color]
[color=blue]Dim[/color] strTest [color=blue]As[/color] String [color=darkgreen]' Assign a memory Point ( Pigeon Hole / ) capable of storing all infomation required for assignment of a String type to this Variable. Give it the "Address" strTest. At this point we have vbNull[color=blue]String[/color], an "Empty" condition with no infomation. http://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4413433[/color]
[color=blue]Let[/color] strTest = "1|2|3" [color=darkgreen]'Infomation given to strTest for memory start positon and length where value "1|2|3" is held[/color]
[color=blue]Dim[/color] SpltSep() [color=blue]As[/color] String [color=darkgreen]'1 D "pseudo Horizontal" Array to hold Split strTest Values. ' Note: String Collection Split will be used which returns an Array of [color=blue]String[/color] Types. Must be Dynamicaly as you cannot generally return Data Filed to a fixed Array. VBA will want to effectively Dimension it on the assigning line.[/color]
[color=darkgreen]'1b) Just Some Variables for presenting obtained values by Split Function[/color]
[color=blue]Dim[/color] strResult [color=blue]As[/color] String [color=darkgreen]'Variable for a string to present output in a Message Box[/color]
[color=blue]Dim[/color] Cnt [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'Loop Bound Variable Count for Looping throught and index, Split(Cnt), of obtained Array Elements. Declared varibale Type of Long will allow immediately all info except the value in the Location ( address) assigned to Cnt, as the Physical length is fixed regardless of actual value[/color]
Rem 2) Split with no third argument should split by all occurances of |. A "Spreadsheet parallel" would be this CSE Formula ws.Range("G1:I1").FormulaArray = "={""1"",""2"",""3""}" [color=darkgreen]' After insertion, [color=blue]For[/color]mula Bar F9 in German gives {"1"\"2"\"3"} in English probably {"1","2","3"}[/color]
[color=blue]Let[/color] SpltSep() = VBA.Split(strTest, "|") [color=darkgreen]'Should return from (strTest) a SpltSep(0) and SpltSep(1) and split(2) , as split by ("|") , No third argument so strTest is split by all occurances of | Remember to use VBA. or it might catch you out when you write the code in a Later Version of Excel then run it in an earlier one, as unqualified it may it may try to referrence by default a Library not present in an earlier version. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays-4.html#post4083569[/color]
[color=blue]For[/color] Cnt = 0 [color=blue]To[/color] [color=blue]UBound[/color](SpltSep()) [color=darkgreen]' ( Start at 0 as "internally" generated Arrays will start at defaualt of base 0 )[/color]
[color=blue]Let[/color] strResult = strResult & " , " & """" & SpltSep(Cnt) & """"
[color=blue]Next[/color] Cnt
[color=blue]Let[/color] strResult = Mid(strResult, 5, ((Len(strResult) - 5) + 1)) [color=darkgreen]'Just take off first " , "[/color]
[color=blue]Let[/color] strResult = "{ " & strResult & " }"
MsgBox prompt:="You ""did a VBA.Split"" of """ & strTest & """ , by a ""|"" with no third argument " & vbCr & vbLf & " so you got an Array with all " & (UBound(SpltSep()) + 1) & " Numbers in it like" & vbCr & vbLf & strResult & vbCr & vbLf & ""
[color=blue]Let[/color] strResult = [color=blue]Empty[/color] [color=darkgreen]'reset so can be used in next section ( by quickest way shouldn't be ...... http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html#post4418655[/color]
Rem 3) Split with third argument "1" so "does no split" as a 1 element is there already!
[color=blue]Let[/color] SpltSep() = VBA.Split(strTest, "|", 1)
[color=blue]For[/color] Cnt = 0 [color=blue]To[/color] [color=blue]UBound[/color](SpltSep()) [color=darkgreen]'[/color]
[color=blue]Let[/color] strResult = strResult & " , " & """" & SpltSep(Cnt) & """"
[color=blue]Next[/color] Cnt
MsgBox prompt:="You ""did a VBA.Split"" of """ & strTest & """ , by a ""|"" with third argument of ""1"" " & vbCr & vbLf & " so you got an Array with only " & (UBound(SpltSep()) + 1) & " thing in it which is the original [color=blue]String[/color] " & vbCr & vbLf & ("{ " & (Mid(strResult, 5, ((Len(strResult) - 5) + 1))) & " }") & vbCr & vbLf & ""
[color=blue]Let[/color] strResult = [color=blue]Empty[/color]
Rem 4) Split with third argument "2" so you split up to 2 times by | until you have 2 parts
[color=blue]Let[/color] SpltSep() = VBA.Split(strTest, "|", 2)
[color=blue]For[/color] Cnt = 0 [color=blue]To[/color] [color=blue]UBound[/color](SpltSep()) [color=darkgreen]'[/color]
[color=blue]Let[/color] strResult = strResult & " , " & """" & SpltSep(Cnt) & """"
[color=blue]Next[/color] Cnt
MsgBox prompt:="You ""did a VBA.Split"" of """ & strTest & """ , by a ""|"" with third argument of ""2"" " & vbCr & vbLf & " - you got an Array with " & (UBound(SpltSep()) + 1) & " things in it like" & vbCr & vbLf & ("{ " & (Mid(strResult, 5, ((Len(strResult) - 5) + 1))) & " }") & vbCr & vbLf & ""
[color=blue]Let[/color] strResult = [color=blue]Empty[/color]
Rem 5) Split with third argument "3" so you split up to 3 times by | until you have 3 parts
[color=blue]Let[/color] SpltSep() = VBA.Split(strTest, "|", 3)
[color=blue]For[/color] Cnt = 0 [color=blue]To[/color] [color=blue]UBound[/color](SpltSep()) [color=darkgreen]'[/color]
[color=blue]Let[/color] strResult = strResult & " , " & """" & SpltSep(Cnt) & """"
[color=blue]Next[/color] Cnt
MsgBox prompt:="You ""did a VBA.Split"" of """ & strTest & """ , by a ""|"" with third argument of ""3"" " & vbCr & vbLf & " - you got an Array with " & (UBound(SpltSep()) + 1) & " Numbers in it like" & vbCr & vbLf & ("{ " & (Mid(strResult, 5, ((Len(strResult) - 5) + 1))) & " }") & vbCr & vbLf & ""
[color=blue]Let[/color] strResult = [color=blue]Empty[/color]
Rem 6) Split with third argument "10" so you split up to 10 times by | untill you have 10 parts
[color=blue]Let[/color] SpltSep() = VBA.Split(strTest, "|", 10)
For Cnt = 0 [color=blue]To[/color] [color=blue]UBound[/color](SpltSep()) [color=darkgreen]'[/color]
[color=blue]Let[/color] strResult = strResult & " , " & """" & SpltSep(Cnt) & """"
[color=blue]Next[/color] Cnt
MsgBox prompt:="You ""did a VBA.Split"" of """ & strTest & """ , by a ""|"" with third argument of ""10"" " & vbCr & vbLf & " - you got an Array with " & (UBound(SpltSep()) + 1) & " Numbers in it like" & vbCr & vbLf & ("{ " & (Mid(strResult, 5, ((Len(strResult) - 5) + 1))) & " }") & vbCr & vbLf & "and i expect VBA stopped trying to Split when it reached the maximum of 3 Elements in this case"
Rem 7) Use Split with no second argument and third argument of "1" to change a [color=blue]String[/color] to a One Element 1 Dimensional Array
[color=blue]Let[/color] SpltSep() = Split(strTest, , 1)
MsgBox prompt:="You did this " & vbCr & vbLf & "SpltSep() = Split(strTest, , 1)" & vbCr & vbLf & "You got a one Element 1 Dimensional Array with your strTest value in it" & vbCr & vbLf & "SpltSep(0)= """ & strTest & """"
Rem 8) Use Split with any second argument and a third argument of 1 to change a String to a One Element 1 Dimensional Arra
[color=blue]Let[/color] SpltSep() = Split(strTest, "Anythink I Like", 1)
MsgBox prompt:="You did this " & vbCr & vbLf & "Split(strTest, ""Anythink I Like"", 1)" & vbCr & vbLf & "You got a one Element 1 Dimensional Array with your strTest value in it" & vbCr & vbLf & "SpltSep(0)= """ & strTest & """"
[color=blue]End[/color] [color=blue]Sub[/color]Alan.
Ref
MS Excel: How to use the SPLIT Function (VBA)
https://msdn.microsoft.com/de-de/library/6x627e5f(v=vs.90).aspx
INDEX: Returning an array of values « EXCELXOR
http://www.mrexcel.com/forum/excel-...ions-evaluate-range-vlookup.html?#post3944863
Last edited:
