Why and How to declare constant

Constant is fixed and cannot be changed normally we assigned certain value with meaningful name and for string we can shorten the length. Constant can be Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String, or Variant because we already know the data type. For example we need to use specific URL in our code and repeat many time, instead of using complete URL we use specific constant to represent this URL to ease your code debugging and more organize. Example:

Public Const LINK_MYSTOCKBZ = "https://www.malaysiastock.biz/Listed-Companies.aspx"

Note: We use LINK_MYSTOCKBZ in our code instead of complete URL.

Normally constant declare on top of module in group, to make your code more organize and ease debugging. For me it is better to assigned one module special for constant declaration, more easy to refer when necessary. We can declare either Cont only or Public Cont. (Cont only consider as private)

For more example refer below table and code.

VBA Vode: (This example is to create folder at Desktop and open for viewing)

Option Explicit
Public Const MY_FLDRPATH As String = "\Desktop\My Folder"
Const MY_PRGRM As String = "explorer.exe "
Sub Examples_Constant_CreateFolderAndOpen()
    Dim StrPath As String
    StrPath = VBA.Environ("UserProfile") & MY_FLDRPATH
    If Dir(StrPath, vbDirectory) = "" Then
        MkDir StrPath
    End If
    Shell MY_PRGRM & StrPath, vbNormalFocus
End Sub

Note: 

Constant has several type include Literal constants,Symbolic constants,Enumerations and Built-in constants.

Microsoft Reference-Declaring-constants
Other Reference-Constants.htm

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code