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?