Introduction to VBA: Step-By-Step Guide

VBA stands for Visual Basic for Applications. It is an object-orientated, event driven programming language.

The VBA language can sound intimidating, but this step-by-step guide will walk you through each step smoothly.

This article is an Excel VBA Tutorial appropriate for all levels!

Learning how to write vba code will help you sort out data, automate some of your workflow and create custom functions. All of this will come together to help you work more efficiently.

You can use VBA in Office applications such as Excel, Word, Access, Outlook and PowerPoint.

We have a whole Excel VBA Course ready for every level of Excel user. Even if you are brand new it will make writing code seamless!

What is VBA in Excel and why should you learn it?

VBA Excel is especially useful, as you can use it to automate workflows, develop user defined functions, and customize the Office Ribbon.

Let’s look at a few reasons why you should consider learning VBA in Excel.

Graphic detailing the reasons to learn VBA in Excel.

 

However, before you dive into learning VBA, you should have a fairly advanced knowledge of Excel’s existing capabilities. Since you don’t want to reinvent the wheel and ultimately you should use VBA to save time and not replicate features that are already built-in to Excel.

Also bear in mind, you cannot develop standalone applications with VBA, so you will always need a host application to run your VBA code.

VBA can even help you with data analysis. Read our guide on Data Analysis In Excel here for more.

Where to Code with VBA?

The Visual Basic Editor comes with Excel, so you don’t need to download or install any additional software. This is where you will write your code. To get started you need to add the Developer Tab to the Ribbon.

1)   So, go to the File Tab and select Options.

2)  You should see the Excel Options Window.

 

Screenshot showing the Excel Options Window.

 

3)   Select Customize Ribbon.

 

Screenshot showing the Customize Ribbon option highlighted.

 

4)   In the Main Tabs Section, select the Developer Tab check box.

 

Screenshot showing the Developer Tab checkbox, selected and highlighted

 

5)  Click Ok.

6)  You should see that the Developer Tab has been added to the Ribbon.

 

Screenshot showing the Developer Tab added to the Ribbon.

 

You can utilize the Developer Tab to:

  • Record a VBA macro
  • Access macros that you created previously and run them
  • Go to the Visual Basic Editor
  • Get Add-Ins that you can download from the Office store
  • Insert Form Controls such as buttons and textboxes in your worksheet
  • Insert ActiveX Controls
  • Use XML commands

 

Accessing the Visual Basic Editor

1)   To access it, go the Code Group on the Developer Tab and click on the VB Option.

 

Screenshot showing the Visual Basic option on the Code Group, on the Developer Tab highlighted.

 

2)   You should see the following.

 

Screenshot showing the Visual Basic Editor.

 

Tip: You can use the shortcut key ALT-F11 to access the VB Editor.

For more tips on streamlining your workflow, read our guide here on Removing Blank Rows or Cells in Excel.

 

The VBA Object Model in Excel

When coding in Microsoft Excel VBA, you will be working with objects. An object is an element that you work with and manipulate in some way through your code.

Examples of objects in Excel are:

  • The actual Excel application
  • A workbook
  • A worksheet
  • A range of cells
  • A cell
  • A hyperlink
  • A Pivot Table

The VBA object model in Excel refers to the hierarchy of these objects.  Objects contain other objects. The Workbooks object, for example contains the Worksheets and VBA Projects objects. An overview of the hierarchy is shown in the diagram below.

 

Graphic showing an overview of the VBA Object Model Hierarchy in Excel.

 

Objects have properties which are attributes or characteristics. For example, an object can have a colour – this would be a property or a characteristic of an object. Objects also have methods, which are actions associated with these objects.

For example, you can copy a value. This would be an example of a method. Some methods have parameters. These parameters can be optional or mandatory.

A useful analogy when conceptualizing objects, is to look at the example of a real-life house. The house is an object. It can have a property as in the colour it is painted. The house can also be cleaned or painted which are two methods/actions associated with houses.

For more on Excel, read our guide to the Transpose Function In Excel here.

 

What to Code?

Now that we have covered some useful theory, let’s look at writing some VBA code.

 

Changing the Text Weight in a Cell to Bold Using VBA – A simple example

In this example, we are working with the range object and the font object. We are setting the bold property of the font object to true.

1)    We have some text in cell A1 on Sheet1.

 

Screenshot showing the text in cell A1.

 

2)   To change the text weight of this text to bold using VBA, go the Code Group on the Developer Tab and click on the VB Option.

 

3)   Go to the Insert Tab and choose Module.

 

Screenshot showing the Module option highlighted.

 

