How to Use VBA Codes in Excel

by L.P. Klages

Visual Basic for Applications (VBA) is a programming language you can use to tailor Microsoft Office applications to fit your specific needs. For example, you may have a particular format you like for your Excel documents; it's possible to write a piece of code that can open and format documents exactly the way you like them.

Writing VBA code involves some basic programming knowledge. However, Microsoft makes it easy for you to learn basic commands with the macro recorder. The macro recorder records your keystrokes and mouse clicks, turning them behind the scenes into code that you can view and edit.

1

Press the Alt and F11 keys at the same time from an open Excel worksheet. This will open a new window and bring up the Visual Basic Editor screen.

2

Click "Insert > Module" when the editor screen appears. You will see a blank editing window.

3

Press F5 to bring up a new macro dialog. Type a name for your macro in the Macro Name text box, then press "Create." Note that you are still in the Visual Basic editor at this point.

4

Locate the two lines of code that say "Sub MacroName" and "End Sub." Type your code into the form between the two lines.

For example, if you want your macro to rename the first worksheet in your workbook, type 'ActiveWorkbook.Sheets(1).Name = [New Worksheet Name]' (without the single quotes).

5

Press F5 to run the code you just wrote. The first sheet in your workbook is now named [New Worksheet Name].

Tip

  • check If you're unsure of what VBA code will accomplish your goals, try recording a macro while performing your action normally. When you stop recording, the macro code will be filled into your code editor automatically. You can access the Record Macro button from Excel's top toolbar.

Items you will need

About the Author

L.P. Klages is an entrepreneur and software developer, concentrating on information theory, software user experience, and mathematical modeling. He has been writing about technology and the business of technology since 1999. His articles have appeared on many sites, including GameDev.net, KenSharpe.net, and eHow. Klages attended Jacksonville University in Jacksonville, Fla.

Photo Credits