Arrays in Excel VBA

[Eliezer]

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

 

[Alan Beban]

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

 

[Tom Ogilvy]

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

 

[Eliezer]

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

 

[Alan Beban]

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