Difference between Excel.ThisWorkbook and just ThisWorkbook?

Question

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.

Answer 1

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.