Learn VBA Macro Coding Basics In Excel [2023 Guide]

No Coding Experience? No Problem!

First I want to congratulate you because if you are taking the time to read this guide I am assuming two things: 

1. You have realized that Excel and the rest of Microsoft’s Office Suite have an enormous amount of power that is just waiting for you to unlock
2. You have noticed that most of your peers don’t understand these capabilities and you want to differentiate yourself from them

Understanding those two points really gave me the motivation to dig deep into the Excel universe (and eventually into Word and PowerPoint) and add every technique I could find to my virtual tool belt.  

In this introductory guide, I’m going to walk you through 5 brief lessons to give you a basic understanding of what VBA is and how you can get started automating tasks in Microsoft Excel.

I’m going to assume that you are in the same situation as I was when I first started learning about VBA. I had absolutely zero computer coding experience and was just looking for a way to save time on boring, repetitive tasks I was having to do in Excel every month.

Here is the lesson plan we will walk through together:

Before we get our hands messy and try to build a foundation that will allow you to do anything you could ever imagine within these programs, I think it is really important to take some time and define what exactly VBA and those “Macro things” are that you’ve probably heard or seen floating around.

LESSON ONE: What Is VBA? What Are Macros?

The acronym VBA stands for Visual Basic for Applications.  This is essentially an offshoot of the Visual Basic computer language that Microsoft created way back in the 90s that allows Microsoft programs to communicate with each other based on events or actions that take place within those programs.  

This language is not only used in Office programs like Excel and PowerPoint, but also in programs like NotePad and Paint.  Since Microsoft created this language to go along with its own applications, the code is very user intuitive.  

For example, if you read a code line in Excel that states Range(“A1:B4”).ClearContents you can make an educated guess that the line of code tells Excel to clear the contents of cells A1 through B4.  This is HUGE because it allows users with very limited or no computer programming knowledge to easily pick up on how the VBA language works.

What Is A Macro?

Macros are what most people who write VBA code use to automate their tasks.  

Definition: A macro (also can be referred to as a Procedure or Subroutine) is a grouping of code that performs a series of tasks or commands within a targeted computer program (aka Application).  

Macros can contain code that performs calculations, copy & pastes, changes formatting, and a bunch of other nifty things; all within milliseconds!  Most Office users use macros to automate routine tasks that take them a long period of time to perform manually (by keyboard & mouse).

What The Function Is “Dim”?

I threw this section into this guide for a friend of mine who was frustrated with the lack of introductory information on VBA.  He said, “Chris, I’ve been searching everywhere and I wish there was a simple introduction to VBA that could get me started.  I mean, I can’t even figure out what the heck a Dim is!”  

So below I will list out a couple of terms you might have come across if you have ever recorded a macro or seen VBA code.  Leave a comment at the very bottom of this guide if there are any other terms you have come across and would like defined.

Dim – This stands for Dimension and is a statement used to declare a variable name and type that you want to create.

Sub – This is short for Subroutine and is your opening statement for your code. Every time Sub is typed a new macro or procedure is created.  The words “End Sub” must be placed as the last line of code for your macro in order to tell VBA that your procedure is finished

Module – This is an area where you can write function and macro codes.  This is also where any macros that you record are stored.

Class Module – This is an area for really advanced VBA users.  In this area, you can write your own custom classes, methods, and collections into the VBA library.  If you are a beginner I would stay away from these for now.  Most VBA writers won’t ever use this type of functionality during their career but it is a very powerful option to have if needed.

Function – VBA gives you the ability to create your own custom functions.  These can either be used by your macros to obtain a certain output or they can be used in the Excel Formula Bar to perform calculations on your cell’s values.

Userforms – These are pop-up boxes that allow users to enter inputs or choose options.  Microsoft uses these all the time in their applications. Some examples of these are:

• Error Message Boxes
• Dialog Boxes
• The Macro Recorder

The cool thing is that VBA gives you the ability to create your own custom Userforms from scratch!  You get to design the look (user interface) and code for all the buttons, inputs, and actions as your user interacts with the form.

LESSON TWO: The Visual Basic Editor

The Visual Basic Editor is your workspace for creating your VBA code.  

The editor can be accessed through your Developer Tab or by using the shortcut Alt + F11.

