Getting Started

It is very easy to record a macro with the Macro Recorder and to create other macros of your own using the Visual Basic Editor.
The Macro Recorder is a very useful tool that can be used to generate the VBA code for you.
Even when you become an expert at programming in VBA you will still use this tool.
The Visual Basic Editor is a development environment that tells you immediately if there are mistakes in your code.
You will use this Editor to test your macros line by line and edit and modify the code at a later date.
Once you have a basic understanding you will be able to copy all the macros available on the internet and use them in your own workbooks.


The Visual Basic Editor (VBE)

The Visual Basic Editor is a program that is built in to Excel, Word, PowerPoint and Outlook desktop versions.
Every user that has Excel installed on a device can access and open the Visual Basic Editor.
When you want Excel to do some work for you, you open the Visual Basic Editor and you write the instructions using the VBA programming language.
Any VBA code you record, or write, in the VBA Editor will become part of the workbook.
When the workbook is shared or sent to someone else, the VBA code will stay with the workbook.


To help you access the VBA Editor quickly you should have the Developer Tab displayed permanently in Excel.
Select (File > Options > Customise Ribbon) Tick the Developer tab.

Once the Developer Tab appears press the "Macro Security" button.
Choose the last option "Enable VBA Macros with Notification" and you are set.

To display the VBA Editor you can press the Visual Basic button on the Developer tab or press (Alt + F11).
You can also use (Alt + F11) to toggle between the Office application and the VBA Editor.
This screenshot shows the Visual Basic Editor without any windows open.

There are 3 important windows that you will constantly be using.
Project Explorer - This displays a hierarchical list of the projects and all of the items contained and referenced by each project.
Properties Window - This window allows us to change some of the properties associated with an object at design time.
Code Window - This displays the code associated with the item selected in the Project Explorer window.


Organising the Windows

Select (View > Project Explorer)
The Project Explorer window should be displayed as a column on the left side of the application.
If the window appears in the middle of the gray area, right-click in the white space and check "Dockable".
Then click on the top blue bar of the Project Explorer window and drag it to the left until the cursor touches the middle of the left side of the screen.
Let go of the mouse and the window should be displayed on the left.
Move your cursor on the line separating the project window and the gray rectangle, hold and move the line sideways.

Select (View > Properties Window)
The Properties Window should be displayed below the Project Explorer window.
If the window appears in the middle of the gray area, select "Dockable" and drag the window to the bottom left corner.

Select (View > Code)
To open the code window you could also double click on the name of a component in the Project window (Sheet1, Sheet2, Sheet3 or ThisWorkbook).
You can maximize any Code window by clicking on its "Maximize" button Maximize button.
Once this has been done your VBA Editor should look like this.

Close the VBA Editor and close Excel. No need to save anything.
Reopen Excel and press (Alt + F11). You will see the three windows in their correct position.


The Project Window

This window displays a full list of all the workbooks that are currently open, and their corresponding components.
As you can see, the Project window shows you all the workbooks that are open ("Book1") in the example below and their components.
You can use the + and - signs to show the details.
A new Excel workbook normally includes three sheets and another component named "ThisWorkbook".
The VBAProject named "Book1" has 3 sheets and ThisWorkbook.

Using the (Alt + F11) key go back to Excel.
Lets add another sheet to this workbook.
Right-click on the tab of Sheet2 and select "Insert". Press OK.

Using the (Alt + F11), go back to the Visual Basic Editor and see that a sheet has been added to the workbook.
Notice that the worksheets are sorted alphabetically in the Project window even if they are not in alphabetical order in the workbook.


The Properties Window

The Properties window shows you the properties of the component that is selected in the Project Window.
If you click on "Sheet1" in the Project window you see the properties of this particular worksheet.

A worksheet has 13 properties that you can change in this Properties window.
Notice that there are two Name properties.
The first property is called (Name) and this refers to the programmatical name of the sheet.
The tenth property is called Name and this refers to the worksheet name that appears in Excel.
Switch back to Excel and change the name of the "Sheet1" tab to "Monday".
Switch back to the Visual Basic Editor to see the changes.
In the Properties window and you will see that the property "Name" has changed, bu the property "(Name)" has not changed.

In the Visual Basic Editor click on "Sheet2" in the Project window.
Use the Properties window to change the value of the property "Name" to "Tuesday" and press Enter.
Switch back to Excel to see that the worksheet name has been changed.


The Code Window

The Code window is where most of the VBA work is done.
This is the window for writing, testing and modifying your VBA code.
Double click on "Sheet1" in the Project Window. On the right is the Code window of "Sheet1".
Click anywhere in the Code window.

Start by typing the first line and then when you press Enter, the Visual Basic Editor (VBE) will add the final Line "End Sub".
Enter the rest of the code within the two lines.
Make sure that everything is typed exactly, including all the quotation marks, periods, parentheses, equal signs and spaces.

Sub EnteringData() 
    Range("A1").Value = 10 
    Range("A2").Value = 25 
    Range("A3").Formula = "=A1+A2" 
    Range("A1").Select 
End Sub

Click on any line of the macro, go to the menu bar at the top of the VBE screen and select (Run > Run Sub/Userform).
Switch to Excel and see what has happened to cells A1, A2 and A3.
The value of cell "A1" is 10, the value of cell "A2" is 25 and there is a formula in cell A3 that sums cells A1 and A2.
Clear the cells A1, A2 and A3 of "Sheet1".
On the Developer tab, select Macros, select "Sheet1.EnteringData" and press Run.

