Home | About Us | Feature | Programs | Support | Training | Development | Downloads | Free Tools | Newsletter | Links | Contact

Microsoft Excel Programming Primer

In this short course we are going to introduce you to the wonderful world of computer programming.  Microsoft Excel is the ideal program to use to learn how to write programs: You can record actions and then see the code; you can step through code and watch the results, and the programming language is much simpler than stand-alone languages like Visual Basic 2005/2008/2010.

Our course uses Excel 2003.  We program in "Macros", also known as VBA (Visual Basic for Applications), or just Visual Basic.  The language is virtually identical to Visual Basic 6, which is a separate program, and now a bit antequated.

In case you have never programmed before, we start slowly.

There's a series of more elementary tips in our free monthly newsletter: Click to Subscribe.
Click Here to ask about a Programming Course for your company.
Click Here to ask about buying the full Excel Programming Course.

In most browsers, you can press Ctrl+F, or Edit > Find, to search this page for keywords.

Contents:

Preview – Your Own Excel Shortcut Key
Lesson 1 – Introducing Macros
Lesson 2 – Coding Macros
Lesson 3 – Recording Macros Absolute and Relative
Lesson 4 – Discussion of Absolute and Relative Macros
Lesson 5 – Overview of the Visual Basic (Macro) Editor
Lesson 6 – the Visual Basic (Macro) Editor, Part II – Code
Lesson 7 – Procedures: Subprograms and Functions
What Next?
Contributed Tips


Preview – Your Own Excel Shortcut Key

Q: How can I create my own Excel shortcut key for frequently-used operations?

A: Record the operation as a Macro: Click Tools > Macro > Record New Macro. Name the Macro (no spaces). Put it in the Personal Macro Workbook if you want it to be available at all times. Here you can also specify a shortcut key: Hold in the Shift key and press a letter on the keyboard. (Shortcut keys with "Shift" work best because there is no risk of their replacing built-in shortcuts like Ctrl+C (Copy). You can however use the letter unshifted.) During recording, pay attention to whether you want to record Absolute or Relative references –the results can be very different! When you have finished performing the actions, Stop Recording! Save your file in case of mishaps. To test your macro, hold in the Ctrl key and press the Shift+Key combination you set earlier.

Lesson 1 – Introducing Macros

The good news is that you don't have to be a programmer to get benefits from Macros.

However, it is possible to make serious mistakes if you don't know what you are doing, so we will give you appropriate warnings.

The first one is to save your spreadsheet before you test any macro, and preferably keep several copies (in case you write a macro that saves the spreadsheet for you).

