VBA Split Function Third Argument refers to Maximum Outputs and “when Splitting Stops!”

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:
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:

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
**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""}"
Just pointing out a way to assign consecutive integers to cells rather assigning array formulas to them...

If the receiving cell range is fixed and the numbers being assigned to them are fixed and relatively few in number, the you can do it this way...

[G1:I1] = [{1,2,3}]

If range is fixed, but the number of values is large (say, 100), but still fixed in number, then you can do it this way...

[G1:DB1] = [COLUMN(1:100)]

Note that if you wanted to number the first 100 cells in Column H, you could do it this way...

[H1:H100] = [ROW(1:100)]

If the number of values is not fixed (let's say the number is assigned to a variable N), then you could use the Evaluate function to do it...

N = 100
Range("G1").Resize(, N) = Evaluate("COLUMN(1:" & N & ")")

Similarly, if you wanted to number the first 100 cells in Column B, you could do it this way...

N = 100
Range("B1").Resize(N) = Evaluate("ROW(1:" & N & ")")
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Hi
Thanks very much for the reply Rick.


There was no significance to me having sequential numbers for my example. Anything would have done like
{ “Any” , “3” , “Finks” }
_...................

I think with your............
_1) [G1:I1] = [{1,2,3}]
Or
_1) [G1:I1] = [{ “Any” , “3” , “Finks” }]

and my……………
_2 ) Range("G1:I1").FormulaArray = "={""1"",""2"",""3""}"
Or
_2) Range("G1:I1").FormulaArray = "={ ""Any"" , ""3"" , ""Finks""} "

_..............We are ( or at least me , maybe** ) taking about different thinks
_...........................................

Examine _2) First of all

I have some idea or way of thinking of how VBA “works”. I think there is only one Cell Object. When you see a Spreadsheet on your Screen is a continual Screen Update of the positions ( in digital increments ) of the strands on two Hairbrushes when you have a Hairbrush in one orientation in one hand and a Hairbrush in the other hand in another orientation... And then you bring them together and slide them against each other, up and down and side to side. Every strand interception ( at a specific digital offset ) triggers a “Cell” on the Screen which is really the one Cell Object that Excel has. If you have “Used” the Cell it is like in VBA you Declared ( Dim ed ) it and Set it. Otherwise Excel it is just a lot of “Pigeon” Holes with a Piece of paper, a “Blue Print” ( With instructions, ( Methods, Properties "Things" etc ) written on ) , in them not yet filled in to give the actual Memory locations of where the various values for all those "things"are held.

