Excel VBA Guide: Introduction To Basic Excel VBA Macros For Beginners #01

Have you ever heard the term Macro or VBA ? I’m sure it has. If not, it looks like you’ve lost so you arrived at this blog.

As an Excel user who is actively learning to improve your ability to maximize office excel, of course, you often have discussions and ask questions about excel problems.

Among these problems, it turns out that the solution is to apply VBA macro codes which may instead of solving the problem actually create new problems for you. Yes, your problem increases because you find it difficult and confused to use or understand the meaning of the VBA macro codes.

From what I have experienced, this is generally caused by the VBA learning method that jumps far, especially when you have never previously known any computer programming language.

It’s like you haven’t fully mastered the techniques of addition, subtraction, division and multiplication but then try to learn calculus. Wow, what kind of creature is this calculus? Same, I also don’t understand. If you really want to know, please use Google , Bing or Yahoo! and his friends.

Before further learning Macro VBA, first I highly recommend you to learn a lot to use and take advantage of the features and functions that have been provided by excel. 

From my experience, this will make it easier for you to enter this VBA Class . Because after all, basically what is processed by Excel VBA are the features that have been included in excel itself.

Are you ready to learn Excel VBA?

What are Excel VBA Macros?

Before we learn more about VBA Macro excel, let’s get acquainted with the terms VBA and Macro in Microsoft Excel.

Understanding VBA

VBA stands for Visual Basic for Application which is a derivative of the Visual Basic language developed by Microsoft .

Visual Basic itself is a derivative of the BASIC programming language (Beginners’ All-purpose Symbolic Instruction Code) which was developed in 1963 by John George Kemeny and Thomas Eugene Kurtz from Dartmouth College.

If you are already quite mastered the Basic language then you will be easier to learn Visual Basic (VB) and so if you are quite proficient in VB then you will definitely be easier to master the language of VBA ( Visual Basic for Application ).

Let alone Visual Basic Language or Basic Language, I even just read there is a programming language term.

You can imagine a programming language such as VBA as a language for human communication where we know the terms English , German , Dutch , Korean and others.

Instead of being used for communication between humans, computer programming languages ​​are used by humans to communicate with computers, more precisely to give instructions or commands to carry out certain tasks and functions.

In contrast to VB which is generally used to create a stand-alone application ( Stand Alone ), as can be read from its name, VBA is designed to work on top of other applications. In VBA Excel terms , this other application is office excel itself. So VBA will not be able to run without Excel.

VBA is not only available in excel, in other office program packages, VBA is also included, so it is also known as VBA Access, VBA Word, VBA Power point and so on. Although with the same way of working of course with the peculiarities of each. Assume that VBA is a regional language, then VBA Excel, VBA Access, VBA Word and VBA Powerpoint are accents or dialects for a regional language.

Because this blog discusses excel, of course, the VBA is referred to in this tutorial and the next tutorial is VBA Excel. which is a computer programming language that allows us to communicate or provide certain instructions with excel.

In summary, VBA is a programming language developed by Microsoft that is included in most of the products that are part of Microsoft Office.

So what are macros?

Understanding Macros

Excel macros and Visual Basic for Applications (VBA) are not exactly the same, although they are closely related, and we often confuse and use them interchangeably. Sometimes called macros, sometimes called VBA and other times called VBA Macros or VBA Macros.

Macros are not a programming language. John Walkenbach defines macros as follows:

A macro is a sequence of instructions that automates some aspect of Excel.

Macros are lines of command or code with which you want Excel to do things automatically. In other words, macros are specific code or scripts, while Visual Basic for Applications is the programming language you use to create macros.

Still, confused too?

Well, try writing on a piece of paper the following instructions:

1. Please stand in front of the house
2. Hold your head with both hands
3. Shout with all your might

Have you done it?

Ms. Office itself defines macros and VBA as follows:

A macro is a tool that enables you to automate tasks and add functionality to your forms, reports, and controls. Like macros, VBA lets you add automation and other functionality to your Access application. You can extend VBA by using third-party controls, and you can write your own functions and procedures for your own specific needs.

A macro is a tool that allows you to automatically execute commands and add functionality to your forms, reports, and controls.

Like macros, VBA allows you to run certain tasks automatically and add other functions to the excel application. You can extend the usability of VBA by using third-party controls, and you can write functions and procedures according to your specific needs.

Maybe you don’t agree with me in interpreting these two terms. No problem, please submit in the comments column for additional references for me.

Okay, then there is also the term VBA Code and also VBA Script. What is the meaning of this?

Never mind, I don’t think we need to go on to discuss the meaning of Macros and VBA. After all, in the end, what is more, important is how we can compose VBA macro codes in excel according to our individual needs. Right?

In the VBA tutorial on this blog, I will use the terms Macro, Code, Script, and VBA in the same sense to make it easier to use the terms.

After getting a little idea of ​​what VBA Macros are, let’s continue to learn these initial steps to learn basic Excel VBA for beginners.

Steps to Learn Basic Excel VBA Macros

