Beginner's Guide to Using VBA Code in Excel

Introduction: 

Visual Basic for Applications (VBA) is a powerful programming language that allows users to automate tasks, create custom functions, and extend the functionality of Microsoft Excel. VBA may seem intimidating to newbies, but this guide aims to introduce step by step how to use this VBA code in Excel. By the end of this guide, you'll have the basic knowledge to start exploring and exploiting the possibilities of VBA.

  1.  Get started with VBA:

To access the VBA editor in Excel, press Alt + F11 on your keyboard or go to the Developer tab and click Visual Basic. This will open a VBA editor window where you can create and edit VBA code.

  2. Familiarize yourself with the VBA editor.

The VBA editor consists of several components.

- Project explorer: - View various modules and objects in your workbook.

- Code window: -Where you create and edit the VBA code.

- Immediate Window: - Useful for debugging and running code line by line.

- Toolbar: - Provides quick access to commonly used functions.

 3. Write your first VBA code.

Example 1:-

Let's start with a simple example. 

On the VBA editor double click on "This workbook" and enter the following code in the code window.

 

Sub HelloExcel()

MsgBox "Hello, Excel!"

end sub

 

This code creates a new subroutine named "HelloExcel" that displays a message box with the text "Hello, Excel!"when run. To run the code, close the VBA editor and return to Excel. Press "Alt + F8" to open the "Macro" dialog box, select "HelloExcel" and click "Run".

 

If you don't know how to use macros please read This Blog on Macros

Example 2: -

Working with Objects

VBA lets you to interact with objects in Excel, such as  Worksheets, ranges, cells, charts, etc. To work with  an object, you must declare a variable of the appropriate object type and use methods and properties. An example is shown below.

 

Sub ModifyWorksheet()

Dim ws As worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1")

ws.Range("A1").Value = "Hello VBA!"

End sub

 

This code declares a variable named "ws" as a worksheet object and assigns it to a worksheet named "Sheet1" in the current workbook. Then use the "Range" property to access cell A1 in this worksheet and assign it the value "Hello, VBA!".

 

Example 3: -

Create custom functions:

 

To use this custom function in Excel, follow these steps: -

1. Open Excel and press Alt + F11 to access the VBA editor.

2. Click Insert from the menu and select “Module” to insert a new module.

3. Copy and paste the below function code into your module.

4. Close the VBA editor.

 

Function AddNumbers(num1 As Integer, num2 As Integer) As Integer

AddNumbers = num1 + num2

End Function

 

This code defines a custom function called “AddNumbers” that takes two integer parameters and returns their sum. This function can be used in Excel formulas like any other built-in function.

To use this custom function, do the following:

1. Type ='AddNumbers'(A1,B1,)' in an Excel cell, replacing 'A1' and 'B1' with the cell references for the numbers you want to sum.

2. Press Enter to calculate the sum using the custom function.

The custom function ='AddNumbers' calculates the sum of the specified numbers and displays the result in a cell.  

Example 4: -

Custom function to calculate the average of 3 numbers using VBA:

Follow the same process as in Example 3 and paste the following code.

 

Function CalculateAverage(num1 As Double, num2 As Double, num3 As Double) As Double

CalculateAverage = (num1 + num2 + num3) / 3

End Function

In this example, the “CalculateAverage” function receives three "Double" parameters ("num1", "num2", "num3"). Calculates the average of 3 numbers by adding the 3 numbers and dividing the result by 3. The average value is returned as a `Double` data type.

The “CalculateAverage” custom function calculates the average of three specified numbers and displays the result in a cell.

You can change the function code and customize it according to your specific needs. For example, you can change the number of parameters, customize the formula to calculate a different kind of average (weighted average, median, etc.), include additional validation or error handling logic, and so on.

 

Note: -Be sure to save your Excel file as a macro-enabled workbook (extension .xlsm) to ensure your custom functions work when you reopen the file.

Using custom functions in VBA gives you more flexibility and allows you to create custom calculations not available with built-in Excel functions.

Conclusion:- 

Learning VBA opens up new possibilities for automating Excel tasks and increasing your productivity. This beginner's guide provided a basic understanding of the VBA editor, writing VBA code, working with variables and data types, working with objects, and using control structures. Gain confidence through practice and exploration to unlock the true potential of VBA in Excel.  

                                        Click Here to Download latest MS Office