I want to populate an array with the values of a range in other sheet different than the active one. I do need to load into arrays data ranges values from many different sheets and then made thousands of operations.
I cannot be thinking about activating sheets, using loops, or even worse accessing the data in the sheets by cells(). And I want to write a neat and clean code avoiding loops for inteligibility.
I started by:
dim claimsarray as variant
claimsArray = Range(Cells(1, 1), Cells(a, b)).Value
a and b are integers It seems to work. No error. But the values of the array are empty because the array is populated with the values of the active sheet. I want to get the values froma sheet called "claims".
claimsArray = sheets(“claims”).Range(Cells(1, 1), Cells(a, b)).Value
that gives me an error 1004
looking for solutions in stackoverflow I tried the following modifications:
FIRST worksheets() instead of sheets():
claimsArray = worksheets(“claims”).Range(Cells(1, 1), Cells(a, b)).Value
gives me an error 1004
SECOND
Changing the dimensioning of the array
dim claimsarray as variant
vs
dim claimsarray() as variant
all combinations give me error 1004
It seems to me that you can populate an array in this way only in the active sheet. So I modify:
sheets(”claims”).activate
claimsArray = worksheets(“claims”).Range(Cells(1, 1), Cells(a, b)).Value
it works.
THE QUESTION: How to populate an array from a different sheet without using loop and without having to activate it. Why cant I not refer to other sheet to populate the array? Where is the flaw? is it just that VBA is weak code?
NOTE1:
I read many websites about populating arrays with ranges: http://www.cpearson.com/excel/ArraysAndRanges.aspx https://excelmacromastery.com/excel-vba-array/
at non avail. They don't really deal with this particular problematic.
NOTE2: I ended up using the typical loop_:
For i = 1 To a
For j = 1 To b
claimsArray(i, j) = Sheets("claims").Cells(i, j).Value
Next j
Next i
5 lines instead of one. Makes the code so much longer and conbersome...
thanks to @banana I understood where is "the flaw" of passing a range to an array.
claimsArray = sheets(“claims”).Range(Cells(1, 1), Cells(a, b)).Value
does not work properly when "claims" is not the active sheet because Excel gets confused as to which cell is actually cells(1,1)
therefore the elegant and efective solution is simply telling excel this way:
dim ST as worksheet
dim claimsArray as variant
set ST = thisworkbook.sheets("claims")
claimsArray = ST.Range(ST.Cells(1, 1), ST.Cells(a, b)).Value
It is also very important as @banana pointed out in the coments that ST is initialize as to refering to "thisworkbook" to avoid that several workbooks are open having a sheet called "claims". This whole problematic might be the reason why then populating arrays with loops is at the very end the less problematic way.