Ganesh wrote into the DataPig hotline to ask:
“Mr. DataPig, Can you tell me what is the difference between ThisWorkbook and ActiveWorkbook?”
.
Anything for one of my 12 fans!
.
ThisWorkBook object refers to the workbook that the code is contained in. ActiveWorkBook object refers to the workbook that is currently active.
Most times, they will refer to the same workbook. But if the workbook running the code is not the active workbook then they will point to different objects.
.
Take this example code, where I’m deleting all but the active worksheet.
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws
In this case, I don’t want to risk deleting sheets in other workbooks, so I use ThisWorkBook.
.
I hope that clears it up Ganesh; thanks for the question.
Now I’m craving chocolate ganache.
That code will not work.
You have to insert a line between line 1 and 2 with the code
ws.Activate
Although it WILL work if the last sheet in the workbook is activated at the start. But not if any of the others are…
Some macros being launched from visual basic editor, i use this snippet to ensure i’m not overwriting a workbook that was manually misactivated
Public Function bExecute() As Boolean
‘ Vérifier que le classeur appelant soit actif.
‘ Checking is calling workbook is the active one.
bExecute = (ThisWorkbook.Name = ActiveWorkbook.Name)
End Function
sub demo
If Not bExecute Then end
… continue
end sub