DO YOU REALIZE THAT YOU CAN WRITE MACROS WITHOUT KNOWING HOW TO CODE?
That is an interesting title, isn’t it?
Just like touching the clouds. We want it a lot, but unfortunate we just cannot do it.
The situation is just like that for those who don’t know and write macros.
But don’t worry, don’t be sad about not knowing. PEAKUP is always here with you, for you.
Who doesn’t want to prepare daily, weekly, monthly routine tasks in a few minutes with macros, right?
This will save you a huge amount of time, you will have more time for yourself or will be able to spend more time on other tasks. Aren’t you just done with doing the same thing over and over again?
You know how we always ask ourselves: “For God’s sake, what age is this?”. So, for this reason there shouldn’t be anymore people who don’t know Excel & VBA (Macro).
You must be a bit excited now.
As much as this title sounds and seem a bit magical, we are definitely (and unfortunately) not wizards and witches.
Everything is in your hands.
Keep in mind that if you want to learn something, you should really want it and spare some time for it.
So, how is this going to be?
You will see that while you cannot even imagine coding without knowing macros and codes, you actually will be able to do it.
We know the happiness of being able to do something important for you as much as you do.
What we want from you is for you to read and apply this article patiently.
You will see at the end of the day that you will get your routine daily tasks done way easier with macros without having any coding information.
Now, let’s start to learn how we can do this.
(We will be adding a practice video at the end of the article for those who want to learn fast.)
WHAT ARE EXCEL MACROS?
Let’s start by learning what Excel Macros are.
We call these macros Excel & VBA in general.
What is VBA?
VBA
stands for: Visual Basic for Applications.
I.E. it is the structure that was adapted for the Office applications and that allows us to access the Visual Basic objects, methods and features.
Microsoft Office offered the Macro command in some of the Office packages to the users in order to automatize the routine actions.
While preparing Macros, the Visual Basic programming language that works in the background of Excel waits there. When you save anything, this programming language becomes active and translates the macro command you have prepared into the programming language. This way, when you want to run or edit the macro you’ve prepared, Excel offers you this opportunity easily.
WHAT IS THE RECORD MACRO METHOD?
It is a tool and method that activated the Visual Basic that is in the background of Excel and that codifies all the actions we have executed in workbooks, worksheets or cells.
You can complete your tasks faster with this read-made code. We will be using ready-made codes using this feature.
You can access the Record Macro feature in 3 different ways.
1- Through the Developer Menu
If you don’t have this menu in the Ribbon, you can add it like this:
File ‣ Options ‣ Customize the Ribbon ‣ Developer ‣ OK.
2- Through the View Menu
3- Through the StatusBar
You can active the Record Macro feature by using the method you want.
Now I will tell you about how to activate it. But just read it, we will practice it together later.
When your press Record Macro, this screen will pop up.
You can give a name in the Macro1 box about what you will do.
For example: if you will use it to filter, you can write filter and then press the OK button.
The moment we press it, the record will start, and it will record and codify each action in the background. When you are done, you need to Stop Recording. You can click Stop Recording in the same place.
Now, let’s see how easy it is with an example.
We all convert out data into a table. I mean, we have titles in the first row and data under those titles, right? And we filter many times in that table during the day. What do we do when we filter? We click the filter arrows in the field (column) to be filtered and select the data in the window that pops open or look it up and click OK and the table is filtered by the data we want.
We spend time even on this simple filter and lose extra time for nothing. Open the filter all the time, select, click OK. And when we are looking for another data, open the filter again, select and then click OK over and over again.
But only if we had an empty cell that we would use to filter and we could just click Enter or click the button to filter, wouldn’t that be way simpler and quicker?
This will save you time just for one action you will be executing in a day. But, imagine speeding up all your actions this way.
IN WHICH CASES CAN YOU RUN THE CODE YOU HAVE WRITTEN?
When you:
- Select,
- Righ-click,
- Double-click a cell.
- Enter data into the cell,
- Open the page,
- Open the file,
- Close the file,
- Click a button,
- Press a key in your keyboard
etc. you can run the codes you have written.
Now, let’s execute this action with an applied example.
We have a file like in the image, you can try things out on that file and then practice on your own files.
Download the file here.
We want the data of a Brand to be filtered when we write that Brand’s name into the H1 cell in this file. When we write QUARTZ into the cell, the QUARTZ will be filtered in the Brand field. When we write YELKEN, YELKEN will be filtered. Thus, we will be using that cell as a filter box.
We will take the action in just a bit.
But before, you need to know this. There are fields to write codes depending on the running methods we have stated under the In Which Cases Can You Run the Code You Have Written? title.
These fields are:
- Module – (Codes create with Record Macro or manually are stored here.)
- Code Window of the Page (Code running actions of the related page are stored here.)
- General Code Window of the Book (Actions that would affect the whole book are stored here.)
Since we want to filter when we enter a data into a cell in the page, we will paste the ready-made codes we have obtained with Record Macro to the Code Window of the Page. When data is entered to the cell, Change will be triggered and filter will be applied.
Don’t forget that you can use the Record Macro for all your tasks.
Let’s say that the steps to be taken will always be:
- Click Record Macro
- Manually do the action you want
- Stop Recording
Codes will be prepared in the background.
WHERE CAN YOU ACCESS THESE CODES?
There are multiple ways to do something in Excel, just like in life. You can access these codes in a few different ways.
- By choosing View Macros where we chose Record Macro.
2. By pressing the Alt + F8 keys and accessing the window below with a shortcut.
3. By pressing the Alt + F11 keys and accessing the VBE window directly.
4. By choosing View Code after right-clicking the sheet tab.
Alriighht! Now that we have learnt enough, let’s take the action.
If you have already downloaded the file I have sent you, click Record Macro. Name your Macro, for example: Filter, and then click OK to start recording.
Choose any cell in our table and choose Filter in the Data menu.
Note: If you hover on the Filter a bit, it will give you the shortcut if there is. You can activate the filter with that shortcut as well.
And then unmark all in the Brand field and choose YELKEN, and then click OK.
Since we basically want to get the code of filtering, we have executed the action and we are done.
Now click Stop Recording to stop recording. Click View Macros.
The Macro List window will pop up and the Filter macro will be selected on it.
You can press the Edit button and view the codes in the Module1.
The codes that have been created will look like the image below.
The green rows that start with an apostrophe are comment rows, they don’t affect the codes, they are just for the explanation.
Note: You can delete the Selection.AutoFilter row. The row under it does the real job.
As you can see, we have easily obtained the codes of filtering.
Here is another information: You can assign the codes that start with Sub in the Module like above, and then run the macro by pressing the button. You can try this method if you please.
I will show how to filter the moment data is entered into the cell -which is a faster method. So, please keep reading.
Yes… we have obtained the codes.
Now there is only making the criteria dynamic so that it filters whatever we write into the H1 cell instead of “YELKEN” which was stated as the criterion in codes and running it when data is entered to the cell.
We will make a simple edit for this. Here is the code row that filters:
ActiveSheet.Range(“$A$1:$E$52″).AutoFilter Field:=3, Criteria1:=”YELKEN”
If you write the cell address like below where “YELKEN” is written in the code, you can use that cell as a filtering box dynamically.
ActiveSheet.Range(“$A$1:$E$52”).AutoFilter Field:=3, Criteria1:=Range(“H1”).Value
We have made the criteria dynamic. Now, let’s run this code depending on the data in the cell.
We have mentioned that we were going to write this code into the Change action of the Code Window of the Page since we want it to run when a data in the page changes.
So, what do we need to do now?
Right-click the tab and access the code window of the related page by choosing View Code. It will look like the image below at first.
Choose Worksheet in General.
Page actions that you can use will be uploaded to the Declarations field, choose the Change action there.
Related action will be added to the window.
Write the name of the macro we have created as Call Filter in that action.
Since the Selection_Change is unnecessary now, you can delete it.
When a data is entered/changed in your page, the Filter macro will run.
At this point we might come across some difficulties. We have left our code as data entry in a cell, not in a certain cell. Even if we make a change in a cell other than H1, filtering will be done. Thus, after every action we take in the page, the filter will be applied.
It would make more sense to adjust is as run only a change is done in the H1 cell.
And if we accept this as the last touch to make the code run in a more stable way, we can settle the matter by adding a little condition.
That last touch will be this condition: If Target.Address(0, 0) = “H1” Then
Explanation: We complete our code by saying If the address of the cell into which data was entered is H1…
You will see the result in the page like this. Whatever you write into H1, that name will be filtered in the Brand field.
Here is a little tip: if you write yel* into the cell and press Enter or add & “*” to the end of the code, the cell will filter without you writing the full name of the brand. For example: if you write yel and press enter, it will list all the records that start with yel.
ActiveSheet.Range(“$A$1:$E$52”).AutoFilter Field:=3, Criteria1:=Range(“H1”).Value & “*”
After everything is done and it is time to save the file, you need to save the file as Excel Macro-Enabled Workbook. (The Save As shortcut is F12.)
Congratulations! 👍🏻
You have just gained your first coding (creating a macro) experience.
These are very fun subjects, apart from also being very useful. I am sure the you will want more as you keep learning.
This article was for making you realize that you can get your tasks done quicker by codifying the actions you execute on Excel all the time.
You can access the last version of the file here.
Don’t forget that we can help you climb the success ladder in Excel with the Excel & VBA (Macro) Training and Consultancy Services as PEAKUP.
You can share this post to help many people get informed and get Excel Training to use Excel more efficiently and productively. 👍🏻
