This introductory Excel VBA tutorial explains what is VBA in Excel, what is the difference between VBA and Macros, and briefly talks about the VB Editor interface and various components:
In our next tutorial on Macros, we will learn that Excel allows us to automate any action by recording. But what if you want to write your own code to implement different actions. VBA gives us this capability using VB Editor.
In this tutorial we will learn what VBA is, the difference between Excel VBA and a Macro, VB Editor Interface, and also touch base on Msgbox, Inputbox, Range functions, and brief about ActiveX controls.
What You Will Learn:
List Of Tutorials In This VBA Series
Tutorial #1: Excel VBA Tutorial – Introduction To VBA In Excel [This Tutorial]
Tutorial #2: Excel Macros – Hands-On Tutorial For Beginners With Examples
Tutorial #3: VBA Data Types – Numeric And Non-Numeric Data Types In VBA
Tutorial #4: VBA Variables And Option Explicit In VBA
Tutorial #5: Excel VBA Array And Array Methods With Examples
Tutorial #6: Conditional Statements: If, Else-If, If-Then And Select Case
Tutorial #7: Excel VBA Functions And Sub Procedures [With Examples]
Tutorial #8: Error Handling In VBA – Tutorial With Practical Examples
What Is Excel VBA
VBA stands for Visual Basic for Applications. VBA is a tool for programming, editing, and running an application code.
VBA is not a standalone code i.e. it can only run the code from within the host application. In our tutorial, it’s going to run all the code in Excel itself.
Difference Between Excel VBA And Macros
Beginners may get confused between the Excel VBA and a Macro. Though both are closely related, they are not the same.
Enlisted below are the differences:
|VBA is a programming language.||A macro is a set of actions that you can automate to perform a desired task.|
|Using VBA you can edit a recorded Macro and add complex code.||You can always record a new Macro.|
|You need to know Syntax and build logic to write a VBA code.||Non Programmer can record any action inside Excel and create a simple macro.|
For instance, if you have written a code and at the end, you realize that you missed adding a piece of code then record a macro and insert that into your original code.
VB Editor Interface
VB Editor allows you to write and edit custom scripts that can automate actions in Excel.
Before you start coding, you have to open the VB editor. Navigate to the Developer tab and click the Visual Basic button. Alternatively, you can use the keyboard shortcut Alt + F11.
Once you click on Visual Basic, the Editor will launch.
Project Explorer: This contains all the sheets in the workbook and the workbook itself. It has a Modules folder that stores all the VBA code. When you record macros, they’re included in a module. To add a new, empty module, click on VBA Project (Book1) -> RightClick -> Insert -> Module.
Properties Window: It contains all the properties of an object like font, color, height, width, etc. Mostly used while working with UserForms.
Code Window: This is where the VBA code appears and hence you can edit the code and also create code from scratch. You can double click on an object or use View Code from the context menu.
Msgbox is used to display a user-specified message in a dialog box. You can customize the dialog based on your choice. VBA provides a lot of options that can help the user to design an intuitive dialog.
Syntax: MsgBox (prompt, [ buttons, ] [ title, ] [ helpfile, context ])
prompt: Required. This is the String message that you want to be displayed in the dialog.
Buttons: Optional, You can create any buttons like Ok, Yes, etc in the dialog. Default is the OK button.
Title: Optional. You can enter the string title of the dialog box.
Helpfile: Optional. You can specify the Help file path to use.
Context: Optional. You can specify the number that is the Help context number.
Consider a simple code as below.
Open the VB Editor (Alt + F11 or Developer tab -> Visual Basic), Right-click on Sheet -> insert -> module. Type in the below code in the editor.
Function msgboxtest() Response = MsgBox("Do you want to submit the form", vbYesNo + vbQuestion + vbMsgBoxHelpButton, "Submit Form", "helpfile.hlp", 1000) End Function
In the above example
Msgbox: This is the keyword that is used to generate the dialog box.
Do you want to submit the form: This is the message to be displayed on the dialog
vbYesNo + vbQuestion + vbMsgBoxHelpButton: These are the set of buttons that will be displayed on the dialog. This will give us a Yes and a No button, A Question mark(?) icon, and a Help button on the message box.
There are many such combinations that you can select from the list VBA provides.
Submit Form: This is the title text of the message box.
helpfile.hlp: This is used to define the help file.
1000: This defines the help context number.
To execute the code, press F5 or the run button on the toolbar.
The output of the code is shown below
VB Input Box
Input Box function is used when a user is expected to enter any values. For example, to get the sum of 2 numbers, the user is expected to enter the 2 numbers.
This function displays a dialog box to allow the user to input values.
Syntax: .InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextID)
Prompt: This is the message that must be displayed in the dialog box.
Title: This is the title of the dialog box.
Default: It displays the default value in the input box.
Left: This is to specify the x position for the dialog box.
Top: This is to specify the y position for the dialog box.
HelpFile: You can specify the Help file to use.
HelpContextID: You can specify the number that is the Help context number.
Function inputboxtest() Dim firstNumber, secondNumber As Integer firstNumber = InputBox("Enter 1st number", "1st Number", 0, 202, 136, "help.hlp", 100) secondNumber = InputBox("Enter 2nd number", "2nd Number", 0, , , "help.hlp", 1000) MsgBox (firstNumber + secondNumber) End Function
Explanation of InputBox(“Enter 1st number”, “1st Number”, 0, 202, 136, “help.hlp”, 100)
Enter 1st number: This is the text displayed in the InputBox.
1st Number: This is the title of the InputBox.
.0– Default value, that is when the inputBox is displayed, 0 will be pre-populated.
202 and 136 give the position of the Input Box.
Help.hlp and 100 is the help file and its corresponding index.
Explanation of InputBox(“Enter 2nd number”, “2nd Number”, 0, , , “help.hlp”, 1000)
As mentioned above “Enter 2nd number”, “2nd Number”, 0 as the text, title, and default values, Here I don’t want to mention a specific position for the input box so I have left it blank. Hence the input box is displayed in the default position
It is always recommended to add brief details of the programs or function so that it becomes easy for anyone trying to understand the logic of the program. Commented lines will be ignored by the compiler and hence are not executed.
To comment a single line you just need to add a single quote (‘) before writing anything as shown in the example below. Commented lines are displayed in Green.
MsgBox (firstNumber + secondNumber) 'Message box displays adding of 2 numbers
If you want to comment a block of code. Go to View -> ToolBar -> Edit
Select a block of code and hit the comment option.
You can even uncomment the block of code in a similar way. Select the block of the commented code and hit the Uncomment option.
VBA Range is a single cell or a group of cells upon which VBA will perform the action specified.
The range is used to specify just a single cell.
Let’s open VB Editor and create a module in the worksheet. Refer to the below code for the single-cell range.
Sub SingleRangetest() Range("B1").Value = "Software Testing Help" End Sub
This will enter the value in cell B1.
This will help you to specify multiple cell values and can perform an action on all the cells at once.
Sub MultipleRangetest() Range("C1:C14").Value = "Software Testing Help" End Sub
This code will enter “Software Testing Help” in cells from c1 to c14.
You can also add different cell ranges as shown in the code below.
Sub MultipleRangetest2() Range("A17:A18", "D18:D19").Value = "Testing Multiple range" End Sub
This will enter the specified value from cell A17 to cell D19.
Refer With Cells Object
This refers to cells using row and column index numbers.
Sub cellobject1() ActiveSheet.Cells(7, 1).Formula = "=Sum(B2:B5)" End Sub
This code will place a formula to sum from B2 to B5 at Cell A7.
Rows In Range
You can use Rows on a worksheet and obtain a range of all rows or a single row on the worksheet.
Sub rowrange1() Worksheets(1).Rows(1).ClearContents End Sub
This method will clear all the contents on the row1 present in worksheet1
You can use the rows using the Item object.
Sub rowrange2() Worksheets(1).Rows.Item(16).Delete End Sub
This will Delete all the items present in row 16.
You can also use a range of rows as shown below.
Sub rowrange3() Worksheets(1).Range("C5:D10").Rows(3).Delete Worksheets(1).Range("A5:B10").Rows.Item(2).Delete End Sub
This will delete the ranges C7:D7 and A6:B6 of the first sheet of the active workbook.
Columns In Range
Similar to rows you can access columns as well.
Sub colrange1() Worksheets(1).Columns(1).ClearContents End Sub
This will clear the content of the first column.
Multiple ranges in Column is quite tricky. Just consider the code below.
Sub colrange2() Worksheets(1).Range("A5:D10").Columns(2).ClearContents 'This will clearcontents of B5 to B10 Worksheets(1).Range("A5:D10").Columns.Item(3).Select 'This will select the cell c5 to C10 Worksheets(1).Range("A5:D10").Columns("D").Delete 'This will Delete the column D5 to D10 Worksheets(1).Range("A5:D10").Columns.Item("B").Value = "Testing range coumn" 'This will enter the value from B5 to B10 End Sub
Offset (row, column), where row and column are the rows and column offsets.
Sub offsetrange() Worksheets("Sheet1").Activate Selection.Offset(3, 1).Range("A1").Select End Sub
This will select the cell three rows down and one column to the right of the cell of the current selection. You have to activate the worksheet.
VBA ActiveX Controls
ActiveX controls provide more flexible design options. Using ActiveX we can customize the appearances of an object. You can also initiate different events and write a code to control those events.
To open ActiveX Controls, head to the Developer tab -> Insert and select ActiveX control of your choice. You need to click on any ActiveX control and draw them in the worksheet.
There are mainly 11 ActiveX Controls available as listed below.
|Command Button||This is used to execute a macro.
It performs an action when user click on the button
|Combo Box||This is used to display the drop down lists.
You can add items to the list of your choice through VBA code.
|Check Box||This is used to display the selection or deselection to the user.|
|List Box||This control shows a list of items from which user can select one or more items at the same time.|
|Text Box||This displays the information entered by user at run time.|
|Scroll Bar||This gives a navigation with large list of items either horizontally or vertically.|
|Spin Button||This is used to increase or decrease a number in the cell.|
|Option Button||This is used to select only one option at a time.|
|Label||This gives a description of the control's purpose.|
|Image||This is used to display graphics.|
|Toggle Button||This is used to hide and unhide the rows.|
You can access different available options for the controls in the Context menu.
Example: You can change the properties like change the caption, font, color, etc, add code for the controls, etc.
Example: Draw a command button, Right-click on the same, and select View Code, this will open the code explorer in the VB Editor. Enter the below code.
Private Sub CommandButton1_Click() MsgBox "This is the command button" End Sub
Now go back to the Excel Sheet and click on the command button, it will automatically execute the code and give you the msgbox.
Picture of all the ActiveX controls is shown below
Q #1) How do you create a yes or no msgbox in VBA?
Answer: Use vbYesNo keyword in the msgbox syntax as shown below.
Function msgboxtest() Response = MsgBox("Do you want to continue", vbYesNo) End Function
Q #2) What is vbInformation in msgbox?
Answer: This gives an information icon on the msgbox. Refer to the code and screenshot below.
Function msgboxtest() Response = MsgBox("Do you want to submit the form", vbYesNo +vbInformation) End Function
Q #3) What is VbOKOnly in VBA?
Answer: The msgbox will only display OK button. In the syntax, if you don’t mention any specific button value then vbOKOnly is chosen by default.
Q #4) What are the types of inputbox?
Answer: There are 2 types of inputbox.
#1) Application.InputBox: Allows you to specify the datatype of the result
Example: Firstnumber = Application.InputBox(“Enter 1st number”, Type:=1)
#2) InputBox: Doesn’t allow to specify the datatype of the result
Example: Firstnumber =inputBox(“Enter number”)
Q #5) How do I assign a macro to an ActiveX button?
Answer: Right-click on the control button -> View code and then you can enter the required action to be performed for that particular button.
In this tutorial, we learned how to use VB Editor and also looked at the different components along with their purpose. We also understood the basic differences between Excel VBA and a macro.
Further reading =>> Functions and Subprocedures in VBA
The functionality of Msgbox, InputBox, and the different ways of using Range was also discussed. We explored ActiveX controls and saw how to add them to an Excel sheet, along with their options.