Assign Values To Variables

You can assign values to variables at any point within a procedure after declaring the variable using the Dim statement. VBA uses variables as storage locations for data values. By using a variable you can change the value of an expression by simply assigning a different value to the variable. Most programmers commonly initialize, or assign an initial value, immediately after declaring the variable, but you can change the value of the variable at any location in the code as long as the variable is valid.

You assign a value to a variable that matches the data type specified for the variable. In other words, if you declare the variable as an integer value, you can only assign integer values to the variable. If you attempt to assign a value other than an integer to the variable, such as a string of text, you receive an error message when you run the macro.

If you assign a string value to a variable declared as an integer, Excel returns a "Type Mismatch" error when you run the macro. If you assign the variable a decimal value, such as 45.67, VBA truncates the decimal portion of the value and retains the integer value. See Chapter 3 for more information on data types.

When working with values that you type in a worksheet or dialog box, you need to check them before assigning them to variables to ensure that they are the proper data type. You can use the IsNumeric function to check the value before assigning it to a variable to ensure that a cell contains a numeric value. The IsNumeric function looks at the specified value and returns a Boolean value of True for numeric values.

The Variant data type works well in situations where the returned value is a different data type than the variable needs. When you use a Variant data type, VBA accepts any type of data value in the variable. Because VBA code runs more efficiently when you declare an actual data type, such as Integer, or Long, you should limit your use of the Variant data type.



-D Create a new subroutine.

B Declare variables using the Dim statement.

0 Assign the value to the variable.

-D Create a new subroutine.

0 0

Post a comment