Note To learn how to run a macro see Chapter

GLOBAL VARIABLES

You can declare global variables that all modules in a project can access. When you talk about where you can use a variable, you refer to the scope of the variable. When creating Excel macros in VBA, you can use variables on three different levels: procedure level, private module level, and public module level. Of these three variable types, the private module level and public level are two different types of global variables.

Available only to other procedures within the same module, you declare private module-level variables at the top of the module with the use of the Private keyword. In other words, they are global within the module, but not available to other modules in the procedure. If the module only contains one procedure, declaring a private module-level variable is the same as a procedure-level variable.

You declare the other type of global variable, public module-level variables, at the top of a module, which makes them available globally, or to all modules within the corresponding procedure. You declare these variables using the Public keyword.

Keep in mind that you can use the Dim keyword at the module level to declare variables. When you use the Dim keyword at the module level, it has the same effect as the Private keyword and creates a private module-level variable. Because the Dim keyword is more confusing than using Public and Private keywords, you typically avoid it with module-level variables.

Keep in mind, you declare procedure-level variables within a specific subroutine or function using a Dim statement. Because you can use them only within the procedure, you typically refer to the variable as a local variable. Because a local variable is only valid within that procedure, other procedures can have variables with the same name, and you have no conflict.

USING GLOBAL VARIABLES

USING GLOBAL VARIABLES

-D At the top of a module, type Public PubVar As DataType, replacing PubVar with the variable and DataType with the variable's data type.

0 Create a new subroutine.

□ Create another subroutine in the same module.

-D At the top of a module, type Public PubVar As DataType, replacing PubVar with the variable and DataType with the variable's data type.

Note: For more information on data types, see Chapter 3.

0 Create a new subroutine.

-■ You can type Private in front of the subroutine so it does not appear on the macro list.

Set the value of the PubVar variable.

□ Create another subroutine in the same module.

Set the value of the variable to be the current value plus a value.

0 0

Post a comment