Arrays in Excel VBA
 

I have a loop that has a number of strings (unknown until
runtime) that I need to capture in an array. I know it
won't be more than 44 strings, but if I declare the array
for size 44 - I end up with a bunch of empty strings in my
array.

Do Until...
repName 'that's my string value I need to insert into
the array
Loop

How do I declare / populate the array with only
actual "repName"s (i.e. not when "repName" = "")?

Thanks!
Eliezer

Arrays in Excel VBA
 

Eliezer wrote:

I have a loop that has a number of strings (unknown until
runtime) that I need to capture in an array. I know it
won't be more than 44 strings, but if I declare the array
for size 44 - I end up with a bunch of empty strings in my
array.

Do Until...
repName 'that's my string value I need to insert into
the array
Loop

How do I declare / populate the array with only
actual "repName"s (i.e. not when "repName" = "")?

Thanks!
Eliezer

Assuming a 1-based 1-D array named "myArray", simply keep track of the
number of repName's that have gone into it (say, k) and at the end use

ReDim Preserve myArray(1 to k)

Alan Beban

Arrays in Excel VBA
 

Just to add some sample code to point out you need to have a dynamic array

Sub BBB()
Dim MyArray() As String
Dim k As Long
ReDim MyArray(1 To 44)

k = 0
Do While Cells(k + 1, 3) < ""
k = k + 1
MyArray(k) = Cells(k, 3)
Loop

ReDim Preserve MyArray(1 To k)
Debug.Print LBound(MyArray), UBound(MyArray)
End Sub


--
Regards,
Tom Ogilvy


"Alan Beban" wrote in message
...
Eliezer wrote:

I have a loop that has a number of strings (unknown until
runtime) that I need to capture in an array. I know it
won't be more than 44 strings, but if I declare the array
for size 44 - I end up with a bunch of empty strings in my
array.

Do Until...
repName 'that's my string value I need to insert into
the array
Loop

How do I declare / populate the array with only
actual "repName"s (i.e. not when "repName" = "")?

Thanks!
Eliezer

Assuming a 1-based 1-D array named "myArray", simply keep track of the
number of repName's that have gone into it (say, k) and at the end use

ReDim Preserve myArray(1 to k)

Alan Beban

Arrays in Excel VBA
 

Thank you Alan, Tom, and Jim! All better now! :-)
Eliezer


-----Original Message-----
Eliezer wrote:

I have a loop that has a number of strings (unknown
until
runtime) that I need to capture in an array. I know it
won't be more than 44 strings, but if I declare the
array
for size 44 - I end up with a bunch of empty strings in
my
array.

Do Until...
repName 'that's my string value I need to insert
into
the array
Loop

How do I declare / populate the array with only
actual "repName"s (i.e. not when "repName" = "")?

Thanks!
Eliezer

Assuming a 1-based 1-D array named "myArray", simply keep
track of the
number of repName's that have gone into it (say, k) and
at the end use

ReDim Preserve myArray(1 to k)

Alan Beban
.

Arrays in Excel VBA
 

Jim Thomlinson wrote:

In the help menu look up the Redim statement Here is one that I was just
working on:

Public Sub LoadRepName()
Dim intCounter As Integer
Dim aryRepName() as String

intCounter = 0

Do While RepName < Empty
ReDim Preserve aryRepName(intCounter)
aryRepName(intCounter) = RepName

'go to next rep anem
intCounter = intCounter + 1
Loop

End Sub

It is generally much better to loop first, then ReDim Preserve once at
the end rather than have a ReDim Preserve call with every iteration of
the loop.

Alan Beban