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.
This post starts a series about an introduction to macros and VBA code.
Excel VBA — What is a macro and enabling the Developer tab
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)
Fig. 1 Customize the Ribbon command
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)
Fig. 2 Developer tab
Now, we can see that in the Developer tab we have the Record Marco command (Fig. 3)
Fig. 3 Record Macro command
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 VBA code.
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 running macro.
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 this task.
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.
If I need to extract only letters from my text, what should I do? Lets find out.
Extract only letters from text
When I need to extract only letters from my text, it means that I also need to extract spaces and enters (new lines).
With Power Query this task is quite easy.
I need to select one cell in my table, then go to the Data tab, and click on the From Table/Range command in order to take our data to Power Query (Fig. 1)
Fig. 1 From Table/Range command
Here, I have four simple pieces of text. One in English and three in Polish. We need to go to the Add column tab, then to the Custom column command (Fig. 2)
Fig. 2 Custom column command
In the Custom Column window, we have the space to name our column. Let’s call it Clean, and below we have the Custom column formula box, where we write the Text.Select function. In the function we need to work with text, so let’s select the right column (1). Then, I need letters. Since there will be string of characters, I have to use curly brackets, where we write upper case letter in the first sequence, which is “A”..“Z”. Then let’s write lower case letters, which is “a”..“z”. Writing two dots between letters means that there is a sequence. Let’s close the curly bracket and the parenthesis. Now, it should be fine (Fig 3)
Fig. 3 Whole function
After pressing OK, the system extracted for us only letters. However, the text presented like this is just gibberish. That’s why we have to add spaces. To do that, we need to go to the View bar, then select the Formula bar checkbox and start modifying our formula. Let’s write a comma and a space in double quotes (1). If we need other signs, we should also write them, e.g. a dot (2). As we can see, we have spaces and dots in our new text (Fig. 4)
Fig. 4 Modifying the formula
However, I don’t need dots in my text. What I really need are enters. We can remove the dot from the formula and write #(lf) in its place. After entering the formula, we can see that we have extracted enters, lower case letters, spaces. But, I didn’t extract Polish letters. In row 4 there are strictly Polish letters. In rows 2 and 3 there are some of them. The question is: how can I extract unusual signs from text using the Text.Select function. First of all, we have to check the Unicode. We can see that letters from A to Z is a sequence of numbers from 65 to 90. It’s similar in lower case letters. The most important lesson from it is that we have a sequence (Fig. 5)
Fig. 5 Unicode
Now, let’s go back to our formula. If I want to have Polish letters, I can write a sequence of “Ó”..“ż”. After entering the formula, we have extracted also Polish signs (Fig. 6)
Fig. 6 Extracting Polish signs
Now, that we have extracted all signs we need, we can go to the Home tab and click on the Close&Load to command to transfer our results to Excel (Fig. 7)
Fig. 7 Loading results to Excel
In the Import Data window, we have to select a cell (Fig. 7)
Fig. 7 Choosing the target cell
As we can see, the text is written in one line, so we need to wrap it. Let’s go to the Home tab and click on the Wrap Text command (Fig. 8)
Fig. 8 Wrap Text command
Now, we clearly see upper case letter, lower case letter, spaces, enters. I removed signs that I didn’t need (Fig. 9)
When we need more space while working in Dashboard in Excel, we can just hide unnecessary options. How? Let’s find out.
Hide scroll bars, formula bars, column headers
The options we can hide are e.g. scroll bars, sheet tabs, formula bars as well as column/row headers. If we want to hide then we just go to the File menu and choose the Options command (Fig. 1)
Fig. 1 Options
In the Excel Options window, we choose the Advanced tag. If you prefer using shortcuts, you can press Alt + T + O, which also opens the window. Lets go to the Display area and uncheck the Show formula bar checkbox. Remember that it works for all Excel files (Fig.2 )
Fig. 2 Show formula bar checkbox
Then, we can go to the Display options for this workbook area. We have to uncheck the Show horizontal scroll bar, Show vertical scroll bar, Show sheet tabs checkboxes (Fig. 3)
Fig. 3 Three checkboxes
We can even go to the Display options for this worksheet area where we can uncheck the Show row and column headers checkbox and press OK (Fig. 4)
Fig. 4 Show row and column headers checkbox
We can also double-click on the Home tab to hide the ribbon, which gives us even more space than before.
Now, I can press Ctrl + Page Down to go to another workbook. Here, we can see that column and row headers are visible. However, when we go back to our workbook by pressing Ctrl + Page Up, we can see that there are no row or column headers. That’s how you make more space in your Dashboard.
How to add a drop-down list in Excel? It’s a really simple task.
First of all, we have to have our list (1). Then, we have to select a cell or cells to which we want to add the drop-down list (2). Then, we’re going to the Data tab (3) and click on the Data Validation command (4) (Fig. 1)
Fig. 1 Preparing data
In the Data Validation window, we have to select the List option in the Allow bar, then we’re selecting the range and write it into the Source bar. Then OK (Fig. 3)
Fig. 2 Data Validation window
Now, we have a drop-down list (Fig. 3)
Fig. 3 A drop-down list
Since our cells with data are in a table, when we add a new line to the table, it also involves the drop-down list.
Even, if we cut and paste our new data, the cells in the selected column will still have the drop-down list. What’s more, in the newest Excel version, we have even Lookup options. We just have to start writing the first letter of a word we’re looking for, then press the Tab key.
Fig. 4 Lookup option
This function, however, doesn’t work when we start from the middle of our text. It works only with the first letter.
