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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,785
Messages
5,833,690
Members
430,222
Latest member
Nickkarl

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