Excel VBA: Power Tips to Revolutionize Your Data Analysis (2024)

What Is VBA?

Visual Basic for Application is a human-readable and editable programming code that gets generated when you record a macro. Today, it is widely-used with other Microsoft Office applications such as MS-Word, MS-Excel, and MS-Access.

Enable Developer Option in Excel

It hides the Developer tab on the ribbon by default. To customize the ribbon, follow the steps mentioned below:

• Right-click on the ribbon (anywhere) click on the Customize the Ribbon option.

• Go to Customize the Ribbon and select the Developer checkbox.

VBA Editor Interface

You can open the VBA interface by using the ALT + F11 keyboard shortcut, or you can go to the Developer tab and click on Visual Basic.

Create an Excel Macro using a Command Button

Create a Command Button

Now that you have enabled the developer tab and have some familiarity with the VBA editor, let’s start creating a macro by using a command button.

To place a command button on your worksheet, follow these steps:

• Go the Developer tab > Insert > ActiveX Controls > Command button.

• Drag the command button on your worksheet.

Assigning a Macro to a Command Button

To assign a macro to the command button, follow these steps:

• Right-click on the command buttons and select View Code.

• Add the following lines of code shown below.

• Close the VBA editor and click on the command button on the worksheet. Make sure to deselect the design mode.

Create a Message Box

The MsgBox is a dialog box in Excel that will prompt a message on your worksheet. To create a MsgBox, add the following lines of code to the command button.

Now, when you click on the button, you will get the following message.

Create an Input Box

The InputBox function prompts the user to enter the values and returns the information entered in the dialog box.

Syntax:

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])

To add the InputBox to your command button, execute the following steps:

• Declare the variable name and keep the type variant. A variant variable can hold any type of value.
• Add the following code to show the input box.

• Now when you click on the command button, you will get a prompt asking for a value.

• Enter your input and click OK. It will enter your input on cell A1.

Variables, Constant and Operators in VBA

Variables

Variables are the data types that are used to hold a value. We can change the variables during the execution of the program.

Syntax: Dim <<variable_name>> As <<variable_type>>

We can divide the VBA data types into two categories:

• Numeric Data Types: Numeric data types consist of byte, integer, long, single, double, currency, and decimal.
• Non-Numeric Data Types: Non-numeric data types consist of string, date, boolean, object, and variant.

Constants

Constants are the fixed value that cannot be changed during the execution of the program.

Syntax:

Const <<constant_name>> As <<constant_type>> = <<constant_value>>

Example:

When you click on the command button, you will get the following output:

String Manipulation

String manipulation refers to the process of analyzing, manipulating, and effectively handling string values.

Join Strings

You can join the two strings by using & operator. This is also known as concatenation.

Click on the command button to get the following result.

Left

The left keyword will extract the characters from the leftmost side of the string.

If, If-Else, For and While Loop

If statement

The ‘If statement’ is a conditional statement that consists of an expression followed by another expression. If the condition is true, the lines of code under the If statement are executed.

Syntax:

If(boolean_expression) Then

Statement 1

.....

.....

Statement n

End If

Example:

Enter the marks in cell A1 and click on the command button to get the result.

If-Else

The ‘If statement’ is a conditional statement that consists of an expression followed by another expression. If the condition is true, the lines under the body of the If statement are executed. If it says the condition to be False, it executes the statements under the Else Part.

For Loop

For loop is a control flow statement that allows the user to write a loop that can be executed repeatedly.

Example:

The following code will prompt the message box showing the value from 0 to 6 with the gap of 2.

While Loop

In a while loop, if the statements are true, they are executed till they encounter the Wend keyword. If the statement is false, the loop is exited, and it jumps to the next statement.

Syntax:

While condition(s)

[statements 1]

[statements 2]

...

[statements n]

Wend

Example:

Functions and Sub Procedures

Functions

Functions are the reusable code that can be called anywhere in the program. You can use the code over and over again in your program. To create the function in the VBA window, go to Insert > Module and it will create a new module.

The following code shows an example of a simple VBA function procedure that receives two arguments.

The below-given code calls the function that was defined above:

Result:

Sub-Procedure

Sub procedures are similar to functions with some minor differences. The sub-procedure does not return a value and can be called without a call keyword.

The following code shows an example of a simple VBA Sub procedure that will calculate the area.

The above example illustrates how sub procedures perform actions but do not return values.

Result:

Conclusion

With this, you have made it to the end of this Excel VBA tutorial for beginners. In this tutorial, we have covered several topics ranging from VBA editor to functions and sub-procedures.

Boost your analytics career with powerful new Microsoft Excel skills by taking the Post Graduate Program in Business Analysis course, which includes Power BI training

This Business Analytics certification course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

Do you have any questions for us? Feel free to ask them in this article’s comments section, and our experts will promptly answer them for you!

FAQs

1. What is Excel VBA?

Excel VBA, short for Visual Basic for Applications, is a programming language that empowers users to automate tasks and create personalized solutions within Microsoft Excel.

It enables users to create macros, which are instructions that automatically perform repetitive tasks.

2. How do I run VBA in Excel?

To execute VBA code in Excel, simply press "Alt + F11" to access the VBA editor. Then, select the macro or code you want to run and click the "Run" button or press "F5."

3. Where is VBA code in Excel?

VBA code in Excel is accessed through the VBA editor. To open it, press "Alt + F11," and you'll find your VBA code modules listed in the Project Explorer window.

4. Is VBA necessary for Excel?

VBA is not necessary for regular Excel usage, but it becomes essential when you want to automate tasks, create custom functions, or build more advanced solutions.

5. What is the difference between Excel and VBA?

Excel is a spreadsheet software used for data analysis and calculations, while VBA is a programming language used to extend Excel's functionality and automate tasks.

6. What is VBA coding?

VBA coding refers to writing instructions in the Visual Basic for Applications (VBA) language to create macros, automate tasks, and add custom functionality to Excel.

7. What is the use of VBA in Excel?

VBA in Excel is used to automate repetitive tasks, build custom functions, create user-defined forms, manipulate data, and add interactive features to workbooks.

8. Is Excel VBA a good skill?

Yes, Excel VBA is a valuable skill, especially for professionals working with data analysis, finance, or project management. It can significantly improve productivity and open up opportunities for more complex tasks.

9. Is Excel VBA easy to learn?

The difficulty of learning Excel VBA depends on your programming background. For beginners, it may take some time to grasp the concepts, but with practice and resources, it can be learned effectively.

10. What language does VBA use?

VBA uses the Visual Basic programming language, which is a version of the Visual Basic language adapted for automation and customization in Microsoft Office applications like Excel.

11. Is VBA free with Excel?

Yes, VBA is included for free in Excel. It is a built-in feature of Microsoft Excel and is available in all versions of Excel, including the Office 365 subscription. Users can start using VBA without additional cost.