Hi everyone! I don't know if this is a simple thing or not but I've been looking through macros to use in my projects and I see that sometimes the macros say to put them in a Module or a Worksheet or the Workbook. But sometimes when I find a macro it doesn't say where to put it and I was wondering if there is a quick and simple way to know where to put it?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Macro in module or worksheet or workbook?
0
Answers
0
The code sheets of ThisWorkbook and each of the worksheets are intended for code which extends their functionality, especially by building on workbook and worksheet events and possibly functions directly supporting such procedures. All other code doesn't belong in those modules and often won't even run if placed there.
The inversion of the above is that code belongs in a "normal" code module unless it is an event procedure. Event procedures are easily identified by their prescribed names like Worksheet_Change or Worksheet_SelectionChange or Workbook_Open. These names are also indicative of the code sheet on which they should be placed.
Procedures which are called by event procedures may be placed on the same code sheets as the event procedures that call them if they are declared as Private. If they are to be called from other parts of the VBA project they must be declared as public and placed in a "normal" code module.
Discussion
so if it begins with "Worksheet_" then it gots into the worksheet where you run the macro and if "Workbook_" then it goes into the ThisWorkbook area and if it's not one of those it most likely goes into a module, right?
jujube Jun 19, '18 at 7:59 am
Yes, that's the first criterion to sort by. For all others you won't ever go wrong placing them in a standard code module with a Public scope.
Please remember to mark my answer as "Accepted" if you found it helpful. Thank you.
Please remember to mark my answer as "Accepted" if you found it helpful. Thank you.
Variatus (rep: 4889) Jun 19, '18 at 9:50 pm