The editor will display in a completely separate window from your Office Application and each one of the programs in the Office Suite has its own VBA Editor (so you can have the Excel and PowerPoint VBA Editors open at the same time).

I will not be going into too much detail about all of the Visual Basic Editor’s capabilities in this article but I do want to show you enough so that you can understand its setup and how it functions.  

Below I have a screenshot of some of the main windows that can be shown within the  Visual Basic Editor. Some of these may not be showing on your editor by default and I will explain how to get them showing in the descriptions below.  I will be describing Excel’s Visual Basic Editor but much of the information can easily be translated over to PowerPoint, Access, or any of the other Office program’s Visual Basic Editors.

Project Window

This window will show you all the files that you have opened.  The Project Window uses a tree view where you can drill down into each file that you have open and see the areas in which you can insert VBA code.

Notice that in my screenshot above that there are two files that are open in my Excel application: Book1 (a workbook) and VBHTMLMaker (an add-in).  In Book1 you can see 3 subfolders:

• Microsoft Objects -This folder houses a code area for your workbook (ThisWorkbook) and each of your workbook’s spreadsheet tabs (Sheet1).
• Forms – This folder stores any userforms that you create.  I touched a little bit on this in Lesson One so I won’t repeat myself here.  If you do not see this folder you can add it by right-clicking anywhere within the projects folder tree and going to Insert -> Form.
• Modules – A Module folder stores your macro and function code.  If you do not see this folder then that means that the project most likely does not have any macro code in it (note: there can still be code stored in the forms folder or in the Objects folder).  You can add this folder by right-clicking anywhere within the projects folder tree and going to Insert -> Module.

Properties Window

The Properties Window will allow you to modify certain aspects of whatever object, form, or module you have highlighted.  Typically the only thing I change using this window is the Name field.  This is a good idea because you can give your modules or forms a more meaningful name than the default names that the Visual Basic Editor provides.  Custom names in the Name field can only be one word in length. 

Code Writing Area

This is where the magic happens!  In this area, you can actually write and edit your VBA code.  Each macro must begin with a Sub statement (which is opened with Sub [insert your macro name] ( ) and closed with End Sub).  Notice also that the VBA Editor color-codes some keywords in a few different colors.  This helps make your code more organized.  I have two major tips that I like to share with people when they are first learning to write VBA code:

1. Use Indentations – Always try to use indentations (via the Tab key) within your code.  There are various methodologies for tabulating code but as long as you are consistent and it makes sense it will help you enormously when you are trying to add to or debug your code.  It also helps when someone else is trying to help you with your code.
2. Write in Lowercase – If you haven’t noticed already, every word in the VBA language has at least one capitalized letter.  How is this an advantage?  Well, the Visual Basic Editor is not case sensitive and it likes to correct you when it can.  This means that if you type in “workbook”, the editor will automatically change it to “Workbook”.  My rule of thumb is to type everything in lowercase and if the VB editor doesn’t capitalize at least one letter, I know that I either misspelled that word or that word is not defined.  Having the Visual Basic Editor correct every word I type has really made my code less buggy and prevented a lot of frustration over the years.

Immediate Window

I like to refer to this area as my piece of scratch paper.  The Immediate window lets you do all sorts of tests while writing and running your code.  You can use the code Debug.Print to tell VBA to send the information that follows to the Immediate window.  This could be the output value of a function, the value of a cell, or what a current application property is set to.  When I first began writing VBA code I had no idea the Immediate window ever existed (it’s usually hidden by default but you can use the shortcut Ctrl + g to view it), but once learned everything it could do I never stopped incorporating its functionality into my code writing and testing processes.

Watch Window

The Watch Window is kind of like an X-ray machine.  It will show you all the data that is stored inside a variable!  Some variables (like the ones you create in your code) will not have very much data stored in them.  However, if you were to “watch” a variable that was assigned to a cell, you would see a whole bunch of data (font color, value, height, fill color, etc…).  This is mostly useful when you are trying to debug your code and want to understand what value your variable has at any given point in your code.

In order to watch a variable, you need to highlight your variables text and click the Add Watch button (this is the eyeglasses icon located on the debugging toolbar).  You should then see your variable appear in the Watch Window.  Once you start running through your code and load a value to your variable, you should see an option (plus sign) to drill down or expand out the contents that are now stored in the variable.  

