In the tutorial about VBA variables shared before, maybe you have initially learned the knowledge about data types. Although the data type is not mandatory in VBA, the correct use of the data type can make the program easier to understand and improve the operating efficiency of the program.
Today, we will learn more about the data types in VBA through actual code examples.
Text Type
The text type can be said to be the most commonly used data type. In practice, almost all data is of text type. Therefore, a good understanding of the text is the basis for learning VBA.
Let’s take an example, declare a text type variable, String is a type identifier, the syntax is as follows:
In VBA, the text needs to be in quotes.
If the number is in quotes, it will become a text type and no longer represent a number.
You can also use the text in the cell to assign values to text-type variables.
Like the text functions provided by Excel, VBA also provides a variety of text functions that can be used directly in programs, including:
- Format: Format the data and return it as text
- InStr: Returns the position of the specified character
- InStrRev: return the specified character position in the opposite direction
- Left: Returns the text of the specified length on the left
- Len: return text length
- LCase: Convert uppercase letters to lowercase letters
- LTrim: Clear the leading spaces
- Mid: Returns the text between the specified start and end positions
- Replace: Replace the specified characters in the text
- Right: Return to the text of the specified length on the right
- RTrim: Clear the space at the end
- Space: Returns the space text with the specified number of repetitions
- StrComp: return the result of comparing two texts
- StrConv: Convert the text to the specified format
- String: Returns the text with the specified number of repetitions
- StrReverse: Reverse the provided string
- Trim: Clear the text at the beginning and end
- UCase: Convert lowercase letters to uppercase letters
The usage of each function, which we will continue to share with you in the subsequent article.
Number Type
The number type is the second basic data type. When writing VBA code, you should select the appropriate number type according to the specific number size.
If small numbers are stored in a large range of digital types, computer memory will be wasted; if large numbers are stored in a small range of digital types, VBA will automatically convert them into corresponding small-range numbers, causing the numbers to lose precision.
The number types in VBA include the following.
Integer, Long, and Double have commonly used number types.
To declare that the number type is similar to text, use the following statement:
Numeric variables can participate in various arithmetic operations like numbers:
Logical Type
The logical type has only two values, True and False.
Although there are only two logical values, they are widely used in programs. Logical values can often be used in judgment statements in VBA.
First, to declare a logical variable, use the following statement:
If you assign a value to a logical variable, you can use the logical value directly, or you can use an expression that returns a logical value.
Date and Time Type
Date and time in VBA are represented by numbers, the integer part represents the date, and the decimal part represents the time.
The date starts from 100-1-1 to 9999-12-31.
The time is from 00:00:00 to 23:59:59.
To declare a date type variable, use the following statement:
When assigning a value to a date variable, you can directly place the date between two #s, use a number, or assign the date as a text:
Variant Type
The Variant type is a general type that can represent any type of data. It is also the default type when declaring a variable without specifying a data type.
Although the Variant type is convenient, correspondingly, it takes up more memory space and also affects the efficiency of the program. Therefore, it is recommended to select the specified data type when clearly knowing the type of data; if the data type is variable or ambiguous, then, you can use the Variant type.
Leave a Reply