Excel used to be an application used mainly for accounting and finance tasks. But not anymore. With the introduction of VBA, Excel became a powerful tool for automating any type of task. VBA allows you to automate repetitive tasks and customize Excel's functionality to suit your needs.

When it comes to salary and career, becoming an expert in VBA can open up a range of opportunities, particularly in roles related to data analysis, automation, and business process improvement. Proficiency in Professional Excel development using VBA allows you to tackle complex tasks, build custom solutions, and create efficiency gains within an organization.

This post will help you understand the fundamentals of VBA, aiming to guide beginners understand and apply VBA to maximize Excel even more.

VBA Basics

First, let's understand the basics of VBA. The abbreviation VBA stands for Visual Basic for Applications. It is a programming language built into Microsoft Office applications like Excel. VBA allows you to automate tasks and customize Excel with your own functions and macros.

On the other hand, the VBA Editor is where you write and manage VBA code. To open the VBA Editor in Excel, press Alt + F11 or go to the Developer tab and click Visual Basic.

The VBA Editor has three main panes:

• The Project Explorer shows all open workbooks and modules.
• The Code window is where you write and edit VBA macros and functions.
• The Immediate window allows you to test snippets of VBA code.

Within the Code window, you can navigate using various objects like Workbooks, Worksheets and Range. You can also declare Variables and write procedures like Subroutines and Functions.

Creating Your First Macro

Macro refers to a set of instructions that are executed automatically to perform a task. You can create macros to automate repetitive tasks in Excel.

1. Enable the Developer Tab

• Before you can create or record a macro, you'll need access to the Developer tab in Excel.
• To enable it, go to File > Options > Customize Ribbon. In the right panel, check the box for Developer and click OK. The Developer tab should now appear in your Excel ribbon, giving you access to the tools needed for macro creation.

2. Recording a Simple Macro

• Once the Developer tab is enabled, click on Record Macro from the Developer tab. A dialog box will pop up.
• Name your macro something easy to remember, as this will be how you identify it later. Avoid spaces in the name, and you can include an optional description of what the macro will do.
• Choose where to store the macro. The options are:
• This Workbook: The macro will only be available in the current workbook.
• New Workbook: The macro will be available only in a new workbook.
• Personal Macro Workbook: This option allows you to use the macro in any workbook you open.
• Optionally, assign a keyboard shortcut to make running the macro faster.

3. Perform Actions to be Automated

• Once you start recording, Excel will track every action you take. Perform the tasks you want to automate, such as entering data, formatting cells, or creating charts. For example, you might:
• Type a set of data.
• Format columns (bold, colors, etc.).
• Insert formulas.

4. Stop Recording the Macro

• After you’ve completed the actions, you want to automate, go back to the Developer tab and click Stop Recording. Excel will no longer record your actions, and your macro is now saved.

5. Run the Macro

• To run the macro, go back to the Developer tab and click Macros. You’ll see a list of all your macros. Select the one you just recorded and click Run.
• Alternatively, if you assigned a keyboard shortcut, press the shortcut to run the macro instantly.

6. Viewing and Editing the Macro Code

• If you're curious to see how your macro works, or if you want to make adjustments, click on Visual Basic in the Developer tab. This will open the VBA Editor, where you can see the code that was recorded. Each action you perform is translated into VBA (Visual Basic for Applications) code.
• You can manually edit the code here to refine your macro or add advanced functionalities.

7. Save the Workbook with Macros

• When saving your workbook with macros, choose the Excel Macro-Enabled Workbook (.xlsm) file type. This ensures that your macros are saved and can be used again.

This simple macro demonstrates the basics of creating a macro in Excel VBA. It uses the Sub keyword to define a macro named FirstMacro and the MsgBox function to display a simple message. From here, you can expand your macro by adding more functionality.

Writing Your First VBA Code

Writing your first VBA code in Excel might seem tricky at first, but it's simpler than it looks when broken down into steps. First, enable the Developer tab in Excel, which allows you to access the VBA editor. You can do this by going to File > Options > Customize Ribbon, and then checking the Developer tab option.

Once the Developer tab is visible, follow these steps:

1. Open the VBA editor: In the Developer tab, click Visual Basic to open the editor.
2. Insert a new module: Inside the editor, go to Insert > Module. This is where you’ll write your code.
3. Write a simple subroutine: Start with a basic task like displaying a message box when you open your workbook. For example, you can type:

Sub WelcomeMessage()

MsgBox "Hello, welcome to your first VBA code!"

End Sub

1. This subroutine shows a pop-up message when you run it.
2. Run your code: Close the editor and go back to Excel. In the Developer tab, click Macros, select your subroutine WelcomeMessage, and click Run. You’ll see your message pop up!

These steps will give you a taste of what VBA can do. As you become more comfortable, you can explore more complex tasks like automating repetitive processes or interacting with Excel's data.

Core VBA Concepts

As mentioned earlier, learning the core concepts of VBA will help you write more advanced code in the future. Here are some of the most important ones:

Variables and Data Types

Variables allow you to store and manipulate data in your code. You first declare a variable with a Dim statement, giving it a name and data type. For example:

Dim name As String

Dim age As Integer

Here, name is a string variable and age is an integer. Other common data types in VBA include:

• String - Text data
• Integer - Whole numbers
• Long - Larger whole numbers
• Double - Decimal numbers

You then assign values to your variables:

name = "John"

age = 30

And use them in your code:

MsgBox "Hello " & name & "! You are " & age & " years old."

This would show:

“Hello John! You are 30 years old.”

Variable naming follows these rules:

• Start with a letter
• Can contain letters, numbers and underscores
• Cannot contain spaces
• Are case-sensitive

Control Structures

Control structures allow you to control the flow of your code. They include:

If/Then Statements

You can execute certain code conditionally using If statements:

If age > 30 Then

  MsgBox "You are over 30!"

End If

This will only show the message box if the condition age > 30 is true.

Select Case Statements

The Select Case statement allows you to check multiple conditions:

Select Case age

   Case Is < 18

      MsgBox "You are a minor."

   Case Is < 30

      MsgBox "You are a young adult."

   Case 30 To 50

      MsgBox "You are an adult."  

   Case Else

      MsgBox "You are above 50."

End Select

This will show different messages depending on the age variable.

Loops

Loops allow you to repeat code while a condition is true. Common loops in VBA include:

• For...Next loops for repeating a fixed number of times
• Do...While/Until loops for repeating while/until a condition is true
• While...Wend loops for the same.

Error Handling

Even the most carefully written code can encounter errors during runtime. VBA has several ways to handle errors:

On Error Statements

The On Error statement allows you to specify what should happen when an error occurs:

• On Error Resume Next - Resumes execution at the next statement
• On Error GoTo 0 - Uses VBA's default error handling
• On Error GoTo label - Jumps to a specified label

For example:

On Error GoTo ErrorHandler

'Some code that may cause an error

Exit Sub

ErrorHandler:

MsgBox "An error occurred!"

End Sub

Error Number

The Err.Number property contains the error number. You can check this to respond to specific errors.

Error Description

The Err.Description property contains a description of the error, which you can show to the user.

Conclusion: Take Your VBA Skills To The Next Level

It's not surprising that Excel is so widely used in businesses today. By learning VBA, you can take your Excel skills to the next level and automate tasks that would otherwise be tedious and time-consuming.

Do you want to enhance your Excel skills and automate routine tasks? Intellezy offers courses and materials that will help you take your Excel VBA expertise to the next level. Request a free trial now and check out our resources for yourself and see how we can help you excel in Excel.