Are You Confused Yet?

I know this is a lot to take in but you really should use this as a reference sheet that you can refer back to as you learn.  You will not use all the features of the Visual Basic Editor when you are first learning but it is handy to know what they are in case someone who is trying to help you refers to a feature in the VB Editor.  For example, I first learned about the Immediate Window when I kept seeing the Debug function used in code that community members on Excel forums would include in their VBA code solutions.  I had no clue what Debug did and since my Immediate Window was hidden, it took a lot of research for me to understand how and why the forum members were using that functionality.

I will continue to update this page as I do have more detailed blog posts that I am planning to write about the Visual Basic Editor and I will make sure to link to them when the articles are posted!

LESSON THREE: The Macro Recorder

What Is The Macro Recorder?

The Record Macro feature in Excel and Word is hands down the best programming tool you could ask for.  You can visualize it as a tape recorder with 2 buttons: record and stop.  When the Record Macro functionality is set to record, the program writes the code and stores every action that you do within either Excel or Word in a VBA module.  You can then go back and look at the code that was written and do various things with it. The macro recorder may seem like a VBA beginner’s tool but even Excel gurus will use it all the time because they understand the efficiency the recorder can add.

How To Turn Your Onscreen Actions Into Code

The Record Macro feature can be accessed through the Developer tab and resides in the Code section.

You can also access it by clicking on the Record Macro icon in the bottom left-hand corner of your window (I did not know this for the longest time!)

 After clicking the icon you should see the Record Macro dialog box

In this dialog box you have a couple of options:

Macro Name [Required]: The recorder will always default a name in the dialog box but you are free to change it to a more meaningful name which I always recommend.  If you forget to change the name or want to change it later, don’t worry you can always change your Macro name after you’re done recording.  The naming convention allowed has to follow the below criteria:

• The name must start with a letter
• No spaces are allowed (use an underscore “_” if you want to put a space between words)

Shortcut Key [Optional]:  This allows you to trigger or run the code you recorded via a keyboard shortcut.  As a word of caution, this does overwrite the default Excel shortcuts so as a best practice I always use the capital letter instead of the lowercase one.  For example, instead of making my macro shortcut Ctrl + c (which of course is the universal shortcut for copying), I would use Ctrl + Shift + C or Ctrl + C.

Store Macro In [Required]: This lets you determine the location of where the recorder will store the code.  All currently open workbooks will show as options in the dropdown along with your Personal Macro file (if you have one set up)

Description [Optional]:  I don’t think I have ever used this field as I always go back to my recorded code and immediately add comments to describe what the code does, but if you want you can type in a brief description of what you are about to record and the recorder with code that for you too.

Once you have filled out the dialog box to your liking and can click OK.  Your every move is now being recorded #BigBrother!  After you are done performing whatever action(s) you want to code just it the Stop Recording button (located in the Developer tab or down in the left-hand corner of your window)

Not Just For Excel

The macro recorder can also be found and used in Microsoft Word. 

This feature used to be available in PowerPoint a few software iterations ago however it is believed that the Microsoft Office team, unfortunately, axed the feature because it was too buggy.  This was a huge mistake as even a buggy macro recorder would have been useful to users trying to teach themselves how to write VBA for PowerPoint (especially since there is not a whole lot of information on PowerPoint VBA out there on the web).  Fortunately for you, I have taken it upon myself to cover VBA for PowerPoint on this site so you won’t have to pull your hair out trying to figure it out!

Weakness Of The Macro Recorder

Like most things, the Recorder does have some weaknesses.  I will attempt to list some of the ones I run into on a regular basis.

• Some Actions Aren’t Recordable –  There are some instances where I have tried to record a command and it would not get coded by the recorder.  This can get frustrating sometimes!
• Some Programs Don’t Have A Recorder – Unfortunately, only Excel and Word currently have the Macro Recorder capability.  For the rest of the Office programs, you will have to teach yourself how to write the VBA code (or just read my blog) as each program has its own unique quirks and functionality.
• Records Scrolls & Clicks – This is probably more of an annoyance than a downfall but when I say the Macro Recorder records everything, I mean EVERYTHING!  That means every scroll your make and every cell you click on.  This usually causes the recorded macro code to be extremely lengthy.  I always recommend going in and deleting those extraneous lines of code as it will make your code slower, harder to read, and could potentially cause errors.

