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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Flammy

Board Regular
Joined
Apr 19, 2011
Messages
51
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?
 

xladept

Board Regular
Joined
Apr 29, 2012
Messages
199
Hi Flammy,

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

Flammy

Board Regular
Joined
Apr 19, 2011
Messages
51
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.
 

xladept

Board Regular
Joined
Apr 29, 2012
Messages
199

ADVERTISEMENT

Glad its running - that was my only thought.
 

Flammy

Board Regular
Joined
Apr 19, 2011
Messages
51
Maybe 'Column' is reserved but not 'Columns'. Thanks for the advice anyway!
 

Flammy

Board Regular
Joined
Apr 19, 2011
Messages
51

ADVERTISEMENT

bump
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Flammy

Board Regular
Joined
Apr 19, 2011
Messages
51
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.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,526
Messages
5,602,181
Members
414,510
Latest member
mande358

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
Top