Coming back to my “.... syntax and notation convention consistency...” :... I am thinking that
{ “Any” , “3” , “Finks” }
Is what you see in the Formula bar after selecting on either a formula ( Like your Column(__ ) or a Range ( formula ) like =G1:I1 and after selecting hit F9 to get an “Evaluation” shoen in the Formula bar.
I am trying ( very badly) to explain that
{ “Any” , “3” , “Finks” }
Is Excel’s representation of one of the two Hairbrushes. We only seeas if we are looking, as it were, in a direction parallel to the strands of the brush. Or we only see the pointed ends. Type 2 Controlling the Shift ( Orientation ) Formula In the spreadsheet Entries ( CSE ) , just ties down the “length” bit of the strand that we see.
To Demo this: If you pervert a bit my Range("G1:I1").FormulaArray = "={ ""Any"" , ""3"" , ""Finks""} "
And do this instead:
Range("E8:G9").FormulaArray = "={ ""Any"" , ""3"" , ""Finks""} "
Then you get this:
Row\Col
E​
F​
G​
8​
Any​
3​
Finks​
9​
Any​
3​
Finks​
(_............... How “far” this works is a bit complicated. It is to do with the “Brush” Strand lengths. Different systems, Excel versions, can all mess this up a bit. Microsoft have not quite got this right yet since going above XL 2003 and the different Limits can sometimes throw a Spanner in the Works,
Transpose bug in 2013 and 2016 | Excel Matters
as well as “Worksheet Functions” not always working the same as WorksheetFunction’s
Trouble writing huge array into worksheet range [SOLVED]
_.............)

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

The Reason why the 1 Dimensional Array is “seen” pseudo by Excel as a “row” is that it uses the same “Brush” as that used for the Excel “row”. It just has not been intercepted yet with any strands from the “column” brush. When you Put the 1D Array in a Spreadsheet, Excel has a lot of “long column brush strands” there and the relevant one ties it down finally.
Things like the Split Function and the Array Function ( = Array (_,_,__) ) result in this “pseudo” Horizontal Array which extends vertically to a length Microsoft have not quite figured out yet. This “pseudo” Horizontal Array is the default one of Excel and VBA.

When you “make a column call” excel does something similar to the other brush. (which is “transposed” effectively .....)
So for example to Demo this ( or "reveal a bit of it" )
Range("E10:F11").FormulaArray = "={ ""Biscuit"" ; ""Barrel"" } "
gives
Row\Col
E​
F​
10​
Biscuit​
Biscuit​
11​
Barrel​
Barrel​
_..............................

Another way of thinking about this is that the Spreadsheet works like a continually updating .Index Function. Consider this “Pseudo” Code
First this
Spreadsheet = .Index ( Cells , { 1 , 2 , 3 ....}~~~~~~,~~~~~~~{ 1 , 2, 2 ....} )
Is “Condified” ( Modified by compiling ) to pseudo code
Spreadsheet = .Index ( Cells , { 1 , 2 , 3 ....} ~~~~~,~~~~~~~~{ 1 ; 2; 3 ....} )
This effectively makes the Third argument the Second brush. The two brushes are initially set so that the strands in such a case will give you from those particular values a “default un orientated so 1 D pseudo Array” of 3 intercepts and returns in a 1 D Array the values in
A1 __B2__ C3.
Excel is just a bit of Mathematics like what pgc did here:
http://www.mrexcel.com/forum/excel-...1-dimensional-single-column.html?#post4370502
To get the full screen.

Further if you pervert the second argument of .Index by transposing it then my “theory” goes some way to explain how this for example, ( Pseudo” code )
= .Index ( Cells, .Transpose( { 1 , 3 } ) ~~~~~,~~~~~~~1 , 2 , 3 )
Returns you an Array 2 “rows” by 3 “columns”, the values in it being

~~~~A1~~~B1~~~C1
~~~~A3~~~B3~~~C3

Effectively here brushes of fixed strand length are laying now on top of each other instead of intercepting. So now, at fixed offset digital points along a strand multiple intercepts are obtained where in the un perverted case only one was made
https://usefulgyaan.wordpress.com/2...y-without-loop-application-index/#comment-591
Application.Index with Look Up Rows and Columns Arguments as VBA Arrays
http://www.mrexcel.com/forum/excel-...1-dimensional-single-column.html?#post4372710

A Further intersecting perversion based on all the ideas so far can do something interesting with the
.Match
Eileen's Lounge • Information

_..........

Please do not ask me to explain or elaborate on what I just said in _2 ) . I get a bad headache every time I try to understand what on earth it is I think I might be talking about**.
And get a bit worried!!
http://www.mrexcel.com/forum/lounge-v-2-0/929097-artificial-general-intelligence.html#post4466440

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

_1 ) As for [G1:I1] = [{1,2,3}].....................

I think with
[G1:I1] = [{1,2,3}]
You are maybe doing something a bit different. ( Or maybe it is the same** )
The RHS is an interesting alternative to doing....... Dim vTemp() As Variant.......
vTemp() = Array(“Any” , “3” , “Finks” ).

And further
vTemp() = [ {"Any" , "3" , "Finks"} ]
Is a lot nicer ( or simpler when strings are involved ) than the full Evaluate line of
vTemp() = Evaluate("=" & "{" & """any""" & " , " & """3""" & " , " & """Finks""" & "}")
This last point is, i think, as the "Shorthand" [ ] has one fundamental difference in that it does not take a string as its argument , - It is directly related to the Spreadsheet . And the Evaluation of a single quote in [ ] is to say here comes a string. (_....I think however this can lead to some limitations...for example it does not allow combinations of Excel and VBA to build a string......
https://usefulgyaan.wordpress.com/2...y-without-loop-application-index/#comment-565
_.....)
_................

Anyway, All are giving the 1 D Array. (Interestingly the last two “Evaluate” ones are returning ( 1 to 3 ) and not ( 0 To 2 ). - Possibly as they are referring now to a spreadsheet but not to a specific Range so not yet a 2 Dimension 1 row Array )

In any case i think the RHS is retuning a Field of Variant Types ( In my case with Strings in them ).
This would normally be allowed by VBA “One Liner “ to assign the values therein to a Spreadsheet Range thus
Range("G1:I1").Value = vTemp()
Your
[G1:I1] = vTemp()
And
[G1:I1] = [{1,2,3}] or [G1:I1] = [ {"Any" , "3" , "Finks"} ]
Is just “cutting out the middle man”!

