Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Elements and Arrays in Excel | Excel Worksheet Functions | |||
how do i get the intersection of two arrays in excel? | New Users to Excel | |||
arrays in excel | Excel Worksheet Functions | |||
arrays on forms in Excel | Excel Programming | |||
dynamic arrays in excel | Excel Programming |