Declaring array variable

[dugdugdug]

If I have a variable called myarray, I usually declare it as follows:

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:

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

Dim myarray As

preferable to

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

 

[Firefly2012]

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]

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.

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]

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

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]

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!