WRITING MACROS WITHOUT KNOWING HOW TO CODE!?

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.

  1. 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. 👍🏻

Methods of Declaring VBA Variables

In this article, we will be talking about Declaring VBA Variables. Variables are essential elements of programming. Using and managing variables are one of the musts while creating a project. I will try my best to tell it as simple as possible. Reminder: You can find other articles about VBA on our blog. 👍🏻

 

About Declaring VBA Variables

Variables are usually used to store a data and use it when necessary. They are usually separated into two classes. Global variables and Local variables. Global variables can be used by all the functions of the program, but the local variables are used by the functions that have declared them.

It can be called back, reassigned or fixed during the execution of a procedure, function or module.
Declaring a variable will enable you to indicate the names of the variable you’ll use and the data type the variable will contain.

For example, if Result = 10, the variable Result can be declared as Integer Whole Number .

We usually name the variables in a short and easily remembered way. The most frequently used variable names are one character names like i, a, n, x ,y ,z, s so that it is easy to write in the code. If the variable name is a name that you can remember while using in the code, the probability of making a mistake while writing the code decreases.

Now we can move on to the declaring part.

 

The syntax concerning declaring variables is usually like this.
Dim variable_name [(stringsize)] As type
Public variable_name[(stringsize)] As type
Static variable_name[(stringsize)] As type

 

Along side this general declaring, the declaring methods below can be used as well.

  1. Declaring with Dim
  2. Declaring with Data Indicators (Abbreviations)
  3. Declaring with DEF

Declaring with Dim

It is the most known and used VBA Variable Declaring method.

We indicated the syntax Syntax below. Let’s make it clear with a few examples. Let’s say that we will declare a variable named row to use in the rows (cells) in the A column. Since the row numbers are whole numbers, we can used one of the whole number types we’ve indicated in our Data Types article. It would be better to use the variable data type depending on the row number we’ll get controlled or the maximum number that can be in the cell.

As well as we can use as Number or Whole Number, we have 3 basic variable data types: ByteInteger and Long. If the number we’ll assign to the row variable is 255 or less, then we can use the Byte variable data type. If the number we’ll assign to the row variable is between –32767 and+32768, then we can use the Integer variable data type. If it can be a bigger whole number, then we should use the Long variable data type. If a bigger number than what the variable can contain is sent, that the Overflow error occurs. And if a text data is sent to a variable that was determined as number, Type Mismatch error occurs.

 

Let’s give a few examples of declaring variables with Dim:

Sub PEAKUP()
    Dim row As Long
    Dim column As Byte
    Dim text As String
    Dim start As Date
    Dim money As Currency
    Dim object As Object
    row = 15
    column = 5
    text = "Excel Turkey Forum"
    start = "24.06.2018"
    money = 300
    Set object= ActiveSheet
End Sub

We can write each variable one by one in different rows like that, but we also can write them side by side like this. We just need to put Dim in the beginning and put a comma between each variable.

Sub PEAKUP()
    Dim row As Long, column As Byte, text As String
    Dim start As Date, money As Currency, object As Object
End Sub

We need to be careful about this here: Some users make a mistake and declare incorrectly.

If you write the code I gave above like this one below, I mean if you start with Dim and think that you’ve declared the first variable and not declare the other variables with the suitable variable data types. Since in the “column”, “text” variable the data type is not stated, Byte and String are not indicated but Variant is -which is undefined data type. Since in the first variable I’ve declared with Dim, you declare the data type in the first variable. So, it doesn’t mean that you declare the next variables as well. You need to state the data type of each variable one by one.

 

Declaring with Data Identifiers (Abbreviations)

Abbreviations 

They are also known as Type Indication suffixes.
They are not used much but they help to save in codes.
It is also possible to tell a variable type by adding a special character to the end of the variable name in VBA.

Dim number% 'Integer
Dim longnumber& 'Long
Dim sum! 'Single
Dim subtotal# 'Double
Dim payment@ 'Currency
Dim name$ 'String
Dim longestnumber^ ' 64 bit LongLong

 

Data Type Abbreviation Characters

VBA

, as a fast way of declaring data type, lets you add a character in the name of a variable.
This method shouldn’t be used to declare variables and it can be used for retrospective purposes only.
The row below will declare a Double data type and a variable.

Dim dDouble#

But it is better for this row to be declared with the “As” keyword.
Dim dDouble As Double

 

Data Type Abbreviation/ Suffixes

If you you abbreviations, you don’t have to declare the type.
If you use the % expression, you don’t need to write “As Integer”.
These abbreviations can be helpful to get available information to Variants.

For example: count =10#

 

 

Declaring with DEF

We can declare our variables with different methods like we mentioned, one of these methods is declaring with DEF.
This declaration is usually done free from the procedure at the top of the code window.
We can abbreviate and declare the data type we use as variable like below.
The letter that comes after Def+Type indicated that the variables starting with that letter belong to that type.

def ile değişken tanımlama tablosu

Let’s see an example that shows the difference between declaring with Def and Dim.

First, let’s declare our variables like this with Dim.

Sub PEAKUP()
    Dim row As Integer, column As Integer
    Dim text As String, letter As String, word As String
    Dim date As Date, start As Date
    Dim number As Double, price As Double
    row= 10
    column= 5
    text = "PEAKUP"
    letter = "E"
    word = "Book"
    date = "24.06.2018"
    start = "14.12.1980"
    number = 1453.48
    price = 5647.15
End Sub

Now, let’s do the same declaration with Def.

DefInt R
DefStr L, W, T
DefDate S, D
DefDbl P, N
Sub PEAKUP()
    row = 10
    column = 5
    text = "PEAKUP"
    letter = "E"
    word = "Book"
    date = "24.06.2018"
    start = "14.12.1980"
    number = 1453.48
    price = 5647.15
End Sub

As you see, we got to indicate the type by using the initials and declare the variables. At this point, you want the variable declarations not to take too much space and be seen in less rows. As indicated below, you can write the Def rows in a row next to one other with a colon (:).

DefInt R: DefStr L, W, T: DefDate S, D: DefDbl P, N
Sub PEAKUP()
    row = 10
    column = 5
    text = "PEAKUP"
    letter = "E"
    word = "Book"
    date = "24.06.2018"
    start = "14.12.1980"
    number = 1453.48
    price = 5647.15
End Sub

By the way, you can easily follow and evaluate the names, values and types of all variables from Locals Window.

def ile değişken tanılamayı locals windowda görüntüleme

 

You can take a look at the Microsoft Docs page for more information.

See you in other articles, bye.🙋🏻‍♂️
You can share this post with your friends and get them informed as well. 👍🏻