Numeric Expressions


Numeric expressions are what I normally think of when I use the generic term "expression." Whether it's calculating gross margin, figuring out commissions, or determining the monthly payment on a loan, many expressions perform some kind of number crunching. You saw VBA's arithmetic operators earlier in this chapter. This section adds to that by giving you a quick look at VBA's built-in math and financial functions.

VBA's Math Functions

Understanding Operator Precedence


You'll often use simple expressions that contain just two values and a single operator. In practice, however, many expressions you use will have a number of values and operators. In these more complex expressions, the order in which the calculations are performed becomes crucial. For example, consider the expression 3+5^2. If you calculate from left to right, the answer you get is 64 (3+5 equals 8 and 8^2 equals 64). However, if you perform the exponentiation first and then the addition, the result is 28 (5^2 equals 25 and 3+25 equals 28).



The Assignment Operator
We have already seen the first of VBA's operators: the assignment operator, which is just the equals sign (=). It is used to assign the result of an expression to a variable.
Always keep in mind that VBA always derives the result of the right side of the equation (that is, the expression) before it modifies the value of the left side of the equation.

For example, consider the following code fragment:

currentYear = 2004
currentYear = currentYear + 1

VBA Expression


VBA Expressions



A comment is descriptive text embedded within your code. The text of a comment is completely ignored by VBA. It’s a good idea to use comments liberally to describe what you’re doing.

You can use a complete line for your comment, or you can insert a comment after an instruction on the same line. A comment is indicated by an apostrophe. VBA ignores any text that follows an apostrophe—except when the apostrophe is contained within quotation marks—up until the end of the line. For example, the following statement does not contain a comment, even though it has an apostrophe:




Variables , Data Types and Constants


Variables are basically used in a Code for Temporary storage.

Declaring a Variable
To declare a variable is to tell the computer to reserve space in memory for later use. To declare a variable use a Dim (short for Dimension) statement.

dim myVar as String

Here String is the Data Type of the variable, which tell what kind of data to store in the variable. Here we have used String which means that myVar will be used to store characters.

Assigning value to a Variable

myVar = "Ravi Sagar"

Programming Constructs in VBA


We will briefly discuss the basic Programming constructs used in the VBA Programming Programming. Any VBA code is build up using these constructs. You can say that these are the building blocks of the Macro.

Hello VBA World!


Yeah it is compulsory to have a Hello World! Program in all the Programming Books. So here it is.

1. Create a new Module as explained earlier.
2. Type sub Hello_World and press Enter.
3. Press Tab, type msgbox "Hello VBA World!", and press Enter.
4. Press CTRL + S to save the Module. After completing these steps your Macro would appear as below.

Sub Hello_World()
 MsgBox "Hello VBA World!" 
End Sub

Run this Macro and be amazed.

1. Go back to the Excel Sheet
2. Press ALT + F8

VBA Editor


So you have recorded your first Macro in Excel VBA but most of the times you want to do something more that cannot be achieved with just recording and for customizing the Macro you need to Modify the VBA Code. VBA Editor is just for that, modifying and writing your own code.

What is VBA Editor?

VBA Editor is the Window where Code for Excel VBA is written. It is just like any other Text editor like Notepad and Wordpad. Instead of plain English the Code is written in the Language which Excel can understand. VBA Editor is also a Program to create and Edit VBA Macros. You can also call it as IDE (Integrated Development Environment)

How to Open VBA Editor

Select Tools > Macro > Visual Basic Editor Or simply press ALT + F11, it is the shortcut to open the VBA Editor.

Parts of VBA Editor

VBA Editor can be customized by the user. Many Windows can be added and removed but It is important to know the following Windows.

- Editing window: Displays VBA code for editing and User Forms for design.
- Project Explorer window: Displays the loaded VBA projects and the components of each project.
- Properties window: Used to view and edit the properties of the current object.
- Immediate window: Displays the output of debugging statements in your code.

Create a new Module

Steps to create a new Module.

1. Open the Excel Worksheet where you want to create a Macro.
2. Press ALT + F11 to open the VBA Editor
3. In the Project Explorer Window right click on the VBAProject(Excel Sheet Name) > Insert Module
4. By Default a Module with the name "Module1" will be created under a Folder Modules.

Edit existing Module

Steps to edit any existing Module.

1. In the Project Explorer Window > Click on the small plus sign in front of VBAProject (Excel Sheet Name).
2. Sub Folders will be expanded
3. Go to Modules Sub Folder > Double Click on the Module you want to edit
4. A new window containing the content of the Module will be opened. Do the necessary modification > Save the Module and Close the Window.
5. Now you have just edited the Module!



AutoFit is something that makes the data in the Sheet more presentable and readable. This is one example where we can create a simple Macro to apply AutoFit property to the sheet.


1. Start Recording a Macro and name it AutoFit
2. Select entire sheet
3. Double click on the divider between Column A and Column B
4. Stop the Macro

The following code will be generated.

Sub AutoFit()
' AutoFit Macro
' Macro recorded 3/10/2008 by Ravi Sagar
End Sub

What is VBA Programming


In Excel, you can automate tasks by using macros. A macro is a set of instructions that tells Excel what to do. These commands are written in a computer programming language called VBA. VBA is a short form of Visual Basic for Applications. It is a Powerful Programming Language with rich features. Following are the list of few things that you can do with VBA.

Syndicate content

Suggested Book to learn VBA