VBA basics
12. Variables and data types (Dim)

Excel macro basics and applications, introduction to Excel VBA
last updated:08-18-2019

12. Variables and data types (Dim)

Some of this topic was processed by machine translation. Original

This variable is the first thing that macro VBA beginners will trip,
A variable is a container that temporarily stores numeric values, character strings, etc. (ie data).
It is well explained, but this is a good idea first.

There are various types of containers.
Well, there are monkeys, cardboard boxes, and buckets.
Of course, if you put water, it ’s a bucket,
You can't put water in the colander, and it will break if you put it in cardboard.

Similarly, depending on the type of variable,
There are things that can be put in and some that cannot.
This type of container (data) is called a data type.

Data type

Data type Name Range that can be stored
Integer integer type -32,768 ~ 32,767
Long Long integer type -2,147,483,648 ~ 2,147,483,647
Single Single-precision floating-point number type -3.402823E38 ~ -1.401298E-45(negative value)
1.401298E-45 ~ 3.402823E38(positive value)
Double Double precision floating point type -1.79769313486232E308 ~ -4.94065645841247E-324(negative value)
4.94065645841247E-324 ~ 1.79769313486232E308(positive value)
Currency Currency type -922,337,203,685,477.5808 ~ 922,337,203,685,477.00
String String type Up to approximately 2 billion characters
Date Date type Date and time from January 1, 100 AD to December 31, 9999 AD
Boolean Boolean type True or False
Object object type object
Variant Variant type All data

*This is not all. There are many more, but the above are only commonly used.
Up to Boolean is the basic type (primitive type) of VBA

Well, now you don't have to remember everything.
If you remember first,

Integer is Long
A few are Double
Character is String

First of all, it is enough to remember this much.
Macro (VBA) has something very useful,

You can enter any Variant at the bottom of the table above.

And if you don't specify a data type, it will be Variant.
In other words, if you don't specify a data type, that's fine.
Data types aren't too slow to remember once the macro has improved a bit.
But remember the Long, Double, and String that I wrote above.

Using variables

Declare variables
To say.
Dim variable name [As data type]
This is the declaration.
The [As data type] part means that it can be omitted.
If omitted, it becomes the Variant type mentioned above, and anything can be entered.

By the way, Dim is an abbreviation for dimension, but I won't explain that.
If you are interested, please check the Internet.

Multiple variable names can be written on one line.
Dim variable name [As data type], variable name [As data type], ...
You can write multiple variables on one line by separating them with, (comma).

Rules for variable names

Use half-width and full-width numbers and letters
The length of the variable name can be up to 255 characters (up to 127 characters).
Only underscores can be used for symbols (periods and spaces cannot be used)
The first character cannot use numbers, underscores, or symbols
Words that VBA makes sense cannot be used
No distinction between uppercase and lowercase letters.

The above is a minimum rule, but if you write it normally, there is usually no problem.

Commonly used variable names

Variable names have conventions that are commonly used.

Dim i, j, k

These are often used only for numerical values and for counting the number of rows.

The variable used to count the number of rows can be i, j like this,
Other variables are
Make sure to specify a variable name that is meaningful to you.

Also, it is often used to prefix the data type abbreviation.

Dim intNo As Integer

This is a variable that puts an integer number, prefixed with an int, which is an abbreviation for the integer type Integer.
It is a translation that declares.
This way of naming variables is called Hungarian notation.
There are some critical opinions about Hungarian notation,
VBA is an old programming language and should not be spoken in line with the new language.
I do n’t really want to use Hungarian notation,
As a basic naming method, this is a notation you should always remember.
Full-width Japanese can also be used, as in the previous rules for variable names.

Dim lng line As Long

This is a long integer type, and you're declaring that you'll put a line number.
There seems to be controversy about Japanese variable names,
When considering the current PC situation (both hardware and software)
Discussing the pros and cons of Japanese variable names is meaningless.

If it is easy to understand, that is the best.
No more, no less.

At first, you can just mimic the variables you see in books and online.
Little by little, you will be able to create the variable names you want to add.

Necessity of variable declaration

Make sure to declare variables.

Checked "Force variable declaration" in VBE option.
There are some options that should be set a little before you start writing macros. No, it's a VBE Set Options that must be set. Be sure to set it first. VBE _Tools_ → _Options_ The following screen will appear.
If you insert a standard module after checking this,

Option Explicit

This will automatically be inserted at the beginning of the standard module.
You must use variables after you declare them.
I told Excel.

This way, if you start using it without declaring variables,
“I said I would declare a variable, but I did n’t declare it.”
And Excel gets angry.
Make sure to declare variables so that Excel doesn't get angry.

Variables are very profound and can be said to be the point of programming.
Learn little by little,
Use variable names that are easy to write and read by yourself.

Articles with the same theme "VBA basics"

9. How to specify cells in Range
10. Specification method other than Range (Cells, Rows, Columns)
11. How to use Range and Cells
12. Variables and data types (Dim)
13. Constants and type declaration characters (Const)
14. Character concatenation and continuation line
15. Four arithmetic operations and annotations (comments)
16. Iterative processing (For Next)
17. Iterative processing (Do Loop)
18. Get last row (End, Rows.Count)
19. General practice question 1

  • >
  • >
  • 12. Variables and data types (Dim)

  • If you find this site useful, please share and bookmark.

    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.

    If you find this site useful, please share and bookmark.
    To the bottom of the text