Closure
Closure
Home » Business Intelligence » Microsoft Excel » What is VBA and how to use it in Microsoft Excel

What is VBA and how to use it in Microsoft Excel

What is VBA and how to use it in Microsoft Excel
, , Leave a comment
What is VBA and how to use it in Microsoft Excel
Latest posts by Stratos Matzouranis (see all)

We all know it Microsoft Excel as one of the most basic and first programs created for use with a graphical interface on computers.

Excel is a program that manages spreadsheets. It is a mini database that allows us to store data in its files. We can also do tasks like pivoting and add charts.

Microsoft has given it like the entire Office platform the ability to have its own programming language called VBA (Visual Basic for Applications).

What is its use?

By using VBA we can automate any process such as functions, data change, storage, display with a single click.

As if all these uses were not enough, through VBA we can manage any program installed on the computer through command prompt and PowerShell.

How is it activated?

In Excel – File – Options – Customize Ribbon – click the Developer box.

What is VBA and how to use it in Microsoft Excel

View Code

What is VBA and how to use it in Microsoft Excel

We see the VBA management window has opened. We can choose where the VBA will be performed, e.g. in the Worksheet and as marked in red on the right we can select the action that will trigger it.

What is VBA and how to use it in Microsoft Excel

However, we can also add buttons to Excel that can trigger an action.

What is VBA and how to use it in Microsoft Excel

With the Design Mode option we can change its shape, move it or change its text.

What is VBA and how to use it in Microsoft Excel

As Name is defined the name of the button and as caption the name that will appear written on the object.

What is VBA and how to use it in Microsoft Excel

Selecting View Code will take us to the button code.

What is VBA and how to use it in Microsoft Excel

Let's say that when we press the button we want Sheet1 and Sheet2 to be cleared and only Sheet3 to remain.

To do this we need to add this code to the button.

Private Sub KoumpiTest_Click()
    Application.DisplayAlerts = False
    ActiveWorkbook.Sheets("Sheet1").Delete
    ActiveWorkbook.Sheets("Sheet2").Delete
    Application.DisplayAlerts = True
End Sub

Press save and close its window VBA.

Finally we have to save the file with the extension .xlsm.

What is VBA and how to use it in Microsoft Excel

In our case, when a message appears asking us for permission to run a macro, we will press accept.

As soon as we press the button we will see that Sheet1 and Sheet2 have disappeared.

What is VBA and how to use it in Microsoft Excel

Sources:

Share it

Leave a reply