You have just run the macro from the ribbon in Excel.
Any code you write will appear in black by default.
Any reserved words are displayed in blue and any comments are displayed in green.
It is possible to change these formatting options but most people never do.


Developing Macros in Excel

In the previous example we added a macro to the code module that sits behind the Sheet1 worksheet.
Now we are going to add a macro to the workbook and put this macro into an independent code module.
Right mouse click on the VBAProject name and select (Insert > Module).
SS
A new folder has been added to your VBA Project called "Modules".
There is also a code module called "Module1" that has been created underneath.
SS
Enter "sub ReadingData()" without using a capital "S" as the beginning of "sub".
After entering the closing parenthesis press Enter.
You will see these two lines of code.

Sub ReadingData() 

End Sub

Notice that the VBE automatically adds the line "End Sub"
It is also worth mentioning that the Editor has an Automatic Capitalisation feature.


Enter the following code into the Sheet1 code module.

Sub ReadingData() 
   Sheets("Sheet1").Select 
   Range("A1").Select 
   Do Until Selection.Offset(0, 1).Value = "" 
      Selection.Offset(0, 2).Value = Selection.Offset(0, 0).Value & " " & Selection.Offset(0, 1) 
      Selection.Offset(1, 0).Select 
   Loop 
   Range("A1").Select 
End Sub

This code will go down column "A" and concatenate the text in column "A" with the text in column "B" seperated by a space and place this in column "C".
The macro will continue all the way down until there are no more first names in column "B".
Once the macro reaches the end, it will then select cell "A1".
Enter the following items into Sheet1.

To run this macro place the cursor inside this subroutine.
Select (Run > Run Sub/UserForm).
Switch back to Excel to see the results
SS
Run this again with different values in columns "A" and "B".


Testing Macros in Excel

It is possible to step through the code one line at a time.
Click anywhere within the macro and then press the F8 key at the top of your keyboard.
The Editor will highlight the first line of the subroutine in yellow.
SS
Pressing F8 again will execute the line of code that is highlighted in yellow.
Keep pressing F8 and at the same time keep an eye on Excel to see the corresponding changes.
While you are stepping through the code you can stop execution at any time by selecting (Run > Reset).


The Macro Recorder

One of the most useful features when learning about macros is the Macro Recorder
When switched on Excel will automatically keep track of all your actions and generate the corresponding VBA code for you.
To start recording your steps press the button in the bottom left corner.
SS


A small window appears titled "Record Macro". We will review its components in the downloadable tutorial. For now just click on "OK".
When you do so the small window disappears and in the "Developer" ribbon VBA macro recorder is replaced by VBA stop recording telling you that you are going in the right direction. The macro recorder is ON.
In the sheet below (Sheet1) select cells B1 to B5, go to "Sheet2", select cell B6, come back to "Sheet1" and select cells D2 to D5.
Stop recording
Delete all the values.
Select cell "A1" of "Sheet1".
To run this new recorded macro, display the View tab and select (Macros > View Macros)
In the window that appears Macro1 is selected.
Excel run macro
Go to the Visual Basic Editor and you will see the following macro in the code window when you double click on Module 1 in the Project Window:

Sub Macro1() 
' Macro1 Macro

    Range("B1:B5").Select 
    Sheets("Sheet2").Select 
    Range("B6").Select 
    Sheets("Sheet1").Select 
    Range("D2:D5").Select 
End Sub

This code can be edited directly or copied and pasted into your subroutines.


Using a Built-in Event

Macros can be triggered from built-in events.
The most commonly used events fire when:
* a workbook is opened
* a particular worksheet is selected
* a particular cell is selected
* the value in a particular cell changes
In the Project window click on "ThisWorkbook".
Copy and paste this code into the corresponding code window.

Sub Workbook_Open() 
   Range("A1" ).Value = 34 
   Range("A2" ).Value = 66 
   Range("A3" ).Formula = "=A1+A2" 
   Range("A1" ).Select 
   Call MsgBox("Data added") 
End Sub

SS
Delete all the values on Sheet1
Save and close the workbook
Reopen the workbook
Just after the workbook appears you will see a message box appearing saying "Data added"
SS
Press OK to close the dialog box
This confirms that our macro automatically ran when the workbook opened.
The cells "A1", "A2" and "A3" will also be populated.


Pressing a Shape on the Worksheet

Macros can be triggered using various different worksheet objects.
The most common worksheet objects are shapes and Buttons
Select the Insert tab and add a shape (for example, Rectangle) to the worksheet.
Right click on the object and select "Assign Macro".

Choose the macro you want to run and press OK.
You can now click on this shape to trigger the macro.
Macros can be assigned to pictures, shapes, icons, smartart and even text boxes.


Adding Shortcut Keys to your Macros

Macros can be triggered using Shortcut Keys
Display the "Macro" dialog box (View tab, Macros, View Macros)
Select the macro and press Options, to display the "Macro Options" dialog box

Place the cursor in the small textbox and press (Shift + B).
Press OK to accept the shortcut key
You can now use this shortcut key to trigger the macro.
If you have any Third Party add-ins installed your shortcut keys may already be assigned to something else.


More Information

Saving your macros in your Personal.xlsb
How to create an Excel add-in

© 2025 Better Solutions Limited. All Rights Reserved. © 2025 Better Solutions Limited TopPrevNext