Can you think of any other downfalls to the Macro Recorder?  Leave a comment below and I will continue to update this list!

LESSON FOUR: Writing Code On Your Own

Where Do I Begin…

I’m sure there are many ways to teach people how to code but I am going to use my own methods that I have developed while explaining VBA to my co-workers over the years.  There is no way I can explain everything about coding here in this post but I will try to cover the basics and hopefully provide you with a good foundation that you can build on going forward.

How VBA is Structured

When explaining VBA code to someone who has no experience with computer languages, I like to use the analogy of a computer’s folder hierarchy.  A typical folder structure on a PC might look like this:

Hopefully everyone is comfortable seeing a structure like above and I am here to tell you that VBA coding is kind of setup like your computer’s folder structure.  

Walking Through The Hierarchy

I am a visual learner myself and I always find it easiest to explain how to write a line of code with an example.  So let’s write a VBA line that tells Excel to clear the contents of range A1:C50.

C:\ Drive

Instead of “drives” on a computer, VBA starts out with a reference to the computer program or in its terms “Application” that you are wanting to target. This can be Word, Excel, NotePad, Internet Explorer, or whatever program you want to access that also speaks the Visual Basic language.  

VBA assumes that whatever program you are storing your code in is the application you writing for.  Because of this, most people do not add the program name to their line of code but for purpose of this example, we will.  

With this in mind, the first part of our line of code will be as follows since we are wanting to work with the Excel Application:

Sub ClearCellContents()

    Excel.Application.

End Sub

‘User’ Folder

After we specify the Application we want to use, we then need to tell Excel which Workbook we want to modify.  You can do this in a couple of different ways:

ThisWorkbook

This references the workbook that your VBA code is written in.  Typically you use this when you want the macro to only affect one workbook.  The benefit to this is that if you are viewing another workbook and you accidentally run your code it will not affect that workbook.  REMEMBER YOU CAN NOT UNDO A MACRO’S COMMANDS ONCE IT HAS RUN!

ActiveWorkbook

This refers to the workbook that you are currently viewing on your screen.  You will mainly use this type of reference when you create code that is aimed at affecting any workbook (for example if you write a macro to format text a specific way)

Workbook("Book1.xlsx")

Writing your workbook level reference this way allows you to specify the name of the workbook you want to take action with.  I rarely refer to workbooks in this way because if I or someone else changes the name of the workbook, the macro will not be able to find it because it is referencing the old name.

Workbook(1)

I don’t believe I have ever used this form of referencing a workbook but I will throw it out there just as an FYI.  When you use a number to reference a workbook you are referencing the order in which the workbooks were opened by Excel.  This can get tricky because if you have add-in files active, those are going to be the first workbooks opened when your Excel Application starts up.  You can determine which position your workbook is in by referencing its order in the VBA Project Window (location is shown in Lesson Three).

For this example, I am going to decide that I only want the scope of the macro to be within our workbook, so we are going to use a ThisWorkbook reference in our code:

Sub ClearCellContents()

    Excel.Application.ThisWorkbook.
    
End Sub

‘My Docs’ Folder

After we specify the workbook we want our code to target we can then move on to the next level and specify the tab or worksheet that we want to get our code to point to.  Like the Workbook level, the Worksheet level has a few different referencing options:

ActiveSheet

This is very similar in functionality to ActiveWorkbook.  ActiveSheet points you to the tab that you are currently viewing.  This is great to use when you want to give your code the flexibility to run on a worksheet of your choosing.

Worksheet("Sheet1")

In this form, you can spell out the name of the worksheet you want to target.  Because your sheet name is considered text and is not part of the VBA language, you will need to make sure your tab name is surrounded by quotes.  This lets the Worksheet know that you are spelling out a name of a tab.  If your name does not have quotes then VBA will think that Sheet1 is a variable that you created instead of text.

Worksheet(2)

You can also target the order of your worksheets.  The above code is bringing you to the second worksheet from the left inside your workbook.

