Dim Array(10,x) as variant - Is this possible?

Flammy

Board Regular
Joined
Apr 19, 2011
Messages
51
Error message is says 'Private Const' is not allowed in Subs or Functions, so I assume only modules. This is in userform. It wouldn't let me dimension the array using just the variable so I tried to set it as a constant...


Code:
Private Const Columns = NumberOfCollumns()

Dim ColumnTitles(Columns) As Variant
Dim HUGEarray(Columns + 1, 100000) As Variant
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Also, will the 100,000 rows in an array cause issues? I am fairly sure I will only have ~10k rows of data to start with, but the sheet I'm getting it from has 113k (will vary from day to day)... Will 10,000 rows (10 columns) filled with strings (10-40 characters) use waaay too much RAM? Is there any sort of VBA capability that detects it is running out of RAM, goes and writes the data to another sheet, empties the array then goes back for more?
 
Upvote 0
Hi Flammy,

It seems that you're using reserved words for your declaration - try changing Columns to Cols or whatever else.
 
Upvote 0
Hi Flammy,

It seems that you're using reserved words for your declaration - try changing Columns to Cols or whatever else.

Really? It is running just fine right now. I've seen the error message that pops up for them (like if I actually called my array 'array', I know that is one)

It runs when I dimension the arrays with numbers rather than (constant) variables.
 
Upvote 0
A Const statement assigns a constant expression:

Code:
Const sMyName As String = "Bob"
Const numColumns As Long = 42

You cannot assign a dynamic array to a constant as your statement attempts to do.
 
Upvote 0
A Const statement assigns a constant expression:

Code:
Const sMyName As String = "Bob"
Const numColumns As Long = 42

You cannot assign a dynamic array to a constant as your statement attempts to do.

OK thanks. I understand that you can't have the array changing randomly because the variant used in it's demensioning keeps changing... but I hoped there was a way around this as I'm not planning on changing the variable after the array is set, I just want the array to be different sizes on different runs, not changing during one run.

If anyone else has a work around, please let me know. Thanks.
 
Upvote 0
A constant in VBA is a simple scalar variable -- a number, a string, a Boolean -- not an array of any kind.

You can declare an array of fixed size, and the dimensions can use previously defined constants:

Code:
Const m As Long = 3
Const n As Long = 5
Dim myArray(1 To n, 1 To m, m To n)
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top