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

How to Declare Variables and Assign Them Value in VBA

Date:2021-5-26 Author:Sandra

Actually, declaring the variables and assign them value is the starting and a basic part of running a code. A good declaration way and correct assignment process are fundamental to writing accurate and efficient code. This article will share with you what’s the declared variables and some basic syntax.

In the previous article, we have explained what are variables. If you still have questions about it, you can read it again.

Declaring Variables

1.Basic Variable Types:

The Dim statement is usually be used to declare variables. The following example creates the variable and specifies String and Date. Basic types of variables are those that store single data, such as string, date, number.

How to Declare Variables and Assign Them Value in VBA

2. Variant Variable Type

How to Declare Variables and Assign Them Value in VBA

3. Array Type

How to Declare Variables and Assign Them Value in VBA

4. Object Type

How to Declare Variables and Assign Them Value in VBA

Where to Write the Declaration Statement?

The statement that declares a variable must be written before the statement that uses it.

Wrong way

How to Declare Variables and Assign Them Value in VBA

Correct Way

How to Declare Variables and Assign Them Value in VBA

How to Declare Multiple Variables of the Same Type?

I believe that most people’s first reaction to this problem should be to write the following code.

How to Declare Variables and Assign Them Value in VBA

First of all, the above writing method is grammatically okay and there will be no errors. However, in this way of declaring variables, the Integer type is only used as the data type of the second j variable. The h variable would has a data type of Variant, not an Integer type.

So, You cannot combine declaration statements in VBA. The correct declaration method is as follows:

How to Declare Variables and Assign Them Value in VBA

Is it Necessary to Declare Variables?

To be precise, it is not necessary to declare variables in VBA. If you don’t declare the variable and start using it directly, there is no error.

However, do not declaring variables is a bad habit, and it often brings many errors. This is why this is not mentioned in the previous tutorials.

Typical disadvantages of not declaring variables include:

  • The data type is automatically set to the Variant type, which is inefficient.
  • If the variable name is wrong, no error will be prompted.
  • Cannot use VBA code auto-completion.
  • When the data type does not match, no error will be prompted.

For the above reasons, it is strongly recommended that every time you use a variable, you need to declare its variable name and data type. If you use the variable directly without declaring the variable, VBA will prompt a “variable not defined” error, which is convenient for checking the code.

How to Declare Variables and Assign Them Value in VBA

VBA provides an option to force variable declaration, that is, write the following statement in the module header:

How to Declare Variables and Assign Them Value in VBA
How to Declare Variables and Assign Them Value in VBA

When setting the variable declaration options of the VBA editor, you can automatically insert Option Explicit statements for each module without manual writing. The setting method is as follows:

How to Declare Variables and Assign Them Value in VBA
How to Declare Variables and Assign Them Value in VBA

Assign the Aalue to Basic Type Variables

In VBA, assign values to basic type variables, starting with Let. Assignment operation is to assign the variable on the left of =, use the data on the right of = to specify the value it represents. In the subsequent code, the variable represents the specified data. But in fact, the Let keyword can usually be ignored, and the assignment statement can be written directly at the beginning of the variable.

The syntax for assigning a basic type variable is as follows:

How to Declare Variables and Assign Them Value in VBA

Assign The Value To Variant Type Variables

Variant type assignment is straightforward and there is no mandatory requirement. After the Variant type variable is assigned for the first time, you can continue to assign other types of data.

How to Declare Variables and Assign Them Value in VBA

Although Variant type variables are more flexible, they also have many drawbacks. Therefore, in actual development, it is not recommended to use this type and use exact type variables.

Assign the Value to Array Type Variables

An array is a data type that can store multiple elements of the same type. Generally, specify its data length when declaring. When assigning a value to an array, the serial number of each element is generally used.

How to Declare Variables and Assign Them Value in VBA

Assign the Value to Object Type Variables

When assigning an object type variable, different from the basic type variable using the Let (can be ignored) keyword, the object uses the Set keyword, and the Set cannot be omitted.

How to Declare Variables and Assign Them Value in VBA
How to Declare Variables and Assign Them Value in VBA
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 *