Visual Basic for Application ( VBA ) is a programming language that allows you to extend the functionality of Excel and automate repetitive tasks. With VBA, you can create macros, forms, custom functions, and interactive applications. In this article, you will learn the basics of VBA in Excel and how to use it to perform simple or complex operations.

How to access VBA in Excel?

To access VBA in Excel, you need to enable the Developer tab in the ribbon. To do this, follow these steps:

• Click on the File button then on Options.
  
• In the Excel Options window, click Customize Ribbon.
  
• In the list of main tabs, check the Developer box and then click OK.

You will then see the Developer tab appear in the ribbon. This tab contains several VBA-related tools, such as the Visual Basic button, the Record Macro button, or the Insert Control button.

The Visual Basic button opens the Visual Basic Editor (VBE), where you create and edit VBA code. The Record Macro button automatically generates VBA code that reproduces the actions you perform in the application. The Insert Control button allows you to add interactive elements to your worksheet, such as buttons, check boxes, or drop-down lists.

How to write VBA code in Excel?

To write VBA code in Excel, you must use the Visual Basic Editor (VBE). To open it, click the Visual Basic button on the Developer tab of the ribbon.

The VBE consists of several windows:

• The Project window displays the hierarchical structure of objects containing VBA code. For example, if you have a workbook named MyWorkbook.xlsm with two sheets named Sheet1 and Sheet2, you will see the objects Sheet1 and Sheet2 as well as an object named ThisWorkbook appear under MyWorkbook.xlsm.
  
• The Properties window displays the properties of the object selected in the Project window. For example, if you select the Sheet1 object, you will see its properties such as its name (Name), its color (Tab.Color), its code (CodeName), etc.
  
• The Code window displays the VBA code associated with the object selected in the Project window. For example, if you select the Sheet1 object, you will see the VBA code that runs when you interact with the Sheet1 worksheet.
  
• The Immediate window displays VBA code results or error messages. For example, if you use the MsgBox function to display a message to the user, you will see the message appear in the Immediate window.
  

To write VBA code, you must follow certain syntax rules:

