Workbook Activate vs Select Revisited

[Cyberdude]

I'm still struggling with workbook activation vs selection.
Question 1: When my macro opens another workbook, is the newly opened workbook
a) Activated
b) Selected
c) Both
Do I also need an Activate/Select statement after opening?
Question 2: Workbook A and workbook B are both open.
I want to toggle between the two, so should I use Select or Activate to do the switching? Is there some subtle difference in the results?
Question 3: Now that the other workbook is open, my macro will continue executing statements. Will all statements automatically refer to the newly opened workbook? Examples"
a) If both workbooks have a defined name called "MsgFlag", if I write a statement like
FlagVal = Range("MsgFlag")
which of the two locations will be read?
b) If both workbooks have a macro named "Confusion",
if I write the statement
Call Confusion
which of the two will execute?

[Killian]

Does the Workbook object have a Select method? Not AFAIK... so Activate it is.
You should be using object variables to avoid even the possibility of confusion

Dim wbA As Workbook
Dim wbB As Workbook
Set wbA = Workbooks.Open("WorkBookA.xls")
    Set wbB = Workbooks.Open("WorkBookB.xls")
'now just refer to each object variable
    wbA.Activate
    wbB.PrintOut

The Call statement will only run a routine from the project it's used in. To run a routine from another workbook, you need to specify it as an argument of the Run method

Run ("Book1!Confusion")

[TonyJollans]

Hi Sid,

Activation and Selection are part of the User Interface. I won't say that they are never needed in code, but it is very rarely that they are. Just so you know, each window has an active sheet, and each worksheet has an active cell; unqualified references are to objects in the active window (strictly active pane, but let's not complicate things). The active cell in a window is always part of the selection on the sheet in that window.

When you open a workbook, a new window is created and it becomes the active window. The newly opened workbook is presented in the new window and becomes the active workbook. A sheet in that workbook becomes the active sheet in that window (although more than one may, possibly, be selected) and, assuming it's a worksheet, a cell in that sheet becomes the active cell. One or more cells (always including the active cell) on the active sheet (and, actually, on every other sheet as well) are selected. References to ActiveCell, ActiveSheet, ActiveWorkbook, ActiveWindow, and Selection will all refer to the active sheet in the newly opened workbook. Finally, AFAIK, a workbook cannot be selected.

You do not need or want to be referring to active, or selected, elements however - and especially so if you're working with multiple workbooks. Every time you activate or select something you are making a change to the user interface, which does not benefit the user and which you will then probably want to suppress (with ScreenUpdating) - far better not to make the change in the first place. In code you can, and usually should, refer directly to the workbooks in memory instead of via the user interface. Set object variables to the workbooks/worksheets/cells you want to use, for example

Dim mySheet1 As WorkSheet
Dim mySheet2 as WorkSheet
Set mySheet1 = ActiveSheet ' Grab a reference to your starting point
Workbooks.Open ' provide your own details here
Set mySheet2 = ActiveWorkbook.Sheets("Sheet2") ' Reference to the sheet you want in the new workbook
' Now you can use mySheet1 and mySheet2 to refer to the two sheets without any selecting or activating, for example
Msgbox mySheet1.Range("A1").Text
Msgbox mySheet2.Range("B1").Text

Hope I haven't confused you too much with that. It's my bedtime now and I've written enough for the moment, so I'll leave the rest of your questions for later, or someone else.

[Cyberdude]

Thanx Killian and Tony for the responses. I should never have posted that note. At the time my mind had entered the realm of mush, and I wasn't thinking it out. I subsequently ran some tests that confirmed what you posted.
As for using object variables, I've never really developed a feel for them, and I know you're right. I think if I'd ever taken a course in VBA, it would have been a big help, but I've been just muddling through as best I can.
Thanks again.
Sid