Declaring array variable

dugdugdug

Active Member
Joined
May 11, 2012
Messages
342
If I have a variable called myarray, I usually declare it as follows:

Code:
Dim myarray() As ' whatever you want but usually I choose Variant

Assuming I have some data starting in cell (A1), to assign data to the variable I write:

Code:
myarray=Cells(1,1).CurrentRegion.Value

However, I have noticed if there is only one value, ie only cell A1 is populated, all other cells around it are not, then declaring myarray() would fail.

Instead, simply declaring myarray (without the ()) works.

So my question is, is this

Code:
Dim myarray As

preferable to

Code:
Dim myarray() As

I thought adding the brackets would help identify that myarray is an array variable but it seems it has its limitations.

Thanks
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
When the range is just a single cell, the Value property returns a scalar value rather than an array hence the mismatch when you've explicitly declared myArray as an array.

I never bother using the () if it is going to contain variants. I'm sure there'll be some that say that's bad idea but to me it just seems like an unnecessary couple of characters.
 

dugdugdug

Active Member
Joined
May 11, 2012
Messages
342
When the range is just a single cell, the Value property returns a scalar value rather than an array hence the mismatch when you've explicitly declared myArray as an array.

I never bother using the () if it is going to contain variants. I'm sure there'll be some that say that's bad idea but to me it just seems like an unnecessary couple of characters.

Let's assume cells(1,1) and cells(2,1) contain the values 1 and 2 respectively.

Code:
Dim myarray() As Integer

myarray=Cells(1,1).CurrentRegion.Value

This does not work even though the values 1 and 2 are integers. So I always define myarray() as Variant.

So how can I ever define myarray() as anything other than a variant?
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
Let's assume cells(1,1) and cells(2,1) contain the values 1 and 2 respectively.

Code:
Dim myarray() As Integer

myarray=Cells(1,1).CurrentRegion.Value

This does not work even though the values 1 and 2 are integers. So I always define myarray() as Variant.

So how can I ever define myarray() as anything other than a variant?

This bit:

Cells(1,1).CurrentRegion.Value

returns a variant array hence the mismatch. Don't use the Value property of a multi-cell range object if you don't want to return a variant array. That's just the way VBA works.
 

dugdugdug

Active Member
Joined
May 11, 2012
Messages
342
This bit:

Cells(1,1).CurrentRegion.Value

returns a variant array hence the mismatch. Don't use the Value property of a multi-cell range object if you don't want to return a variant array. That's just the way VBA works.

Thanks, just trying to do everything "correctly" by fully qualifying things, hence .value!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

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