Excel VBA — How Absolute and relative references in macro work | Introduction to macros and VBA05
by Excel and Adam | Feb 26, 2024 | Excel
Today, we will talk about absolute and relative references in macros as well as different ways to run ourmacro.
Today, we are going to record a macro, where we will put the TODAY function. However this time, we will press ENTER instead of the Ctrl + Enter shortcut. We can start with cell A5. Let’s use the Record button located in the status bar tab this time instead of using the Developer tab (Fig. 1)
Let’s call our macro ‘Today2’. We dont’t need any shortcut but let’s try to add a shortcut here by clicking the Ctrl + D combination and see what happens later on. We want to save our macro in the Personal Macro Workbook. When we press OK, Excel won’t allow us to save our macro, because we already have a macro with this shortcut. Let’s delete the shortcut then. When we use a shortcut that already exists in Excel, macro shortcut for macro will be more important than the default shortcut, e.g. Ctrl key. Thus, we shouldn’t use such kind of shortcuts. Let’s press OK (Fig.2)
Now, we just write TODAY in cell A5. It’s important that the Use Relative References isn’t highlighted (Fig.3)
After we finished writing TODAY, let’s press ENTER. After writing the formula, we can see that Excel went down one cell by default. Now, we just press STOP on the status bar (Fig.4)
After we stopped our macro, how can we run it now? We can go to the Developer tab and press the View Macros command or just click the Alt + F8 key shortcut (Fig.5)
In the window that has appeared, we can see all available macros, which are in All Open Workbooks. Now, we have only two. We can see that before the macro name, we have the file name. We can choose macros from many files (Fig.6)
For us, it’s important to run the Today2 macro, so let’s select it and press the Run button (Fig.7)
Excel has written the TODAY function in our selected cell, which is C5 and has gone to cell A6 (Fig.8)
Let’s try a similar thing with the firs macro. Let’s press Alt + F8 shortcut, select the Today2 macro and press RUN. Now, let’s see what exactly our macro has done. Let’s press Alt + F11. We should see our first macro and our new macro Today2, as well as some comments (Fig.9)
Let’s delete the comments as we don’t need them and let’s locate the lines so that they are clearer to read. Now, we have two lines of the code. The first line of the Today2 macro is very similar to the Today macro. However, instead of ‘Selection’, we have ‘ActiveCell’. It means that our formula is written in the active cell. After the line that contains our macro, Excel will always go to cell A6, We can see it in the next line of ‘Range (“A6”). Select. ‘Select’ means the action, i.e. that we have selected the cell (Fig.10)
This is how macros are working. If we select a range, the TODAY function will be placed only in the active cell. Then, Excel will go to cell A6.
Let’s try a similar macro. The only difference is that this time we will be using relative references. So let’s do it once again: click on the Record Macro button (1), let’s call it Today3 (2), This Workbook (3) and press OK (4) (Fig.11)
We’re writing everything once again: TODAY function in cell A7 (1), then Enter (2), then the Stop Recording button (3) (Fig.12)
This time we were using relative references. Let’s press Alt + F11. Where is our macro? It’s not in the PERSONAL.XLSB file but in our Macro file, where we record. It means that we have to find Modules (1), where Module 1 should be added (2). Let’s double-click it and we can see our macros (Fig.13)
We don’t need comments so let’s delete them again. Let’s also adjust the size of the windows so that we can see it clear. We have the TODAY 2 and TODAY 3 functions here. We’ve found our macro (Fig.14)
There is one more way to find our macros. Let’t go to the Developer (1) tab and go to the View Macros button (2). In the window that appeared we can see that we have many options there like Run, Step into, Edit, Delete and Options (3). For our macro, the Options button is the most significant one, because here we can see shortcuts for our macro (4) (Fig.15)
If we delete D, this macro will delete the shortcut if I press OK. However, we’re not going to do that. Let’s close the Macro Options window and let’s focus on the Today3 macro. Since this file is still open, we don’t see any names before. Let’s go to the Edit option of this macro (Fig.16)
This option leads us to the exact window our macro is stored. The first line in the Today3 macro is exactly the same as in Today2 macro. However, the second line is totally different. First of all, both lines start with ‘ActiveCell’. Then, we have Offset (1, 0) — by one row and 0 columns. It means that we went one cell down. Then, VBA tells us that we selected Range A1. In fact, we didn’t select A1 or any other cells. Sometimes, it’s hard to understand VBA due to some inputs. It’s true that we didn’t select A1, but when we went one cell down from the active cell, we selected a range that is the same size as range A1cell.
Once again. First we start with Active Cell, then there is a dot, which means that we take another step, then choosing the Offset, one row, 0 columns, then we have the Range A1 and what we do with this range, which is selecting the range (Fig.17)
Let’s go back to our worksheet and select cell A8, then go once again to our macro by clicking Alt + F11. This time, we press Play (Run Sub) in our VBA editor or F5 as a shortcut (Fig.18)
What we notice here is that Excel has written the Today function in cell A8 and went one cell below (Fig.19)
Let’s select a range this time and press F5 (Fig.20)
What we’re noticing is that the value has been written only in the active cell, although we had selected the whole range. Then, Excel went one cell down (Fig.21)
Summing up, there is a small difference between relative and absolute references, but still, we have to consider whether we want to use the first one or the second.
Excel VBA — Where is my macro | Introduction to macros and VBA04
by Excel and Adam | Feb 22, 2024 | Excel
In this post, we will see where our macro issaved.
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)
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)
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)
This window shows us information about files or worksheets (Fig.4)
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)
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 the first macro | Introduction to macros and VBA03
by Excel and Adam | Feb 19, 2024 | Excel
With this post, we will record our firstmacro!
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)
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)
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)
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)
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)
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)
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 unnecessarycode.
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)
Now, we can see that we have our date, wherever we use the shortcut (Fig.8)
Excel VBA — Preparation before recording first macro | Introduction to macros and VBA02
by Excel and Adam | Feb 15, 2024 | Excel
In this post, we will work on something we should consider before starting recording our firstmacro.
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)
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 ourmacro.
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)
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 dangerousones.
When you start working with VBA, you should know something about the VBA code. If you see something unfamiliar, you shouldn’t run thiscode.
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 currentdata.
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 firstmacro.
Excel VBA — What is a macro and enabling the Developer tab | Introduction to macros andVBA
by Excel and Adam | Feb 12, 2024 | Excel
This post starts a series about an introduction to macros and VBAcode.
What is a macro? In most cases it’s a synonym for VBA code that we run. It’s almost the same thing. Sometimes we differentiate it and we call macros the VBA code that was recorded by Excel — Record Marco command. If we want to use this command, we should turn on the Developer tab. In order to do that, we have to right-click on our ribbon and go the Customize the Ribbon command (Fig.1)
In the Excel Options window that has appeared, we have to find the Developer tab on the right side, press it and press OK (Fig.2)
Now, we can see that in the Developer tab we have the Record Marco command (Fig.3)
This command allows us to record actions we are doing on a worksheet, such as cell formatting, putting values in a cell, size modification, etc. It’s a good place to start learning about the VBAcode.
Macros are good for two main reasons.
First, they are good for repeating tasks. If we want to make reports on a daily basis, we can just record all actions we are doing in a worksheet and then we are able to use it again, probably with tiny modifications. We will have a report format by runningmacro.
Second, the VBA code allows us to do the things that are not possible or very hard when using Excel functions. Those things will be much simpler using the VBA code. It may even happen that somebody has already written a proper code to thistask.
If we want to save macro, we have to use the ‘xlsm’ extension, where ‘m’ stands for macros. We can run macros on ‘xlsx’, which is a standard extension for Excel, however Excel won’t save macros in those files. Remember, only ‘xlsm’ works for macros.