Before learning more about Excel VBA Macros, there are a few things you need to do to follow the next tutorials. To equal perceptions, although you don’t have to do it, I suggest you to follow and practice each of the following steps directly.

Create a Special Folder for Learning Excel VBA

The first step you need to do is create a special folder to store VBA learning results and files from your practice. It’s up to you where you will put the folder. For example, you can save this folder in Drive D and give the name for this folder ” VBA Class “.

Showing the Developer TAB

After you have finished creating the “VBA Class” folder, the next step is to make sure that the Developer TAB has appeared in the excel application you are using. 

Check Security Settings

To ensure that the VBA codes that we will write run normally, make sure that the excel you are using already allows the use of macros. The method is as follows:

• In the Developer TAB, click ” Macro Security ” in the ” Code ” group.
• After the Trust center window appears, just set it as shown below:

Information:

1. Disable all macros without notifications: Disable macros without notifications. Macros cannot be run at all
2. Disable all macros with notifications: Disable macros with notifications, macros can run if allowed by the User.
3. Disable all macros except digitally signed macros: This option will only allow Macros (VBA) that have included a digital card.
4. Enable all macros (not recommended, potentially dangerous code can run): Allows all macros to run in excel
5. Trust Access to the VBA project object model: This option item is special permission to be able to access the VBProject component. If checked, it will be given permission to access the VBProject component.

Setting Trusted Locations

In addition to using digital signatures, excel files containing trusted VBA can bypass the security macro section by placing the file in a trusted location.

By saving the file in this trusted folder, the excel file containing the VBA macro codes can run without checking so that it raises a warning or notification as usual. This location is usually your setting in the Trust Center in the Trusted Locations section.

Here are the setup steps you need to do to enter the “VBA Class” folder that we created earlier as one of the trusted locations :

1. In the Developer TAB, click ” Macro Security ” in the “Code” group as before.
2. After the Trust center window appears, select the ” Trusted Locations ” section.
3. Next select Add new location.

4. Select the Browse menu button and locate the folder you want to trust earlier.

5. If other sub folders or folders in the folder that we choose will also be put in a trusted location, check the Subfolders of this location are also trusted section

6. Next provide a description for the selected location (optional) 

7. Click OK and OK again to exit settings

If the steps you have taken are correct, the result will be something like this:

Saving Files With Proper Extension

If the excel file you are using uses Macro VBA, it must be saved in the proper excel file format. Namely an excel file format that supports enabled macros.

This is important for you to know because if you save the wrong format in a format that does not support Macros, the macro code that you write can be lost in the 7th layer of the earth.

For now I suggest you save the excel file in Excel Macro-Enabled Workbook(.xlsm) format with the file name ” Macro Class 1 “.

Loch doesn’t be dumbfounded, please save the file in the folder you specified earlier? Or maybe you haven’t even opened the excel program? Loch, we haven’t written any code yet? Yes, that’s okay, we’ll make the file in installments first.

Okay, until this stage we already have an excel file with the name ” Macro 1.xlsm ” which is stored in the ” VBA Class ” folder.

Next, we will write our first macro code or script. OK

Opening the Visual Basic Editor

The next step is to open VBE or Visual Basic Editor . VBE is a facility to interact to compose VBA codes.

There are three ways to open VBE in excel:

1. In TAB Developer — Code group –Choose the menu of Visual Basic.

2. Right-click the name of a sheet in the sheets tab and select View Code.

3. The last and easiest way is to use the Alt+F11 shortcut .

After you successfully open the Visual Basic Editor (VBE), it looks more or less as follows:

For now, just let the VBE look like that in the next tutorial, we will discuss in more detail about the options in this Excel VBE.

Creating a New Module

The next activity you need to do in this Basic Excel Macro lesson for beginners is to create a new module.

In VBE a macro is stored in a module. to create your first module the steps are as follows:

1. In Project Explorer right-click the VBAProject which contains the file name.
2. Select Insert–Module
3. Then a new module will appear in the Project Explorer .
4. The first module you created earlier will be named Module1 by default .

When you double click on a Module in the VBA Project window, the code for that module will be displayed in the main code window. A new module is usually empty, but may contain the text ” Option Explicit ” if you have enabled this option. About Option Explicit will be explained in another section.

Creating the First Macro

Your next task is to write the code ” First Sub Macro ” in the main code window . Then press ENTER.

Automatically after you press ENTER, under the code you created earlier the text ” End Sub ” will appear . In the middle of the code we created earlier, add the following macro code writing:

MsgBox “I’m learning VBA macros”

So the final result of the script or code that you create is as follows:

Sub FirstMacro()

MsgBox “I’m learning VBA macros”

End Sub

Running the First Macro

To view or run the results of the macro code that you have created, do the following:

1. Click any part of the First Macro that you have created, for example in front of the MsgBox text “I am learning VBA macros”
2. Select the Run menu–Run Sub/UserForm

The result will appear a dialog box like this:

Conclusion

This VBA Macros Guide shows you the preparation steps and how to create and run a simple macro using VBA.

For now you are ready to start learning Excel VBA at a later stage. Please re-read and leave a comment after following the simple macro guide above.