Arrays in Excel VBA

  • Thread starter Thread starter Eliezer
  • Start date Start date
E

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
 
Eliezer said:
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
Click to expand...
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
 
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
 
Jim said:
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
Click to expand...

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
 
Back
Top