Excel VBA Tutorial – Introduction To VBA In Excel

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.

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

Tutorial #9: Excel VBA Loops – For Next, For Each, Do While, Do Until

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:

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.

VB MsgBox

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

VB Comment

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

VBA Range is a single cell or a group of cells upon which VBA will perform the action specified.

Single Cell

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.

Multiple Cell

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

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.

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

FAQs

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.

Conclusion

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.

NEXT Tutorial