Are you having difficulty in doing your repetitive and routine tasks on Excel? Do you want a particular document to trigger the user for input when it opens? Do you want to reduce the formulas burden used in Excel reports? You can perform these tasks and accomplish a great deal more by using Visual Basic for Applications (VBA) for Excel – a simple, but powerful programming language that you can use to extend Office applications.

 

What is VBA?

Generally, Excel users don’t get into programming, and programmers don’t care about Excel, so the potential of VBA remains unexploited. Visual Basic for Applications is a programming language developed by Microsoft. VBA allows Microsoft Excel users to create small programs that operate within Microsoft Office software programs. You can also learn the Advance Excel and VBA online.

 

When to use VBA in Excel?

 Excel has been a vital part of almost every profession. If you are spending hours every day in Excel doing repetitive tasks, repeating a lot of the same process, then a VBA procedure just might be the solution for you.

You should think about employing VBA if you need to:

• Automate repetitive tasks.
• Create easy ways for users to interact with your spreadsheets.
• Manipulate a large amount of data.

 

Getting Started with VBA in Excel

Before starting, let’s create a file for us to use VBA:

1. Open a new Excel file.
2. Save it as a Micro Enabled Workbook.
3. Select the Developer Tab.
4. Open the VBA Editor.

Now let’s rock and roll with some easy examples to understand the concept of writing code in a spreadsheet using VBA

Display a Message when users open the Excel Workbook

1. In the VBA Editor, Select Insert à New Module.
2. Write this code in the Module Window:

Sub Auto_Open()
MsgBox (“Welcome to the XYZ Workbook.”)
End Sub

3. Save and close the Workbook. Then reopen the Workbook.

 

You should get a prompt message as shown below.

 

 Allow User to Execute another Procedure

1. In the VBA Editor, Select Insert à New Module.
2. Write this code in the Module Window:

Sub UserReportQuery()
Dim UserInput As Long
Dim Answer As Integer
UserInput = vbYesNo
Answer = MsgBox(“Process the XYZ Report?”, UserInput)
If Answer = vbYes Then ProcessReport
End Sub

Sub ProcessReport()
MsgBox (“Thanks for processing the XYZ Report.”)
End Sub

3. Save and navigate back to the Developer tab.
4. Select the “Button” option.
5. Click on a cell and assign the UserReportQuery macro to the button.
6. Now click the button. The following message should display:
7. Click “Yes” or hit Enter.

 

Note: The secondary subroutine, ProcessReport, could be anything.

Whether you’re an occasional user or a power user, I hope that this article provided useful information about what can be accomplished with just a bit of code in Excel spreadsheets. This is just a small part of it, you can do everything possible. It is just how you explore your spreadsheet.

Want to learn more about Advanced Excel and VBA online? Irizpro’s Advanced MS Excel course helps you learn and explore all the concepts of Microsoft Excel.

 

Check out the video on VBA for a better understanding of the tool.

https://www.youtube.com/watch?v=yru0i8xpgw4&t=53s

 

 

“Interested in this course, please fill the form below and we’ll reach out to you”

[hubspot type=form portal=7015933 id=270929b8-c673-4196-8af0-5272d31e38bc]