In this post, we will see where our macro is saved.
Excel VBA — Where is my macro | Introduction to macros and VBA 04
Let’s go to the Developer tab. On the left, we can see the Visual Basic command. We can also press the Alt + 11 shortcut. So, let’s click it. Now, in the worst case scenario, all we would see is a grey window. In this case we need some more information. Let’s turn on a smaller window by opening the View menu, and select the Project Explorer command (Fig. 1)
Fig. 1 Project Explorer command
What we should see here is all Excel files that are opened, as well as our PERSONAL.XLSB files, which are our personal files for macros (Fig. 2)
Fig. 2 Files
In each file, we should also see some worksheets and similar things, especially modules. Our code should be written as Module 1 by default. Before we click on it, let’s go to the View tab one more time and click on the Properties Window command (Fig. 3)
Fig. 3 Properties Window
This window shows us information about files or worksheets (Fig. 4)
Fig. 4 Worksheet information
Let’s go now to Module 1. After double-clicking on it, we should have a small window opened. Our first macro was very simple, however we have more rows than we need. We don’t need the Option Explicit line, so let’s delete it. Next, we have the Sub key word which starts our macro and the phrase End Sub which stops it. Only the elements that are between those two lines create our macro. Our macro is called TODAY. The other time I mentioned that the TODAY function is a DZIŚ function in Polish, however no matter what version I have on my computer, VBA is American and will use American names and procedures in macros. When we look at our macro, we can see the line with the TODAY function, as well as a few green lines starting with green single quotation marks. They are just comments. In VBA, comments start with a single quotation mark, and are not part of the VBA code (Fig. 5)
Fig. 5 VBA code with comments
Having the above in mind, I can just delete them and leave only the line that is an actual VBA code. Now, it’s quite simple when you read it. ‘Selection’ means that we just selected something, a dot means the next step after selecting. Then, we have the Formula, which means that we put a formula. The R1C1 isn’t important for us. Then, our formula equals the TODAY function. That is the whole VBA code (Fig. 6)
Excel VBA — Recording first macro | Introduction to macros and VBA 03
First of all, we want to open a file with macros. What we have here is a piece of information that says ‘Marcos have been disabled’. You need to click the Enable Content button (Fig. 1)
Fig. 1 Information concerning macros
If you don’t want to click the Enable Content button constantly, like me, go to the Developer tab, then click on the Macro Security command, then the Enable VBA macros radio button (Fig. 2)
Fig. 2 Enable VBA macros command
We can also go to the Trusted Location part and press OK. It will also work for all files and help us with too many notifications or messages (Fig. 3)
Fig. 3 Trusted Location
Today, we want to record very simple macro. We just want to write the TODAY function in cell A2. This cell should be selected before we click the Record Macro command. Since the cell is selected let’s write the equal sign, then TODAY, then click the Ctrl+Enter key combination. It’s important to use this shortcut. That’s all we need in our first macro (Fig. 4)
Fig. 4 Ready function
Now, let’s delete this value and then press the Record Macro. In the window that has appeared, the first thing we do is choosing the macro name. We can use the name of our function’s name. In most cases we should’t do that, however our case is really simple. It’s only macro, not a VBA function. Then, we choose whether we want a shortcut for our macro or not. If the box is empty, it means that our macro hasn’t got a shortcut. If we write there a letter or a sign, this will mean that we have a shortcut. The Ctrl key is a default key for each shortcut. It also applies to the Shift key. That’s why, let’s hold the Shift key and press D at the same time. I’m using D, as ‘today’ is ‘dziś’ in Polish. If we don’t want any shortcuts, we just press the Backspace button. Next, we have the location of our macro. We have a choice of Personal Macro Workbook, New Workbook or This Workbook. Let’s choose the Personal Macro Workbook, as it’s the most significant one. We can also add a description in the next part, however, in most cases the name should be enough to tell us what this macro does. We have already chosen the name, shortcut and storage place, so we can press OK. You need to be careful when pressing OK, because that’s the point when the recording process starts. It means that each action you do in this worksheet will be recorded (Fig. 5)
Fig. 5 Record Macro window
Now, our Record Macro command changed into the Stop Recording command. The button in the bottom left corner of the status bar also changed. It means that we don’t need to go to the Developer tab to stop the recording (Fig. 6)
Fig. 6 Stop Recording button
Let’s write =TODAY and press Ctrl + Enter again. Then press the Stop Recording button. It’s crucial to press this button so that Excel doesn’t record any unnecessary code.
Now, since we added a shortcut to our macro, let’s use it in cell C2 by pressing the Ctrl+Shift+D shortcut. Something actually appeared in the cell, but we need to change the formatting to get the date (Fig. 7)
Fig. 7 Formatting modification
Now, we can see that we have our date, wherever we use the shortcut (Fig. 8)
In this post, we will work on something we should consider before starting recording our first macro.
Excel VBA — Preparation before recording first macro | Introduction to macros and VBA 01
First of all, in the Developer tab, we have the Record Marco command. We can also find this command in the status bar on the left side. It make it easier to start our recording (Fig. 1)
Fig. 1 Record Macro command in the status bar
Then, we should consider relative and absolute reference. We have the Use Relative References command. If it isn’t highlighted, it means that we work with absolute references. For example, if I select cell B9, then select cell B12, Excel will record this as ‘select cell B12′. When I turn on the Use Relative Reference command, then select cell B9, and then cell B12, Excel will record this step as ‘go three rows down’. This issue is very important when recording our macro.
Let’s click on the command one more time to turn it off and work with absolute reference.
There is one more command to look into. It’s called Macro Security. After clicking it, we have a Trust Center window. In the Marco Settings area, we can see an option of Disable VBA macros with notifications (Fig. 2)
Fig. 2 Disable VBA macros with notifications option
This option should be selected by default. It means that when we open a file with macros (with an xmls extension), we will have information that this file contains macro, and some code can be run without our knowledge. However, in most cases, we shouldn’t be afraid of this code. It’s a code which we copy from secure sources, like Google. Google is quite a secure source, as it shows you good websites, not dangerous ones.
When you start working with VBA, you should know something about the VBA code. If you see something unfamiliar, you shouldn’t run this code.
I personally don’t like notifications, especially the ones that show up every time I open a file containing macros. That’s why, in most cases I’m working with the Enable VBA macros option. It’s not recommended, however, from my point of view it’s practical. I’ve been working with macros for many years, and I haven’t come across any dangerous code. The most dangerous thing a VBA code has done to me is rewriting my current data.
Having the above in mind, when you run macros and you don’t fully know what they are doing, you should always create a backup copy before.
The above are the most significant pieces of information you should consider before recording your first macro. In the next post, we will be recording our first macro.
