Excel: ThisWorkBook returns ActiveWorkBook

excel:  thisWorkBook not working.
I am finding that thisWorkBook returns the ActiveWorkBook.

I can solve the problem by making sure that  this is the only workbook open, but then the VBA is not very stable to distribute.
I can also search the workbooks for the name of my workbook, but then I have to be sure that the name doesn't change.

is this defect in excel or am I doing something wrong ?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
In which workbook to you have the code that refers to ThisWorkbook? If the code is in WB1 and you do something in that workbook to trigger the code then WB1 and ActiveWorkbook are one in the same.
Martin LissOlder than dirtCommented:
This code will tell you if there's more than one workbook open

Function isMoreThanOneOpen() As Boolean

If Workbooks.Count > 1 Then
    isMoreThanOneOpen = True
End If

End Function

Open in new window

And you could create a workbook object and loop through all the open workbooks to see the names of the open ones.

This will tell you if there's more than one instance of Excel open.

Sub InstanceCount()
Dim objList As Object, objType As Object, strObj$
strObj = "Excel.exe"
Set objType = GetObject("winmgmts:").ExecQuery("select * from win32_process where name='" & strObj & "'")
If objType.Count > 1 Then
MsgBox objType.Count & " Excel instances are running on your system.", , "More than one instance"
MsgBox "Only this instance is running on your system.", , "One and done"
End If
End Sub

Open in new window

Martin LissOlder than dirtCommented:
And this will tell you if the name of the workbook passed to it is open. The name needs to include the suffix, for example MyWorkbook.xlsm.
Function IsWBOpen(strWB As String) As Boolean

Dim wb As Workbook

For Each wb In Workbooks
    If wb.Name = strWB Then
        IsWBOpen = True
    End If
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sidwelleAuthor Commented:
I was able to loop through the workbooks and the worksheets of each workbook and look for a sheet of my workbook with a unique name.

It works, its just clumsy.  Would have through there would have been a better approach ?
Martin LissOlder than dirtCommented:
Can you post your current code that looks for the sheet or better yet your workbook?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
By all means, ThisWorkbook will always refer to the Workbook in which the code is placed no matter how many workbooks are open at that point of time.

e.g. if you place the following code in a workbook called say YourMacroWorkbook.xlsm, the code will always refer to this workbook only no matter how many workbooks are open or which one is currently the active workbook.
Dim ThisWB As Workbook
Set ThisWB = ThisWorkbook
MsgBox ThisWB.Name

Open in new window

Fabrice LambertConsultingCommented:
I think this is a non-issue:

ThisWorkbook always return the workbook in wich the maco is written in, wich is safe and stable.
ActiveWorkbook always return the workbook manipulated by the user, wich is subject to change anytime, thus by this nature is chaotic.

Are you triggering functions written in a "Library workbook" ?
In that case, such functions should recieve a workbook object as parameter, so they know with what to work with instead of assuming.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.