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

I wish to extend the wise words of Steve Jobs and say everyone in the world should learn how to program a computer. You may not necessary end up working as a programmer or writing programs at all but it will teach you how to think.

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.

Why VBA?

VBA enables you to use English like statements to write instructions for creating various applications. VBA is easy to learn, and it has easy to use User Interface in which you just have to drag and drop the interface controls. It also allows you to enhance Excel functionality by making it behave the way you want.

Personal & Business Applications of VBA in Excel

For personal use, you can use it for simple macros that will automate most of your routine tasks. Read the article on Macros for more information on how you can achieve this.

For business use, you can create complete powerful programs powered by excel and VBA. The advantage of this approach is you can leverage the powerful features of excel in your own custom programs.

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

• 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

How to Enable the Developer Tab

Below is the step by step process on how to enable the developer tab in Excel:

• Create a new workbook
• Click on the ribbon start button
• Select options
• Click on customize ribbon
• Select the developer checkbox as shown in the image below
• Click OK

RELATED ARTICLES

• How to Write Macros in Excel: Macro Tutorial – What is & Use
• Excel VBA Tutorial for Beginners: Learn in 3 Days