VBA basics
13. Constants and type declaration characters (Const)

Excel macro basics and applications, introduction to Excel VBA
last updated:09-01-2019

13. Constants and type declaration characters (Const)

Some of this topic was processed by machine translation. Original


Last time I explained variables, but if there are variables, there are also constants, of course,
In other words, variables are those whose values change, and constants are those whose values do not change.



A constant is a meaningful name used in place of a number or string.

The same value may be used many times in a VBA program.
Also,
You may need to use VBA with numbers that are difficult to remember or difficult to use.
In such cases, use a constant.
Constants are declared with Const.

Const constant basics

Const constant name [As data type] = "constant value"

Declare it like this:
The difference with variables is that you cannot change the value in the middle.
Once declared, use it as it is.

Const str title As String = "Heading"
Const int start line = 3

Declare like this,
Instead of the word “headline”, the str title
Use int start line instead of number 3.

By doing this,
You do not have to write the same letters and numbers in multiple places in the program.
And, when changing the constant, it is only necessary to modify this Const, so "maintainability" is enhanced.

You can do the same with variables, but ...

Dim int start line
int start line = 3

This way, you don't have to use constants.
If you don't have to remember Const ...


Certainly, the macro behavior is the same.
However, think of the macro as a sentence that describes the processing procedure.

Int start line may or may not change value ...

If you look at the macro, you should be able to see it at a glance as an excellent procedure.
In other words, it is a “readability” problem.
In other words, the difference is only in appearance, so there is no problem even with a macro that uses only variables.

*Note). As you learn VBA
At the stage of reading here, it is still difficult to understand,
I wrote, "You can think of it as" readability "for the time being,"
There are places in VBA where variables cannot be specified.

If VBA is specified as a variable, an error may occur saying "A constant expression is required".
That means you must specify a constant for that location.
one two Three
"ABC"
Constant defined by Const
Because it is necessary to make such a specification, it is a place where a variable cannot be specified.

Even if specific VBA is presented here, understanding does not catch up,
In future VBA learning,
It’s enough to remember when you see the message "I need a constant expression".

About double quotation

n the explanation so far,
Enclose the character in double quotes.
Did you notice that the numbers are written as they are?

Please understand that this is what you specify.
However,
In VBA, there is no problem even if the number is enclosed in "."
When entering a numeric variable or constant, write the numeric value as is,
If you put it in a string type variable / constant, it will be cheaper to enclose it in ".


Type declaration character

In the previous explanation of variables, I explained that you don't have to remember the data type yet.
This is correct.
However, if you look at samples such as the net, you will have trouble understanding the meaning.
You don't have to memorize and use everything, but you need to know what types are available.
And there is another way to specify the data type.
That is the type declaration character.

Type declaration character Variable type
Integer (integer type)
Long (Long integer type)
String (string type)
Single (single precision floating point type)
Double (Double precision floating point type)
Currency (currency type)

Dim i%
Const j& = 123456


By declaring in this way, you can specify the data type.
However, there is not much usage like this,
When specifying a literal value (constant value) in the program,
Most of them are used.
123%
123&

In this way, even with the same number
You can specify whether it is an Integer type number or a Long type number.

However, this is also good after learning more macros,
There is no such thing as saying that you can't do it without using it.
If there is such a designation, it can be placed in one corner of the memory.

Hexadecimal constant, octal constant
As expected, I do n’t think I ’ll ever use it,
Only the specified method is posted.

Hexadecimal: & H
Octal: & O


Const i1 As Long = & H10 '16 decimal
Const i2 As Long = & O10 '8 in decimal
A note about date constants
Const Date constant As Date = 2011/12/28 '← No!
It may be specified like this, but this is wrong.
I sometimes do it too. (Lol)

2011/12/28 is the numerical value of the result of 2011 ÷ 12 ÷ 28.
If you want to specify a date constant,
Const date constant As Date = # 12/28/2011 #
Enclose in # like this.
#12/28/2011# is #2011/12/28#
Will be automatically converted to #12/28/2011#.

It is enough to remember that there is such a type declaration character.

Other constants

Up to this point, we have explained the constants that can be set independently.
VBA also has a set of constants.
The constants are roughly divided into the following three.

Built-in constants ... Constants provided in VBA
User-defined constants ... These are the constants that are set independently.
Conditional compilation constants ... Compilation can be changed depending on conditions. Please refer to this page.

About built-in constants
Built-in constants common to VBA
vb○○○
It starts with vb like this.
mso○○○
It starts with mso like this.
xl○○○
It starts with xl like this.

Constants include constants and enumerations alone.
The details of the enumeration are omitted here,
The enumerations make it easy to refer to the Const constants that are limited to numeric values ??as a group. It is difficult to maintain a fixed number in macro VBA. For example, if you want to refer to column position 3 (column C), it will be difficult to change this if you write 3 as many times in VBA.
Multiple numeric constants are grouped together.

VBA is a common programming language that can be used throughout Office.
Therefore,
There are common parts of VBA as a whole and ExcelVBA original parts.


I explained a lot, but the second half isn't a problem if you don't understand it right away.
What I want you to remember firmly here is
Numeric values and characters that do not need to be changed in the macro
It can be declared as a constant in Const.




Articles with the same theme "VBA basics"

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
20. Conditional branch (If)



  • >
  • >
  • 13. Constants and type declaration characters (Const)

  • 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