Is there a way to declare and use a VBA variable as long as the Excel workbook is open?-CodePudding

I don't have a deep knowledge of VBA fundamental programming, I just know how to declare variables, use methods, create some objects and a very weak knowledge of creating classes to make new event handlers.

My question is "Is there any way I can declare a variable to automatically give it a value and use this variable as long as my Excel workbook is open?"

I have written these codes in "ThisWorkBook" and "Sheet1" Module and using a Global variable called Wb to test if this variable remains as long as my workbook is open but it seems it does not.

In "ThisWorkbook" Module:

Public Wb As Workbook

Private Sub Workbook_Open()
   set Wb=ThisWorkbook
End sub

In "Sheet1" Module:

Private Sub Worksheet_Activate()
   MsgBox Wb.Name
End Sub

CodePudding user response：

ThisWorkbook is not a Module. It's an Excel Object.

Right click on the VBAProject
then click Insert
then click Module.

Put this declaration there instead.

Public Wb As Workbook