Excel VBA Tutorial for Beginners - A Complete Guide to Learn VBA Basics
Written by Puneet and last updated on 25-Jan-2019
If you are a beginner and want to learn VBA basics, let me tell you that this is the most comprehensive tutorial which you can find on the Internet to learn it in a Best Way (Step By Step).
The thing is:
With VBA you can be a Excel power user. Yes, that’s right, to be an advanced Excel user is to learn to use VBA.
Don’t you want to write codes in Excel and want to automate your stuff. I'm sure you do. Right?
Here’s the truth:
All the emails which I receive from my audience, 85% of them are related to VBA.
If you do repetitive activities every day in Excel, like copy-pasting data, creating charts, pivot tables, the best way is to write codes to automate them.
Learning VBA can be WIN-WIN for you. Getting excited yet?
You should be!
Table of Content
What is VBA
And if I work for 5 days every week, it will be 10 days in a year. Yes, I’m not kidding, with VBA you can save that much of time.
But there is something more which you can do with it, and that’s creating custom apps for your office applications and you can also use VBA to make two different Office apps coordinate with each other.
This is the beauty of VBA, and the best part is, there is no prior experience required to learn to use it.
Advantages of VBA
With VBA, there come some advantages which you need to understand while jumping into it.
- Execute a Task in the Same Way: Let’s say you want to copy a value from cell A1 to cell B5 and when you write a code statement for this, that code works in the same way, every time you run that code, no matter how many times you run it.
- Perform a Task with Speed: Any activity you perform by using a macro code would be much faster than if you do it manually. Even if you run a task 100 times, the speed of the code would be much faster than any human performing it.
- Performs a Task without Bias: If you different users doing the same task they may be biased as everyone thinks in a unique way, but that’s not the case with VBA.
- Anyone Can Perform the Activity: There could be a lot of tasks that not everyone can do in Excel, but if you write code for then anyone can perform them with a simple click.
- Can Do Impossible Things: VBA can also do those things for you which can not actually possible with normal Excel options.
- Saves a Lot of Time: The moment you automate a task which you do frequently you not only increase your efficiency but also save a lot of precious time.
How VBA Works in Excel
The one good thing about Microsoft Apps is, there’s no need to install an extra add-on or plugin-in to use VBA.
But there is one thing which you need to have before you jump into it, and that's "Developer Tab".
Developer Tab is from where your VBA journey starts as it has all the major options to use VBA.
Unfortunately, when you install Microsoft Office in your system it doesn’t show “developer tab” by default, so you need to activate it first.
- First of all, open a new Excel Workbook (make sure to activate the "Developer Tab" as you have learned in the previous section).
- Now, open the VBE (Visual Basic Editor) from the developer tab.
- From the left side of the VBE, right-click on the workbook's name and insert a module.
- In the code window of the VBE, enter the following code (I'd suggest you write this code instead of copy-pasting it).
Sub MyFirstVBACode() Selection.ClearContents End Sub
- After that, close the VBE and come back to your worksheet and enter a value in a cell and then select that cell.
- From here, go to the "Developer Tab" and open the "Macro".
- Now, select the macro "MyFirstVBACode" and click OK.
Let's understand how VBA actually works.
The code which we write in VBA called “Macro”.
For example, if you want to make a text bold from a cell then the macro code which you use for this will include the code for commands like selecting the text and using the bold option.
Please follow the below steps:
The moment you HIT-RUN, the value in the selected cell cleared by the code.
Congratulations on executing your first VBA code and testing it. This is a moment of proud.
Before I explain to you how this code works, let me share something with you. VBA is an object-oriented language.
And as an object-oriented language, in VBA, we structure our codes in a way where we are using objects and then defining their properties.
In simple words, first, we define the object and then the activity which we want to perform.
There are objects, collections, methods, and properties which you can use in VBA to write your code.
Don't miss this: Let’s say you want to tell someone to open a box.
The words you will use would be “Open the Box”. It’s plain English, Right? But when it comes to VBA and writing a macro this will be:
As you can see, the above code is started with the box which is our object here and then we have used the method “Open” for it.
Let’s go a bit specific, let say if you want to open the box which is RED in color. And for this the code will be:
In the above code, boxes are the collection and open is the method. If you have multiple boxes we are defining a specific box here.
Here’s another way:
Box(“Red”).Unlock = True
In the above code, again boxes are the collection and Unlock is the property which is set to TRUE. I'm sure the first code which you have written now makes sense to you.
Sub MyFirstVBACode() Selection.ClearContents End Sub
Visual Basic Editor
As you can see Visual Basic Editor is fully loaded with options and tools which you can use to write, save, and manage your VBA codes easily.
And according to Richard Mansfield in his book Mastering VBA for Microsoft® Office 2016, he mentioned that VB editor is highly effective and a culmination of more than 20 years of modifications and improvements.
Components of Visual Basic Editor
To understand how VBE works you need to understand its components and how you can use them.
Just like any other application, it has its menu bar where all the options are listed under different sections.
Each section has a drop down with all the options. If you notice closely, for most of the options there are also shortcuts which you can use to open them.
For example, if you want to find something the keyboard shortcut is control + F (it’s the same keyboard shortcut which you use in the worksheet).
The toolbar is the group of specific commands from the menu bar which you can use instantly while working on VBE.
The toolbar in the above image is the default toolbar which you get when you open the VBE first time.
However, there are four different kinds of toolbars which you can add: Standard, Debug, Edit, and UserForm (View ➤ Toolbar).
3. Project Window
On the right side of the VBE, there’s the Project Window which lists all the open projects.
In simple words, every workbook or an add-in which is open is a project and every project further has a collection of objects:
- Worksheet: Each worksheet in a workbook lists with its name as an object.
- The Workbook: It represents the workbook itself as an object.
- Chartsheet: If you have a chart sheet in your workbook then it will also be listed there as an object.
- Module: It’s the place where you write code or your recorded macros stores. In case of writing a macro, you need to insert a module, but, while recording a macro Excel inserts it automatically. To insert a module all you need to do is right-click on the project ➤ Insert ➤ Module.
4. Properties Window
It’s just down from the Project window and you can hide and un-hide it is using the keyboard shortcut F4.
For example: when you select a worksheet, it shows you all the properties it has and you can change it.
- Name of the worksheet
- Enable auto filter
- Make a Worksheet very Hidden
- Much More
5. Code Window
The code window is where you write your code and Excel stores the code when you record a macro.
For every project, there is a code window and you can open it just by clicking on the object.
Whenever you open the VBE, code window is always visible but if it’s not there, you can activate it using the shortcut key F7.
And to close it you can simply click on the close button from the top right of the window.
6. Immediate Window
Let’s say if you want to test the below code all you need to do is type a question mark and then paste the code there and HIT enter.
Range("A1").Value = 9999
It will immediately show the result of the code. The other ways are to add Debug.Print before the code gets its result value in the immediate window.
Quick Tip: Macro Recorder is a great way to know code if you didn't know before. But you need to understand there can be a number of occasions where macro recorder won't be able to code. But it's still worth to learn to use it.
You can compare a macro recorder with a video recorder.
So, if you want to record a video, you need to do some planning before it, like creating a script, having proper lights, and other stuff.
Just like that, for recording a macro, it’s important to do some planning.
For Example: If you want to select a specific cell in the worksheet and want to apply cell color to it, you need to make sure that you select the right cell and apply the right color.
The thing is, you don’t want to get the code for the activity which you don’t want to execute while using the recorded macro again.
The best way for this is to note down the steps you need to perform and then stick to it while recording.
Record a Macro in Excel
Here we are going to record a macro which can make apply following formatting to the selected cell.
- Font Color: Red
- Font Size: 12
- Font Style: Vedena
- Font Text: Bold
As I said planning is important. And before recording this macro there's one thing we need to take care and that is selecting the target cell before actually start recording.
The reason for this is if we select a cell while recording the macro it will record that selection as well.
So when you run this macro again it will select that particular cell to apply all the formatting on it.
But what we want is to apply the formatting on the cell which is active (which is selected).
To record a macro code for all the activities we have listed above follow the below steps:
How Macro Recorder Generates a Code
Macro Recorder has generated the code for you at the back end when you were performing the activities.
To understand this you need to see the code first.
So for this open the visual basic editor first, and open the module1 where you have your code stored.
In the code window, you can see the entire code which is generated by the macro recorder along with all the details which you have entered.
As you can see the name which you have specified has been used as the name of the macro and shortcut key and the description is there.
Now let's understand the code (as I said earlier, the macro recorder doesn't create a perfect code so you need to clean it up every time, and we will learn this in the next recipe).
1. Naming a Macro
Whenever you record a macro it's required to specify a name to that macro but there are a few rules with which a name should comply:
- You can have Macro's name up to 80 characters long.
- You can't use spaces, punctuation, or special characters.
- It must start with a letter.
Apart from these rules, there are few words which are prohibited Excel to use as a name of a Macro and when you any of those Excel's alert you by showing a message like below.
It's really important to use a meaningful name instead of having default names like Macro1 or Test1.
As you can't use spaces in the name of a macro but you can use an underscore or you can make the first letter of each word capital to increase readability.
You have performed the four activities and the macro recorder has generated the code into four parts.
- In the FIRST part, the code says the font color of the selected cell should be red and it has used a numeric value for it.
- The SECOND part of the code, says the font size of the selected cells should be "12".
- And in the THIRD part, it says the font style of the should be "Verdana".
- At last, in the FOURTH part, it says the font in the selected cell should be bold.
As I said, all the code you have in the module right now is generated by Macro Recorder when you were performing the activities.
But to see it generating the code you can do one thing.
You can open the VB editor and the workbook in side by side windows and then start recording the macro.
This'll help you perform the activities and see the macro recorder generates code simultaneously.
While recording a macro there are a few things you need to know and which can be helpful for you.
2. Storing a Macro
Before recording a macro you need to specify where you want to save it and you have three options for this:
- New Workbook: You can select this option if you want to record your macro in a new workbook. The moment you hit OK to record a macro Excel open a new workbook.
- This Workbook: By selecting this option you can record the macro in the current workbook.
- Personal Macro Workbook: From all these three options this is the best place to store your recorded (as well as written) macros. A personal macro workbook is a place where you can store your macros and can access them from any workbook.
Cleaning Up Recorded VBA Codes
At this point, you have recorded your first macro and you have also tested it.
But there's one thing which you need to do, and that's cleaning up the code which is recorded by the macro recorder.
Below is the code which is you have in the module right now:
Sub HighlightCell() ' HighlightCell Macro ' This macro applies a red color, font size 12, font style Verdana, and makes the font bold ' Keyboard Shortcut: Ctrl+Shift+H With Selection.Font .Color = -16776961 .TintAndShade = 0 End With With Selection.Font .Name = "Calibri" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNo .Color = -16776961 .TintAndShade = 0 .ThemeFont = xlThemeFontMinor End With With Selection.Font .Name = "Verdana" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .Color = -16776961 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Selection.Font.Bold = True End Sub
Sub HighlightCell() ' HighlightCell Macro ' This macro applies a red color, font size 12, font style Verdana, and makes the font bold ' Keyboard Shortcut: Ctrl+Shift+H With Selection.Font .Color = -16776961 .Size = 12 .Name = "Verdana" .Bold = True End With End Sub
What you have just done
Using Relative Reference
While recording the macro I told you to select the “Use Relative References” before you hit the “Start Recording” button.
In Excel, when you record a macro, Excel uses the “Absolute Reference” by default but you can change it before recording a macro.
The point is, using relative references is important if you want to use a macro to perform the recorded activity anywhere in the worksheet.
To make you understand the difference between both of the modes of reference, I want you to record activity in two different ways.
- First of all, select the cell A1 and start recording a macro (without turning ON “Use Relative Reference”) and then select the cell E5.
- You’ll get the below code by the macro recorder:
Sub AbsoluteReferenceMacro() Range("E5").Select End Sub
- Now again select the cell A1, turn ON the “Use Relative Reference” and record the same activity (select the cell E5).
- But at this time the code you got is different:
Sub RelativeReferenceMacro() ActiveCell.Offset(4, 4).Range("A1").Select End Sub
You have recorded both of these codes doing the same activity but using different reference mode.
The first macro code is pretty simple to understand, it simply says to select the cell E5.
And if you run this macro, it will perform the same thing i.e. selecting the cell E5, no matter which cell you have selected at this point.
On the other hand, the second macro code says something else. If you select another cell before running this code then it will select a different cell.
When you recorded this macro the active cell was A1 and then you selected E5 which is four cells down and four cells right from the cell A1.
Now as you have recorded this macro with relative reference this will always select the cell which is four cells down and four cells right from the active cell.
Let's say if the active cell is D4 then it will select the H8 cell which is four cells down and four cells.
Write a VBA Code
You can even write a macro code to create a pivot table, but right now I don’t want you to think that far.
Let’s think about an activity which you want to perform in your worksheet and you can write a code for it.
Take a small task, a tiny one.
OK, let me give you something. Alright, so you need to write a code to insert a text value “Done” into the cell A5.
Don’t worry I’m with you. You have Developer Tab on your ribbon and you also familiar with the Visual Basic Editor, right?
So follow the below simple steps to write your first VBA code in Excel:
- First of all, open your “Visual Basic Editor” and insert a new module from the “Project Window”.
- After that, come to the code window and create a macro with the name “Enter_Done” (we are creating a SUB procedure), just like I have below.
- Now, you need to write a code which we have just discussed above. Hold for second and think like this:
- You need to specify the cell where you want to insert the value.
- And then the value which you want to insert.
- So, you need to specify that cell as an object and the value which is the property of the object (range is object and value is its property).
- First, you need to enter the cell reference and for this, you need to use RANGE object and specify the cell address in it, like below:
- Now, enter a dot after that and the moment you add a dot, you’ll have a list of properties that you can define and activities which you can do with the range.
- From here, you need to select the “Value” property and define the text which you want to insert in the cell “A1” and when do it your code with look something like below.
- Last but not least above the line of code, enter a ('this code enters the value “Done” in the cell A5). What is this? It’s a comment (will tell you later in this chapter).
Yes, that’s it. Congratulation! for writing your first macro code.
Sub Enter_Done() 'this code enters the value “Done” in the cell A5 Range("A1").Value = "Done" End Sub
Let’s understand this VBA Code
As I said earlier, the code which you have written is a simple one-line code. You can split this code into two different parts.
In the FIRST part, we have specified the cell address by using the RANGE object.
And, to refer a cell using a range object you need to wrap the cell address with double quotes (you can also use square brackets as well).
In the SECOND part, we have specified the value to enter into the cell.
What you have done is, you have specified the value property for the cell A5 by using “.Value” (you can either select it from the list or simply enter it by typing).
After that, the next thing which you have done is specified the value against the value property.
Whenever you are defining a value (if it's text), you need to wrap that value inside double quotation marks.
Write Clean and Structured Codes
In the starting days when you are learning VBA and writing macros, there’s one thing which you must learn along with everything, and that’s writing clean codes.
In this section, you will learn about the four most important things which you can adopt as habits while writing codes:
- Line Break
- With Statement
Let’s look into all of these.
1. VBA Comment
Just like any other programming language you can add comments in your VBA code.
The point of using a comment is to describe what a particular part of the code does and there's a chance that somewhere in future you forget what it does and if you have a comment which describes it, can be a big help.
Even for another person, it can help to understand what a macro is all about without even get into the code.
Let’s take an example of the code you have written above. It has a line starting with the a ‘ which says (this code enters the value “Done” in the cell A5).
Now when someone reads this comment, that person will able to understand what this code does.
That’s why you need to add comments in your code and there are basically three ways to add a line of a comment:
- The FIRST way is to use an apostrophe (') at the starting of the line by manually typing it.
- The SECOND thing you do is select the line you want to convert into a comment and go to edit toolbar and click on the "Comment Block" button to convert the selected line into a comment (there's also a button to uncomment blocks).
- The THRID way is to use the keyword REM before the line to use it.
The moment you add a comment apostrophe (') or REM that particular line turns green (it makes it easy to recognize all the comments).
In simple words, anything which has an apostrophe (') or REM before it will be taken as a comment and VBA will ignore it while execution.
There's an exception for using an apostrophe ('): If you are using it inside a set of quotation marks then VB editor won't recognize it as a comment.
Below are a few useful ways to use use a comment in your codes:
- If you are testing a macro code then you can use a comment to exclude a line of code from the execution.
- You can also use a comment to keep track of the changes you have made into the code using date, a remark, and name of the person.
- If you are using a function into a VBA code then you can use a comment to describe what that function actually does.
- You can also add instructions that a person needs to follow while editing that code. This can be especially helpful if someone takes over your job or need to make changes in your absence.
2. VBA Indent
Indentation in your codes is something which makes your codes structured.
Let me show you an example here, look at the below snapshot of the code where you have a code where IF statement is used.
And here you have the same code with indentation.
As I said both of these codes are the same but, there’s a huge difference when you look at the second code which is structured.
Now here’s the thing, adding indent with a code is pretty simple and you must make sure do it while writing the code.
There are two simple ways to add indentation in a line of code:
- First: Move your cursor to the starting of the line and press tab to add intent.
- Second: In the same way you can use the “Indent” button from the edit toolbar.
You can also add indent to multiple lines of codes. All you need to do select all the lines and press tab or use the indent button.
3. Line Breaks
When you write code there could be a situation where you are writing a long line of code.
But, there is a problem with this way.
When you write long lines of codes, you (or anyone) need to scroll to the left to read the entire line. So the best way is to use a line break.
Look at the below code where you have a long line and as I said you need to scroll to the left to see the entire code.
But here you have the same line of code which is split into 3 lines using line breaks.
Adding line breaks is simple all you need to do to split a line just by adding a space followed by an underscore ( _) and simply write the rest of the code in the next line of code.
4. With Statement
In your VBA codes, you can use “With-End With” statement. Using this statement you can execute multiple statements which refer to a single object.
Confused? Don’t worry, let me share an example with you.
Let’s say you want to activate the worksheet “Sheet1” and want to change its name. These are two different statements and the code for these would be like below:
For Activating the Worksheet:
For Renaming the Worksheet:
Both of the above codes are referring to the same object and that’s the worksheet “Sheet1”.
But when you run these two statements combine you are referring it twice.
By using “With-End With” you can execute multiple statements by referring to the same object.
Let me show you how: To re-write the above code using the “With” statement you need to start like below:
Here you have used the keyword “With” and then define the worksheet which your object.
After that, the thing is to define all the statements (properties and methods) which you want to execute.
.Activate .Name = "myWorksheet"
And in the end, enter “End With” to end the statement.
With Worksheets("Sheet1") .Activate .Name = "myWorksheet" End With
Now when you run this code it will activate the worksheet “Sheet1” and then change its name to “myWorksheet”.
Run a Macro
1. From List of Macros
In this list of MACROS, you’ll have all macro your have in the open workbooks. Just select the macro you want to run and click on the “RUN” button.
Let’s say you have a macro code which you need to use frequently in your work. In this situation, you can create a button and assign that macro to it.
- First of all, insert a simple shape from Insert Tab ➜ Illustrations ➜ Shapes. Select any of the shapes which you want to use as a button.
- After that, right click on that shape and select “Assign Macro”.
- Now, from the list of macros simply select the macro which you want to assign to the shape.
Now, whenever you click on that shape the macro code which you assigned will execute.
And, if you don’t want to insert a sperate shape into the worksheet you can add a RUN button to the QAT. Here’s what you need to do:
- First of all, go to the File Tab and open Excel options from there.
- After that, go to Quick Access Toolbar and select the “Macros” from the “Choose commands from” dropdown.
- Now you’ll have the list of macros which you have in your workbook (and in the personal macro workbook).
- Next, you need to select the macro which you want to add to QAT.
- After that, select it and click on the “Add” button.
- From here you can also do one thing and that’s adding a Symbol to the button. For this, just click on the “Modify” button and select the symbol you want to add.
- In the end, click OK and click OK again to come back to your worksheet and you will have your button on the quick access toolbar.
1. SUB Procedure
'This macro code shows the message "Hello! World”.
2. Function Procedure
Function RemoveFirstCharacter(rng As String, cnt As Long)
removeFirstC = Right(rng, Len(rng) - cnt)
Function MyWordCount(rng As Range) As Integer
MyWordCount = UBound(Split(rng.Value, " "), 1) + 1
As you have learned, function procedure returns a value and in the above function that count is the value which this function returns.
Now the moment you enter this function into a module you can use it in your worksheet as you can see in the below snapshot.
Difference Between SUB and Function Procedure
To make you understand what are the major differences between SUB and FUNCTION:
- SUB procedure performs an action (or actions) on the other hand, FUNCTION procedure returns a single value.
- SUB procedure starts with the “Keyword” SUB and FUNCTION procedure starts with “Function”.
- All the codes written as SUB can be found in MACRO codes list (Developer Tab ➜ Macros) and you need to use FUNCTION procedure as a function in the worksheet.
VBA Variables, Constants, and Data Types
Let me tell you something straight, a Variable can store a value, it has a name, you need to define its data type (you don’t, but better if you do) and you can change the value it stores.
Below is the example of declaring a VARIABLE:
But, you need to define the name of a variable and it’s data type before you assign a value to it. Some of the major data types you can use are Strings, Integers, Objects, and Variants.
As the name suggests, “VARIABLE” is something whose value is not fixed.
In VBA, Variable is like a storage box which is itself stored in your system but it can store a value in it for you and you can use that value in your code and can change that value if you want.
To declare a variable you need to follow a simple procedure:
- Use the keyword “Dim” in the starting.
- Specify a name for the variable.
- Use the keyword “As” after the name.
- Specify the “Data Type” for the variable according to the value you want to assign to it.
Dim startDate As Date
Here we have used the name “startDate” and specified the data type “Date”. After declaring a variable you can assign a value to it.
startDate = “11/10/2018”
Now, whenever you use start date in your code Excel will use the date you have assigned to it. In the below code, we have used the variable startDate to show with the message box.
Dim startDate As Date
startDate = "11/10/2018"
And when you run the code it shows the date which we have assigned to it.
Explicitly or Implicitly
You can declare a variable either explicitly or implicitly.
An explicit declaration means that before you assign a value to a variable you have declared it and define its data type.
The BENEFIT of this is when you define it and its data type VBA always store data into that format.
Here’s an example:
So when you store value in this VBA will always apply the date format to that.
But with the implicit declaration, you don't bother with that explicit declaration statement. Instead, you define the value to the variable and use it in the code statement.
In the implicit declaration, VBA stores the data in a Variant variable type because you didn't specify the type.
In other words, if you just use a variable in your code without declaring it, it's implicit.
Below is the example of implicit declaration:
myDate = “11/10/1990”
This is how you simply assign the value to a variable and use it in your code statements.
If you ask me, it’s always, I mean always better to declare a variable before you use it. It’s not only good practice but also make your code run faster and make it easier to understand.
Declaring a variable is a good habit 🙂
What is this Static Variable thing?
Well, I have told you that variable is what where you can change the value but this is something different.
With a normal variable when a procedure get completed the value stored in the variable would be deleted from VBA’s memory, but, if you don’t want to lose that value, you can make that variable static.
To make a variable static so that it retains the value you just need to use the keyword “Static” instead of using “Dim”, just like I have used in the below example.
Rules to Follow When You Name a Variable
You can name a variable (these rules also applied to the constant and procedure name) as the way you want but there are a few rules you need to follow:
- You can use alphabets, numbers, and some punctuation characters but the first character of the name must be an alphabet.
- Spaces or periods are not valid characters to use in the name but you can use an underscore to make the name readable.
- You can’t use special characters in a name.
- The length of a name can be 254 characters but using a name that long doesn’t make sense.
- There are many reserved words, which are words that you can’t use for the name. If you attempt to use one of these words, you get an error message.
- VBA doesn’t differentiate between case.
The best way to create a convention of names which can be readable and makes sense to anyone who is reading it.
Below is the example of declaring a CONSTANT:
Const birthDay As Date = "11/10/1990"
Above is a constant which stores a birth date. Now if you think, a birth date is something which supposed to be fixed and this kind of value you can use a CONSTANT.
Unlike a Variable, while declaring a constant you can you can assign value to it in the same line.
VBA Data Types
This is strongly recommended to declare the data type for VARIABLES and CONSTANTS.
When you specify the data type for a variable or a constant it ensures the validity of your data and keep you from entering an invalid type of data.
If you omit the data type, VBA applies the Variant data type to your variable - it's the most flexible and VBA won't guess at what the data type should be.
Here’s the thing: To force yourself to declare the data type for a variable or a constant you can use OPTION EXPLICIT statement.
To ensure that the Option Explicit statement is inserted automatically every time you insert a new VBA module, you can enable the Require Variable Declaration option.
For this, you need to go to the Tools ➜ Options, and then tick mark the then “Require Variable Declaration” (This option will not affect existing modules, only modules created after it is enabled).
Below is the description of all the data types which you can use.
Range of Values
Variant (with numbers)
Any numeric value up to the range of a Double
Variant (with characters)
22 bytes + string length
Same range as for variable-length String
The range of each element is the same as the range of its data type.
10 bytes + string length
0 to approximately 2 billion
Length of string
1 to approximately 65,400
-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
Any Object reference
Long (long integer)
-2,147,483,648 to 2,147,483,647
-32,768 to 32,767
-1.79769313486231E308 to-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
+/-79,228,162,514,264,337,593,543,950,335 with no decimal point;+/-7.9228162514264337593543950335 with 28 places to the right of the decimal
January 1, 100 to December 31, 9999
-922,337,203,685,477.5808 to 922,337,203,685,477.5807
0 to 255
True or False
This is the complete list of data types which you can use but you need to bit smart while choosing the right type of data.
For this, you need to consider two things mainly:
- FIRST, which data type can handle the data which you want to store.
- Once you figure out this, the SECOND thing is to check which out all the favorable data types typically use the fewer bytes.
Above two checkpoints can serve you to select the right data type in general, but there could be a time when you need to dig deep.
Let’s say if you want to store logical value then you need to use Boolean data type instead of String.
In the same way, if you are dealing with numbers you can use choose between Long and Integer base on how large your number could be.
The point is: More you code and creates macros the more you’ll get to know about the right way of defining data types.
At this point, you know about procedures and which procedure is useful in which situation, you also learned about Variables and Constants and how to define their data types.
The next important thing is to understand the SCOPE, which means availability of procedure, variable, and constant.
That’s where it can be used.
When you write a procedure or define a Variable or Constant, the SCOPE defines if you can use them outside their home procedure or not.
There are three ways of defining scopes to variables (constant and even with the entire procedure).
- Procedure-Level: You can only use a variable in the same procedure where you declare it.
- Module-Level (Private): Makes a variable accessible from all the procedure in a module.
- Module-Level (Public): Makes a variable accessible from all the procedures in all the modules.
The default scope of a variable is the procedure (Procedure-Level) in which that variable is declared but let me share something from the real world with you.
I work from a Co-Working Space and the place where I sit is on the first floor in the three-floor building and I mostly sit on the same seat every day.
So if you declare a variable with the Procedure-Level scope you can only use it in the same procedure.
Just like I sit on the same seat on the same floor every day.
But let me tell you more: Even though I sit on the first floor, I can use any seat on other floors too.
And in the below snapshot, we have declared the variable “startDate” at the top of the module using the keyword Private before starting any procedure and I have used it all three codes.
Think this module as a building where you have different floors (Procedures) and as you have already declared the variable at the starting of the module you can use it any of the procedures in that module.
It is called Private Module-level Scope.
Now here’s the last thing: My office has branches in different cities all over the country and if I go there I can go to those offices and use a any of the seats if I want.
So if you have different modules in your workbook you can use a variable in all those modules by declaring it as Public.
VBA Input Box
InputBox(Prompt [,Title][,Default][,Left][,Top][,HelpFile, HelpContextID][,Type])
Let’s write a code for the Input Box which could show when the user opens a file and ask for them and enter that name into the cell A1.
Follow the below steps to create your first input box.
- First of all, create a macro code using auto_open as the macro name, just I have in the below snapshot.
- Next, you need to declare a variable to store the name enter by the user. Here I have used “myName” and defined its data type as a string.
- Now the next thing is to specify the input box as the value to the variable which you have just declared in the above line. This way the value which user enters in the input box will be stored in the variable.
- So you need to enter the code to create an Input Box and for this, type “myName = InputBox(“. The moment you type it you’ll get a tooltip showing all the arguments.
- Now, you need to enter the text you want to show with the input box. As you want the user to enter the name, we need to enter a test “Please! Enter your name.”. Here also you need to wrap the text in double quotation marks.
- Next, you need to specify the title text to show at the title bar. Here I’m using “Enter Name”.
- The last thing is to specify the default text in the input box and here I’m using "John D Green". I can be helpful to show the user how the name should be entered as it serves as a sample name.
- In the end, you need to specify the value which you have stored in the variable “myName” to the cell A1.
- And with this, your code for input box is complete.
Dim myName As String
myName = InputBox("Please! Enter your name.", "Enter Name", "John D Green")
Range("A1").Value = myName
As I have already shared when you auto_open as the name of the macro it will get executed when you open the file.
Create an Input Box to Select a Range
Apart from entering a value into the Input Box, you can let the user select a range of cells. Let me show with an example.
Now in this example, you have used the TYPE argument and assigned the 8 as the data type which returns the Range.
Dim rng As Range
Set rng = Application.InputBox(prompt:="Select the range:", Type:=8)
MsgBox "The you have selected is " & rng.Address
- When you run this code it shows an input box and let the user select the range.
- And when user click it shows a message box back and tell the user which range has been selected.
Text (a string)
A logical value (True or False)
A cell reference, as a Range object
An error value, such as #N/A
An array of values
VBA Objects, Properties, Methods, and Events
Visual Basic for Applications is an Object Oriented language and to make the best out of it you need to understand Excel Objects.
The workbook you use in Excel is made up of different Objects.
And there are a number of properties which are attached to an object which you can access and there several methods which you can use on/with an object.
Let’s understand this with an example.
Think about a big grocery store, which has different sections for products and then those sections further have racks and in those racks have products.
So if you want to buy clothes, you need to go to the clothes section and in the clothes section, you could have the sub-sections for Men and Women.
And when you pick a cloth that cloth would have a specific color, size, and fabric (which are the properties of that cloth).
In the same way, in VBA, there are objects which are structured in a hierarchy.
Object Hierarchy in Excel
In Excel Application, you open a workbook and in that workbook, you have a worksheet (or multiple worksheets) and in that worksheet, you have cells and range of cells.
Excel’s Object Hierarchy starts with itself the Excel Application and below is the structure which defines this hierarchy:
Let’s say if you want to refer to a workbook the code you have write should be like:
In the above code, you are referring to the workbook “Book1”.
What actually you have written is, you refer to the Excel application first and you have used “Workbooks” which is further a part of the Application object.
Now let’s say you want to go further and wants to refer to a worksheet in the workbook “Book1” and the code for this would be:
You know what it means, Right? But let me tell you.
In the above code, after referring to the workbook “Book1” you have referred to the worksheet “Sheet1” which is further part of the workbook.
Let’s go a bit further. Let’s refer to a specific cell in the worksheet “Sheet1” and the code for this would be:
Before you ask, let me tell you this. In the above code, you referred to the cell A1 which is in the worksheet “Sheet1” of the workbook “Book1”.
When you refer to a Range object in this way, it's called a fully qualified reference.
Simplify the reference to an Object
In the above code, you have used a fully qualified reference. That means you tell VBA to which workbook, worksheet, and cell to refer.
But what if you are already in the Book1 workbook?
In that case, you can simply refer to the worksheet in which you want to refer to a cell, just like below code:
Even further, if the worksheet “Sheet1” is activated then you can simply refer to the cell or range of the cells, just like below code:
Isn’t it better?
Now you are clear about objects and the next thing to understand is “Properties”.
As I told you again and again that VBA is an object-oriented language BUT just by learn to refer to an object won’t help you to automate your stuff with VBA.
With each object, there are some properties which you can access and make changes into.
Here’s a real-world example:
Let’s say you have a box and the color of that box is RED. The box is the object and color is the property of it.
To make you understand how to access properties in your VBA codes, let’s write a code and use “Value” property of the range object.
The VALUE property can be used to read and write value from a cell and a range of cells. So let’s say you want to enter a number into the cell A1.
- First of all, you need to specify the range, so the will be Range(“A1”) and the next thing is to enter “.Value”. When you write “.Value” it tells VBA to access this property.
- The next thing is to type the value which you want to enter. Let’s type a text here so “Excel Champs”. If you typing a text you need to wrap it with double quotation marks and when you run this it will simply enter the value in the cell A1.
Range(“A1”).Value = “ExcelChamps”
- In the same way, you can add a number. But with a number, you don’t need to use double quotation marks.
Range(“A1”).Value = 9988
- And if you want to enter the current date. For this, you need to use the DATE function here which returns the current date and when you run it, you will have the current date into the cell A1.
Range(“A1”).Value = Date
- Now let’s try a range of cells now.
Range(“A1:A5”).Value = Date
The idea is the same, specify the range use “.Value” and type the Value which you want to enter.
Let me recall: In the above example, you have read the value property, Right? But you can also read a property of an object.
Even though there are some properties which are only read-only, yes you can only read them.
Let’s write different code to read the value of a cell.
In the below code, you have defined the message box and used the range. After that specified the value property.
And when you run this code it will show a message box with the value you have in the cell A1.
In the same way, you can also read the value from one cell and write it to another cell. In the below code, you have something like this:
Range("A2").Value = Range("A1").Value
The above code reads the value property from the cell A1 and writes it to the cell A2.
Once you understand how to use Object properties in your VBA codes the next thing to master is Methods, which you can use with objects.
In simple words, methods are actions which you can do on or with objects.
If you want to do something meaning full with an object you need to know which is method is for what.
Remember the Box example which I have used in the Introduction of this guide?
Here “Open” is a method which opens the box which is your object. But let write real code. Let’s use a simple example for basic understanding, Right?
Let’s use “ClearContents” method which you can use to clear value and formula from a cell or a range of cells.
- First of all, you need to specify the range so enter Range(“A1”).
- After that, the next thing is to enter ClearContents.
- Now in the same way if you want to clear contents from a range of cells you can define a range as well.
- And if you want to use “ClearContents” with the cell which is selected you can use the below code:
When you run this codes it will simply clear formulas and value from the cell or a range of cell which you defined.
Arguments with Methods
With some methods, there could be some arguments which you need to specify (not all but some methods can have arguments and those arguments could be optional or mandatory).
Let’s say you want to replace value from a particular range of cell. Let’s say from the range A5:G10.
For this, you need to use “.Replace” method which further has arguments which you need to specify.
Once you type “.Replace” and then enter a space you can see the argument which you need to specify.
Here you have all the argument which you can see in the below snapshot. All the arguments which are in square bracket are optional.
To use this method, you need to define two require arguments:
- What: The value you want to replace.
- Replacement: The value which you want to replace with.
Here I’m using “Yes” to replace with “No” and the code would be like below:
Range("A5:G10").Replace "Yes", "No"
This code will simply replace the value “Yes” with the “No” from the range A5:G10 when you run it.
Let me come again: Methods are actions which you can perform with/on an object.
In plain English, an Event means “Something that happens” and the meaning of VBA Events is exactly the same.
Whenever you do something in Excel that’s an event: enter a value in a cell, insert a new worksheet, or insert a chart.
Why do I need to know about Events?
Let me get straight into an example to make it clear to you. In the below example, you have worksheet event macro which triggers a code.
Private Sub Worksheet_Activate()
To test this code follow the below steps:
- First of all, right click on the worksheet tab and open “View Code”.
- Now, paste this code into the code module and close your visual basic editor.
Now, every time you activate the worksheet you’ll have a message box which says "Welcome". Before you ask me, let me tell you how this code works…
In this code, you have used the Worksheet_Activate Event which triggers the message box code every time when you activate the worksheet.
The point what I want to make here is: If you want to trigger a macro code when an event happens you need to learn about using EVENTS.
Types of Events
As you know there are several objects in Excel which you can use in VBA and there are multiple events associated with those objects which you can use.
Below is the classification of events based on the objects:
- Application Events: These are events which are associated with Excel application itself.
- Workbook Events: These are events which are associated with the actions happens in a workbook. For example, when you open a workbook, save it, close it.
- Worksheet Events: These events are associated with the action happens in a worksheet, For example, when you enter a value in a cell or when you activate a worksheet.
- Chart Events: These events are associated with the chart sheets (which are different from worksheets). For example, when you add data labels or when you add gridlines to a chart.
- Userform Events: These events are associated with the action happen with a user form. For example, when you click a button from a user form.
- OnTime and OnKey Events: OnTime events are those which can trigger code on a particular point of time and OnKey events are those which can trigger code when a particular key is pressed.
Now it’s time for action.
Let’s create a macro with which you can enter a check mark symbol in a cell by double-clicking on it. Just like you have in the below example.
Here you need to use “BeforeDoubleClick” event of a worksheet. You can learn more about it from here later but RIGHT NOW follow these simple steps:
- First of all, right click on the worksheet tab and select the “View Code” to open the Worksheet’s code module.
- The next thing is to write code to insert a check in the cell by using the double click.
- For this, first of all, select the “Worksheet” from the first drop down you have above the code window and then “BeforeDoubleClick” from the second drop down.
- At this point, you have a blank macro just like below and the nest thing is to write the code.
- In “BeforeDoubleClick”, you need to define two arguments. The first is the target (cell nearest to the mouse pointer when the double-click occurs) and the second is cancel.