Variables and Their Operations

Variable Naming Rules

Variable names must follow certain rules. There are differences in variable naming rules between Excel VBA and Python. Below are the naming rules for variables in Excel VBA and Python.

Excel VBA Variable Naming Rules

Variable names can consist of letters, numbers, and underscores, but must start with a letter.

Variable names cannot be Excel VBA keywords or internal function names, but can contain them.

Variable names can be up to 255 characters long.

Variable names are case-insensitive.

Variable names often use "small camel case": the prefix is the English abbreviation of the variable’s data type, followed by the variable name. For example, intA—int is the abbreviation of Integer, indicating the variable’s data type is short integer, and A is the variable name.

Python Variable Naming Rules

Variable names can consist of letters, numbers, and underscores, but cannot start with a number.

Variable names cannot be Python keywords or internal function names, but can contain them.

Variable names are case-sensitive (e.g., abc and ABC represent different variables).

Variable names generally use lowercase letters. If there are multiple words, separate them with underscores (e.g., new_variable).

Examples of Invalid Variable Names

Table 2-1: Examples of Invalid Variable Names

Use the following code to view the list of Python keywords:

>>> class="text-secondary">import keyword
>>> keyword.kwlist
['False', 'None', 'True', 'and', 'class="text-secondary">as', 'assert', 'async', 'await', 'break', 'class', 'continue', 'def', 'del', 'elif', 'else', 'except', 'finally', 'for', 'class="text-secondary">from', 'global', 'if', 'class="text-secondary">import', 'in', 'is', 'lambda', 'nonlocal', 'not', 'or', 'pass', 'raise', 'return', 'try', 'while', 'with', 'yield']

Variable Declaration

In programming languages, variables need to be declared before use. On one hand, this explicitly specifies the variable’s data type; on the other hand, it pre-allocates memory space for the variable. However, not all languages require variable declaration.

Excel VBA

In Excel VBA, variable declaration has two methods: explicit declaration and implicit declaration. Explicit declaration uses the Dim command to declare variables, while implicit declaration does not declare variables. Below, use Dim to declare a short integer variable intA. For details on variable data types, refer to Section 2.3. The sample code file path for this section is Samples\ch02\Excel VBA\VariableDeclarationAndAssignment.xlsm.

Dim intA As Integer

Python

In Python, you do not need to declare variables first—variable declaration and assignment are completed in one step. Assigning a value to a variable creates the variable.

Variable Assignment

Excel VBA

The sample code file path for this section is Samples\ch02\Excel VBA\VariableDeclarationAndAssignment.xlsm. In Excel VBA, use the assignment operator = to assign a value to variable intA:

Dim intA As Integer
intA = 3

After explicitly declaring a variable’s data type, if the data type is incorrect during assignment, the program will throw an error at runtime. For example, the following assigns a string to variable intA:

intA = "Hello"

Running the program pops up an error message (Figure 2-2), indicating a type mismatch.

Thus, if using explicit declaration, each variable’s data type is clear, making it easier to troubleshoot issues during debugging. Adding the following statement at the top of a module forces all variables in the module to be explicitly declared before use:

Option Explicit

Excel VBA also supports implicit declaration: assigning a value to a variable without declaring it first. For example, the following does not declare intA but assigns it the value 3:

intA = 3

At this point, the data type of intA is determined by the type of the value assigned to it. Use the VarType function to get the variable’s data type. The following outputs the data type of intA in the Immediate Window:

Debug.Print VarType(intA)

Running the program outputs 2 in the Immediate Window, indicating intA is a short integer.

Assign a string to intA and output its data type:

intA = "Hello"
Debug.Print VarType(intA)

Running the program outputs 8 in the Immediate Window, indicating intA is a string.

In Excel VBA, use the Set keyword to assign a value to an object. For example, the following declares a Worksheet variable sht and uses Set to reference the active worksheet:

Dim sht As Worksheet
Set sht = ActiveSheet

Python

