What is VBA and how to use it in Microsoft Excel
- How we enable Unified Auditing in Oracle Database - 7 October 2024
- What is PostgreSQL and how do we do a full installation - September 2, 2024
- How do we configure GoldenGate replication to read from Oracle Data Guard Standby - 2 August 2024
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.