lochnagar2020
New Member
- Joined
- Jun 19, 2020
- Messages
- 5
- Office Version
- 2010
- Platform
- Windows
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
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