Basics of VBA Coding in Excel [Guide 2022] (2023)

No programming experience? No problem!

First of all I want to congratulate you because if you are reading this guide I am assuming two things:

1. You've discovered that Excel and the rest of the Microsoft Office suite have a lot of power waiting for you to unlock them.

2. You've found that most of your peers don't understand these features and want to differentiate themselves from them.

Understanding these two points really motivated me to delve into the world of Excel (and eventually Word and PowerPoint) and add whatever techniques I could find to my virtual tool belt.

In this introductory guide, I'll walk you through 5 short lessons to give you a basic understanding of what VBA is and how to get started automating tasks in Microsoft Excel.

I assume you are in the same situation as me when I first started learning VBA. I had absolutely no computer programming experience and was just looking for a way to save time on boring, repetitive tasks I had to do in Excel each month.

Here is the lesson plan that we will go through together:

LESSON ONE: What is VBA? What are macros?

acronymVBA means Visual Basic for Applications.🇧🇷 This is essentially a branch of the Visual Basic computer language that Microsoft developed in the 1990s that allows Microsoft programs to communicate based on events or actions that occur in those programs.

This language is used not only in Office programs such as Excel and PowerPoint, but also in programs such as NotePad and Paint. Because Microsoft developed this language to accompany its own applications, the code is very user-friendly.

For example, if you read a line of code in Excel that saysRango("A1:B4").ClearContentsYou can imagine the line of code telling Excel to clear the contents of cells A1 through B4. This is HUGE as it allows users with very limited or no knowledge of computer programming to easily learn 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 (can also be calledprocedureÖSubroutine) is a grouping of code that performs a set of tasks or commands within a specific computer program (also known as an application).

Macros can contain code that performs calculations, copy and paste, change formatting, and lots of other cool stuff; all in milliseconds! Most Office users use macros to automate routine tasks that take a long time to perform manually (with a keyboard and mouse).

What function is "Dim"?

I included this section in this guide for a friend of mine who was frustrated by the lack of introductory information about VBA. He said, "Chris, I've looked everywhere and I wish there was a simple introduction to VBA that could help me get started. I mean I can't even figure it out.What the heck is a dime?!"

Below I list some terms you may have come across if you've ever recorded a macro or viewed VBA code. Please leave a comment at the end of this guide if you came across other terms that you would like to define.

Dark- This stands for dimension and is a declaration used to declare the name and type of a variable that you want to create.

Sub- Is the abbreviation of Subroutine and is the opening statement of your code. Each time Sub is written, a new macro or procedure is created. The words "End Sub" should be placed as the last line of code for your macro to tell VBA that your procedure is finished.

Module- This is an area where you can write function code and macros. Macros that you record are also saved here.

class module- This is an area for really advanced VBA users. In this area you can write your own custom classes, methods and collections in the VBA library. As a beginner I would refrain from doing this for the time being. Most VBA authors will never use this type of functionality in their career, but it's a very powerful option if needed.

profession- VBA gives you the ability to create your own custom functions. They can be used by your macros to get specific output, or they can be used inExcel formula barto perform calculations on the values ​​in your cell.

user forms- These are pop-up boxes where users can enter entries or select options. Microsoft uses them all the time in their apps. Some examples of this are:

• error message fields

• dialog box

• der Makrorecorder

The good thing is that VBA lets you create your own custom UserForms from scratch! You can design the skin (user interface) and code all the buttons, inputs, and actions as the user interacts with the form.

LESSON TWO: The Visual Basic Editor

ÖEditor von Visual Basicis your workspace for creating your VBA code.

The editor can be accessed through yourdeveloper guideor with thePress Alt + F11.

The editor appears in a completely separate window from your Office application, and each of the Office Suite programs has its own VBA editor (so you can have both Excel and PowerPoint VBA editors open at the same time).

I won't go into all the features of the Visual Basic Editor in great detail in this article, but I want to show you enough to understand how it's set up and how it works.

Below I have a screenshot of some of the main windows that can be viewed in the Visual Basic Editor. Some of them may not appear in your editor by default, and I explain how to view them in the following descriptions. I will be describing Excel's Visual Basic Editor, but much of the information can be easily translated into PowerPoint, Access, or another Office program's Visual Basic Editor.

project window

This window shows all files that you have open. Oproject windowuses a tree view where you can drill down into each open file and show areas where you can enter VBA code.

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

