VBA environment and Module

[lezawang]

Hi
I have excel 2013. When I click on Developer-->VBA, excel will open VBA environment. Under project, I see 2 entries
1) Sheet1 (Sheet1)
2) ThisWorkbook

My understanding, if I want to write a code to run for ONLY sheet1 then I click on sheet1 and write the code. At same token, if I want to write a code that can run on any sheet then I click on ThisWorkbook and write the code. Am I right?

If yes then why I need to click on insert-->Module? How module is different from Sheet1 or ThisWorkbook entries?

Thank you.

 

[Marcelo Branco]

See if this helps
https://www.mrexcel.com/forum/excel-questions/1009836-vba-code-sheets-vs-modules.html

M.

 

[lezawang]

Thank you for the link. It is helpful. I have one question, I did not understand this : "Worksheet event code has to be in either the module for that sheet"

Lets say I have Sheet1, Sheet2, Thisworksheet, in project window. When I insert a Module, would that module will be for Sheet1 or Sheet2? or both?

Thank you.

"Worksheet event code has to be in either the module for that sheet, or the ThisWorkbook module if it's the same for more than one sheet. Event code for ActiveX controls must be in the sheet module. Other code should generally be in a normal module, though if it relates specifically to one sheet you can certainly make a case for it being in that sheet's code module."

https://www.mrexcel.com/forum/excel-questions/1009836-vba-code-sheets-vs-modules.html

 

[RoryA]

When I insert a Module, would that module will be for Sheet1 or Sheet2? or both?

Neither. Code in a normal module doesn't relate to anything unless specified. If your code refers to Range or Cells, and doesn't specify a sheet, it will relate to whatever sheet is active at the time.

 

[lezawang]

Thank you. Could you please kindly explain what does "the module for that sheet " mean?

 

[Norie]

Each sheet, and workbook, has a module of its own and that's where any code, e.g. event code, specific to the sheet (or workbook) should go.

 

[lezawang]

and that is called Sheet1 and Thisworkbook right?

 

[RoryA]

Yes, or Sheet2 or whatever the code name for the sheet is.

 

[lezawang]

got it. thank you all