Using Rows.Count to assign dimensions to a multi-dimensional array in VBA

Question

I'm struggling with this program I'm trying to write. In it, I need to use a two dimensional array that looks like array(# rows in table, 2). For some reason, even though I can get the number of rows correctly, I keep getting the Compile error: Constant expression required.

If anybody could give me any help it'd be much appreciated. I can't just assign a static number to it because the table is always going to be increasing as time goes on, and I know you cannot resize an array once it's built.

This is my code if it'll provide any help.

Sub arrayToTest()
Dim rng As Range, row As Range, cell As Range, RowCount As Long, rowsinTable As Integer
Set rng = Sheets("Sheet1").Range("TestTable")
rowsinTable = rng.Rows.Count
Dim arrayTest(rowsinTable, 2) As String
RowCount = 1
For Each row In rng.Rows
   arrayTest(RowCount - 1, 0) = rng.Cells(RowCount, 2)
   RowCount = RowCount + 1
Next row
End Sub

The test table I'm using has 3 rows.

FWIW - Dim arrayTest(rowsinTable, 2) As String (or the correct version of ReDim arrayTest(rowsinTable, 2) As String) is dimensioning your array to be a rowsinTable+1 x 3 array, because you appear to be using base 0 - so the array is sized 0 To rowsInTable, 0 To 2. — YowE3K
– YowE3K, Commented Oct 12, 2017 at 18:46
"you cannot resize an array once it's built" is not necessarily correct - the ReDim Preserve statement will allow modifications to the last dimension of an array without affecting the existing contents of the array. So you could, after building the array, decide to increase your 2 to 3, or reduce it to 1. And, if you used Transpose on the array, you would then be able to change what was the first dimension (but following the transpose is now the second dimension), resize, and then transpose it back again. (But definitely better to do it as you are doing if you know the number of rows!) — YowE3K
– YowE3K, Commented Oct 12, 2017 at 18:53

Scott Craner · Accepted Answer · 2017-10-12 18:38:24Z

2

Sub arrayToTest()
Dim rng As Range, row As Range, cell As Range, RowCount As Long, rowsinTable As Integer
Dim arrayTest() As String 'First Declare the array
Set rng = Sheets("Sheet1").Range("TestTable")
rowsinTable = rng.Rows.Count
ReDim arrayTest(rowsinTable, 2) As String 'Then Re-Declare it to size.
RowCount = 1
For Each row In rng.Rows
   arrayTest(RowCount - 1, 0) = rng.Cells(RowCount, 2)
   RowCount = RowCount + 1
Next row
End Sub

answered Oct 12, 2017 at 18:38

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Rows.Count to assign dimensions to a multi-dimensional array in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related