4)   Enter the following code in the message box to create a sub procedure.

Sub MakingTextBold()

End Sub

 

Screenshot showing the Sub Procedure.

 

  • Note: You enter the VBA code to make the text in cell A1 bold, in the sub procedure. You must give the sub procedure a name – in this case it is MakingTextBold.

 

  • Note: It is also a good idea to add comments to your code. Comments help you to annotate your code and you can describe what is happening in each portion of your code by using comments.

VBA does not execute comments. The way to add a comment is to type an apostrophe and then the comment itself. The text colour of comments is green.

We are going to use comments to explain our code.

 

5)  Type the following code.

 

‘The range object contains the font object and we are setting the property of the font object to bold

Range(“A1”).Font.Bold = True

 

 

Screenshot showing the comment in green and the code to change the text to bold.

 

6)   To run the code, place the cursor in the sub procedure and go to the Run Tab and choose the Run Sub/UserForm option.

 

Screenshot showing the Run Sub/UserForm option in the Run Tab highlighted.

 

7)   The result is the following. The text weight in cell A1 has been changed to bold.

 

Screenshot showing the result of running the VBA code.

 

There you have it, you have just written your first VBA code.

Now you may be thinking that it doesn’t take that much time to change the font to bold in Excel, by going to the Font group on the Home Tab. So, let’s look at a slightly more complex example, building on what we have learned, to demonstrate how VBA can be used to save time.

If you want to learn about how you can Link Data to another worksheet instead of adding multiple sheets to one workbook, read our guide on Linking Data in Excel here.

 

Adding multiple sheets to a workbook at once Using VBA– A more complex example

Let’s say you needed to add multiple sheets to a workbook. In our example we need to add thirty sheets to the workbook. We can do this with a single line of code using VBA.

1)   Go the Code Group on the Developer Tab and click on the VB Option.

2)   Go to the Insert Tab and choose Module.

3)   Create the sub procedure.

 

Sub AddingMultipleSheetsToAWorkbook()

 

End Sub

 

Screenshot showing the Sub Procedure.

 

4)   Type the following lines of code.

 

‘We are using the Add method of our Sheets object to add the new sheets as well as specifying the number of sheets to be added
‘through the Count Parameter
Sheets.Add Count:=30

 

Screenshot showing the code needed to add multiple sheets with a parameter specifying the number of sheets.

 

5)   To run the code, place your cursor in the sub procedure and press F5 on your keyboard.

 

You should see 30 new sheets added to your workbook.

 

  • You can see and access the two macros we just created by going to the Developer Tab and in the Code Group click on the Macros option.

 

Screenshot showing the Macros option on the Code Group in the Developer Tab highlighted.

 

The Macro Dialog Box will appear. From here you can select one of the macros and either edit or run the macro again if needed.

 

Screenshot showing the Macro Dialog Box.

 

  • You will need to save your workbook as a macro-enabled workbook. To do this go to File and select Save As.

Browse to your location of choice. Using the Save As Dialog Box, give your workbook a name and in the Save as type: choose Excel Macro-Enabled Workbook.

 

Screenshot showing the Save As Dialog Box with the Save as type: set to Excel Macro-Enabled Workbook.

 

Click Save.

Want to learn about how to use the Macro recorder to create macros and more about working with VBA Macros? Read our Step-By-Step guide to Macros In Excel.

 

Excel VBA Use Cases

There are many practical applications of Microsoft Excel VBA.

  • As we have seen you can use VBA to insert multiple worksheets at once. However, you could also use VBA to first prompt the user of your workbook for input and ask them how many sheets they would like to insert. Your code would need to be adjusted slightly to accommodate the user input.

 

Screenshot showing a VBA Input Box.

 

  • You can create a UserForm similar to the kinds that one sees on web pages with text boxes and radio buttons for data entry in VBA.

 

Screenshot showing the UserForm.

 

Learning Objectives

You now know how to:

  • Add the Developer Tab to the Ribbon
  • Access the VB Editor
  • Make text bold using VBA
  • Add multiple worksheets to a workbook using VBA
  • Annotate your VBA code with comments
  • Save your workbook as a macro-enabled workbook

Additionally you have an understanding of:

  • The Excel VBA Object Model
  • What an object is
  • What a property of an object is and a method

Conclusion

VBA allows you to program repetitive tasks or create custom functions and has a myriad of other uses.

If you’re an advanced Excel user, you should consider learning VBA to automate repetitive processes and enhance your spreadsheets and dashboards.

This is one of the most powerful microsoft office applications, so take it slow when learning! Feel free to refer back to this VBA tutorial whenever you need.

 

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.