[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Previous) | Next

---

Declaring variables with the smallest viable data type size will increase performance.

1. Description

VBA's main purpose is to manipulate data. Some data resides in objects, such as worksheet ranges. Other data is stored in variables created by the developer. Variables are named storage locations in the computer's memory. Variables can accommodate a wide range of data types.

A data type is the characteristic of a variable that determines what kind of data it can hold. Data types include those in the following table, user-defined types and specific types of objects.

Check the data range for the capacity of values needing to be held:

• A Variant containing an array requires 12 bytes more than the array alone.
• Arrays of any data type require 20 bytes of memory plus 4 bytes for each array dimension plus the number of bytes occupied by the data itself. The memory occupied by the data can be calculated by multiplying the number of data elements by the size of each element. For example, the data in a single-dimension array consisting of 4 Integer data elements of 2 bytes each occupies 8 bytes. The 8 bytes required for the data plus the 24 bytes of overhead brings the total memory requirement for the array to 32 bytes. On 64-bit platforms, SAFEARRAY's take up 24-bits (plus 4 bytes per Dim statement).
• LongPtr is not a true data type because it transforms to a Long in 32-bit environments or a LongLong in 64-bit environments. LongPtr should be used to represent pointers and handle values in Declare statements and enables writing portable code that can run in both 32-bit and 64-bit environments.
• The Decimal data type is unusual because it can't be declared directly. In fact, it is a subtype of a variant. You need to use the VBA CDec function to convert a variant to the Decimal data type.

VBA makes life easy for programmers because it can automatically handle all the details of dealing with data. However, some programming languages are strictly typed, meaning that the programmer must explicitly define the data type for every variable used.

Although VBA can take care of data typing automatically, it comes with a cost: slower execution and less efficient use of memory.

2. How to use

Always declare your variables and use the smallest data type possible, especially for numbers.

Example:

Dim intRow as Integer

• Avoid Variants: it is simple but often overlooked. All variables, parameters, and functions should have a defined data type. If the data is a string, then the data type should be defined as a string. You're using a variant if you don’t give a data type. The variant data type has its uses but not in string processing. A variant means performance loss in most cases.
• Add Option Explicit statements to each module and Dim all variables with a decent data type.
• Review your functions and ensure that they define a return data type.

3. Performance test

4. Results

5. Conclusions

Generally, it is best to use the data type that uses the smallest number of bytes yet can still handle all the data that will be assigned to it. when VBA works with data, execution speed is partially a function of the number of bytes that VBA has at its disposal. In other words, the fewer the bytes used by data, the faster VBA can access and manipulate the data.

---

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Previous) | Next