Sub ClearCellContents()

    Excel.Application.ThisWorkbook.Worksheets("Sheet1").
    
End Sub

‘Finances’ Folder

Once we’ve specified the worksheet we want to modify, we then need to specify which cells we want to do something to (in this case, clear their contents).  There are a bunch of nifty ways to tell VBA which range we want but here I am going to cover the two main ways for referencing a single cell and a range of cells.

Reference A Single Cell

Range(“A4”) – You can reference a cell by inserting its string name (range name surrounded with quotes). 

Cells( 4, 1) – You can also reference a cell by using Cells( ) and inputting the row number and then the column number.  Note that Cells( ) will not accept a letter as an input for the column number.

Reference A Range of Cells

Range(“A1:E5”) – Similar to referencing a single cell by name, you can also reference multiple cells by inserting the top-left cell of your range, a colon, and then the bottom right cell of the range.

Range(Cells(1,1),Cells(5,5)) – By embedding two Cells( ) references inside a Range( ) function, you can refer to multiples cells.  To do this, use a numerical reference (row number, then column number) for the top-left cell in your range and then do the same for the bottom-right cell of the range.

Sub ClearCellContents()

    Excel.Application.ThisWorkbook.Worksheets("Sheet1").Range("A1:C50").
    
End Sub

‘Budget’ Excel File

Congratulations! We have made it to the lowest level of what we wanted to modify or if you want to keep with the analogy we finally clicked on the folder that is storing our file.  Now in this folder, there are hundreds of files we can select so we need to specify which unique file we want to open.  

There are lots of different properties you can change and actions you can take at the Range level.  You can change a color, you can perform a calculation…..you can do pretty much anything you can do manually on a spreadsheet.  A benefit to Microsoft being the creator of both Office and Visual Basic is that most of the computer language is used in the program functionality.  So in our example, if we wanted to delete the values in our selected cells while in Excel, we would simply go to the Home tab, click on the Clear drop-down in the Editing section, and click Clear Contents.  In VBA language to simulate doing the same thing all we have to add to our line of code “ClearContents“.  Pretty easy, right?!

Sub ClearCellContents()

    Excel.Application.ThisWorkbook.Worksheets("Sheet1").Range("A1:C50").ClearContents
    
End Sub

What’s Up With All the Periods?

Now that we have written a fully actionable line of code, you might be asking yourself what is the significance of putting a period in between each level in VBA? I like to compare the use of periods in VBA to the use of backward slashes in a folder’s address.  What these two symbols do in their respective areas is connect the previous level to the next level.  Another way of picturing this is that they are the stairs from one floor to another in a home.  You can’t go from the basement to the first floor without using the stairs to get there.

The VBA Defaults

The VBA Language creators at Microsoft realized that we as computer coders want to type as little as possible.  They found it in their hearts to program in some defaults or assumptions of which levels we may want to be targeting.  Below is a list of some of the major defaults that VBA will assume in Excel

• Excel.Application
• ActiveWorkbook
• ActiveSheet

So if we leave out any of these levels in our code, the VBA language is going to assume the respective level reference above.  Let’s look at a few examples of how we can shorten our code and understand what that means.

• Starting at the Excel Application level
• ThisWorkbook.Worksheets(“Sheet2”).Range(“B3”).Value = 10
• Starting at the ActiveWorkbook level
• Worksheets(“Sheet2”).Range(“B3”).ColumnWidth = 4.3
• Starting at the ActiveSheet level
• Range(“B3”).Interior.Color = RGB(75, 172, 198)

The Visual Basic Editor Wants To Help You!

Last but not least I want to leave you with a tip and you may have noticed this while typing in the Visual Basic Editor.  Every time you type a period after one of your ‘levels’ you may have noticed that a little window pops up with a bunch of words.

What this floating box ends up displaying is every possible word that can come after your previous code phrase. This is a feature that I often ignored when first learning how to write VBA code because I really didn’t understand what it was.  Eventually, I took the time to figure out what it was showing me and the below little box has helped prevent errors in my code ever since!

LESSON FIVE: Where Do You Go From Here?

Building On Your Foundation

