Using Variables And Arrays

As you develop macros using VBA, the complexity of your code may make it difficult to keep track of different variables. To simplify the process, many developers use a standard naming convention where the variable name reflect the variable type. To use this type of naming convention, you preface each variable name with a standard lowercase prefix that identifies the data type of the variable. For example, you can identify an integer variable by prefixing it with i, to create the variable name iNumVisits. Using the integer prefix makes it clear at any location in the code that the variable holds an integer value. This naming convention is useful if you share your macro code with other people. The following table lists the standard variable-naming conventions for Visual Basic and VBA.

PREFIX

DATA TYPE

b

Boolean

c or cur

Currency

dt

Date/Time

d

Double

i or int

Integer

l orlng

Long

obj

Object

s or sng

Single

str

String

u

User-defined

v or var

Variant

_Q Specify the values for each element of the array.

_O Copy the contents of one array element to another array element.

_Q Specify the values for each element of the array.

■ You can specify a component value by typing NewArray(N).ComponentName.

_O Copy the contents of one array element to another array element.

■ The user-defined data type is created.

EXECUTE A TASK WHILE CONDITION IS TRUE

You can execute a task or a series of tasks as long as a specific condition is true by using the Do While loop statement in VBA. A Do While loop provides a great means for repeating a series of statements. For example, a Do While loop lets you apply changes to a series of cells as long as the cells contain a numeric value.

When you use the Do While loop, the statements specified between the Do and Loop statements execute as long as the condition is true. As soon as the looping structure determines that the condition is no longer true, control moves to the next statement outside the loop.

The Do While loop consists of four basic parts. The Do statement initiates the loop. You can locate the While

EXECUTE A TASK WHILE A CONDITION IS TRUE

condition statement following the Do statement, or at the end of the loop. The body of the loop contains a series of statements to perform as long as the condition is true. Finally, the Loop statement marks the end of the loop.

When you locate the While condition following the Do statement, the Do Loop verifies that the condition is true before executing. If the condition is not true, the loop does not execute. With this form of the Do Loop, the loop may never execute.

When you locate the While condition at the end of the loop, the Do Loop always executes once and then checks the condition. If the condition evaluates false at that point, the Do Loop stops execution, and control passes to the next VBA statement in your macro.

EXECUTE A TASK WHILE A CONDITION IS TRUE

—H Create a new subrouti

'—0 Initialize the value of the counter variable.

■ Alternately, you can skip step 5 and perform steps 6 through 8 to place the While condition at the end of the loop.

—H Create a new subrouti

Note: See Chapter 3 for information on creating subroutines.

^0 Type Dim N As Integer, replacing N with the name of the variable to use as the counter for your loop.

'—0 Initialize the value of the counter variable.

'-0 Type While N < M, replacing M with the maximum value for the counter variable and N with the variable specified in step 2.

■ Alternately, you can skip step 5 and perform steps 6 through 8 to place the While condition at the end of the loop.

0 0

Post a comment