Populate a list box with array produces error 1

[traveller4]

I have a user form in excel that has a list box.

I am populating it with an array that has two columns.

There are currently 12 records but this will increase over time.


If I use the following code there is no error

Dim intrCount As Long
Dim i As Long
Dim arFill(1 To 1000, 1 To 2)


Sheets("CaseSetup").Select
Range("A2").Activate

Set dbRange = ActiveCell.CurrentRegion

intrCount = dbRange.Rows.Count


For i = 1 To intrCount
arFill(i, 1) = Sheets("CaseSetup").Cells(i, 2)
Next i
For i = 1 To intrCount
arFill(i, 2) = Sheets("CaseSetup").Cells(i, 1)
Next i
ListBox1.ColumnCount = 2
ListBox1.List = arFill


An error occurs when I change Dim arFill(1 To 1000, 1 To 2)
to Dim arFill(1 To intrCount , 1 To 2)

The error is

Compile error: Constant expression required

I am trying to fill this array with just the records by doing a record count, hence the variable intrCount.

My question is can I declare this two column array dynamically without having to hardcode in a maximum value


--Thanks in advance

 

[PHV]

Take a look at the ReDim instruction in the VBA help.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Just a though: why not simply playing with the RowSource property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traveller4]

Thanks PH

The ReDim worked perfectly for what I need

Have a star for that one.

--Thanks again