Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Array
How do I declare an array where I know the Number of Columns but the number
of rows is Dynamic The Columns should be type text -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Array
there is a Redim sttatement in VBA.
The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts). You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array. However, you can't declare an array of one data type and later use ReDim to change the array to another data type, unless the array is contained in a Variant. If the array is contained in a Variant, the type of the elements can be changed using an As type clause, unless youre using the Preserve keyword, in which case, no changes of data type are permitted. If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array. ReDim X(10, 10, 10) .. . . ReDim Preserve X(10, 10, 15) "Fredriksson via OfficeKB.com" wrote: How do I declare an array where I know the Number of Columns but the number of rows is Dynamic The Columns should be type text -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Array
Would I do something like this for a 2 dimensional array
Dim Array1() Dim NumOfRows as Integer NumOfRows = 200 ReDim Array1( 0 to NumOfRows, 1 To 2 ) Joel wrote: there is a Redim sttatement in VBA. The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts). You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array. However, you can't declare an array of one data type and later use ReDim to change the array to another data type, unless the array is contained in a Variant. If the array is contained in a Variant, the type of the elements can be changed using an As type clause, unless youre using the Preserve keyword, in which case, no changes of data type are permitted. If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array. ReDim X(10, 10, 10) . . . ReDim Preserve X(10, 10, 15) How do I declare an array where I know the Number of Columns but the number of rows is Dynamic The Columns should be type text -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Array
Yes, that will work.
-- Regards, Tom Ogilvy "Fredriksson via OfficeKB.com" wrote: Would I do something like this for a 2 dimensional array Dim Array1() Dim NumOfRows as Integer NumOfRows = 200 ReDim Array1( 0 to NumOfRows, 1 To 2 ) Joel wrote: there is a Redim sttatement in VBA. The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts). You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array. However, you can't declare an array of one data type and later use ReDim to change the array to another data type, unless the array is contained in a Variant. If the array is contained in a Variant, the type of the elements can be changed using an As type clause, unless youre using the Preserve keyword, in which case, no changes of data type are permitted. If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array. ReDim X(10, 10, 10) . . . ReDim Preserve X(10, 10, 15) How do I declare an array where I know the Number of Columns but the number of rows is Dynamic The Columns should be type text -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Array
will you know the number of rows before you declare the array?
Dim v() as Double i = Inputbox("Enter number of rows") if not isnumeric(i) then exit sub End if redim v(i,10) -- Regards, Tom Ogilvy "Fredriksson via OfficeKB.com" wrote: How do I declare an array where I know the Number of Columns but the number of rows is Dynamic The Columns should be type text -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Array
I will need to calc the number of rows from a worksheet that is being updated.
Once I get my Array loaded. Is there a quick way to display the contents in the array to verify that I loaded it correctly. Thanks for your help Tom Ogilvy wrote: will you know the number of rows before you declare the array? Dim v() as Double i = Inputbox("Enter number of rows") if not isnumeric(i) then exit sub End if redim v(i,10) -- Regards, Tom Ogilvy How do I declare an array where I know the Number of Columns but the number of rows is Dynamic The Columns should be type text -- Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Array
Dim Array1()
Dim NumOfRows as Integer Dim sh as Worksheet, sh1 as Worksheet NumOfRows = 200 ReDim Array1( 0 to NumOfRows, 1 To 2 ) ' code that loads the array set sh1 = Activesheet Worksheets.Add After:=Worksheets(worksheets.count) set sh = Activesheet sh.Range("A1").Resize(ubound(Array1,1) - _ lbound(array1,1) + 1, 2).Value = Array1 Msgbox "Look at the array" Application.DisplayAlerts = False sh.Delete Application.displayAlerts = True sh1.Activate -- Regards, Tom Ogilvy "Fredriksson via OfficeKB.com" wrote: I will need to calc the number of rows from a worksheet that is being updated. Once I get my Array loaded. Is there a quick way to display the contents in the array to verify that I loaded it correctly. Thanks for your help Tom Ogilvy wrote: will you know the number of rows before you declare the array? Dim v() as Double i = Inputbox("Enter number of rows") if not isnumeric(i) then exit sub End if redim v(i,10) -- Regards, Tom Ogilvy How do I declare an array where I know the Number of Columns but the number of rows is Dynamic The Columns should be type text -- Message posted via http://www.officekb.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Array
Thanks
Tom Ogilvy wrote: Dim Array1() Dim NumOfRows as Integer Dim sh as Worksheet, sh1 as Worksheet NumOfRows = 200 ReDim Array1( 0 to NumOfRows, 1 To 2 ) ' code that loads the array set sh1 = Activesheet Worksheets.Add After:=Worksheets(worksheets.count) set sh = Activesheet sh.Range("A1").Resize(ubound(Array1,1) - _ lbound(array1,1) + 1, 2).Value = Array1 Msgbox "Look at the array" Application.DisplayAlerts = False sh.Delete Application.displayAlerts = True sh1.Activate -- Regards, Tom Ogilvy I will need to calc the number of rows from a worksheet that is being updated. [quoted text clipped - 20 lines] How do I declare an array where I know the Number of Columns but the number of rows is Dynamic The Columns should be type text -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Array
I will need to calc the number of rows from a worksheet that is being updated.
Once I get my Array loaded. Is there a quick way to display the contents in the array to verify that I loaded it correctly. Thanks for your help Tom Ogilvy wrote: will you know the number of rows before you declare the array? Dim v() as Double i = Inputbox("Enter number of rows") if not isnumeric(i) then exit sub End if redim v(i,10) -- Regards, Tom Ogilvy How do I declare an array where I know the Number of Columns but the number of rows is Dynamic The Columns should be type text -- Message posted via http://www.officekb.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Array
Once I get my Array loaded. Is there a quick way to display the contents in
the array to verify that I loaded it correctly. If it's 1-D or 2-D and you know its size, you can easily write it to a worksheet, for example: Sheets("Sheet1").Range("A1:C3") = myArray If it's more than 2-D, it will take more work. Hth, Merjet |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Array
Thanks
merjet wrote: Once I get my Array loaded. Is there a quick way to display the contents in the array to verify that I loaded it correctly. If it's 1-D or 2-D and you know its size, you can easily write it to a worksheet, for example: Sheets("Sheet1").Range("A1:C3") = myArray If it's more than 2-D, it will take more work. Hth, Merjet -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variables in Declaring an Array | Excel Programming | |||
Declaring one bidimensinal Array | Excel Programming | |||
declaring a public array | Excel Programming | |||
Q: Declaring a dynamic array | Excel Programming | |||
declaring an array of CheckBox's | Excel Programming |