How to Addins

[excel_Bert]

I'm making a Book that by clicking a button creates all the necessary pages for the user, the catch is that I need VBA event code to be present on each newly created page and also on the modules. I don't know if there's a programmatically way to do all that, though. To load the code on the back of each page, to insert modules and to load the corresponding code on them.
Since I don't want the users to mess with the code I started reading a bit about addins, and I am wondering about it.
Can they detect events on the pages and get programs started?
Do they also work like modules?
How can I get started working on them?
Do I write the code on a notepad and then load it into Excel?

Can anyone help me out with this?
Really appreciate the help.

Bert

 

[PA HS Teacher]

If the desired code is the same or virtually the same for each 'page', you might want to try a custom class like the following. Insert a class module. In this case I named the class module MyClass.

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">WithEvents</SPAN> WS <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> WS_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>  MsgBox "Hi, you just made a change in cell " & Target.Address & " on sheet " & WS.Name<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Then, in a regular module, you need a routine to add pages, and set them equal to a worksheet object in the custom class. They will then contain all of the Worksheet Events you've written into your custom class.

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> SpecialCol <SPAN style="color:#00007F">As</SPAN> Collection<br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Make3SpecialSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> WSht <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> SpecialWS <SPAN style="color:#00007F">As</SPAN> MyClass<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> SpecialCol <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> SpecialCol = <SPAN style="color:#00007F">New</SPAN> Collection<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 3<br>        <SPAN style="color:#00007F">Set</SPAN> WSht = ThisWorkbook.Worksheets.Add<br>        <SPAN style="color:#00007F">Set</SPAN> SpecialWS = <SPAN style="color:#00007F">New</SPAN> MyClass<br>        <SPAN style="color:#00007F">Set</SPAN> SpecialWS.WS = WSht<br>        SpecialCol.Add SpecialWS<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#007F00">' You will also need some code in the workbook open event to loop through the pages already in the</SPAN><br><SPAN style="color:#007F00">' workbook, decide which existing pages you want to have the SpecialWs.WS events, and add them to the</SPAN><br><SPAN style="color:#007F00">' SpecialCol collection</SPAN></FONT>

I didn't write the code, but you'll want a routine that runs perhaps when the workbook opens, to decide which existing pages you want to have the MyClass.WS events, and add them to the SpecialCol collection. Otherwise you will only have MyClass.WS events for worksheets that were just added using the above routine.

 

[xenou]

Some other notes: Addins are not much different than regular workbooks but they are hidden by default, and their functions are made available to other workbooks (at least as User-Defined worksheet functions go). The code isn't much safer than anything else in Excel VBA though. There is a little bit of help for the user to load/unload addins via the Excel GUI - which is nice.

Most Sheet events have a counterpart in the ThisWorkbook module (class) - so for instance, sheets have a Change event, but in the ThisWorkbook module you have a Worksheet_Change event, and so on.

As noted above it really isn't very hard to create an application class with events, either. I personally write all my Excel vba code in the Excel visual basic editor just for the convenience of intellisense and easy compilation (which is good for debugging your code as you go).