Take a Guess to Win Windows 10 Pro Key!
previous arrow
next arrow
Slider

VBA Data Type Basic Tutorial

Date:2022-1-29 Author:Sandra

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.

VBA Data Type Basic Tutorial

Let’s take an example, declare a text type variable, String is a type identifier, the syntax is as follows:

VBA Data Type Basic Tutorial

In VBA, the text needs to be in quotes.

VBA Data Type Basic Tutorial

If the number is in quotes, it will become a text type and no longer represent a number.

VBA Data Type Basic Tutorial

You can also use the text in the cell to assign values to text-type variables.

VBA Data Type Basic Tutorial

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.

VBA Data Type Basic Tutorial

Integer, Long, and Double have commonly used number types.

To declare that the number type is similar to text, use the following statement:

VBA Data Type Basic Tutorial

Numeric variables can participate in various arithmetic operations like numbers:

VBA Data Type Basic Tutorial

Logical Type

The logical type has only two values, True and False.

VBA Data Type Basic Tutorial

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:

VBA Data Type Basic Tutorial

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.

VBA Data Type Basic Tutorial

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:

VBA Data Type Basic Tutorial

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:

VBA Data Type Basic Tutorial

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.

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *