Paste Macro Into VBA

July 09, 2002 - by Bill Jelen

Bob asked this week's Excel question.

I have a problem. The Microsoft Knowledge Base offers an Excel macro to fix the problem, but I can not figure out how to get that macro into Excel. What is VBA? Where do I paste the macro?

Excellent question. There are lots of sites on the Internet offering help, but they all assume you know the basics about macros. Here is a step-by-step set of directions for using a macro from a web site.

Step 1: Start the Excel Visual Basic editor by hitting Alt-F11 or using the Tools > Macro menu.

OK, sure - this looks intimidating on your first try. I would run away if I were you, too!

On the bottom left is a properties window. You will not need this until you try to do your own userforms, so don't worry about it now.

On the top left is the project window. The project window is useful when you have more than one Excel file open at a time. Excel VBA macros can be stored right in a workbook. You can use the project window to switch from book to book. If you can't see all of the text because the window is too narrow, grab the right edge of the project window and drag to the left.

The big grey area on the right side of the screen is where your macro will eventually go. Here is just a little bit of the lingo you need. An Excel macro is called a "Procedure". Several procedures can be stored in a single "Module".

The first thing to do is to insert a blank module in this workbook. From the menu, select Insert > Module. You are now ready to paste in a macro from the web.

Using a browser, surf to a page with a macro. Using your mouse, click and drag to highlight the entire macro. This includes the line that starts "Sub" all the way through the line "End Sub". Use the Edit > Copy command from the menu to copy the macro to the clipboard.

Go back to the Visual Basic editor. Click in the large white code window, right below the "(General)". Do Edit > Paste to paste the copied macro into your workbook.

Here are some things to notice. The top line that starts with "Sub" ends with the macro name. It is important to remember this macro name so that you can run the macro later. Lines of code which start with an apostrophe are comments in the code. These lines will show up green. Read through them to see if there are any notes you need to worry about. For example, Microsoft will often advise you if the code has to be different for an older version of Excel.

You are ready to run. Go back to Excel. Under the Tools menu, select Macro. Select your macro name from the dialog box and click RUN.

When you save this Excel workbook, the macro will be saved in the workbook. Any time this workbook is open, you can run the macro.

Congratulations! You can now copy and run useful macros from the web.

Note for Excel 95 users: Excel 95 did not offer the same VB editor. From Excel 95, you would Insert > Module right from Excel. The Module sheet would then appear as a sheet in the Excel workbook. Paste the macro on this blank module sheet and use the Tools > Macro menu to run just as above.