PDA

View Full Version : Automated Macros



KevinPhilips
07-18-2013, 08:28 AM
Hi all,

I received an Excel file (*.xlsb) that contains 8 Modules full of code. I am supposed to read myself into it, since the programmer is not around anymore. I have two quick questions:

1. Does an automated macro (that runs when you open the Excel file) always start at the top of Module1?

2. Does anyone have any suggestions on working yourself into a pretty big project?

Thank you so much in advance!

Best regards,
Kevin

Kenneth Hobs
07-18-2013, 08:40 AM
Welcome to the forum!

The usual method to run a macro on Open is to put the code or call to a Sub in a Module in the ThisWorkbook object's Open event.

Module code does not run automatically.

Do the project in pieces. In the Visual Basic Editor (VBE), press F8 while in the Sub to execute each line one at a time with each press. Hover cursor over parts to see what variable are being evaluated to some value. Other debug tools are in the Debug menu of the VBE.

MsgBox() and Debug.Print are good ways to get some feedback as to what is happening in some places if you don't want to debug by one line at a time using F8 to step through the code.

As with any project, do small parts until it is completed.

SamT
07-18-2013, 03:00 PM
Automatic subs (Macros) AKA Event Subs always have an Underscore in the name.

The underscore is preceded by an Object Type name such as Workbook, Worksheet or such, and followed by an Event name, such as Open, Close, Activate, etc.

The only subs that are triggered when a workbook opens are Private Sub Workbook_Open()
Private Sub Workbook_ActivateHowever, they can call other subs, so you could have a whole string of subs run when the book opens.

About understanding a large amount of code:

Make an index:
First Export all the modules , including any sheet code or ThisWorkbook code page, to a new folder of your choosing. (Right click Module name in VBA). Open the folder and select one module (Left click.) Press Ctrl+A, Ctrl+C, and Ctrl+V to make copies of all of them.

Choose a copy and rename it by deleting the "Copy of" and changing the ".bas" to ".txt"

Open the copy in Notepad. you'll see
Attribute VB_Name = "mod_FilesAndPaths" at the top. Delete "Attribute VB_Name = " and the quotes around the name.

Delete everything in the file except the Module name at the top, the words "Property," "Function," "Sub," in front of any macros, and the actual names of the Macros. Property subs come in three flavors; Only leave the first one, as they all three have the same name. If it is a Property Sub, Delete the word "Sub" in front of the name.

You should be left with a list of macro names preceded by "Property," "Function," or "Sub." Remove any duplicate Property type names.

Replace all the spaces between the type labels and the macro names with Tabs.

At the top, put a Tab in front of the Module name and copy the Tab and name. Paste them at the end of all macro names.

You should be left with a list of macro names preceded by Type labels and followed by the Module name, and all spaces between everything is actually a Tab.

Copy the list and paste it into a new Excel workbook sheet. Name the Sheet "Macro Index." Save the Workbook in the same folder as the Exported Modules. Save and close Notepad.

Repeat the above for every "Copy Of" file. In the workbook sheet, paste each new list under the last one.

After you've completed all the lists, save Excel and delete all the ".txt" files, we're done with them.

In Excel, Cut column "A", the Type label column, and Insert it before column "C", the module name column. Data Sort the three columns, Ascending, on column "A " The three columns should now be Macro Name >> Macro Type >> Module Name. Save.

Rename Sheet2 to "Macro Calls." Copy "Macro Index," Column "A", and on "Macro Calls," cell "A1", Paste Special >> Transpose. Make a copy of the sheet "Macro Calls" and name it "Macro Parameters." Row(1) of these two should have the list of names across the top.

The sheet "Macro Purpose" needs a copy of the Macro names column from the Index.
The sheet "Function Returns" gets a list of Function Type Macros.

You might want a sheet "Module Contents" that has one or more columns for each module and contains at least the list of Macro names and Types pertaining.

Save your work.

As you go through the code the first time, the purpose should be to fill out the "Calls," Parameters," "Purpose," and "Returns" sheets. When you've done that you will have a very good overall understanding of how the system works. This is very important to understanding each macro individually.

Good luck and have fun. :devil2:

Kenneth Hobs
07-21-2013, 10:26 AM
Just to complete this thread, there is a third way to run a macro when the workbook opens. The order is: 1. Thisworkbook's Wordbook_Open() 2. Thisworkbook's Wordbook_Activate() 3. A Module's Auto_Open(). ' http://office.microsoft.com/en-us/excel-help/running-a-macro-when-excel-starts-HA001034628.aspx

SamT
07-21-2013, 12:00 PM
Correction: The sheet "Function Returns" should be named "Function Data Types."

KevinPhilips
07-22-2013, 05:09 AM
Thank you guys. Very helpful, very fast! :)