• Microsoft objects-This folder contains a code area for your workbook (ThisWorkbook) and all of your workbook's sheet tabs (Sheet1).

• to form- All UserForms you create are stored in this folder. I mentioned some of this in lesson one, so I won't repeat myself here. If you don't see this folder, you can add it by right-clicking anywhere in the project's folder structure and Goinsertion->The picture.

• Module- A module folder stores your macro and function code. If you don't see this folder, the project probably doesn't contain macro code (note: there may still be code stored in the Forms folder or in the Objects folder). You can add this folder by right-clicking anywhere in the project folder structure and going toinsertion->Module.

properties window

The Properties window allows you to change certain aspects of an object, form, or module that you have selected. Usually the only thing I change with this window is theNameInner. This is a good idea because you can give your module or form a more descriptive name than the default names provided by the Visual Basic Editor. custom names inNameThe field can only contain one word.

Area for writing codes

This is where the magic happens! In this area you can write and edit your VBA code. Every macro must begin with aSubInstruction (which starts withSub[Enter the name of your macro]( )and closed withexit sub🇧🇷 Also note that the VBA editor encodes some keywords in a few different colors. This helps to organize your code better. I have two top tips that I love to share with people who are learning to write VBA code for the first time:

1. Use indents- Always try to use indentation (via the tab key) in your code. There are several methods of tabulating code, but as long as it's consistent and meaningful it will help you tremendously when trying to add or debug your code. It also helps if someone tries to help you with your code.

   See Also

   Office Q&A: How to prevent Excel paste task from overwriting target cell formattingExcel VBA Tutorial: Get Your Personal Macro Code Vault (aka Personal Macro Workbook)Excel DATE function with example formulas for calculating dates

2. write in lowercase- In case you haven't noticed, every word in the VBA language has at least one capital letter. How is that an advantage? Well, the Visual Basic Editor is case-insensitive and is happy to correct it when it can. That is, if you type "workbook," the editor will automatically switch to "workbook." My rule is to type all lowercase letters, and if the VB editor doesn't capitalize at least one letter, I know I've misspelled that word or that that word is undefined. Having the Visual Basic Editor validate every word I type has really made my code less buggy and saved me a lot of frustration over the years.

instant window

I like to refer to this area as my blade. In the Immediate window, you can run all kinds of tests as you write and run your code. You can use the codedebug.printto instruct VBA to send the following information to the Immediate window. This can be the output value of a function, the value of a cell, or the setting of a current application property. When I started writing VBA code, I had no idea the Immediate window existed (it's usually hidden by default, but you can use the shortcutScraper + Mrto see it), but after learning everything it can do, I've never stopped incorporating its functionality into my code writing and testing processes.

monitoring window

The Watch Window is like an X-ray machine: it shows all the data stored in a variable! Some variables (like the ones you create in your code) don't store much data. However, if you "watched" a variable assigned to a cell, you would see a lot of data (font color, value, height, fill color, etc.). This is especially useful when you're trying to debug your code and want to understand what the value of your variable is at a certain point in the code.

To watch a variable, you need to highlight your variable text and click the Add Watch button (it's the glasses icon on the debug toolbar). You should then see your variable in the watch window. Once you start running your code and upload a value to your variable, you'll see an option (plus sign) that allows you to drill down or expand the content that's now stored in the variable.

are you still confused

I know this is a lot, but you really should use this as a reference sheet to refer to as you study. You won't use all the features of the Visual Basic Editor while learning, but it's helpful to know them in case someone trying to help you refers to a feature of 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 included in their VBA code solutions. I had no idea what debug was doing, and since my immediate window was hidden, it took a lot of research to understand how and why forum members were using this functionality.

I will continue to update this page as I have more detailed blog posts I want to write about the Visual Basic Editor and will be sure to link to them when the articles are published.

LESSON THREE: The Macro Recorder

What is the macro recorder?

The macro recording feature in Excel and Word is by far the best programming tool you could ask for. You can visualize it as a recorder with 2 buttons: record and stop. When the Record Macro feature is set to Record, the program will record the code and save each action performed in Excel or Word in a VBA module. You can then go back and look at the code that was written and do a lot of things with it. The macro recorder might seem like a tool for VBA beginners, but even Excel gurus will use it all the time because they understand the efficiency the recorder can add.

How to turn your screen actions into code

Örecord macroThe function can be accessed via thedeveloperleads and resides inCodeSection.

You can also access it by clicking the Record Macro icon in the bottom left corner of the window (I didn't know that for a long time!)

After clicking the icon, you should see thegrab macrodialog box

In this dialog you have a few options:

Macroname[Required] - The recorder always has a default name in the dialog, but you can change it to something more descriptive, which I always recommend. If you forget to change the name or want to change it later, don't worry, you can always rename your macro after you're done recording. The allowed naming convention must meet the following criteria:

• The name must start with a letter.

• Spaces are not allowed (use an underscore "_" if you want to insert a space between words)

keyboard shortcuts[Optional]: Allows you to activate or run the code you write using a keyboard shortcut. As a warning, this will override the default Excel shortcuts. Therefore, as a best practice, always use uppercase rather than lowercase. For example, instead of using my macro shortcut Ctrl + C (which is obviously the universal shortcut for copying), I would use Ctrl + Shift + Color Ctrl + C.

save macro in[Required]: Allows you to specify where the recorder should store the code. All currently open workbooks will appear as options in the drop-down menu, along with your personal macro (if you have one set up).

description[Optional]: I don't think I've ever used this field as I always go back to my saved code and immediately add comments to describe what the code does, but if you like you can write a short description of what it is. about the recording and the recorder with code for you too.

After filling out the dialog to your liking, you can click OK. Your every move is now being tracked #BigBrother! When you're done, do whatever you want, just code the Stop Recording button (on the Developer tab or in the bottom-left corner of the window).

Not just for Excel

The macro recorder can also be found and used atMicrosoft Word.

This feature was available in PowerPoint a few software iterations ago, but it is believed that the Microsoft Office team unfortunately removed the feature as it was too buggy. This was a big mistake as even a buggy macro recorder would have been useful for users trying to learn how to write VBA for PowerPoint (especially since there isn't much information about PowerPoint VBA on the internet). Lucky for you, I took care of thatcover VBA to PowerPoint on this pageso you don't have to pull your hair out to find out!

Weakness of the macro recorder

Like most things, the recorder has some weaknesses. I will try to list a few that I encounter regularly.

• Some actions cannot be registered- There are some cases where I've tried to record a command and the recorder doesn't encode it. This can sometimes be frustrating!

• Some programs do not have a recorder- Unfortunately, currently only Excel and Word have the macro recording feature. For the rest of the Office programs, you'll need to learn how to write VBA code (or just read my blog) as each program has its own quirks and unique features.

• Scroll and click Logs- This is probably more of an annoyance than a bug, but when I say the macro recorder records everything, I mean EVERYTHING. That means every scroll you create and every cell you click. This often makes recorded macro code extremely long. I always recommend removing these redundant lines of code as it will make your code slower, harder to read and can cause errors.

Can you think of other disadvantages of the macro recorder? Leave a comment below and I'll keep updating this list!

LESSON FOUR: Write your own code

Where should I start...

I'm sure there are many ways to teach people to code, but I'll use my own methods that I've developed while teaching VBA to my peers over the years. I can't possibly explain everything about programming here in this post, but I'll try to cover the basics and hopefully give you a good foundation to build on in the future.

How VBA is structured

When explaining VBA code to someone new to computer languages, I like to use the folder hierarchy analogy on a computer. A typical folder structure on a PC might look like this:

I hope everyone feels comfortable looking at a structure like the one above, and I'm here to tell you that VBA coding is set up just like your computer's folder structure.

go through the nest

I'm also a visual learner and I always find it easier 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 the range A1:C50.

C:\drive

Instead of "drives" on a computer, VBA starts with a reference to the computer program, or in your terms "application" you want to point to. This can be Word, Excel, NotePad, Internet Explorer, or any program you want to access that also speaks the Visual Basic language.

VBA assumes that the program you save your code in is the application you write it for. Because of this, most people don't add the program's name to their line of code, but in this example we'll do it.

With this in mind, the first part of our code line looks like this as we want to work with the Excel application:

Users folder.

After specifying the application we want to use, we need to tell Excel which workbook we want to change. You can do this in two different ways:

this workbook

This points to the workbook where your VBA code is written. Typically you would use this if you only want the macro to affect one workbook. The benefit of this is that if you look at another workbook and accidentally run your code, it won't affect that workbook.Remember that you cannot undo a macro's commands once they have been run!

active workbook

This refers to the workbook you are viewing on the screen. You will use this type of reference primarily when you are creating code that is intended to affect any workbook (for example, when you are writing a macro to format text in a specific way).

Workbook ("Book1.xlsx")

When you write your workbook-level reference this way, you can specify the name of the workbook you want to respond to. I rarely refer to workbooks this way because if I or someone else changes the workbook's name, the macro can't find it because it's referencing the old name.

Apostille (1)

I don't think I've ever referenced a workbook this way, but I'll put it there just for your information. When you use a number to refer to a workbook, you are referring to the order in which Excel opened the workbooks. This can be difficult because if you have additional files active, these will be the first workbooks opened when the Excel application is launched. You can determine where your workbook is by looking at its order in the VBA project window (position shown in Lesson 3).

In this example, I decide that the scope of the macro should only be in our workbook, so we'll use a ThisWorkbook reference in our code:

My Documents folder.

After specifying the workbook we want our code to point to, we can go to the next level and specify the tab or worksheet we want our code to point to. Like the workbook level, the sheet level has a few different reference options:

active worksheet

This is very similar in functionality to ActiveWorkbook. ActiveSheet takes you to the tab you are currently viewing. This is great when you want to give your code the flexibility to run on a table of your choice.

Sheet("Sheet1")

This form allows you to spell out the name of the worksheet you want to target. Because your worksheet name is considered text and not part of the VBA language, you must ensure that the tab name is enclosed in quotes. This lets the worksheet know that you are spelling the name of a tab. If your name doesn't contain quotes, VBA thinks Sheet1 is a variable you created instead of text.

Worksheet(2)

You can also determine the order of your worksheets. The code above takes you to the second worksheet from the left in your workbook.

Finance folder.

After specifying the worksheet that we want to change, we need to specify in which cells we want to do something (in this case, delete its contents). There are many neat ways to tell VBA what range we want, but here I'll cover the two main ways to refer to a single cell and a range of cells.

Reference to a single cell

Rango ("A4")- You can refer to a cell by typing its string name (range name in quotes).

cells (4, 1)- You can also refer to a cell with Cells( ) and enter the row number and then the column number. Note that Cells() does not accept a letter as input for the column number.

Referencing a range of cells

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

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

Excel file 'Budget'

Congratulations! We've reached the lowest level of what we wanted to change, or if you want to keep the analogy, we finally clicked on the folder where our file is saved. Now in this folder there are hundreds of files that we can select, so we have to indicate which file we want to open.

There are many different properties you can change and actions you can take at the bucket level. You can change a color, you can do a calculation... You can do almost anything you can do manually in a spreadsheet. An advantage of Microsoft as the developer of Office and Visual Basic is that most of the computer language is used in the program's functionality. So, in our example, if we wanted to clear the values ​​in our selected cells in Excel, we would just go toHaustab, clickcleardrop-down listeditionsection and clickclean content🇧🇷 To simulate the same in VBA language, just add our line of code "clean content". Just right?!

And all periods?

Now that we've written a fully actionable line of code, you might be wondering what it means to put a period between each level in VBA. I like to compare using periods in VBA to using backslashes in a folder address. These two icons in their respective areas connect the previous level to the next level. Another way to represent this is that they are stairs from one floor to another in a house. You cannot go from the basement to the first floor without using the stairs to get there.

VBA-Muster

The creators of the Microsoft VBA language recognized that we, as computer programmers, want to type as little as possible. They decided to program in some standards or assumptions of the levels we want to aim for. Below is a list of some of the most important defaults that VBA assumes in Excel.

• Excel.Application

• active workbook

• active worksheet

So if we omit any of these levels in our code, the VBA language will assume the corresponding previous level reference. Let's look at some examples of how we can shorten our code and understand what that means.

• starts atExcel applicationLevel

• This workbook.Worksheets("Sheet2").Range("B3").Value = 10

• starts atactive workbookLevel

• Sheets ("Sheet2"). Rating ("B3"). column width = 4.3

• starts atactive worksheetLevel

• Rango("B3").Interior.Color = RGB(75, 172, 198)

The Visual Basic Editor wants to help you!

Finally, I would like to leave a piece of advice that you may have noticed while writing thisEditor von Visual Basic🇧🇷 Every time you type a period after one of your "levels", you may have noticed that a small window appears with a series of words.

What this flyout ends up displaying are all the possible words that could appear after the previous keyword phrase. This is a feature I often overlooked when learning to write VBA code because I didn't really understand what it was. Eventually I took the time to figure out what it showed me, and the little box below has helped avoid bugs in my code ever since.

LESSON FIVE: Where do you go from here?

Building on your foundation

I hope that after reading the previous lessons I have given you enough information to give you a good start in taming the VBA beast. I included this final lesson in hopes of providing you with some solid resources to use while learning how to write VBA code.

Challenge yourself!

The main reason I have the knowledge I have about Microsoft Office is because I've been challenging myself on a regular basis. I have taught many people who have taken some of my courses and gotten nowhere because they have not put what they have learned into practice.

The BEST way to learn these things is to use them regularly. I can assure you that if you use Excel, you can create literally hundreds of automation macros to make your work faster and more accurate.

I have a colleague for whom I wrote a set of VBA procedures and he jokes that soon all he has to do in Excel is press a set of buttons that run macros and his work for the day is done. in 15 minutes. The funniest thing is that VBA saved me hours of manual work I wasted copying and pasting, formatting and double checking your numbers.

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

web resources

The following resources are sites I used daily for training, inspiration, and troubleshooting while learning Visual Basic for Applications. We hope this list will save you the time and hassle of searching the internet for answers.

Google- Google is my favorite search engine and I think their results provide the best places to answer my questions. When I look up a question, I always start my search by typing "VBA Excel" or "VBA PowerPoint". I found this out by doing your google search with the wordsVBAand the program you're trying to target, you'll usually find the answer you're looking for in the first few results.

Blogs- I tend to prefer written content when trying to learn something as it is usually the most efficient way to understand how to achieve the technique I am trying to learn. I can quickly scan an item and determine within seconds if the item is likely to have what I need or if I need to try something else. There are many great blogs such asChandoo,how to get noticed,ExcelJet,Excel offline, &Campus-Excel(Just to name a few). I use an RSS feed manager calledFeedly to keep up with new posts from my favorites and this allows me to learn new techniques on a regular basis.

They were- There are many great forums with many great people willing to share their knowledge for free! My personal favorite is a forum calledSr Excelhas an amazing forum that is used all over the world. If you have a question and can't find the answer, post it in this forum and several people will try to answer your question (usually within minutes).

Youtube- Video tutorials created over the past few years have become really valuable. With the introduction of sections in YouTube videos, it's now easier to jump to a specific section of the video to get your answer without having to go through the entire process. There are also videos showing you how to create amazing skins and templates in Excel. Some of my favorite channels are:other levels,Sr Excel,leila garani,Excel is fun, &the office lab.

Take a class/class!

I can't stress enough how attending a two day course has exponentially increased my knowledge of VBA and its power. Although there was a lot of information to digest, it opened my eyes to what VBA is really capable of.

I would definitely recommend going to a live class, either in person or online. The advantages of attending a live class are that you can ask questions that are on your mind and the teacher is usually willing to adapt the lecture to cover some areas related to your specific field of work .

Although I don't currently offer any courses, this form of education is definitely on my list of long term goals as I believe it can be a very important resource in helping people understand VBA programming.

What do you recommend?

I look forward to hearing from you and finding out what helped you become a VBA guru! Leave me a note in the comments section to tell me your most valuable VBA features and I'll try to compile a community list with everyone's suggestions in this post. I will constantly check to make sure the list reflects the best and most up to date resources available.

Hope this helped!

I hope I've provided a solid foundation for you to understand the purpose of VBA and to explore the many strengths it has to offer. I want to make this the best possible introduction to VBA as I think this is a very important feature in Excel that is often overlooked. If you have questions about a specific section or suggestions for improving this guide, let me know in the comments section below.

About the author

Hi! My name is Chris and I run TheSpreadsheetGuru website in my spare time. By day I'm actually a finance professional who relies heavily on Microsoft Excel in the corporate world. I love sharing the things I'm learning in the "real world" with everyone here on this site so you too can become a spreadsheet guru in your business.

Over my years in the corporate world, I have taken opportunities to improve working with Excel and have created a variety ofExcel-Add-Ons, from inserting tick symbols to automating copy/paste from Excel to PowerPoint. If you want to keep up to date with the latest Excel news and have the top Excel tips I've learned over the years delivered straight to your email, you canSign up for my free newsletter🇧🇷 I hope I was able to offer you something valuable today and hope to see you again soon!

-kris