Finally i say “maybe it is the same**” as Excel holds all these arrays as complex intercepts with
Offsets.. so it is i guess indirectly coming back to the same thing, .. may be
http://www.mrexcel.com/forum/excel-...ubs-get-byref-argument-error.html#post4336751

_..................................
Finally..
_3) Evaluate ( Row Column Stuff ).
Thanks for bringing that up. The Evaluate ( Row Column Stuff ) is a very useful tool in all this work, especially for quickly getting at a continuous consecutive integers as You clearly demonstrated.

Adding a Function to get at the column Number from a Column Letter can make this even more useful
https://usefulgyaan.wordpress.com/2...y-without-loop-application-index/#comment-514

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

Thanks again, Rick, for adding to the Thread. It helped me to think a bit and pick up some more interesting points.


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

Code:
[color=blue]Sub[/color] PerverseIntasecsFinks() [color=darkgreen]'    http://www.mrexcel.com/forum/general-excel-discussion-other-questions/929381-visual-basic-applications-split-function-third-argument-refers-maximum-outputs-%93when-splitting-stops-%94.html    Rick : http://www.mrexcel.com/forum/general-excel-discussion-other-questions/929381-visual-basic-applications-split-function-third-argument-refers-maximum-outputs-%93when-splitting-stops-%94.html#post4467983[/color]
10  [color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = ThisWorkbook.Worksheets("pgcArrays")
20  [color=darkgreen]' Excel is "Shooting Out intercepts" Demo: Can be "seen part of by extending the "CSE Type 2 ( Controlling the Shift ( Orientation )  Formula In the spreadsheet Entries ) "      http://www.mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column.html?#post4372710[/color]
30  ws.Range("E8:G9").FormulaArray = "={ ""Any"" , ""3"" , ""Finks""} "
40  ws.Range("E10:F11").FormulaArray = "={ ""Biscuit"" ;  ""Barrel"" } "
50  [color=darkgreen]' Make an Array[/color]
60  [color=blue]Dim[/color] vTemp() [color=blue]As[/color] Variant: [color=blue]Let[/color] vTemp() = Array("Any", "3", "Finks") [color=darkgreen]'returning ( 0 To 2 ),[/color]
70
80  [color=blue]Let[/color] vTemp() = [ {"Any" , "3" , "Finks"} ] [color=darkgreen]'returning ( 1 to 3 )[/color]
90  [color=blue]Dim[/color] strEval [color=blue]As[/color] String: [color=blue]Let[/color] strEval = "=" & "{" & """any""" & " , " & """3""" & " , " & """Finks""" & "}": Debug.Print strEval [color=darkgreen]'    http://www.mrexcel.com/forum/excel-questions/696820-quotes-visual-basic-applications.html#post4283381      http://www.excelfox.com/forum/f2/special-concatenation-2042/index3.html#post9517[/color]
100 [color=blue]Let[/color] vTemp() = Evaluate(strEval)
101 [color=blue]Let[/color] vTemp() = Evaluate("=" & "{" & """any""" & " , " & """3""" & " , " & """Finks""" & "}") [color=darkgreen]'returning ( 1 to 3 )[/color]
110 [color=darkgreen]' Assign Values of an Array to a Spreadsheet range[/color]
120 [color=blue]Let[/color] ws.Range("G1:I1").Value = vTemp() [color=darkgreen]' allowed by VBA "One Liner " to assign the values in an Array to a Spreadsheet Range[/color]
130 [G1:I1] = vTemp() [color=darkgreen]' "Shorthand" of Line 120[/color]
[color=blue]End[/color] [color=blue]Sub[/color]
 
Last edited:

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Hi,
Just one very last follow up here
: Rick has shown me nice way to simplify ( at least going by what you see ) , a Code Line I often use to get an Array of specific rows and columns out of a spreadsheet

I often use something of this Form ( here for example to get rows 2 and 4 and columns 1 and 2 )

MyArray() = Application.Index(Cells, Application.Transpose(Array(2, 4)), Array(1, 2))
_........................................................

Now i see a more pleasant looking alternative:

MyArray() = Application.Index(Cells, [ { 2 ; 4 } ], [ { 1 , 2 } ])

:)

The key is the
[ { 2 ; 4 } ]
Representing a 2 Dimension “1 Column” Array.
This possibility is overlooked maybe, as often we see the “1 Dimension” or “row” form represented by things like
Array(1, 2)
And
{ 1 , 2 }

Alan
 

Watch MrExcel Video

Forum statistics

Threads
1,095,806
Messages
5,446,569
Members
405,408
Latest member
Bravomb96

This Week's Hot Topics

Top