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.xls" has 3 sheets and ThisWorkbook.
 |
Using the (Alt + F11) key go back to Excel.
Add a sheet. 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.
In a new workbook if you click on "Sheet1" in the Project Window you see the properties of this particular worksheet.
 |
A worksheet has 12 properties that you can change in this Properties window.
Notice that there are 2 "Name" properties. On the first line there is the programmatical name of the sheet (Sheet1).
The second "Name" property (9th line) is the name (or caption) that appears on the tab of the sheet in Excel.
Switch back to Excel and change the name on one of the tabs.
Switch back to the VBA Editor, Properties Window and you will see that the property "Name" (9th line) has also changed.
In the VBE select "Sheet2" in the Project window.
Use the Properties window to change the value in the property "Name" to something else. Click "Enter"
Go to Excel and notice that you now have a sheet named "Spreadsheet" .
The Code Window
The Code Window is where most of the VBA work is done; writing VBA code, testing your VBA code/macros and modifying code written by other people.
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 VBE will add the final Line "End Sub".
Enter the rest of the code within the two lines.
Make sure that everything is there 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 click "Run" then click "Run Sub/Userform".
Switch to Excel and see what has happened to cells A1, A2 and A3
The value of cell "A1" is 34, the value of cell "A2" is 66 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 and select "EnteringData" and press Run
 |
You have run the macro from the menu bar of Excel. There are a few different ways to run a macro.
Any code you write will appear in black. Comments appear in green. Reeserved words in blue and when you make a mistake the font color turns to red.
There are some formatting options you can change.
Developing Macros in Excel
All your macros will be written in a code module window.
Enter "sub lowercase()" without using a capital "S" as the beginning of "sub".
After entering the closing parenthesis click on "Enter". You get these two lines of code:
Sub lowercase()
End Sub
VBE adds the line "End Sub" and capitalizes the "S" of "Sub" .
The VBE capitalizes letters appropriately when the word is spelled correctly.
This is one interesting feature that you should always use when writing macros.
In this way, whenever VBE unexpected fails to capitalize a letter, you will know that something is wrong.
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.Value = Selection.Offset(0, 0).Value & " " & Selection.Offset(0, 1)
Selection.Offset(1, 0).Select
Loop
Range("A1").Select
End Sub
Enter the following items into Sheet1.
SS
This code will go down column "A" and concatenate the first name of column "A" and the last name from of column "B" with a space in the middle.
It will perform this task all the way down until there are no more first names in column "A" . It will then place the cursor in cell "A1".
To test this macro (VBA procedure) follow the steps below:
Go to Excel (ALT/F11) and enter first names in cell A1 to A5.
Enter surnames in cells B1 to B5.
SS
Come back to the VBE (ALT/F11) and click within the macro in the code window.
From the menu bar select "Run/Run Sub/Userform".
Go back to Excel and see the result.
SS
You can erase everything in column C Excel and retry with more names and surnames.
Try it again removing the first name in cell A3. Notice that the macro stops on line 2.
Testing Macros in Excel
Testing the VBA procedure step by step
NOTE: While you are running the macro step by step you can stop the execution at any time by clicking on the stop button in the toolbar.
VBA for Excel reset button
Testing is the most time-consuming part of any VBA project.
During the testing phase, you will correct bugs, typos and the logical errors.
More importantly you will improve your original project, fine tune it, discover better ways to do things and add code.
Go to Excel and make sure that cells A1, A2 and A3 of Sheet1 are empty.
In VBE go to the Code window of Sheet1 and copy/paste the following macro:
Sub EnteringData()
Range("A1").Value = 10
Range("A2").Value = 25
Range("A3").Formula = "=A1+A2"
Range("A1").Select
End Sub
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. VBE highlights the first line of code in yellow.
SS
Right-click on the small yellow arrow and see a menu appear
SS
Press on "F8" a second time.
No line has been executed yet and if you go to Excel you will see that cells A1 to A3 are still empty.
The next time you press "F8" , VBE will execute the yellow-highlighted line.
Press "F8" a third time.
The yellow-highlighted line is now "Range("A2").Value = 66".
VBE has executed the previous line "Range("A1").Value = 34" has been executed so if you go to Excel (ALT/F11) you will see 32 in cell A1.
Come back to VBE (ALT/F11) and press "F8" again. Go to Excel and see what happened in cell A2.
Come back to VBE (ALT/F11) and press "F8" again. Go to Excel and see that there is a formula in cell A3.
Come back to the VBE (ALT/F11) and press "F8" again, cell A1 is now selected in Excel.
Press "F8" again. Nothing happens in Excel but "End Sub" is highlighted in yellow
Press "F8" again. Nothing happens in Excel no more lines in VBE are highlighted in yellow.
In the code change the addresses A1, A2 and A3 respectively to B1, B2 and B3. Test the macro again. Do it as many times as you want.
The Macro Recorder
There are no risks to your computer by using this feature
One of the tools that makes the programming environment in Excel unique is the Excel Macro Recorder.
When you start the macro recorder anything you do in Excel is recorded as a new macro.
That makes the macro recorder the best VBA teacher and also a great assistant who will write a lot of the words and sentences that you need without a single typo.
It will also be there when you do not remember something that you do not use often.
Open Excel and a new workbook.
Go to the "Developer" ribbon to click on VBA macro recorder
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.
In the "Developer" ribbon click on VBA stop recording
Select cell "A1" of "Sheet1".
In the "Developer" ribbon click on the Excel macros button
You can also run it from the View tab
In the window that appears Macro1 is selected.
Excel run macro
See how fast the macro runs. You do not even see Excel go to Sheet2 (but it does). At the end of the execution cells D2 to D5 are selected.
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
As you can see the macro recorder recorded your instructions in a language that Excel understands (VBA).
You can now use VBA's written code to have Excel perform this task.
Using a Built-in Event
When does the VBA procedure (macro) start?
When an EVENT happens. The event is what triggers the VBA Excel procedure.
In the Visual Basic Editor you have gone to the menu bar and clicked on "Run/Run Sub/Userform" and the macro was executed.
You have also clicked on the F8 key at the top of your keyboard and the macro got executed line by line.
The event can also be: opening the workbook, selecting a sheet, the value of a cell changing due to a manual input or due to the recalculation of a formula, clicking on a selected keystroke or going to the right menu item in Excel.
To complete the following exercises, copy paste the code below from your browser to the code window of "Sheet1" of the new Excel workbook as you have learned in previous lessons.
Sub Workbook_Open()
Range("A1" ).Value = 34
Range("A2" ).Value = 66
Range("A3" ).Formula = "=A1+A2"
Range("A1" ).Select
End Sub
Select "Macros" from the "Developer" ribbon. You will see the "Macro" dialog window below.
"Sheet1.proFirst" being selected in the list box and its name appearing in the text box above the list box just click "Run". The macro is automatically executed
Erase the contents of cells A1, A2 and A3
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