Worksheet References using Object Variable

[gototcm]

I have a workbook with 2 worksheets: sheet1 and sheet2. I am attempting to populate an array (mntharray) from the Range "A6:D10." This range is not fixed and can have more or fewer rows so a formula is used to compute it. Here is the simplified code.

Please Login or Register  to view this content.

Here is the issue. If I have the focus on sheet 1 (sheet1 is selected in the Excel worksheet) and I run the code, it runs fine. If the focus is on sheet2, I get the "Run-time" error, Range of object failed.

My understanding is that by defining the object variable sheet1WS, this is sufficient for VBA to determine the reference for mntharray. An important observation is that if the line
mntharray =sheet1WS.Range("A6:D10") the code works as I would expect, regardless of the focus. Obviously, my understanding is wrong and there is a small complexity added with the more complicated formula in mntharray. Can you explain this to me?

[shg]

Please edit your post to add CODE tags.

Thanks.

EDIT: Thank you.

[MollyBrennholz]

We probably did not make it clear enough when we tried to explain this in one of your last posts.

When the “focus” is on the first worksheet, then the Active worksheet is the first worksheet. So for coding in a “normal” code module, this code line,
sheet1WS.Range(Range("A6"), Range("D" & numrows + 5)
is something like
sheet1WS.Range(Activesheet.Range("A6"), Activesheet.Range("D" & numrows + 5)
so something like
sheet1WS.Range(sheet1WS.Range("A6"), sheet1WS.Range("D" & numrows + 5)

When the “focus” is on the second worksheet, then the Active worksheet is the second worksheet. So for coding in a “normal” code module, this code line,
sheet1WS.Range(Range("A6"), Range("D" & numrows + 5)
is something like
sheet1WS.Range(Activesheet.Range("A6"), Activesheet.Range("D" & numrows + 5)
so something like
sheet1WS.Range(Sheet2WS.Range("A6"), Sheet2WS.Range("D" & numrows + 5)
or
sheet1WS.Range(Worksheets(“Sheet2”).Range("A6"), Worksheets(“Sheet2”).Range("D" & numrows + 5)
etc…..



So you are mixing up references in different worksheets , so VBA gets its knickers in a twist, and gets mixed up and upset, poor thing


Molly

Edit:
I think this version would work with the focus on any worksheet:
sheet1WS.Range("A6", "D" & numrows + 5)
I am not quite sure what that is doing there. I expect it is defaulting in that situation somehow to object variable sheet1WS
Or maybe it is in general looking at such a code line as using the Range Property of the object sheet1WS , so only gets upset when you give it specifically something else, as you were doing…

I would personally recommend avoiding using the Range(__ , __) way of doing things. It seems a bit strange sometimes on how it does things. I don’t know why that Range(__ , __) way ever came about. It often confuses the socks off people , as it has done you. Instead I would stay with the Range( __:__) way.
In your example that would be like sheet1WS.Range("A6:D" & numrows + 5)
That way seems less strange and has the extra advantage of allowing you to reference non contiguous ranges as well, like Range("A6:D" & numrows + 5 & ",Z23")

[bakerman2]

Please Login or Register  to view this content.

[gototcm]

Thanks for the clarification and this was extremely helpful. I don't know how you get all your expertise in this sometimes very confusing VBA OOP language but it is something I aspire to. For those of us still struggling with understanding some of the nuances of VBA, it oftentimes seems like unless you developed VBA at Microsoft you have no chance of really understanding it.

[gototcm]

Thanks, but it doesn't work. Furthermore, where did you find this syntax?

[MollyBrennholz]

What doesn’t work. Which syntax

[bakerman2]

Array is in Sheet1. Regardless number of rows array expands.

Please Login or Register  to view this content.

[gototcm]

Molly,
In using your latter two methods, neither worked. The two code snippets are shown below. It is interesting that even using WS.Range("A6:D10") doesn't work. There is something fundamentally flawed in using the object variable WS to explicitly activate a worksheet.

/
Sub test()

Dim WS As Worksheet
Dim numrows As Integer
Dim mntharray() As Variant

numrows = 10
ReDim mntharray(5, 4)

Set WS = Sheets("sheet1")
WS.Range("A6", "D" & numrows).Select

End Sub
/

/
Sub test()

Dim WS As Worksheet
Dim numrows As Integer
Dim mntharray() As Variant

numrows = 10
ReDim mntharray(5, 4)

Set WS = Sheets("sheet1")
WS.Range("A6:D" & numrows).Select

End Sub
/

Thanks again. With all these complications, it's no wonder so many newbies continue to use Select and Activate. While inefficient, they are bullet-proof.

[MollyBrennholz]

They seem to be the same two snippets. Edit: NO! - my mistake - see post #13
When I try them, they select the range A6 to D10

I feel your pain
I sympaphise.
I know how madly frustrating it can be learning VBA
But the things we have been suggesting definitely do work usually. There must be some other problem that you are experiencing

---

(P.S. if you hit the # in the reply window you will get these things, (called CODE tags ):
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]
If you put your coding inside that it will look a bit prettier in the posted response in a nice pretty code window)
Attachment 659553 : https://app.box.com/s/ibd4uz3bf2c5osnw0roww7ye88zb2k6t


So you would post like
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]
then after you post it looks like

Please Login or Register  to view this content.

or like , you post

[CODE[COLOR="#000000"]Please Login or Register to view this content.[/CODE]

[shg]

Again: Please edit your post to add CODE tags.

Thanks.

[gototcm]

Thanks Molly. This is maddening. I don't know if my problem is related to Excel 2007 or my Dell laptop (Win 7) but the two code snippets (which evidently I sent earlier as redundant replies) don't work. I will try on a different computer and do some further experimenting and get back to you. I appreciate all your efforts. And thanks for the tip on using the Oglethorpe (#).

[MollyBrennholz]

BTW, I just noticed on second glance that I had overlooked that your two code snippets in post #9 were indeed different.
I thought I had better say that to avoid me confusing you more by my error in reading.
I was wrong to say they are the same

As you quite rightly said you are using the two different ways.

WS.Range("A6:D" & numrows)

WS.Range("A6", "D" & numrows)

I missed seeing that the first time around.

But they do the same thing, or should.
In both cases they should reference a single rectangular area of contiguous cells, starting top left at A6 to bottom right of D10

[gototcm]

Don't worry. Been there done that.

I thought I sent a more recent update (around 2:00 p.m. EST) but I don't see it now. So to summarize that reply, I ran both snippets on an HP laptop using Excel 2013. Same bad result. Did you actually try running this on your computer? Anyway, more experiments on my side to see if I can figure this out.

I got so desperate that I simply had a one line code segment just to see if I could even select a single cell on sheet1 when the focus was on sheet 2. Didn't work. But if I replaced Select with Value (by including a "variable="), Clear, copy, all is well. Of course, the original code tried with your suggestions, even after adding any of these suffixes still doesn't function. I hope I'm not confusing you.

Please Login or Register  to view this content.

[shg]

You can't select a cell like that on anything other than the active worksheet.

You can do

Please Login or Register  to view this content.

... unless it's hidden (you would have to unhide it first).

Third request: Would you both please edit all posts necessary to add CODE tags. Otherwise a moderator is going to suspend the thread.

[MollyBrennholz]

.... Did you actually try running this on your computer? .....

Yes I did run the macros, and a few variations of them.
They did do what I expected.
I would be very surprised if they did not.
But I had Sheet1 active…..as shg said, you would need to have the focus on the sheet, when selecting it. I may have overlooked to say that. Generally I do not use select much in coding , since it is mostly not needed in coding.
Sorry I may have overlooked that along the way.
Possibly that was what you were saying, and I may have missed that. Re reading what you have written , and noting what shg said, that could be where the problem lies. I may have mislead a bit there. I think I lost track a bit as we moved away from the original code snippet in post #1 *** ( It was not clear to me in post #9 that you were trying those snippets with a different worksheet active …. We had moved “our focus” from referencing cells to selecting them , and I had not noticed, ( or at least I had not noticed that you were doing the Selecting when the worksheet to be selected was not active )


If you performed any other action, then the basic coding ideas would work.
For example,
sheets("Sheet1").Range("A1").value = “hello”
would work without you having to have the worksheet , Sheet1, in focus ( active).
( I think you may have said words to the effect that you had tried something like that in your last post )
An important basic point is that VBA does not have to “see” the worksheet that it is doing things to , or select a cell or cells , in order to do anything to the cell or cells .

An exception I suppose is Selecting, since you are not really doing anything to the cell, if that makes any sense.

In VBA when we are talking about doing things to cells , we usually mean making some change to the Range object. . Selecting a cell does not change anything in the range object. I know this will sound very confusing to a newbie. Sorry

Before selecting with code , you would need to activate the worksheet with like
Sheets("Sheet1").Activate.

I think that might have cleared it up.
shg’s comment has probably tweaked me to what was the issue.-


*** ( Important: - Your original issue in Post #1 was not a focus issue. There it was the issue with missing putting the worksheet qualifier before those Range( ) bits in the wide bracket
In your first post macro, you are filling an array with things from a worksheet. You do not have to have that worksheet in focus ( active) when you do that. It errored because you missed out the two required references to the worksheet, sheet1WS.

---

.. There is something fundamentally flawed in using the object variable WS to explicitly activate

Not quite. You can use it to Activate the worksheet, like
WS.Activate
But selecting a cell in the worksheet will not activate the worksheet , and I really should have said that at some point, sorry.
As you learn VBA you tend to get in the habit of not selecting or activating things very much.

(BTW. , The macro recorder produces coding that does select a lot, as you may find when you are learning, since it tries to reproduce what you have to do when doing things to cells. But VBA does not need to do that selecting usually. A typical learning exercise it to take the code returned from a macro recording, and remove all the unnecessary Activating and Selecting).
In real life coding you avoid selecting and activating as much as possible. Because every interaction with a worksheet slows things down a lot.

---

This would make your code snippets work regardless of what worksheet was active at the start

Please Login or Register  to view this content.

Or ( using a worksheets object to activate the worksheet )

Please Login or Register  to view this content.

But in most real life coding, it would be rare that you would ever want to do something like that. Because you do not need to select a cell or cells with coding , before you do something to a cell or cells with coding.
You and me manually have to do that selecting and activating so that we can see what we are doing.
VBA does not have eyes, but it knows where things are, if you refer to them correctly . … the original post #1 problem was that you were giving VBA conflicting information.

---

.... just to see if I could even select a single cell on sheet1 when the focus was on sheet 2. Didn't work.

correct. You can’t, as shg said. I had not twigged at that time, that you were wanting to do that.
I was thinking that you were trying to reference the cell in order to do something to it ( apart from selecting it ) , or get something from it.
In general you can do things to the cell or cells, ( changing things in them ) or get things from the cell or cells , using coding, without selecting them or activating the worksheet in which the cell or cells are.

[gototcm]

thanks. I understand now.

[MollyBrennholz]

Us welcome, Thx for feedback ( Edit: and Rep thing )
Molly

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.