Opening multiple workbooks causes identity issues

[insomniac53]

When opening more than one workbook in Excel (latest Office 365 version) VBA code does not know which workbook it belongs to. For example, suppose you have two workbooks open and you switch from one to the other, perhaps using ALT-TAB. Any procedures or functions referring to a particular sheet will crash because it's looking in the active workbook, not the one it belongs to.

Private Function MyFunction() As integer
    Dim w1 As Worksheet: Set w1 = Sheets("name of sheet")
    <code>*** code ***
End Function

This is fine until another workbook has been opened at which point the code fails, even if this is the active one on screen. I understand that you could specify a workbook and sheet within a single function, but this isn't feasible when you have many functions using the simple Sheets command above - you would need to alter each one individually.

Could someone tell me if there is a way around this on a global level so that when switching from workbook to workbook, the vba code knows which one it belongs to? Thanks!</code>

 

[Yongle]

Passing variables
Variables can be passed to a function like this

Sub PassVariables()
    Dim wb1 As Workbook, wb2 As Workbook, fName As String
    Const fPath = "C:\Test"
    
    fName = fPath & "\" & "name1.xlsx"
    Set wb1 = Workbooks.Open(fName)
    fName = fPath & "\" & "name2.xlsx"
    Set wb2 = Workbooks.Open(fName)
       
    MsgBox GetWorkbookSheetCount(wb1)
    MsgBox GetWorkbookSheetCount(wb2)

End Sub

Private Function GetWorkbookSheetCount(wbk As Workbook) As Integer
    GetWorkbookSheetCount = wbk.Sheets.Count
End Function

Global Variables

a variable declared as Public at the top of a standard module is available globally

Here is a useful guide from SpreadsheetsMadeEasy.com

 

[insomniac53]

Passing variables
Variables can be passed to a function like this

Thanks for this. I understand passing variables, and even pointers to variables, but it isn't the answer here as I'd still need to change every procedure and function, which is what I'm trying to avoid. I'm looking for a way to get Excel to know which sheet is active before calling any of these functions. I suspect I might be looking for the impossible.

 

[Jaafar Tribak]

Thanks for this. I understand passing variables, and even pointers to variables, but it isn't the answer here as I'd still need to change every procedure and function, which is what I'm trying to avoid. I'm looking for a way to get Excel to know which sheet is active before calling any of these functions. I suspect I might be looking for the impossible.

Is the same function used in the different workbooks ? and do the sheets in both different workbooks have the same names like Sheet1, Sheet2 etc ..?

And where are you calling the function from ?

 

[insomniac53]

Is the same function used in the different workbooks ? and do the sheets in both different workbooks have the same names like Sheet1, Sheet2 etc ..?
And where are you calling the function from ?

Thank you.

No, the workbooks don't have the same name, and no, the same function is not used in both workbooks. In fact, it doesn't matter which secondary workbook is opened, even a very simple one with no VBA, the primary workbook loses focus and no longer recognises its own forms and sheet names.

I'm not calling from one specific function. The code in the primary worksheet fails at whichever point it has to access a sheet or form after I've switched back from another workbook.

 

[Yongle]

Set variables for each workbook when opened and then activate the correct workbook before calling the function...and if necessary activate another workbook after...

Sub MyMacro()
Dim fName As String
Const fPath = "C:\Area"
fName = fPath & "" & "name1" & ".xlsx"
Set [COLOR=#ff0000]wb1[/COLOR] = Workbooks.Open(fName)
fName = fPath & "" & "name2" & ".xlsx"
Set wb2 = Workbooks.Open(fName)
   
[COLOR=#ff0000] wb1.Activate[/COLOR]
 MsgBox ActiveWorkbook.Name  'prove it to yourself with this
 'now the correct workbook is active  

If the other workbook needs to be active before continuing...
wb2.Activate

... rest of VBA

End Sub

 

[Jaafar Tribak]

Thank you.

No, the workbooks don't have the same name, and no, the same function is not used in both workbooks. In fact, it doesn't matter which secondary workbook is opened, even a very simple one with no VBA, the primary workbook loses focus and no longer recognises its own forms and sheet names.

I'm not calling from one specific function. The code in the primary worksheet fails at whichever point it has to access a sheet or form after I've switched back from another workbook.

Sorry but I am confused ... Are you saying that the function no longer works even after switching back to the workbook that has the function ?

Maybe someone else would read this thread and be able to provide some help.

 

[insomniac53]

Almost. It isn't one function that fails, it's any function in the primary workbook that accesses a sheet or a form. Excel seems to 'forget' which workbook it's looking at once I've switched to view another workbook.

 

[Yongle]

read post6

 

[Jaafar Tribak]

Just a stab in the dark ... Try calling the function with a small delay like the following and see what you get :

Application.OnTime Now, "MyFunction"