CREATING MACROS IN EXCEL WITH
VISUAL BASIC FOR APPLICATIONS
VBA
RAFAEL HINOJOSA
2021
INDEX
00 BEFORE WE START
00.01.- Before we start.
To complement, facilitate and speed up your learning process, I want to invite you to see the video tutorials I have prepared and that are available in my YouTube channel Friccin Mental.
www.youtube.com/c/friccionmental .
To go directly to the reproduction list for the series in English, click AQUI .
01 INTRODUCTION AND PREPARATION
01.01.- Introduction.
Visual Basic for Applications (VBA) is a programming language that is contained inside of the Microsoft Office suite and applications and it is, in a way, some kind of dialect of Visual Basic, but we may say that it is a smaller and lighter version, besides, it is adapted to be much more friendly to work inside of Office and be able to manipulate many things inside of Office.
In this small book, I will focus on using VBA inside of Excel to be able to create more advanced macros than the ones that we can create by just clicking on the Excel buttons, but I want to invite you to try inside of other Office application, some of the things we will see and learn here.
The main purpose of this small book is to show you some of the most basic things that you can do and must know in VBA. It is far to be able to teach you VBA in all its extension, but I hope I can help you to get started and to create in you, a deeper desire to keep learning, knowing, and investigating more about VBA once you see what you can do with just the basic stuff. Imagine what you could create with more advanced programming concepts and practices in VBA.
If you want to know if a particular Office application (Word, Power Point, Access, Outlook, etc.) has the VBA editor available, just press Alt+F11 and the editor will open if it is available in that application.
VBA allows us to create macros. A macro is basically a small computer program, but it only works inside of another program (in this case Excel or some other Office application). These macros allow us to automate tasks that are usually very repetitive and tedious and that are generally ruled by a basic set of rules (the sequence or the logic followed to perform these tasks) .
Creating a macro to automate a task can save us a lot of time and can also avoid that we make mistakes.
01.02.- Requisites.
To get the most out of this book you will need the following:
1- Knowledge in Microsoft Excel, it may be to a relatively basic level, but at least you should be familiar with some Excel concepts like books, sheets, columns, rows, cells, functions, etc. Obviously, the more you know about Excel, the better.
2- A computer with Microsoft Excel. The version of Excel is not very important because VBA has not changed in many years and Excel versions.
You do not need to have previous knowledge about programming. In fact, this is a book in a very basic level and on many occasions, you will not need more that what we will see here to be able to create your own macros and automate tasks in Excel.
Remember that if you want to save your work to continue later, you must save the Excel workbook as an Excel Macro-Enabled Workbook.
Save you file as Excel Macro-Enabled Workbook.
01.03.- Enabling the Developer Ribbon.
Having the Developer Ribbon enabled, is not mandatory, but it may be really convenient to work with VBA. To enable it, you can do the following (there are a few different ways to do it, but I will show you one that I think is very simple).
Click with the secondary button of your mouse in any place over the Ribbon and a contextual menu should appear. In that menu, click on Customize the Ribbon.
On the Excel Options dialog box, on the right side is the section to modify the Ribbon. Select the Developer box and then click on the OK button.
This will cause the Developer Ribbon to be shown in Excel with the other Ribbons.
Secondary click over the Ribbon and select Customize the Ribbon.
Adding the Developer Ribbon.
Developer Ribbon added to Excel.
01.04.- Access to the VBA editor.
We can access the VBA editor in a couple of different ways.
The first one is by pressing the combination of keys Alt+F11. This will open the VBA editor. Pressing Alt+F11 again will take us back to Excel without closing the VBA editor.
The second one is through the Developer Ribbon, on the Code group, clicking on the Visual Basic icon (if you leave the mouse pointer for a moment over the Visual Basic icon, Excel will show the keyboard short cut to activate that command, in this case, Alt+F11).
Accessing VBA using the Alt+F11 key combination or through the Developer Ribbon.
VBA editor.
01.05.- VBA creating a macro.
To give yourself an idea of how VBA creates a macro inside of Excel (or other Office application), open the VBA editor and place it next to Excel in a way that you can see both programs at the same time.
What we will do next is create a simple macro just to see how VBA writes the code when some specific actions take place (not everything you see happening on the screen gets coded).
Besides of giving you an idea of what we will be doing and creating in this book, the most important thing is that you observe that you can take the advantage of the fact that VBA writes some code so that you do not have to reinvent the wheel. If you can do something just by clicking and maybe writing on the Excel cells, you can simply record a macro and then just copy and paste that code into your new macro and then add more code to accomplish your needs.
On the Developer Ribbon, find the Code group (it is generally the first group) and click on the icon to start recording a macro. On the dialog box, write a name for your macro. Keep it simple for now and write just one word (no numbers, spaces, special characters, etc.). In mi case, I will simply name it Practice. To start the recording process, click on the OK button.
On the VBA editor, you will see, inside of the area that shows the projects, a folder named Modules. Expand that folder and you will see the Module1. Double click on that module to open it and you will be able to see how VBA has created the space to start recording.
Dialog box to name a macro before start recording it.