Exercise:

  • Start a new spreadsheet and enter some data (it doesn't matter what - we are going to be doing formatting).

  • Select a cell that has something in it.

  • Click (menu) Tools > Macro > Record New Macro.

  • For Macro name, type Format1 (no spaces!)

  • Press Tab. This takes you to "Shortcut Key".  Press Shift+F –note that this means that you will press Ctrl+Shift+F to run the macro.  Tip: Always use Shift in your Macro shortcut keys; then you will never have conflicts with the built-in shortcuts.

  • For "Store Macro In", make sure that "This Worksheet" is chosen.

  • Click OK.  A small toolbar should appear.  Everything you do now will be recorded (we hope not as evidence against you!).

  • On the Formatting Toolbar, click the Bold "B" (if you can't see it, just press Ctrl+B)

  • On the Formatting Toolbar, click the Italic "I" (if you can't see it, just press Ctrl+I)

  • On the Formatting Toolbar, click the Underlined "U" (if you can't see it, just press Ctrl+U)

  • Now click the left-hand button (the blue square) on the "Stop recording" toolbar.  If you can't see it, use Tools > Macro > Stop Recording.

To Test the Macro:

  • First, save your file (we may have mentioned this before).

  • Now, select some different cells, also containing data.

  • Press the magic keys you just created:  Ctrl+Shift+F

  • The cells you selected should be formatted with Bold, Italic, and Underscore.

You have just saved two whole keystrokes or mouse clicks!  Impressive, isn't it?

Close the file and open it again.  Excel should ask you if you want to enable macros.  Click Enable Macros.  If Excel does not ask this, go into Tools > Macro > Security and choose Medium.  Then close the file and open it again.

Finally: Using the same file, record another macro that does different formatting: Perhaps a box around the cells, or a number format, for example.  Save and test.

Lesson 2 – Coding Macros

Last Lesson we introduced programming Excel Macros (Visual Basic).  Now we will look at the code we created.

For this next exercise you will need the macro you recorded last time.  If you didn't do it before (naughty, naughty!) do it now.

Exercise:

  • Open the file containing the macro.  Excel should ask you if you want to enable macros.  Click Enable Macros.  (If Excel does not ask this, go into Tools > Macro > Security and choose Medium, then close the file and open it again.)

  • Click (menu) Tools > Macro > Macros.  Note that the shortcut for this is Alt+F8.

  • You should have a small window headed "Macros".

  • In the drop-down list at the bottom, "Macros in", select "This Workbook".

  • In the big list in the middle, you should see your macro name, "Format1".

  • Click on it, then on the button Edit.

  • This will open a new program "Microsoft Visual Basic", with a window containing two panes: A small vertical one on the right, and a larger one occupying the rest of the screen, containing your macro.  The code will probably look like this:

Option Explicit

Sub Format1()
'
' Format1 Macro
' Macro recorded 16/02/2005 by Rick Raubenheimer
'
' Keyboard Shortcut: Ctrl+Shift+F
'

    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Underline = xlUnderlineStyleSingle
End Sub

  • You will notice that your Macro, Format1 has become a "Sub" (subprogram), ending with an "End Sub".

  • Lines starting with a single quote (') appear in green.  They are comments, for your benefit, and ignored by the program.

  • The real program consists of three lines, corresponding to the three actions you performed: Bold, Italic, and Underline.

  • The first two are straightforward.  Supposing we want to change the macro to turn Bold and Italic off, how do you think we would change the macro?

  • Make the change now.

  • Swap to the spreadsheet (using the task bar, Alt+Tab, or the Excel Icon on the toolbar).

  • Select an are that is already Bold and Italic, and test the macro as before, with Ctrl+Shift+F

  • The cells you selected should have their formatting of Bold, Italic removed, but still have Underscore.

You have just done your first programming.  Well done!

Lesson 3 – Recording Macros Absolute and Relative

Last Lesson we looked at the code we created recording our first Excel Macro.  This time we will look at a subtle but important difference in the ways we can record.

We will record two macros that will be very similar to the first macro we recorded:

First Exercise:

  • Start a new spreadsheet and enter some data (it doesn't matter what - we are going to be doing formatting).
  • Select a cell with something in it.
  • Click (menu) Tools > Macro > Record New Macro.
  • For Macro name, type Format2 (no spaces!)
  • Press Tab. This takes you to "Shortcut Key".  Press Shift+G (this means that you will press Ctrl+Shift+G to run the macro).
  • For "Store Macro In", make sure that "This Worksheet" is chosen.
  • Click OK.  A small "Stop recording" toolbar should appear. On the left is a button with a square ("Stop").  On the right is the "Relative Reference" button.
  • Click the "Relative Reference" button and note the small difference - is it "pressed in" (with a border) or "out"?  Make sure it is out.
  • On the Formatting Toolbar, click the Bold "B" (if you can't see it, just press Ctrl+B)
  • On the Formatting Toolbar, click the Italic "I" (if you can't see it, just press Ctrl+I)
  • On the Formatting Toolbar, click the Underlined "U" (if you can't see it, just press Ctrl+U)
  • On the keyboard, pres the right arrow -> to move one cell right.
  • Now click the left-hand button (the blue square) on the "Stop recording" toolbar.  If you can't see it, use Tools > Macro > Stop Recording.

Second Exercise:

  • Record a new Macro named Format3, with Shortcut Key Ctrl+Shift+H.

  • Repeat the exercise above, with one exception: This time, make sure the "Relative Reference" button is "pressed in".

To Test the Macro:

  • First, save your file (we may have mentioned this before).
  • Now, select some different cells, also containing data.
  • Run using the shortcut you just created:  Ctrl+Shift+G
  • Repeat, using Ctrl+Shift+H.
  • What is the difference?

Discussion next Lesson: Save your macros until then!

Lesson 4 – Discussion of Absolute and Relative Macros

Above we created two identical macros to Bold, Italicise, and Underline the selection, then move one cell right.  The first macro, Format2 (Ctrl+Shift+G), was recorded Absolute (using the right-hand button on the "Stop Recording" toolbar).  The second, Format3 (Ctrl+Shift+H), was recorded Relative.

We will now look at the code we created.  Open the file you saved last time (If you don't have it anymore, or didn't do it, scroll up again and record the macros quickly).  Excel should ask you if you want to enable macros.  Click Enable Macros.  If Excel does not ask this, go into Tools > Macro > Security and choose Medium.  Then close the file and open it again.

When you run the macros, Format2 (Ctrl+Shift+G), always finishes in the same cell, no matter where you started.  This is the meaning of "Absolute".  The Format3 (Ctrl+Shift+H), macro always moves one cell right from wherever you start (Relative).  Let's look at the code:

Press Alt+F11 to open the Visual Basic editor.  You should find the macros in Module1 (Ctrl+R for Project Explorer, expand Project, expand Modules).  As you will see, the only difference between the two is in the last lines (before End Sub):

    Range("C1").Select

versus
    ActiveCell.Offset(0, 1).Range("A1").Select

The first selects a specific cell (in our case, C1) - the second moves by an offset (0 rows, 1 column) from the current cell.  The ".Range("A1")" is in fact redundant and can be omitted - it does not relate to cell A1 in the worksheet.

Lesson 5 – Overview of the Visual Basic (Macro) Editor

Last Lesson we examined our first macros.  Now we will look at the editor in more detail.

It is useful, but not essential, to open the file you viewed last time  (If you don't have it anymore, or didn't do it, record the macros quickly using Tools > Macro > Record New Macro).  Excel should ask you if you want to enable macros.  Click Enable Macros.  If Excel does not ask this, go into Tools > Macro > Security and choose Medium.  Then close the file and open it again.

Press Alt+F11 to open the Visual Basic editor.

On the left-hand side of the screen near the top you should see a window headed "Project - VBAProject" or some variation thereof.  It will contain a Tree View.  This is the Project Explorer.  If you do not see it, press Ctrl+R or click View > Project Explorer.  If you see a "plus" sign next to any item, you can click it to expand that part of the tree.  Click a "minus" sign to collapse that part of the tree.

In the Project Explorer we find Projects at the upper level.  Projects consist of Microsoft Excel Objects and Modules.  The Objects are Sheets and "This Workbook".  You can have macros in Sheets, "This Workbook", and as many Modules as you like.  When you record macros the automatically go into Module1.  If you close Excel, reopen, and record new macros they go into Module2, and so on.

It is useful to rename the objects, particularly the Modules, to give them meaningful names.  To do this, press F4 to open the Properties Window (or click View > Properties Window).  It will probably open directly below the Project Explorer.  You can adjust its height by dragging the grey bar just above its title bar up or down.

To rename a Module, make sure you have the Properties window visible.  Click on the Module.  In the properties, next to (Name), the name should appear.  Change it to the new name and press Enter.  You can also change the names of "This Workbook", and the modules associated with Sheets.  In the case of the latter, you see the sheet name (as set on the Sheet Tab) in brackets.

In the larger, right-hand window, we see our macro (Visual Basic) code.  We'll look at that area next...

Lesson 6 – the Visual Basic (Macro) Editor, Part II – Code

Last Lesson we started looking at the editor, in particular the Project Explorer (Ctrl+R) and the Properties Window (F4).

It is useful, but not essential, to open the file you viewed last time (If you don't have it anymore, didn't do it, or did not get the July newsletter, you can record any macro again quickly using Tools > Macro > Record New Macro).  Remember, you need Tools > Macro > Security > Medium Press Alt+F11 to open the Visual Basic editor:

On the right-hand side of the screen you see a window headed by two drop-down lists.  This contains your code.  The macro code is written in VBA (Visual Basic for Applications).  You need to know something about Visual Basic if you wish to change the code in your Macros.

The best course of action in learning how Excel does things is to record the action, and then examine the recorded code.  For example, if  Selection.Font.Bold = True  sets Bold ON, what do you think sets it OFF?

Comments, which start with a single quote (‘) (shown in green), are ignored by the computer.  They are worthwhile as later reminders of what you were doing.

Select any word you want to find out more about, then press F1 for Visual Basic Help.  For example, select xlUnderlineStyleSingle in the above example, press F1, and you should get help on the various underline styles available.

This is not a substitute for our four-day Visual Basic Beginners’ and Intermediate courses, but here are some basic concepts:

Lesson 7 – Procedures: Subprograms and Functions

Every macro is a Subprogram procedure, and must start with Sub name and end with End Sub.

You can also create Function procedures, which work like Excel’s built-in functions.  The form of a function is:

Function FnName(Parameters)
  FnName =
some calculation using Parameters
End Function

In a worksheet cell you would then enter e.g. =FnName(66).

Example: Try this function (copy from here and paste it in at the bottom of the VB code):

Function CellFormula(cel As Range) As String
  ' Document another Cell: e.g. =CellFormula(A1)
  CellFormula = Replace$(cel.Address, "$", "") & ": " & cel.Formula
End Function

This is very handy for displaying a cell's formula so that you can print it out next to the result.  To use it, in cell A4, enter =Sum(A1:A3), and then enter in cell B4: =CellFormula(A4) ―isn't that cool?

What Next?

This is just a part of our full Excel Programming Course.  We can run it for your company at your premises if you are in Gauteng, South Africa:  Click Here to ask about a Programming Course for your company.

Alternatively, you can purchase the full text of the course, with examples, for your private or company use:
Click Here to ask about buying the Excel Programming Course.


Contributed Tips

Would you like to add a tip of your own (due acknowledgement will be given!) – click here to send tip.

See alsoExcel Spreadsheet Tips, Microsoft Word Tips, Microsoft Access Tips, Maximizer Tips, Tips on Windows and other Windows Programs, Free Software for Programmers.

Related Information for Microsoft Excel 2007

The Must Fix List for Microsoft Office 2007. For Microsoft Word, Excel And Outlook 2007. Fix all the quirky little changes the engineers designed into the 2007 Office Software. When all you want is 'normal' but you upgraded to 'freaky', this will bring you back. It's fast and friendly. Click Here for more information!

 

Press Ctrl+F to search this page for keywords.


| Back to top | ©2000-2013 Communication in Action cc t/a Software Africa. All rights reserved.  Updated 19 June 2013 e-mail Webmaster.