vba excel populate array with values of non active sheet

Question

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...

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 pay attention to the quote marks as well, yours seem to be of the wrong type. — Banana
– Banana, Commented Jan 7, 2018 at 11:41
It works. OmG. thnkas a lot. I made it even more concise: claimsArray = Worksheets("claims").Range(Worksheets("claims").Cells(1, 1), Worksheets("claims").Cells(a, b)).Value In this way avoiding to create more varaibles. So there is the flaw. Inside the Range() excel does not know what is cells() when not reffering to the same sheet. so cells has to be preceeded by the corresponing worksheet. and there is when the code ends by not being so concise and almost so long as using a double loop. — JFerro
– JFerro, Commented Jan 7, 2018 at 11:47
i would recommend keeping ST, not everything is best written in a one liner. if you need to refer to your sheet more times in the future, you will end up with lots of thisworkbook.sheets("claims") instead of simple ST and in case you decide later to rename your sheet, you will have to change it all throughout your code instead of simple altering the first line — Banana
– Banana, Commented Jan 7, 2018 at 11:52
Moreover, since what I am looking is conciceness in the code I also tryed it out and workd with sheets("claims")which makes the code more readable. claimsArray = Sheets("claims").Range(Sheets("claims").Cells(1, 1), Sheets("claims").Cells(a, b)).Value — JFerro
– JFerro, Commented Jan 7, 2018 at 11:54
Thats a very good point @Banana I might even try to use public ST as worksheetin the module with all the routines. So be it. — JFerro
– JFerro, Commented Jan 7, 2018 at 11:57

JFerro · Accepted Answer · 2018-01-07 12:08:16Z

3

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.

answered Jan 7, 2018 at 12:08

JFerro

3,55310 gold badges49 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sérgio Backes · Accepted Answer · 2022-12-14 13:59:24Z

0

In my case it worked when I added the Sheets reference also inside the Range inputs (in each cell reference):

claimsArray = Sheets(“claims”).Range(Sheets(“claims”).Cells(1, 1),Sheets(“claims”).Cells(a, b)).Value

answered Dec 14, 2022 at 13:59

Sérgio Backes

495 bronze badges

Collectives™ on Stack Overflow

vba excel populate array with values of non active sheet

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related