An Introduction to the Visual Basic for Application in Microsoft Excel

VBA stands for Visual Basic for Application. It is a source of language to communicate with Excel. Excel understands the command to VBA language, and then acts accordingly.

In this article, we will learn the basics of VBA. Following is a quick summary of what it is all about:-

  • Code: -VBA code is executed to perform the action in VBA code. We write the VBA code in a VBA module.
  • Module: – VBA modules are stored in Excel workbook, but if you want to view and edit a module, it can be done by using Visual Basic Editor.
  • Procedure: – A procedure is a principle element of computer code that performs the same action. In VBA, we have two types of procedures:- Sub Procedure and Function Procedure.
  • Sub:- A sub procedure is an assignment that is accepted but does not give back a result. To write the Sub Procedure, start with Sub then procedure name with parentheses, as shown in the below example:

Sub Test()
Sum = 5 + 6
MsgBox “The total is ” & Sum
End Sub

  • Function:- A function procedure is used to make VBA perform a task to return a result. In below example, we can see that how we can use the function procedure in VBA:-

Function Add(arg1, arg2)
Add = arg1 + arg2
End Function

  • Objects: -VBA is an object-oriented language. We use the various types of objects to perform a task in VBA. Excel provides us with more than 100 classes of objects to manipulate.

For Example: – Excel is an object and it contains other objects such as workbook objects. The workbook objects contain other objects such as worksheet objects and chart objects. A worksheet object contains objects such as range objects, Pivot table objects and so on.

  • Collections: – The collections is a series of items in which all the items can be described the same way and also it is an objects itself. For example, in a workbook, the worksheets collection contains all the worksheets.
  • Object Hierarchy: – An object contains another object and that object can contain another object; that is why we call it an object hierarchy. For example, a workbook contains books and a book contains sheets.
  • Active Object: -If we neglect a specific reference to an object, Excel refers to active objects.
  • Objects Properties: -Every object has its own properties. Properties are like the features of an object. For example:- In below shown code, we can see that range is a VBA object and one of its properties is value

Private Sub CommandButton1_Click()
Range (“A1:A4”)= 10
End Sub

  • VBA Variables: – You can assign values to VBA. As you know, every object has its own properties. Properties are like the features of an object. For example, in below shown code, we can see that range is a VBA object and one of its properties is value.
  • Object Methods: – Object method is an action that an object can perform. For Example, for a range object, we have lot of methods in which Clear format is one of the methods. This method will clear the format from the range.

Next Chapter

Leave a Reply