Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating an array on the fly
what is the syntax for creating an array while running a for next loop?
let's say the array is called arr, the string to add is called sStr. there could be up to 50 elements. thanks -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating an array on the fly
Gary,
Not too difficult.. you'll need to declare the array with no constraints in the parenthesis first Each time in your loop you need the statement "Redim Preserve" prior to making the variable assignment.. see example below. ------------- Option Base 1 Dim myArray() As String Dim y As Integer Dim msg As String Sub redim_myArray() msg = "myArray" For y = 1 To 20 ReDim Preserve myArray(1 To y) myArray(y) = Cells(y, 1).Value msg = msg & vbCrLf & y & " " & myArray(y) Next MsgBox (msg) End Sub ReDim Preserve myArray(1 To y) Gary Keramidas wrote: what is the syntax for creating an array while running a for next loop? let's say the array is called arr, the string to add is called sStr. there could be up to 50 elements. thanks -- Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating an array on the fly
having some trouble building the array. this will loop through about 50 items.
if the checkbox is true, it returns the value of the corresponding text box. it works the fist time it finds a checkbox, but not when it finds the next one. intellisense says subscript out of range. For i = 1 To lastEMP If Me.Controls("CheckBox" & i) = True Then n = n + 1 ReDim Preserve arr(1 To n) arr(n) = Me.Controls("textbox" & i).Value End If Next i -- Gary "Will" wrote in message oups.com... Gary, Not too difficult.. you'll need to declare the array with no constraints in the parenthesis first Each time in your loop you need the statement "Redim Preserve" prior to making the variable assignment.. see example below. ------------- Option Base 1 Dim myArray() As String Dim y As Integer Dim msg As String Sub redim_myArray() msg = "myArray" For y = 1 To 20 ReDim Preserve myArray(1 To y) myArray(y) = Cells(y, 1).Value msg = msg & vbCrLf & y & " " & myArray(y) Next MsgBox (msg) End Sub ReDim Preserve myArray(1 To y) Gary Keramidas wrote: what is the syntax for creating an array while running a for next loop? let's say the array is called arr, the string to add is called sStr. there could be up to 50 elements. thanks -- Gary |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating an array on the fly
looks like it started working ok for some reason
-- Gary "Will" wrote in message oups.com... Gary, Not too difficult.. you'll need to declare the array with no constraints in the parenthesis first Each time in your loop you need the statement "Redim Preserve" prior to making the variable assignment.. see example below. ------------- Option Base 1 Dim myArray() As String Dim y As Integer Dim msg As String Sub redim_myArray() msg = "myArray" For y = 1 To 20 ReDim Preserve myArray(1 To y) myArray(y) = Cells(y, 1).Value msg = msg & vbCrLf & y & " " & myArray(y) Next MsgBox (msg) End Sub ReDim Preserve myArray(1 To y) Gary Keramidas wrote: what is the syntax for creating an array while running a for next loop? let's say the array is called arr, the string to add is called sStr. there could be up to 50 elements. thanks -- Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating an array on the fly
Using ReDim Preserve within the loop is inefficient. Might better consider
ReDim myArray(1 To 50) For y = 1 To 20 myArray(y) = Cells(y, 1).Value msg = msg & vbCrLf & y & " " & myArray(y) Next ReDim Preserve myArray(1 To y - 1) Alan Beban Will wrote: Gary, Not too difficult.. you'll need to declare the array with no constraints in the parenthesis first Each time in your loop you need the statement "Redim Preserve" prior to making the variable assignment.. see example below. ------------- Option Base 1 Dim myArray() As String Dim y As Integer Dim msg As String Sub redim_myArray() msg = "myArray" For y = 1 To 20 ReDim Preserve myArray(1 To y) myArray(y) = Cells(y, 1).Value msg = msg & vbCrLf & y & " " & myArray(y) Next MsgBox (msg) End Sub ReDim Preserve myArray(1 To y) Gary Keramidas wrote: what is the syntax for creating an array while running a for next loop? let's say the array is called arr, the string to add is called sStr. there could be up to 50 elements. thanks -- Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating an array | New Users to Excel | |||
creating an array | Excel Worksheet Functions | |||
question about creating array | Excel Programming | |||
creating array using name references | Excel Programming | |||
Creating an array | Excel Programming |