Learning VBA

This page provides an organized entry to the MS VBA documentation. Each heading can be clicked to go the to page with that name. The paragraph below it gives summary information what can be found there. It is assumed you have some practical acquaintance with programming in general.

Also see:

This page in German, French, Spanish, Portuguese
Learn Word VBA, Learn Excel VBA

Understanding Visual Basic syntax

The syntax in a Visual Basic Help topic for a method, function, or statement shows all the elements necessary to use the method, function, or statement correctly. The examples in this topic explain how to interpret the most common syntax elements.

Writing Visual Basic statements

A statement in Visual Basic is a complete instruction. It can contain keywords, operators, variables, constants, and expressions.

Writing declaration statements

You use declaration statements to name and define procedures, variables, arrays, and constants. When you declare a procedure, variable, or constant, you also define its scope, depending on where you place the declaration and what keywords you use to declare it.

Writing assignment statements

Assignment statements assign a value or expression to a variable or constant. Assignment statements always include an equal sign (=).

Writing a Sub procedure

A Sub procedure is a series of Visual Basic statements enclosed by the Sub and End Sub statements that performs actions but doesn't return a value.

Writing a Function procedure

A Function procedure is a series of Visual Basic statements enclosed by the Function and End Function statements. A Function procedure is similar to a Sub procedure, but a function can also return a value.

Calling Sub and Function procedures

To call a Sub procedure from another procedure, type the name of the procedure and include values for any required arguments. The Call statement is not required, but if you use it, you must enclose any arguments in parentheses.

Understanding named arguments and optional arguments

When you call a Sub or Function procedure, you can supply arguments positionally, in the order that they appear in the procedure's definition, or you can supply the arguments by name without regard to position.

Using parentheses in code

Sub procedures, built-in statements, and some methods don't return a value, so the arguments aren't enclosed in parentheses.

Declaring variables

When declaring variables, you usually use a Dim statement. A declaration statement can be placed within a procedure to create a procedure-level variable. Or it may be placed at the top of a module, in the Declarations section, to create a module-level variable.

Understanding the lifetime of variables

The time during which a variable retains its value is known as its lifetime. The value of a variable may change over its lifetime, but it retains some value. When a variable loses scope, it no longer has a value.

Using data types efficiently

Data type summary

A data type is the characteristic of a variable that determines what kind of data it can hold. Data types include those in the following table as well as user-defined types and specific types of objects.

Declaring constants

Your code might contain frequently occurring constant values, or might depend on certain numbers that are difficult to remember and have no obvious meaning. You can make your code easier to read and maintain by using constants. A constant is a meaningful name that takes the place of a number or string that does not change. You can't modify a constant or assign a new value to it as you can a variable.

Using If...Then...Else statements

You can use the If...Then...Else statement to run a specific statement or a block of statements, depending on the value of a condition. If...Then...Else statements can be nested to as many levels as you need.

Using Select Case statements

Use the Select Case statement as an alternative to using ElseIf in If...Then...Else statements when comparing one expression to several different values. While If...Then...Else statements can evaluate a different expression for each ElseIf statement, the Select Case statement evaluates an expression only once, at the top of the control structure.

Looping through code

By using conditional statements and looping statements (also called control structures), you can write Visual Basic code that makes decisions and repeats actions.

Using Do...Loop statements

You can use Do...Loop statements to run a block of statements an indefinite number of times. The statements are repeated either while a condition is True or until a condition becomes True.

Using For...Next statements

You can use For...Next statements to repeat a block of statements a specific number of times. For loops use a counter variable whose value is increased or decreased with each repetition of the loop.

Understanding objects, methods, properties, and events

An object represents an element of an application, such as a worksheet, a cell, a chart, a form, or a report. In Visual Basic code, you must identify an object before you can apply one of the object's methods or change the value of one of its properties.

Creating object variables

You can treat an object variable exactly the same as the object to which it refers. You can set or return the properties of the object or use any of its methods.

Writing executable statements

An executable statement initiates action. It can execute a method or function, and it can loop or branch through blocks of code. Executable statements often contain mathematical or conditional operators.

Writing a property procedure

A property procedure is a series of Visual Basic statements that allow a programmer to create and manipulate custom properties. Property procedures can be used to create read-only properties for forms, standard modules, and class modules. Property procedures should be used instead of Public variables in code that must be executed when the property value is set.

Object models

To customize the working of an Office application, you have to understand the Object model hierarchy of that application. Linked pages shows these models for Excel, Word and PowerPoint.