Numbers

Integers

Excel VBA

In Excel VBA, integers are divided into short integers and long integers. The range of short integers is −32,768 to 32,767 (−2¹⁵ to 2¹⁵−1), and long integers range from −2⁶³ to 2⁶³−1. The sample file path is Samples\ch02\Excel VBA\NumberTypes.xlsm.

Create a short integer variable intA and assign it 10:

Dim intA As Integer
intA = 10

Create a long integer variable lngB and assign it 100:

Dim lngB As Long
lngB = 100

If the assigned value exceeds the range, a runtime overflow error occurs. Below, assign 1,000,000 to short integer intA, which exceeds its range:

Dim intA As Integer
intA = 1000000

Running the program pops up a message box indicating an overflow error.

Python

Integers are whole numbers (no decimal point) with positive/negative signs. Python 3 has no distinction between short and long integers.

>>> a = 10
>>> a
10
>>> b = -100
>>> b
-100

Python integers have no size limit and can represent very large numbers without overflow:

>>> c = 99999999999999999999999
>>> c
99999999999999999999999

For readability, you can add underscores as separators to numbers:

>>> d = 123_456_789
>>> d
123456789

Integers can be represented in hexadecimal or octal. Hexadecimal is often used for colors (e.g., 0x0000FF for red):

>>> e = 0x0000FF
>>> e
255

Floats

Excel VBA

In Excel VBA, floats are divided into single-precision and double-precision. The range of single-precision floats is −3.40E+38 to +3.40E+38 (−2¹²⁸ to +2¹²⁸), and double-precision floats range from −1.79E+308 to +1.79E+308 (−2¹⁰²⁴ to +2¹⁰²⁴). The sample file path is Samples\ch02\Excel VBA\NumberTypes.xlsm.

Create a single-precision float variable sngA and assign it 10.123:

Dim sngA As Single
sngA = 10.123

Create a double-precision float variable dblB and assign it 100.123:

Dim dblB As Double
dblB = 100.123

Python

In Python 3, there is no distinction between single and double-precision floats. Floats have decimal points and can be in decimal or scientific notation. For example, 31.415 can be written as:

>>> a = 31.415
>>> a
31.415
Or:
>>> a = 3.1415e1
>>> a
31.415

Note: Scientific notation returns a float even if the part before "e" is an integer.

>>> b = 1e2  # 100
>>> type(b)
<class 'float'>

When integers and floats are mixed in operations, the result is a float:

>>> a = 10
>>> b = 1.123
>>> c = a + b
>>> type(c)
<class 'float'>

Complex Numbers

Python supports complex numbers, which consist of real and imaginary parts, represented as a+bj or complex(a,b). Both the real part (a) and imaginary part (b) are floats.

Examples of creating complex variables:

>>> a = 1 + 2j
>>> a
(1+2j)
>>> type(a)
<class 'complex'>
>>> b = -3j
>>> b
(-0-3j)
>>> c = complex(2, -1.2)
>>> c
(2-1.2j)
>>> type(c)
<class 'complex'>

Variable b represents a complex number with only an imaginary part; the real part defaults to 0.

Issues with Type Conversion

Excel VBA

Two issues may arise during type conversion in Excel VBA: loss of precision when converting from high to low precision, and overflow during calculations.

When converting from high to low precision, variables lose some precision. The following example demonstrates converting a float to an integer. The sample file path is Samples\ch02\Excel VBA\FloatToInt.xlsm.

Dim intA As Integer
Dim dblB As Double
dblB = 10.789
intA = dblB
Debug.Print intA

Running the subroutine outputs 11 in the Immediate Window, indicating that converting a float to an integer rounds the decimal part (rounding).

When variables of different precisions are used in arithmetic operations, the result is high-precision. In the following code, short integer intA and long integer lngD are added, and the result is a long integer. Implicit conversion occurs when assigning the result to short integer intC. The sample file path is Samples\ch02\Excel VBA\NumberTypes.xlsm.

Dim intA As Integer
Dim intC As Integer
Dim lngD As Long
intA = 999
lngD = 99999
intC = intA + lngD

Running the subroutine causes an overflow error because the sum of intA and lngD exceeds the range of short integers. This can be resolved by declaring intC as a long integer:

Dim intC As Long

Python

When converting a float to an integer in Python, the decimal part is truncated (not rounded).

>>> e = 1.678    # Convert float to integer
>>> f = int(e)
>>> f
1
>>> type(f)
<class 'int'>

Thus, the float 1.678 becomes 1 after conversion—directly truncating the decimal part, unlike Excel VBA’s rounding.

Python’s Integer Caching Mechanism

In command-line mode, Python caches integer objects in the range [−5, 256]. These small integers are frequently used; without caching, frequent memory allocation and deallocation would reduce performance and cause memory fragmentation. Caching them in a small integer pool improves overall Python performance.

Assign 100 to two variables and compare their memory addresses with is:

>>> a = 100
>>> b = 100
>>> a is b
True

Thus, a and b have the same address—after assigning 100 to a, the variable b (created later) points to the same object as a.

Assign 500 to two variables and compare their addresses:

>>> a = 500
>>> b = 500
>>> a is b
False

Since 500 is outside [−5, 256], Python does not cache it in command-line mode, so a and b point to different objects.

Note: In PyCharm or when running as a file, the cached range is larger: [−5, any positive integer].