In Python, use the assignment operator = to assign values to variables. For example, assign 1 to variable a:

>>> a = 1

At this point, the value of a is 1.

>>> a
1

Use the type function to get the data type of a:

>>> type(a)
<class 'int'>

The data type of a is numeric.

Assign the string "hello python" to a:

>>> a = 'hello python'
>>> a
'hello python'

Use the type function to get the data type of a:

>>> type(a)
<class 'str'>

The data type of a is string.

You cannot call a variable before assigning it a value. For example, if c is not assigned a value, calling it throws an error indicating the name "c" is not defined:

>>> c
Traceback(most recent call last):
  File "<pyshell#205>", line 1, in <module>
    c
NameError: name 'c' is not defined

Use the print function to output a variable’s value:

>>> a = 1
>>> print(a)
1
>>> b = 'hello python!'
>>> print(b)
hello python!

Chained Assignment

Assigning the same value to multiple variables in a single assignment statement is called chained assignment.

Python

Python supports chained assignment. For example, assign 1 to both a and b:

>>> a = b = 1

The above statement is equivalent to:

>>> a = 1; b = 1

Note: In Python, multiple statements can be placed on the same line, separated by semicolons.

Excel VBA

Excel VBA does not allow chained assignment; you must assign values to two variables separately. For example, assign 1 to both intA and intB:

Dim intA As Integer
Dim intB As Integer
intA = 1: intB = 1

Note: In Excel VBA, multiple statements can be placed on the same line, separated by colons.

Sequence Unpacking Assignment

Assigning different values to multiple variables in a single assignment statement is called sequence unpacking assignment.

Python

Python supports sequence unpacking assignment. For example, assign 1 to a and 2 to b:

>>> a, b = 1, 2
>>> a
1
>>> b
2

Excel VBA

Excel VBA does not support sequence unpacking assignment; you must assign values to different variables in separate statements. Below, declare two integer variables intA and intB and assign them values separately:

Dim intA As Integer
Dim intB As Integer
intA = 1
intB = 2

Swapping Variable Values

Python

In Python, swap the values of a and b directly:

>>> a, b = 1, 2
>>> a, b = b, a
>>> a
2
>>> b
1

Excel VBA

The sample code file path for this section is Samples\ch02\Excel VBA\VariableDeclarationAndAssignment.xlsm. In Excel VBA, swapping the values of intA and intB requires a temporary variable:

Dim intA As Integer
Dim intB As Integer
Dim intC As Integer
intA = 2: intB = 1
intC = intA
intA = intB
intB = intC

Clearing or Deleting Variables

Excel VBA

After using variables, it is good practice to clear their values. In Excel VBA, the methods to clear values for different data types are:

For numeric variables: Set the variable to 0.

For string variables: Set the variable to "".

For object variables: Set the variable to Nothing.

For variant variables: Set the variable to Null.

Python

In Python, use the del command to delete variables. After deletion, calling the variable throws an error:

>>> del a
>>> a
Traceback(most recent call last):
  File "<pyshell#11>", line 1, in <module>
    a
NameError: name 'a' is not defined

Three Elements of Python Objects

In Python, for the assignment statement:

>>> a = 1

1 is an object, and the assignment establishes a reference from variable a to object 1.

In Python, everything is an object—numbers, strings, lists, dictionaries, classes, etc. Each object occupies a certain amount of memory space. Variables reference objects and store their addresses. An object’s storage address, data type, and value are called the three elements of a Python object.

The storage address is like our ID card number—it is the unique identifier of the object. Use the built-in function id to get the object’s storage address:

>>> id(a)
8791516675136

Each object’s value has its own data type. Use the type function to view the object’s data type:

>>> type(a)
<class 'int'>

The return value <class 'int'> indicates that the value of variable a is an integer.

A variable’s value refers to the data represented by the object it references. If a is assigned 1, its value is 1.

>>> a
1

Use == to compare if object values are equal, and is to compare if their addresses are the same.