Excel VBA — Where is my macro | Introduction to macros and VBA 04

by Excel and Adam | Feb 22, 2024 | Excel

In this post, we will see where our macro is saved.

Let’s go to the Devel­op­er tab. On the left, we can see the Visu­al Basic com­mand. We can also press the Alt + 11 short­cut. So, let’s click it. Now, in the worst case sce­nario, all we would see is a grey win­dow. In this case we need some more infor­ma­tion. Let’s turn on a small­er win­dow by open­ing the View menu, and select the Project Explor­er com­mand (Fig. 1)

What we should see here is all Excel files that are opened, as well as our PERSONAL.XLSB files, which are our per­son­al files for macros (Fig. 2)

In each file, we should also see some work­sheets and sim­i­lar things, espe­cial­ly mod­ules. Our code should be writ­ten as Mod­ule 1 by default. Before we click on it, let’s go to the View tab one more time and click on the Prop­er­ties Win­dow com­mand (Fig. 3)

This win­dow shows us infor­ma­tion about files or work­sheets (Fig. 4)

Let’s go now to Mod­ule 1. After dou­ble-click­ing on it, we should have a small win­dow opened. Our first macro was very sim­ple, how­ev­er we have more rows than we need. We don’t need the Option Explic­it 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 ele­ments that are between those two lines cre­ate our macro. Our macro is called TODAY. The oth­er time I men­tioned that the TODAY func­tion is a DZIŚ func­tion in Pol­ish, how­ev­er no mat­ter what ver­sion I have on my com­put­er, VBA is Amer­i­can and will use Amer­i­can names and pro­ce­dures in macros. When we look at our macro, we can see the line with the TODAY func­tion, as well as a few green lines start­ing with green sin­gle quo­ta­tion marks. They are just com­ments. In VBA, com­ments start with a sin­gle quo­ta­tion mark, and are not part of the VBA code (Fig. 5)

Hav­ing the above in mind, I can just delete them and leave only the line that is an actu­al VBA code. Now, it’s quite sim­ple when you read it. ‘Selec­tion’ means that we just select­ed some­thing, a dot means the next step after select­ing. Then, we have the For­mu­la, which means that we put a for­mu­la. The R1C1 isn’t impor­tant for us. Then, our for­mu­la equals the TODAY func­tion. That is the whole VBA code (Fig. 6)

https://www.youtube.com/watch?v=Idag9QNODuY

Excel VBA — Recording the first macro | Introduction to macros and VBA 03

by Excel and Adam | Feb 19, 2024 | Excel

With this post, we will record our first macro!

First of all, we want to open a file with macros. What we have here is a piece of infor­ma­tion that says ‘Mar­cos have been dis­abled’. You need to click the Enable Con­tent but­ton (Fig. 1)

If you don’t want to click the Enable Con­tent but­ton con­stant­ly, like me, go to the Devel­op­er tab, then click on the Macro Secu­ri­ty com­mand, then the Enable VBA macros radio but­ton (Fig. 2)

We can also go to the Trust­ed Loca­tion part and press OK. It will also work for all files and help us with too many noti­fi­ca­tions or mes­sages (Fig. 3)

Today, we want to record very sim­ple macro. We just want to write the TODAY func­tion in cell A2. This cell should be select­ed before we click the Record Macro com­mand. Since the cell is select­ed let’s write the equal sign, then TODAY, then click the Ctrl+Enter key com­bi­na­tion. It’s impor­tant to use this short­cut. That’s all we need in our first macro (Fig. 4)

Now, let’s delete this val­ue and then press the Record Macro. In the win­dow that has appeared, the first thing we do is choos­ing the macro name. We can use the name of our func­tion’s name. In most cas­es we should’t do that, how­ev­er our case is real­ly sim­ple. It’s only macro, not a VBA func­tion. Then, we choose whether we want a short­cut for our macro or not. If the box is emp­ty, it means that our macro has­n’t got a short­cut. If we write there a let­ter or a sign, this will mean that we have a short­cut. The Ctrl key is a default key for each short­cut. 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 Pol­ish. If we don’t want any short­cuts, we just press the Back­space but­ton. Next, we have the loca­tion of our macro. We have a choice of Per­son­al Macro Work­book, New Work­book or This Work­book. Let’s choose the Per­son­al Macro Work­book, as it’s the most sig­nif­i­cant one. We can also add a descrip­tion in the next part, how­ev­er, in most cas­es the name should be enough to tell us what this macro does. We have already cho­sen the name, short­cut and stor­age place, so we can press OK. You need to be care­ful when press­ing OK, because that’s the point when the record­ing process starts. It means that each action you do in this work­sheet will be record­ed (Fig. 5)

Now, our Record Macro com­mand changed into the Stop Recording com­mand. The but­ton in the bot­tom left cor­ner of the sta­tus bar also changed. It means that we don’t need to go to the Devel­op­er tab to stop the record­ing (Fig. 6)

Let’s write =TODAY and press Ctrl + Enter again. Then press the Stop Record­ing but­ton. It’s cru­cial to press this but­ton so that Excel does­n’t record any unnec­es­sary code. 

Now, since we added a short­cut to our macro, let’s use it in cell C2 by press­ing the Ctrl+Shift+D short­cut. Some­thing actu­al­ly appeared in the cell, but we need to change the for­mat­ting to get the date (Fig. 7)

Now, we can see that we have our date, wher­ev­er we use the short­cut (Fig. 8)

https://www.youtube.com/watch?v=NBEE3mA45xg