Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking up a Text String
Jim's reply is exactly on the mark. I would add one code-style comment. I
realize Jim's reply was a quick and dirty response to illustrate the Split function, and was not intended to be an example of commercial-quality code. As a general good-coding practice, you should never refer to array element by number explicitly. Instead, you should get in the habit of using LBound and UBound to determine the lower and upper limits of the array. While some arrays, such as the array returned by Split, are always 0-based, other arrays take their lower bound from the Option Base module directive, if present. For example, the LBound of Arr below depends on the value of Option Base: Dim Arr As Variant Arr = Array(1, 2, 3) Debug.Print LBound(Arr) The proper way to avoid confusion and possible errors is to use LBound as the base element and work upwards: Debug.Print Arr(LBound(Arr)) Debug.Print Arr(LBound(Arr) + 1) Debug.Print Arr(LBound(Arr) + 2) and so on. If you're going to loop, do something like the following: For N = LBound(Arr) To UBound(Arr) Debug.Print Arr(N) Next N Extra care must be exercised when importing code that sizes an array with only an upper bound. For example, the number of elements in Arr below depends on the Option Base statement: Dim Arr(1) This is poor coding practice, and should never be used in commercial-quality code. The reason is that the number of element in Arr is either 1 or 2, depending on the Option Base statement. If the Option Base statement is added, removed, or changed, or you copy the code to a module with a different Option Base statement, the number of elements in the array is changed, and this will likely lead to errors. Instead, you should declare both the lower and upper bounds of the array in Dim or ReDim statement. E.g., Dim Arr(1 to 3) As Long In this case, the Option Base setting is ignored. This is all only tangentially related to the original question, but I've seen enough bugs with improperly declared and referenced arrays that I figured it was worth mentioning. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Jim Thomlinson" wrote in message ... Take a look at the split function... something like this... Sub test() Dim str As String Dim arr() As String str = "A;B;C;D" arr = Split(str, ";") MsgBox arr(0) & vbCrLf & _ arr(1) & vbCrLf & _ arr(2) & vbCrLf & _ arr(3) End Sub -- HTH... Jim Thomlinson "jayklmno" wrote: I have a string of data that I want to break up. It's a list of semi-colon seperated names. Before I spend hours trying to figure this out, what is the easiest way to read that into a variable, break it up and feed it into an array. Lets say it's... "A; B; C; D" And I want it to become an array... Array(1) = A Array(2)= B Anyone? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Breaking a string of letters | Excel Discussion (Misc queries) | |||
Breaking a string of text | Excel Discussion (Misc queries) | |||
Breaking a string of letters | Excel Discussion (Misc queries) | |||
Breaking up a string into separate columns | Excel Worksheet Functions | |||
Breaking up a string | Excel Programming |