How to Write Macros in Excel and Use

Today, We will know How to Write Macros in Excel, What is Macro and How to Use Macro.

Excel Macro is a piece of programming code that runs in an Excel environment but you don’t need to be a coder to program macros. VBA Macros save time as they automate repetitive tasks. Though, you need basic knowledge of VBA to make advanced modifications in the macro.

Why are Excel Macros Used in Excel?

As humans, we are creatures of habit. There are certain things that we do on a daily basis, every working day. Wouldn’t it be better if there were some magical way of pressing a single button and all of our routine tasks are done? I can hear you say yes. Macro in Excel helps you to achieve that. In a layman’s language, a macro is defined as a recording of your routine steps in Excel that you can replay using a single button.

For example, you are working as a cashier for a water utility company. Some of the customers pay through the bank and at the end of the day, you are required to download the data from the bank and format it in a manner that meets your business requirements.

You can import the data into Excel and format. The following day you will be required to perform the same ritual. It will soon become boring and tedious. Macros solve such problems by automating such routine tasks. You can use a macro to record the steps of

• Importing the data
• Formatting it to meet your business reporting requirements.

What is VBA in a Layman’s Language?

VBA is the acronym for Visual Basic for Applications. It is a programming language that Excel uses to record your steps as you perform routine tasks. You do not need to be a programmer or a very technical person to enjoy the benefits of macros in Excel. Excel has features that automatically generated the source code for you. Read the article on VBA for more details.

Excel Macro Basics

Macros are one of the developer’s features. By default, the tab for developers is not displayed in Excel. You will need to display it via customize report

Excel Macros can be used to compromise your system by attackers. By default, they are disabled in Excel. If you need to run macros, you will need to enable running macros and only run macros that you know come from a trusted source

If you want to save Excel macros, then you must save your workbook in a macro-enabled format *.xlsm

The macro name should not contain any spaces.

Always fill in the description of the macro when creating one. This will help you and others to understand what the macro is doing.

Enable Developer Option on Excel

To execute the VBA program, you have to have access to the developer option in Excel. Enable the developer option as shown in the below Excel macro example and pin it into your main ribbon in Excel.

Step 1)Go to main menu “FILE”

Select option “Options.”

Step 2) Now another window will open, in that window do the following things

• Click on Customize Ribbon
• Mark the checker box for Developer option
• Click on OK button

Step 3) Developer Tab

You will now be able to see the DEVELOPER tab in the ribbon

VBA in Excel: What is Visual Basic for Applications, How to Use

What is Visual Basic for Applications (VBA)?

Visual Basic for Applications (VBA) is an event-driven programming language implemented by Microsoft to develop Office applications. VBA helps to develop automation processes, Windows API, and user-defined functions. It also enables you to manipulate the user interface features of the host applications.

Before we go into further details, let’s look at what computer programming is in a layman’s language. Assume you have a maid. If you want the maid to clean the house and do the laundry. You tell her what to do using let’s say English and she does the work for you. As you work with a computer, you will want to perform certain tasks. Just like you told the maid to do the house chores, you can also tell the computer to do the tasks for you.

The process of telling the computer what you want it to do for you is what is known as computer programming. Just as you used English to tell the maid what to do, you can also use English-like statements to tell the computer what to do. The English-like statements fall in the category of high-level languages. VBA is a high-level language that you can use to bend excel to your all-powerful will.

VBA is actually a sub-set of Visual Basic 6.0 BASIC stands for Beginners All-Purpose Symbolic Instruction Code.

Introduction to Visual Basic for Applications

Before we can write any code, we need to know the basics first. The following basics will help you get started.

• Variable – in high school we learnt about algebra. Find (x + 2y) where x = 1 and y = 3. In this expression, x and y are variables. They can be assigned any numbers i.e. 1 and 3 respective as in this example. They can also be changed to say 4 and 2 respectively. Variables in short are memory locations. As you work with VBA Excel, you will be required to declare variables too just like in algebra classes
• Rules for creating variables
  • Don’t use reserved words – if you work as a student, you cannot use the title lecturer or principal. These titles are reserved for the lecturers and the school authority. Reserved words are those words that have special meaning in Excel VBA and as such, you cannot use them as variable names.
  • Variable names cannot contain spaces – you cannot define a variable named first number. You can use firstNumber or first_number.
  • Use descriptive names – it’s very tempting to name a variable after yourself but avoid this. Use descriptive names i.e. quantity, price, subtotal etc. this will make your Excel VBA code easy to read
• Arithmetic operators – The rules of Brackets of Division Multiplication Addition and Subtraction (BODMAS) apply so remember to apply them when working with expressions that use multiple different arithmetic operators. Just like in excel, you can use
  • + for addition
  • – for subtraction
  • * for multiplication
  • / for division.
• Logical operators – The concept of logical operators covered in the earlier tutorials also apply when working with VBA. These include
  • If statements
  • OR
  • NOT
  • AND
  • TRUE
  • FALSE

VBA Hello World!

Now we will demonstrate how to program in the VBA programming language. All program in VBA has to start with “Sub” and end with “End sub”. Here the name is the name you want to assign to your program. While sub stands for a subroutine which we will learn in the later part of the tutorial.

Sub name()
.
.
. 
End Sub

We will create a basic VBA program that displays an input box to ask for the user’s name then displays a greeting message

• Create a new work book
• Save it in an excel macro enabled worksheet format *.xlsm
• Click on the DEVELOPER tab
• Click on INSERT drop down box under controls ribbon bar
• Select a command button as shown in the image below

Draw the command button anywhere on the worksheet

You will get the following dialogue window

• Rename the macro name to btnHelloWorld_Click
• Click on new button
• You will get the following VBA code window

Enter the following instruction codes

Dim name As String
name = InputBox("Enter your name")
MsgBox "Hello " + name

HERE,

• “Dim name as String” creates a variable called name. The variable will accept text, numeric and other characters because we defined it as a string
• “name = InputBox(“Enter your name”)” calls the built in function InputBox that displays a window with the caption Enter your name. The entered name is then stored in the name variable.
• “MsgBox “Hello ” + name” calls the built in function MsgBox that display Hello and the entered name.

Your complete code window should now look as follows

• Close the code window
• Right click on button 1 and select edit text
• Enter Say hello

• Click on Say Hello
• You will get the following input box

• Enter your name and Press OK
• You will get the following message box