Create VBA Modules in Excel

Very often that people want to make some operations not supported by Excel itself. Yes, some friends asked me about this, and I also jumped into such situation sometimes. From a perspective of programmer, all operations like this can be done with some programming work. For general Excel users, they may not like programming and like some interactive way instead. Moreover, it's more straightforward to read and write files if working with Excel.

Here, I summarize a (hopefully) simple procedure to add customized VBA modules in Excel. Just for those who are interested.

1. Show "Developer" menu in Excel Ribbon

File → Options → Customize Ribbon, check "Developer" and click OK

2. Open a blank Excel workbook, and save as .xlam format in the default directory

For Office 2010 on Windows 7, the default directory is
C:\Users\[username]\AppData\Roaming\Microsoft\AddIns

3. Make the add-in available in Excel

Developer → Add-Ins, check or browse to locate the add-in you just created

4. Open VBA editor

Developer → Visual Basic, or ALT + F11, and then select proper project

5. Insert and edit customized module

In VBA editor, Insert → Module, then edit or copy VBA code

6. Save and exit

If you want to add the add-in to Excel Ribbon, you need create some XML markup first. I prefer not to do so, because I don't want to spend time doing that.

To use the add-in, after opening the data file on which you want to make operations, open VBA editor as above, then select the proper module and click Run Macro (F5).

I attach a sample VBA module code to delete the lower triangular entries of a matrix.


Reference:

Microsoft MSDN Library