VBA Array Problem

[lochnagar2020]

Hi,

I'm new to VBA and hoping someone can answer a query I have regarding populating an array, with cells from a worksheet - I have had a look through the various posts on this forum, but to no avail.

Set-up:
I have numbers 1 to 5 in cells A1 to E1 on worksheet "Sheet1", and numbers 6 to 10 in cells A1 to E1 on worksheet "Sheet2".
I have the following code in a module (within the same workbook);

Sub populate_array
Dim ary_() as Variant
ary_ = Worksheets("Sheet1").Range(Cells(1,1), Cells(1,5)).Value
Debug.Print ary(1, 1)
End Sub

Query:
I use Debug.Print to interrogate values within ary_ to check the code has worked (thou I believe there is a better to check / view an array's contents than printing to the Immediate Window?). What I have noticed (and can't seem to figure out) however, is that if I change the worksheet I have visible to any sheet other than "Sheet1" (as specified in the macro), I get a 'Run-time Error '1004':' Application-defined or Object-defined error message pop-up when running the macro.

I had just assumed that by specifying (in VBA) which worksheet the range of cells resided in, I wouldn't have to worry about what worksheet of the workbook was visible. Is it possible to populate an array using the above approach, without having to worry about what worksheet is visible, as long as the workbook itself is open / minimised?

Any help will be much appreciated.

Kind regards,

Lochnagar2020

 

[mikerickson]

In

ary_ = Worksheets("Sheet1").Range(Cells(1,1), Cells(1,5)).Value

The Cells are un qualified so their values are cells on the ActiveSheet.
And the two argument form of Range would return a range on that same ActiveSheet.
But the Worksheets("Sheet1") is the wrong sheet for that range to be on, hense the error.

The solution is to fully qualify the .Cells to sheet 1.

With Worksheets("Sheet1")
    ary_ = Range(.Cells(1, 1), .Cells(1, 5)).Value
End With

 

[lochnagar2020]

Thanks for the prompt reply mikerickson. I hadn't realised that the cells were unqualified, and seen as values instead of references. Out of interest, I tried adding .Address, to see if this would work (i.e. ary_ = Worksheets("Sheet1").Range(Cells(1,1).Address, Cells(1,5).Address).Value) and it seemed to.

Thanks mikerickson!

lochnagar2020.

 

[mikerickson]

That is another way.