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

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

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

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

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

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

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

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

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

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

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