CODEWITHSUNDEEP
excelvbaobjectqualifiers
Jeffrey Knight
Jeffrey Knight

Reputation: 51

Difference between Excel.ThisWorkbook and just ThisWorkbook?

I have some VBA code that ran fine in Excel at my desk at work but at home it's crashtastic. Turns out all I had to do was change

Set shtObj = ThisWorkbook.Sheets(1)

to

Set shtObj = Excel.ThisWorkbook.Sheets(1)

Anyone know why? Our organization has just moved to a SharePoint platform, which I suspect is causing all manner of voodoo to creep up, but I'm just guessing.

Upvotes: 3

Views: 586

Answers (3)

Jeffrey Knight
Jeffrey Knight

Reputation: 51

This appears to be a bug in my particular user/profile. Others are now using the code/workbook with no reports of trouble. Yay!!?

Upvotes: 0

ThunderFrame
ThunderFrame

Reputation: 9471

Does it work if you change it back to ThisWorkbook?

I suspect it will, and the reason would be because the VBA recompiled itself (and didn't compile properly the first time - hence the propensity to crash).

Recompilation occurs when the version details embedded in the file differ from the version of Office/VBA in use, or there's a change from 32 to 64 bit Office. Editing the line is enough to recompile the line, so adding Excel. before ThisWorkbook was enough to make it recompile. Removing Excel. before ThisWorkbook should force it to recompile again.

The only other thing it might be is if there's a variable named ThisWorkbook, but then I'd expect you to get error 91, "Object variable or With block variable not set", or some other error, but not a crash.

Upvotes: 5

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

ThisWorkbook is a global-scope Workbook object that you can use to refer to the workbook instance that contains the very VBA code that you're looking at.

So in theory, an unqualified ThisWorkbook is exactly the same object as Excel.ThisWorkbook or Application.ThisWorkbook.

Proof:

Sub Test()
    Debug.Print ObjPtr(ThisWorkbook), _
                ObjPtr(Excel.ThisWorkbook), _
                ObjPtr(Application.ThisWorkbook)
End Sub

Outputs 3 times the same pointer address.

Excel.ThisWorkbook is a member (Property Get) of the hidden Excel._Global module, and Application.ThisWorkbook is a member (Property Get) of the Excel.Application class.

Not sure what's up with your file, but there's not supposed to be any difference between the two.

Upvotes: 2

Related Questions