How to create and record macro in microsoft excel? Step by step guide

When working in this type of software, it is very common for repetitive tasks to be carried out , that is, the same commands must be entered constantly to perform some functions . Therefore, making use of macro is essential to save time when working on them .

This tool known as a macro is nothing more than a series of instructions that are responsible for registering and saving a sequence of commands so that they can be automatically executed by the user at certain times. In this way, this type of element becomes a great help since it saves time when working on these programs.

In the case of Microsoft software, you have the ability to create and record simple macros at any time . That is why here we are going to explain a little more about what this function is about, its main benefits and how you can easily start recording them . To do this, follow in detail everything that we will teach you below.

What is a Macro and what are they for in Microsoft Excel?

It is not a secret for anyone that Microsoft Excel is one of the most used database programs in the world, this is because it offers a wide variety of functions that allow tasks to be carried out in a very simple and very professional way. This is how all users have the ability to create and record macros to further facilitate their work on it.

This is how this Excel function uses the Visual Basic For Applications (VBA) scripting language, which is also used by the other programs of the office suite such as Word, Access and PowerPoint . This language allows macros to be created so that they can be executed automatically at any time.

All of this has made it possible for users to record frequently used commands or even add new functions to the spreadsheet . In this way, it can be said that the main function of this tool is to automate repetitive tasks, thus avoiding that users have to repeat instructions or steps over and over again.

When working in Excel, it is very common that at times you have to perform tasks over and over again, so this function becomes very useful. But, this is not the only function that macros fulfill, they also allow you to create your own functions, as well as create complete programmed applications, forms , interact with other programs, among many other activities.

Finally, it should be mentioned that this tool allows you to automatically record all the instructions that you think are easily necessary, so you will not have to be a programming expert to carry out this . Being able to make use of this Excel functionality will undoubtedly make your work easier and save you the time you invest in it .

What are the benefits of using macros in Excel spreadsheets?

When a tool of this type is used in a calculation program such as Excel, it is because it offers a great number of advantages for users.

This means that if you want to get the most out of this type of software, you will have to start using macros, which will become the right hand for each of your tasks in the program .

In accordance with this, here we show you the main benefits that all this offers you:

It allows you to decrease the error rate

One of the most important benefits this offers you is that it helps you eliminate the error rate in the program . Keep in mind that every time you enter a command manually in the spreadsheet, the possibility that you may make a typing error increases, especially if it involves complex sequences.

This means that if you write the same command 10 times or more it is very possible that in some of them you will end up making a mistake . That is why recording macros will help you eliminate all this risk, since the only possible error that can occur here is that the person makes a mistake when creating it.

This means that if you create it correctly everything will work excellently, which will eliminate any risk of mistakes .

It allows you to reduce work time

It is no secret to anyone that you will save work time if you can add certain tasks automatically instead of having to enter them manually.

Keep in mind that macros are created only once and can be used whenever you need them. All this will save you time in each of your Excel jobs .

Helps you increase the usefulness of Excel

One of the main advantages of this program is that it allows you to program macros and develop new functions. This allows users the possibility to expand the functions within the program .

In this way users can work more comfortably in Excel, either by pinning quick access buttons to their own macros on the symbol bar .

Steps to create and record a macro to automate tasks in Excel

Taking into account all the benefits that this offers, the following will explain how you can start creating and recording a macro to automate each of your tasks in the program. Keep in mind that to create it you must activate the Visual Basic editor, which is part of the developer tools and is not activated by default.

Therefore, to carry out this procedure it is necessary that you carry out each of the steps that we will teach you below:

Add the Developer tab to the ribbon

  • To do this you must go to the “File” tab and in the options menu that appears on the screen select “Options”.
  • Now select “Customize the ribbon” in which there is a list of all the main tabs, there you will see Developer (Developer or programmer).
  • Here you must check the box and click on “Accept”.

  • The next thing will be to see the ribbon in the “Developer” tab , there you should see the Macro and Visual Basic option .

Create a book for your new macros

Once this is done, the next thing will be to create a book that will serve as a basis for the registered macros.

To do this, you must perform these steps:

  • Here you must click on macros located in the “Programmer” tab , there a pop-up window will appear where you must enter the name of the macro you want to create. Keep in mind that here you can put  whatever name you want .
  • Once the name is defined, you must click on “Create”.

  • Then the Visual Basic window will open .

  • “Sub” in this case is equivalent to a subroutine, so it refers to a macro . When a macro is started , the code between Sub and End Sub executes . To run a test of this, you can run a test by typing the name of the macro as “command World!”.

  • The code is saved in .xslm format and the window is closed to return to the Excel spreadsheet .
  • Now again you must click on “Macros” and there select the macro that you have created.
  • To be able to execute it, you must click on the “Execute” button .

  • A small information box will appear with the text that appears in the image and click “OK” to close the window.

  • Finally the book will be saved in Visual Basic .

Create a shortcut button for macros

When you have completed the steps above, the next thing to do is to create the quick access button . Keep in mind that macros are accessible for execution either from the “Programming” tab or from View.

However, if you have created a macro that you must use very often then it is best to create a shortcut in the list of shortcut symbols .

To do this, you must follow each of these steps:

  • Go to the “File” tab .
  • There you must select the “Options” item again .
  • The next thing will be to click on the list of quick access symbols.
  • Now you must select command from the “Macro” option and there look for the name of the macros that you have previously created.
  • Here you must add it to the list of quick access symbols by selecting it and clicking “Add”.
  • Before selecting “OK” to confirm this whole procedure, you can also determine the symbol with which it will be displayed. To do this, select the name of the macro you created and click on “Change”, here select the one you like the most.
  • When you have finished all this, click on “OK” to save the changes .

Record the macro

Now in the Visual Basic editor that is included in Microsoft applications, it allows you to create macros without having much experience with it . In this way you will create a macro that changes the name of a sheet automatically.

To do this, you must perform each of the steps that we will teach you below:

  • To do this, you must enter the “Programming” tab and there select the “Record macro” option .

  • In the dialog box that appears on the screen, you must enter the following name “Rename work sheets” and start the recording by clicking “OK”.

  • Now change the name of sheet1 to “New name” and end the macro recording by clicking on “Stop recording”.

  • The next thing will be to return to the “Visual Basic Editor”, for this follow this path: “Macros”> “Edit”> “Edit”. Here you will be shown the following:

When you have entered the “Visual Basic Editor” again,  you will see the first four lines of code under the line that begins with Sub, which begins with an apostrophe, this constitutes comments that have no influence on the general functionality of the macro . This means that they only serve to have a better compression of the code .

All these comments allow you to temporarily disable some of the code lines. Keep in mind that these lines are not necessary so they can be eliminated without causing any kind of problem.

The lines below contain the Select method to select “sheet 1” a step that is performed during


Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button