Declaring Array

  • Thread starter Thread starter Fredriksson via OfficeKB.com
  • Start date Start date
F

Fredriksson via OfficeKB.com

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
 
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 you’re
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)
 
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 )
 
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)
 
Yes, that will work.

--
Regards,
Tom Ogilvy


Fredriksson via OfficeKB.com said:
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 )
Click to expand...
 
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
 
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
 
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.
Click to expand...

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
 
Thanks
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
Click to expand...
 
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
 
Thanks
Tom said:
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.
Click to expand...
[quoted text clipped - 20 lines]
Click to expand...
 
Back
Top