Excel tools
Know absolutely all options that this program can offer can be difficult, both to discover and to know. Among all this range of tools, there are macros. An important fact about macros is that they can be easily edited and modified.
Something that can be really complicated at first, because it's a programming language. But once you get the hang of it, you'll be even more impressed with how easy it is to manipulate Excel.
Macro in Microsoft Excel
Macros can be thought of as instructions that can be executed sequentially or in any other order.
Its great utility lies in the fact that creating a macro in Excel documents can do save a lot of time in case of repetitive activities, automating the processes. Therefore, if you find that you create the same table with different data very regularly, this can save you a lot.
How Do Macros Work?
To use macros, you need to use Visual Basic for Applications (VBA), which is responsible for creating and editing the macros needed for the Excel document.
Activate VBA in Excel
This method of creating macros with VBA is not visually available to everyone in Microsoft Excel. However, that doesn't mean you can't enable it. The goal is for it to appear on the ribbon, the Developer tab. To do this, follow these steps:
- Located on any ribbon tab.
- Right click to view a menu.
- click on Customize Ribbon.
- You must check the box Developer and click Accept.
- Go to the Developer tab and click Visual Basic. Next:
- Find and click on the tab Instruments.
- Select the option References.
- Check the box corresponding to Microsoft VBScript Regular Expressions 5.5 and click OK.
Automatic macros
Macros are very useful when performing repetitive tasks, turning Microsoft Excel into an automated system that you it will help save time and effort in recreating the entire format of the document that you prepared a few hours before or the day before.
How to adapt macros to your needs?
Macros can run according to the schedule provided to them. When it is time to perform an action, start a new sheet or a new book. These types of actions are known as events and here you will learn how to schedule them.
- Enter the VBA via the tab Developer.
- In the General bar to select the option Worksheet, so you can work on the sheet.
- In the next bar, Excel will give you a number of options to help you determine the option you want to perform more quickly.
It is important to note that they are used for actions that can be performed within the sheet. You can also give instructions on performing certain actions when opening an Excel document:
- To do this, instead of describing the commands on the sheet, click ThisWorkbook.
- Likewise, select the option Worksheet in the options bar.
- It will send you to the event by default Open, then it will perform a certain action when opening a document.
Simple examples
These types of events can be confusing. So, this time around, you'll learn how to schedule a few simple events.
In case you want a dialog box to appear when opening an Excel file, here's what the VBA box should look like:
Private Secondary Workbook_Open ()
Msgbox "Hello!"
End below
Remember that the workbook must be saved as a macro-enabled Excel workbook, otherwise, the instructions you set will not be carried out. If you want this dialog to appear every time you select a certain sheet within your Excel workbook, it should be as follows:
Private Secondary Worksheet_Enable ()
Msgbox "Who is there?"
End below
TagsEccellere
