How to declare variables?

The most common declaration is by using Dim Statement as below example:

        Dim StrName as String

1. If this statement within Sub procedure then StrName can be use within the procedure itself.
   
   
   
   
   
2. If this statement on top module then StrName can be use for all procedures inside that particular module.
   
   
   
   
   

Note:
StrName create on your own depending on what kind of data we are dealing with so that your code is more readable and easy for debugging. It can be MyName, RefName or anything except for reverse keywords. My habit is to use 1st 3 character refer to data type for example String then Str.

Methods: (In This example we have 3 variables to declare iNumA, iNumB and iNumC as integer)

1. One statement per line.
   
   Dim iNumA as integer
   Dim iNumB as integer
   Dim iNumC as integer
2. Multiple statement with individual declaration per line.
   
   Dim iNumA as integer, iNumB as integer, iNumC as integer
   
3. Multiple statement with end declaration per line.
   
   Dim iNumA, iNumB, iNumC as integer
   Note: Meaning is different from above bacause iNumA and iNumB will declare as Variant, only iNumC is integer.
4. Multiple statement with
   
   Dim iNumA%, iNumB%, iNumC as integer
   
   Note: The shorthand for the types is: % -integer; & -long; @ -currency; # -double; ! -single; $-string
   

Beside Dim statement we can use.

1. Public Statement
   
   Public StrName as String
   
   Note: Declare at Module level but can be use for the whole project.
   
2. Private Statement
   
   Public StrName as String
   
   Note: Declare at Module level but can be use only procedure inside the same module and same as Dim statement.
3. Static
   
   Static StrName as string
   
   Note: If Static replace Dim in procedure,the declared variable will retain its value between calls to that procedure.
   

Declaring Variable with object automation.

Dim FSO as Object
Set FSO = CreateObject("Scripting.FileSystemObject")

  Another Example:     

Dim MstWB As Workbook
Set MstWB = Workbooks.Add(1)

Note: If we use a too many Public Statement then we should assigned one module just for variables declaration to ease debugging and editing.

Microsoft Reference-Declaring-variables
Microsoft Reference-Data-type-summary
Other Reference-Data-type-summary

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code