• Each statement must end with a newline or a colon (:).
• VBA language keywords must be written in upper or lower case, but not the names of objects, properties, or methods, which are case sensitive.
• Comments must be preceded by the apostrophe (') or Rem character and must be written on a separate line or after a statement.
• Character strings must be surrounded by double quotes (“).
• The arithmetic operators are +, -, *, / and ^ (power).
• The logical operators are And, Or, Not, Xor and Eqv.
• The comparison operators are =, <>, <, <=, > and >=.
• The predefined constants are True, False, Null, Empty, Nothing, and vbNullString.

Here is an example of VBA code that displays a message to the user:

Sub Hello() ' This is a MsgBox comment "Hello, I'm Bing!" ' Displays an End Sub message 

How to use Excel spreadsheet functions in VBA?

You can use most Excel spreadsheet functions in your VBA statements. To do this, you must use the following syntax:

Application.WorksheetFunction.FunctionName(argument1, argument2, ...)

For example, if you want to use the SUM function to calculate the sum of the values ​​in the range A1:A10, you can write:

Sub Sum() Dim result As Double ' Declares a variable of type Double result = Application.WorksheetFunction.Sum(Range("A1:A10")) ' Assigns the result of the SUM function to the variable MsgBox result ' Displays the result End Sub

You can also use custom functions that you create yourself in VBA. To do this, you must use the following syntax:

Function FunctionName(argument1 As Type1, argument2 As Type2, ...) As ReturnType ' Declarations and instructions FunctionName = expression ' Assigns to the function the result to be returned End Function

For example, if you want to create a function that calculates the square of a number, you can write:

Function Square(number As Double) As Double ' Declares a function which takes a number as an argument and returns a number Square = number * number ' Assigns the square of the number to the function End Function

You can then use this function in your VBA statements or in your Excel formulas. For example :

Sub Test() Dim x As Double ' Declares a variable of type Double x = 5 ' Assigns the value 5 to the variable MsgBox Square(x) ' Displays the square of x End Sub

How to create macros in Excel?

A macro is a set of VBA instructions that executes all at once. You can create macros to automate recurring or complex tasks in Excel. There are two ways to create macros: by recording them or by writing them.

Record a macro

Recording a macro involves using the Record Macro button on the Developer tab of the ribbon to capture actions you perform in Excel and convert them to VBA code. This is the simplest and fastest way to create a macro, but it can produce poorly optimized or redundant code.

To record a macro, follow these steps:

• Click the Record Macro button on the Developer tab of the ribbon.
  
• In the Record Macro dialog box, give your macro a name, choose a keyboard shortcut if you want, select a location to store your macro (active workbook, new workbook, or personal workbook), and write a description if you want .
  
• Click OK to start recording.
• Perform the actions you want to automate in Excel. For example, if you want to create a macro that formats a table, select the table, style it, sort it alphabetically, etc.
• Click the Stop Recording button on the Developer tab of the Ribbon to finish recording.

You can then run your macro using your chosen keyboard shortcut, by clicking the Macros button in the Developer tab of the Ribbon, or by using the Run button in the VBE.

Write a macro

Writing a macro involves using the VBE to directly type the VBA code that corresponds to the actions you want to automate. This is the most flexible and powerful method for creating a macro, but it requires programming knowledge.

To write a macro, follow these steps:

• Open the VBE by clicking the Visual Basic button on the Developer tab of the ribbon.
  
• In the Project window, select the object where you want to store your macro (active workbook, new workbook, or personal workbook).
• In the Code window, type the VBA code that corresponds to the actions you want to automate. For example, if you want to create a macro that formats a table, type the following code:

Sub FormatTable() ' Selects the table Range("A1:D10").Select ' Applies a style to the table Selection.Style = "Table 1" ' Sorts the table alphabetically by column A ActiveWorkbook.Worksheets("Sheet1" ).Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A10") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook .Worksheets("Sheet1").Sort .SetRange Range("A1:D10") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub

You can then run your macro by using the Run button in the VBE or by assigning it a keyboard shortcut or control.

FAQs

Here are some frequently asked questions about using VBA in Excel:

Private Sub Workbook_Open() ' Runs the Hello macro when opening the workbook Hello End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) ' Runs the AuRevoir macro when closing the workbook AuRevoir End Sub

Sub ShowForm() ' Shows the form UserForm1 UserForm1.Show End Sub

Function FunctionName(argument1 As Type1, argument2 As Type2, ...) As ReturnType ' Declarations and instructions FunctionName = expression ' Assigns to the function the result to be returned End Function

Function Square(number As Double) As Double ' Declares a function which takes a number as an argument and returns a number Square = number * number ' Assigns the square of the number to the function End Function

Sub Test() Dim x As Double ' Declares a variable of type Double x = 5 ' Assigns the value 5 to the variable MsgBox Square(x) ' Displays the square of x End Sub

Function FunctionName(argument1 As Type1, argument2 As Type2, ...) As ReturnType ' Declarations and instructions FunctionName = expression ' Assigns to the function the result to be returned End Function

Function Square(number As Double) As Double ' Declares a function which takes a number as an argument and returns a number Square = number * number ' Assigns the square of the number to the function End Function

Sub Test() Dim x As Double ' Declares a variable of type Double x = 5 ' Assigns the value 5 to the variable MsgBox Square(x) ' Displays the square of x End Sub

=Square(A1) ' Calculates the square of the value of cell A1

Conclusion

You learned in this article how to use Visual Basic for Application (VBA) in Excel. You have seen what VBA is and why to use it, how to access VBA in Excel, how to write VBA code in Excel, how to use Excel worksheet functions in VBA, how to create macros in Excel with VBA, how to create forms in Excel with VBA and how to create custom functions in Excel with VBA. Among the custom functions you can create with VBA is to do a subtraction in Excel . You can now harness the programming power of VBA to customize and automate Excel as needed.