Home ➜ VBA
What is VBA?
VBA (Visual Basic for Applications) is a programming language that empowers you to automate almost every in Excel. With VBA, you can refer to the Excel Objects and use the properties, methods, and events associated with them. For example, you can create a pivot table, insert a chart, and show a message box to the user using a macro.
The crazy thing is:
For all the tasks which you perform manually in minutes, VBA can do it in seconds, with a single click, with the same accuracy. Even you can write VBA codes that can run automatically when you open a document, a workbook, or even at a specific time.
Let me show you a real-life example:
Every morning when I go to the office, the first thing I need to do is to create a pivot table for the month-to-date sales and present it to my boss. This includes the same steps, every day. But when I realized that I can use VBA to create a pivot table and insert it in a single click, it saved me 5 minutes every day.
Note: VBA is one of the Advanced Excel Skills.
How VBA Works
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:
Box.Open
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:
Boxes(“Red”).Open
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 that is set to TRUE.
What is VBA used for in Excel?
In Excel, you can use VBA for different things. Here are a few:
- Enter Data: You can enter data in a cell, range of cells. You can also copy and paste data from one section to another.
- Task Automation: You can automate tasks that want you to spend a lot of time. The best example I can give is using a macro to create a pivot table.
- Create a Custom Excel Function: With VBA, you can also create a Custom User Defined Function and use it in the worksheet.
- Create Add-Ins: In Excel, you can convert your VBA codes into add-ins and share them with others as well.
- Integrate with other Microsoft Applications: You can also integrate Excel with other Microsoft applications. Like, you can enter data into a text file.
Excel Programming Fundamentals
A procedure in VBA is a set of codes or a single line of code that performs a specific activity.
- SUB: Sub procedure can perform actions but doesn’t return a value (but you can use an object to get that value).
- Function: With the help of the Function procedure, you create your function, which you can use in the worksheet or the other SUB and FUNCTION procedures (See this: VBA Function).
2. Variables and Constants
You need variables and constants to use values in the code multiple times.
- Variable: A Variable can store a value, it has a name, you need to define its data type, and you can change the value it stores. As the name suggests, “VARIABLE” has no fixed value. It is like a storage box that is stored in the system.
- Constant: A constant also can store a value, but you can’t change the value during the execution of the code.
3. Data Types
You need 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. If you omit the data type, VBA applies the Variant data type to your variable (it’s the most flexible), VBA won’t guess what the data type should be.
Tip: VBA Option Explicit
4. Objects, Properties, and Methods
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 has different objects, and with all those objects, there are several properties that you can access and methods that you can use.
5. Events
Whenever you do something in Excel, that’s an event: enter a value in a cell, insert a new worksheet, or insert a chart. Below is the classification of events based on the objects:
- Application Events: These are events that are associated with Excel application itself.
- Workbook Events: These are events that are associated with the actions that happen in a workbook.
- Worksheet Events: These events are associated with the action happens in a worksheet.
- Chart Events: These events are associated with the chart sheets (which are different from worksheets).
- Userform Events: These events are associated with the action happen with a user form.
- OnTime Events: OnTime events are those which can trigger code on a particular point of time.
- OnKey Events: OnKey events are those which can trigger code when a particular key is pressed.
6. Range
The range object is the most common and popular way to refer to a range in your VBA codes. You need to refer to the cell address, let me tell you the syntax.
Worksheets(“Sheet1”).Range(“A1”)
7. Conditions
Just like any other programming language, you can also write codes to test conditions in VBA. It allows you to do it in two different ways.
- IF THEN ELSE: It’s an IF statement that you can use to test a condition and then run a line of code if that condition is TRUE. You can also write nesting conditions with it
- SELECT CASE: In select case, you can specify a condition and then different cases for outcomes to test to run different lines of code to run. It’s a little more structured than the IF statement.
8. VBA Loops
You can write codes that can repeat and re-repeat an action and in VBA, and there are multiple ways that you can use to write code like this.
- For Next: The best fit for using For Next is when you want to repeat a set of actions for a fixed number of times.
- For Each Next: It’s perfect to use when you want to loops through a group of objects from a collection of objects.
- Do While Loop: The simple idea behind the Do While Loop is to perform an activity while a condition is true.
- Do Until Loop: In the Do Until, VBA runs a loop and continue to run it if the condition is FALSE.
9. Input Box and Message Box
- Input Box: Input Box is a function which shows an input box to the user and collects a response.
- Message Box: Message Box helps you show a message to the user but, you have an option to add buttons to the message box to get the response of the user.
10. Errors
Excel has no luck when it comes to programming errors, and you have to deal with them, no matter what.
- Syntax Errors: It’s like typos that you do while writing codes, but VBA can help you by point out these errors.
- Compile Errors: It comes when you write a code to perform an activity, but that activity is not valid.
- Runtime Errors: A RUNTIME error occurs at the time of executing the code. It stops the code and shows you the error dialog box.
- Logical Error: It’s not an error but a mistake while writing code and sometimes can give you nuts while finding and correcting them.
Write a Macro (VBA Program) in Excel
I have a strong belief that in the initial time when someone is starting programming in Excel, HE/SHE should write more and more codes from scratch. The more codes you write from scratch, the more you understand how VBA works.
But you need to start with writing simple codes instead of jumping into a complex ones. That’s WHY I don’t want you to think about anything complex right now.
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 that you want to perform in your worksheet, and you can write a code for it.
- Go to the Developer Tab and open the Visual Basic Editor from the “Visual Basic” button.
- After that, insert a new module from the “Project Window” (Right-click ➢ Insert ➢ Module).
- 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.
- From here, 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 wish to enter.
- Enter the cell reference, and for this, you need to use RANGE object and specify the cell address in it, like below:
- After that, enter a dot, and the moment you add a dot, you’ll have a list of properties that you can define and activities that you can do with the range.
- From here, you need to select the “Value” property and set the text which you want to insert in the cell “A1” and when to do it, your code with look something like below.
- Finally, above the line of code, enter the text (‘this code enters the value “Done” in the cell A5). It’s a VBA Comment that you can insert to define the line of code that you have written.
Sub Enter_Done()
'this code enters the value “Done” in the cell A5
Range("A1").Value = "Done"
End Sub
Let’s understand this…
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).
- In the SECOND part, we have specified the value to enter into the cell. What you have done is, you have defined the value property for the cell A5 by using “.Value”. After that, the next thing that you have specified is 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.
The Best Way to Learn VBA
Here I have listed some of the most amazing tutorials (not in any particular sequence) that can help you learn VBA in NO TIME.
- Count Rows using VBA in Excel
- Declare Global Variable (Public) in VBA
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- How to Add a Comment in a VBA Code (Macro)
- How to Add a Line Break in a VBA Code (Single Line into Several Lines)
- How to Add a New Line (Carriage Return) in a String in VBA
- How to Apply Borders on a Cell using VBA in Excel
- How to Check IF a Sheet Exists using VBA in Excel
- How to CLEAR an Entire Sheet using VBA in Excel
- How to Copy and Move a Sheet in Excel using VBA
- How to COUNT Sheets using VBA in Excel
- How to Create a User Defined Function [UDF] in Excel using VBA
- How to DELETE a SHEET using VBA in Excel
- How to Find Last Row, Column, and Cell using VBA in Excel
- How to Get Today’s Date and Current Time using VBA
- How to Hide & Unhide a Sheet using VBA in Excel
- How to Insert a Row using VBA in Excel
- How to Loop Through All the Sheets using VBA in Excel
- How to Merge Cells in Excel using a VBA Code
- How to PROTECT and UNPROTECT a Sheet using VBA in Excel
- How to RENAME a Sheet using VBA in Excel
- How to Run a Macro in Excel (Run a VBA Code)
- How to Select a Range/Cell using VBA in Excel
- How to SELECT ALL the Cells in a Worksheet using a VBA Code
- How to Sum Values in Excel using VBA
- How to use a Range or a Cell as a Variable in VBA
- How to use ActiveCell in VBA in Excel
- How to use Option Explicit Statement in VBA
- How to use Special Cells Method in VBA in Excel
- How to use UsedRange Property in VBA in Excel
- How to Write a VBA Code to Create a New Sheet in Excel (Macro)
- Personal Macro Workbook (personal.xlsb)
- How to use MOD in VBA
- How to use Match Function in VBA
- How to Copy an Excel File (Workbook) using VBA
- Random Number
- Record a Macro in Excel
- Top VBA Functions
- Variable in a Message Box
- VBA ABS Function (Get Absolute Value)
- VBA Activate Workbook (Excel File)
- VBA Add New Value to the Array
- VBA Array
- VBA ARRAY Function
- VBA Array Length (Size)
- VBA Array with Strings
- VBA ATN Function (Syntax + Example)
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA Automation Error (Error 440)
- VBA Calculate (Cell, Range, Row, & Workbook)
- VBA CBOOL Function (Syntax + Example)
- VBA CBYTE Function (Syntax + Example)
- VBA CCUR Function (Syntax + Example)
- VBA CDATE Function (Syntax + Example)
- VBA CDBL Function (Syntax + Example)
- VBA CDEC Function (Syntax + Example)
- VBA Check IF a Cell is Empty + Multiple Cells
- VBA Check IF a Workbook Exists in a Folder (Excel File)
- VBA Check IF a Workbook is Open (Excel File)
- VBA CHR Function (Syntax + Example)
- VBA CINT Function (Syntax + Example)
- VBA Clear Array (Erase)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA CLNG Function (Syntax + Example)
- VBA Close Workbook (Excel File)
- VBA Combine Workbooks (Excel Files)
- VBA Concatenate
- VBA Constants
- VBA Copy Range to Another Sheet + Workbook
- VBA COS Function (Syntax + Example)
- VBA Create and Write to a Text File
- VBA Create New Workbook (Excel File)
- VBA CSNG Function (Syntax + Example)
- VBA CSTR Function (Syntax + Example)
- VBA CVAR Function (Syntax + Example)
- VBA CVERR Function (Syntax + Example)
- VBA DATE Function (Syntax + Example)
- VBA DATEADD Function (Syntax + Example)
- VBA DATEPART Function (Syntax + Example)
- VBA DATESERIAL Function (Syntax + Example)
- VBA DATEVALUE Function (Syntax + Example)
- VBA DAY Function (Syntax + Example)
- VBA DDB Function (Syntax + Example)
- VBA Delete Workbook (Excel File)
- VBA Dim Statement
- VBA Do While Loop – The Ultimate Guide with Examples
- VBA Dynamic Array
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Error 400
- VBA ERROR Handling
- VBA Exit IF
- VBA Exit Sub Statement
- VBA EXP Function (Syntax + Example)
- VBA FileDateTime Function
- VBA FILTER Function (Syntax + Example)
- VBA FIX Function (Syntax + Example)
- VBA FOR LOOP (For Next, For Each) – The Guide + Examples
- VBA FORMAT Function (Syntax + Example)
- VBA FORMATCURRENCY Function (Syntax + Example)
- VBA FORMATDATETIME Function (Syntax + Example)
- VBA FORMATNUMBER Function (Syntax + Example)
- VBA FORMATPERCENT Function (Syntax + Example)
- VBA FV Function (Syntax + Example)
- VBA GoTo Statement
- VBA HEX Function (Syntax + Example)
- VBA HOUR Function (Syntax + Example)
- VBA IF – IF Then Else Statement
- VBA IF And (Test Multiple Conditions)
- VBA IF Not
- VBA IF OR (Test Multiple Conditions)
- VBA IIF Function (Syntax + Example)
- VBA Immediate Window (Debug.Print)
- VBA Insert Column (Single and Multiple)
- VBA INSTR Function (Syntax + Example)
- VBA INSTRREV Function (Syntax + Example)
- VBA INT Function (Syntax + Example)
- VBA Interview Questions
- VBA Invalid Procedure Call Or Argument Error (Error 5)
- VBA IPMT Function (Syntax + Example)
- VBA IRR Function (Syntax + Example)
- VBA ISARRAY Function (Syntax + Example)
- VBA ISDATE Function (Syntax + Example)
- VBA ISEMPTY Function (Syntax + Example)
- VBA ISERROR Function (Syntax + Example)
- VBA ISMISSING Function (Syntax + Example)
- VBA ISNULL Function (Syntax + Example)
- VBA ISNUMERIC Function (Syntax + Example)
- VBA ISOBJECT Function (Syntax + Example)
- VBA JOIN Function (Syntax + Example)
- VBA LBOUND Function (Syntax + Example)
- VBA LCASE Function (Syntax + Example)
- VBA LEFT Function (Syntax + Example)
- VBA LEN Function (Syntax + Example)
- VBA LOG Function (Syntax + Example)
- VBA Loop Through an Array
- VBA LOOPS (Beginner to Advanced) – A Guide
- VBA LTRIM Function (Syntax + Example)
- VBA MID Function (Syntax + Example)
- VBA MINUTE Function (Syntax + Example)
- VBA MIRR Function (Syntax + Example)
- VBA Module | Insert, Remove, Restore, and BackUp
- VBA MONTH Function (Syntax + Example)
- VBA MONTHNAME Function (Syntax + Example)
- VBA MSGBOX – A Complete Guide to Message Box Function + Examples
- VBA Multi-Dimensional Array
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Nested IF
- VBA NOW Function (Syntax + Example)
- VBA NPER Function (Syntax + Example)
- VBA NPV Function (Syntax + Example)
- VBA Object Doesn’t Support this Property or Method Error (Error 438)
- VBA Object Required Error (Error 424)
- VBA Objects
- VBA OCT Function (Syntax + Example)
- VBA Open Workbook (Excel File)
- VBA Out of Memory Error (Error 7)
- VBA Overflow Error (Error 6)
- VBA PMT Function (Syntax + Example)
- VBA PPMT Function (Syntax + Example)
- VBA Protect/Unprotect Workbook (Excel File)
- VBA PV Function (Syntax + Example)
- VBA Range Object – Working with Range and Cells in VBA
- VBA Range Offset
- VBA Range to an Array
- VBA RATE Function (Syntax + Example)
- VBA Rename Workbook (Excel File)
- VBA REPLACE Function (Syntax + Example)
- VBA RIGHT Function (Syntax + Example)
- VBA RND Function (Syntax + Example)
- VBA ROUND Function (Syntax + Example)
- VBA RTRIM Function (Syntax + Example)
- VBA Runtime Error (Error 1004)
- VBA Save Workbook (Excel File)
- VBA ScreenUpdating | How to Turn it ON and OFF
- VBA Search for a Value in an Array
- VBA SECOND Function (Syntax + Example)
- VBA SELECT CASE Statement (Test Multiple Conditions)
- VBA SGN Function (Syntax + Example)
- VBA SIN Function (Syntax + Example)
- VBA SLN Function (Syntax + Example)
- VBA Sort Array
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA SPACE Function (Syntax + Example)
- VBA SPLIT Function (Syntax + Example)
- VBA SQR Function (Syntax + Example)
- VBA Status Bar (Hide, Show, and Progress)
- VBA STR Function (Syntax + Example)
- VBA STRCOMP Function (Syntax + Example)
- VBA STRCONV Function (Syntax + Example)
- VBA STRING Function (Syntax + Example)
- VBA STRREVERSE Function (Syntax + Example)
- VBA Subscript Out of Range Runtime Error (Error 9)
- VBA SYD Function (Syntax + Example)
- VBA TAN Function (Syntax + Example)
- VBA ThisWorkbook (Current Excel File)
- VBA TIME Function (Syntax + Example)
- VBA TIMER Function (Syntax + Example)
- VBA TIMESERIAL Function (Syntax + Example)
- VBA TIMEVALUE Function (Syntax + Example)
- VBA TRIM Function (Syntax + Example)
- VBA Type Mismatch Error (Error 13)
- VBA UBOUND Function (Syntax + Example)
- VBA UCASE Function (Syntax + Example)
- VBA VAL Function (Syntax + Example)
- VBA Variables (Declare, Data Types, and Scope)
- VBA Wait and Sleep Commands to Pause and Delay
- VBA WEEKDAY Function (Syntax + Example)
- VBA WEEKDAYNAME Function (Syntax + Example)
- VBA With Statement (With – End With)
- VBA Workbook – A Guide to Work with Workbooks in VBA
- VBA Worksheet Function (Use Excel Functions in a Macro)
- VBA Worksheet Object -Working with Excel Worksheet in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA YEAR Function (Syntax + Example)