What is VBA and how to use it in Microsoft Excel
Certifications:
- How can we increase performance on Oracle GoldenGate Replicat target with parallelism? - 19 March 2025
- How to create users in databases that belong to a SQL Server Always On Availability Group - 10 February 2025
- How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database - 13 January 2025
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.
View Code
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.
However, we can also add buttons to Excel that can trigger an action.
With the Design Mode option we can change its shape, move it or change its text.
As Name is defined the name of the button and as caption the name that will appear written on the object.
Selecting View Code will take us to the button code.
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.
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.
