I have two Excel macros workbooks open.  While a macro is running in one workbook, and with no action on my part, the other workbook suddenly becomes the ActiveWorkbook.  When that happens, VBA references within the running macro of the form:

    if Range("RangeName") = True then ....

are resolved by looking for the specified Range Name in the Activeworkbook rather than in the workbook within which to VBA macro is running.  If I enter debug mode, manually select the appropriate workbook, and continue macro execution, then all proceeds properly until the other workbook suddenly becomes the Activeworkbook again.

How do I write Excel VBA macro code to explicitly specify the workbook name when referencing the desired Range names?

    If ThisWorkbook.Range("RangeName") = True then ...

doesn't work.  And I can't figure out how to code

    If ThisWorkbook.Sheets("SheetName").Range("RangeName") = True Then ...

without knowing the Sheet name at compile time.  And I don't want to hard code both the Sheet Name AND the Range Name.  If I close the second workbook, the macro code runs just fine.  The running macro requires many minutes to complete.  So I'd like to do work in the other workbook while it runs.  But just having the second workbook open - even if I don't click to make it active - causes the above behavior.

Any suggestions?

Re:  reference a range

Maybe some of the following will help...
"---
Sub DoLotsOfStuff()
  Dim WB As Excel.Workbook
  Dim WS As Excel.Worksheet
 
  Set WB = ActiveWorkbook
  Set WS = ActiveSheet
 
  WS.Range("Sludge").Value = "more"
  '  OR
  WB.Worksheets(3).Range(WS.Range("Sludge").Address) = "Not Enough"
End Sub
'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

Re:  reference a range

Maybe some of the following will help...
"---
Sub DoLotsOfStuff()
  Dim WB As Excel.Workbook
  Dim WS As Excel.Worksheet
 
  Set WB = ActiveWorkbook
  Set WS = ActiveSheet
 
  WS.Range("Sludge").Value = "more"
  '  OR
  WB.Worksheets(3).Range(WS.Range("Sludge").Address) = "Not Enough"
End Sub
'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2
-or-
http://jmp.sh/K95N3ee

• Edited by James Cone Thursday, May 14, 2015 1:42 PM

Thanks for your suggestion Jim.  It might work provided that I can guarantee that I know which workbook and worksheet are the Active ones at the time the assignments to WS and WB are made.  However, given the randomness with which ActiveWorkbook seems to change when I have two or more workbooks open during macro execution, Im not yet confident that I understand the limitations of that approach and the effectiveness of possible alternatives.

Use of ThisWorkbook rather than ActiveWorkbook will eliminate any workbook-level confusion. 

But Im still struggling to identify a means of determining to which Sheet a given Name refers without having to know the answer at compile time.  I strenuously try to avoid using hard-coded workbook or sheet names within my VBA code in order to give myself the freedom to rename a workbook, rename a sheet, or move a defined range from one sheet to another without having to make code changes. 

For _most_ Names, use of: rt = ThisWorkbook.Names(RangeName).RefersTo will return a string of the form =Sheet1!R1C1 from which I can parse out the Sheet name and cell address for use in the ThisWorkbook.Sheets("SheetName").Range("R1C1") construct I asked about initially.

I realize that Names can be duplicated when they have Sheet-level Scope (i.e. when the same Name is defined separately on two or more Sheets within the same workbook.)  But I have so far avoided that issue by making the Names in my workbooks unique. 

I had hoped there would be a more elegant way to identify the Sheet association for a Name than by parsing the individual pieces out each time.  Perhaps not.  Sigh.

cw

Re What sheet am I on

Try...
  MsgBox range(rt).Parent.Name

'---
Jim Cone

Re What sheet am I on

Try...
  MsgBox range(rt).Parent.Name

'---
Jim Cone

• Marked as answer by CharlieWright 19 hours 1 minutes ago

Re What sheet am I on

Try...
  MsgBox range(rt).Parent.Name

'---
Jim Cone

• Marked as answer by CharlieWright Monday, April 20, 2015 12:27 PM

Re What sheet am I on

Try...
  MsgBox range(rt).Parent.Name

'---
Jim Cone

• Marked as answer by CharlieWright Monday, April 20, 2015 12:27 PM

Hi,

Please try Jim's suggestion first and if you have any further question about coding, I recommend you post the question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support

Jim,

Thanks.  It turns out that Range(ThisWorkbook.Names("RangeName").RefersTo) returns the value stored in the cell associated with the defined name "RangeName"  That's exactly what I had been hoping for.  I had never tried it before because the RefersTo value includes both the Sheet!R1C1 address, but it's prefixed with an equal sign (i.e. "=Sheet1:R1C1" rather than merely "Sheet1R1C1")  Without trying to use it, I assumed the equals sign would interfere with how the Range Method interpreted the argument.  By prefixing the Names Method with ThisWorkbook, I'm able to explicitly specify the associated workbook and avoid the need to identify the Sheet, which is implicitly inherent in the Defined Name itself.

George,

Thanks to you too.  I'll bookmark the MSDN Forum for future reference.  There are so many different Forums, it's always been rather a mystery to me which one(s) I should post questions to.

cw

Assuming you know the names of the files, use the following construct.

Workbooks("FileName.xls").Worksheets(1).Range......

That will always refer to the named workbook(assuming it's open!).

Gordon

Thanks Gordon.  I appreciate your reply.

cw