VBA Programming Fundamentals

Contents

Comments Variables Data Types Scope of A Variable Strings Assignment Statement Arrays Object Variables Manipulating Objects and Collections GoTo Statement If-Then Constructs Select Case Constructs For-Next Loops Do While Loops Do Until Loops Reference

Comments

A Comment is a text that is used to describe a code (or it’s part).

It is ignored by VBA & is not executed.

It can be used when there is an error in a program or a program needs a revision.

It’s always a good idea to include comments in one’s program to make things easier.

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.

Variables

A variable is simply a named storage location in your computer’s memory.

Variables can accommodate a wide variety of data types — from simple Boolean values (True or False) to large, double-precision values.

You assign a value to a variable by using the equal sign operator VBA has many reserved words, which are words that you can’t use for variable or procedure names.

If you attempt to use one of these words, you get an error message.

Data Types

Data type refers to how data is stored in memory — as integers, real numbers, strings, and so on.

Scope of A Variable

A variable’s scope determines in which modules and procedures you can use the variable.

Strings

Like Excel, VBA can manipulate both numbers and text (strings).

There are two types of strings in VBA:

Fixed-length strings are declared with a specified number of characters. The maximum length is 65,535 characters.

Variable-length strings theoretically can hold up to 2 billion characters.

Each character in a string requires 1 byte of storage, plus a small amount of storage for the header of each string.

Assignment Statement

An assignment statement is a VBA instruction that makes a mathematical evaluation and assigns the result to a variable or an object.

VBA Logical Operators

Arrays

An array is a group of elements of the same type that have a common name.

You declare an array with a Dim or Public statement, just as you declare a regular variable.

You can also specify the number of elements in the array.

You do so by specifying the first index number, the keyword To, and the last index number — all inside parentheses.

A dynamic array doesn’t have a preset number of elements.

You declare a dynamic array with a blank set of parentheses:

Dim MyArray() As Integer

Object Variables

An object variable is a variable that represents an entire object, such as a range or a worksheet.

Object variables are important for two reasons:

They can simplify your code significantly.

They can make your code execute more quickly.

Object variables, like normal variables, are declared with the Dim or Public statement.

Manipulating Objects and Collections

With-End With constructs - The With-End With construct enables you to perform multiple operations on a single object.

For Each-Next constructs - Suppose that you want to perform some action on all objects in a collection. Or suppose that you want to evaluate all objects in a collection and take action under certain conditions. These occasions are perfect for the For Each-Next construct because you don’t have to know how many elements are in a collection to use the For Each-Next construct.

The syntax of the For Each-Next construct is:

GoTo Statement

The most straightforward way to change the flow of a program is to use a GoTo statement.

This statement simply transfers program execution to a new instruction, which must be preceded by a label (a text string followed by a colon, or a number with no colon).

If - Then Constructs

The basic syntax of the If-Then construct is:

If condition Then true_instructions [Else false_instructions]

The If-Then construct is used to execute one or more statements conditionally.

The Else clause is optional.

Select Case Constructs

The Select Case construct is useful for choosing among three or more options. this construct also works with two options and is a good alternative to If-Then-Else.

The syntax for Select Case is as follows:

For-Next Loops

Simplest Type of Loop is a For-Next Loops, It’s syntax is as follows:

Do While Loops

This section describes another type of looping structure available in VBA.

Unlike a For-Next loop, a Do While loop executes as long as a specified condition is met.

A Do While loop can have either of two syntaxes:

Do Until Loops

The Do Until loop structure is very similar to the Do While structure.

The difference is evident only when the condition is tested.

In a Do While loop, the loop executes while the condition is True; in a Do Until loop, the loop executes until the condition is True.

Do Until also has two syntaxes:

Reference

“Excel 2010 Power Programming with VBA” by John Walkenbach.

THANK YOU