Hopefully after reading the previous lessons I’ve provided you with enough information to give you a good start in taming the beast that is VBA.  I threw this last lesson into the mix with the hope of giving you some solid resources that you can use as you continue to learn how to write VBA code.  

Challenge Yourself!

The number one reason I have the knowledge I do about Microsoft Office is that I kept challenging myself on a regular basis.  I have taught so many people who had taken a few of my classes and got absolutely nowhere afterward because they weren’t putting into practice what they had learned.  

The BEST way to learn this stuff is to keep using it on a regular basis.  I can guarantee that if you use Excel that there are literally hundreds of automating macros that you could create to make your work faster and more accurate.  

I have a co-worker that I have written a bunch of VBA procedures for and jokingly says that pretty soon all he is going to have to do in Excel is press a bunch of buttons that run macros and his work for the day will be done in 15 minutes.  The real funny thing is that through VBA I have saved him hours of manual work per day that he spent copying & pasting, reformatting, and double-checking his numbers.  

This is the power of Visual Basic for Applications and why knowing how to use it can almost guarantee you success in the future!

Resources On The Web

The following resources are sites that I used on a daily basis for education, inspiration, and troubleshooting while teaching myself Visual Basic for Applications.  Hopefully, this list will save you the time and effort of digging through the web to find your answers.

Google – Google is my go-to search engine and I find that its results provide the best sites to answer my questions.  When I search for a question, I always begin my search by typing in “VBA Excel” or “VBA PowerPoint”.  I have found that by starting your Google search with the words VBA and the program you are trying to target,  you can usually find the answer you are looking for within the first couple of results.

Blogs – I tend to prefer written content while trying to learn something because it is typically the most efficient way for me to understand how to accomplish the technique I am trying to learn. I can quickly scan through an article and within seconds determine if the article is likely to have what I need or if I need a try another. There are many great blogs out there like Chandoo, How To Excel, ExcelJet, Excel Off The Grid, & Excel Campus (just to name a few). I use an RSS Feed Manager called Feedly to keep track of new posts from my favorites and this allows me to keep learning new techniques on a regular basis.

Forums – There are many fantastic forums out there with lots of amazing folks who are willing to share their knowledge for free! My personal favorite is a forum called MrExcel has an awesome forum that is used all over the world.  If you have a question and can’t find the answer, post on this forum and you will get multiple people trying to answer your question (usually within minutes)

YouTube – The video tutorials being created over the past years has really become valuable. With the introduction of sections in YouTube videos, it is now easier to jump to a certain section of the video to get your answer without having to sit through the whole thing. There are also videos that show you how to build incredible interfaces and models within Excel. Some of my favorite channels include: Other Levels, MrExcel, Leila Gharani, Excel Is Fun, & The Office Lab.

Take A Class/Course!

I can’t stress enough how much taking a 2-day long class exponentially grew my knowledge of VBA and its power.  While it was a lot of information to take in, it opened my eyes as to what VBA is truly capable of.  

I would definitely recommend going to a live class whether it’s in person or over the web.  The benefits of attending a live class are that you can ask questions that pop into your head right then and there and usually the teacher is willing to tailor the lecture to hit some areas that pertain to your specific line of work.  

While I do not currently offer any classes, this form of education is definitely on my long-term goals list as I believe this can be a very important resource in helping people understand coding in VBA.

What Do You Recommend?

I’m excited to hear from you and discover what has helped you become a VBA Guru!  Drop me a note in the comments section letting me know your most valuable VBA resources and I will try to put together a community list of everyone’s suggestions in this post.  I will keep checking back to make sure the list contains the most current and best resources available.

I Hope This Helped!

Hopefully, I was able to provide you with a solid foundation where you understand the purpose of VBA and can start exploring the many strengths it has to offer. I want to make this the best introduction to VBA I can as I think this is a very important functionality in Excel that is far too overlooked. If you have any questions about a certain section or suggestions on how to improve this guide, please let me know in the comments section below.

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

Quickly Learn Excel’s Power Query Tool [Guide 2022]

Quickly Learn Power Query Now! The goal of this guide/tutorial is to provide you with a great starting point in...

How To Use A VBA Macro You Found On The Internet

You Finally Found It! Yes, I’ve been there! You’ve spent days scouring the internet to find a solution to a...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.