The most common declaration is by using Dim Statement as below example:
Dim StrName as String
- If this statement within Sub procedure then StrName can be use within the procedure itself.
- 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)
- One statement per line.Dim iNumA as integer
Dim iNumB as integer
Dim iNumC as integer - Multiple statement with individual declaration per line.Dim iNumA as integer, iNumB as integer, iNumC as integer
- Multiple statement with end declaration per line.Dim iNumA, iNumB, iNumC as integerNote: Meaning is different from above bacause iNumA and iNumB will declare as Variant, only iNumC is integer.
- Multiple statement with Dim iNumA%, iNumB%, iNumC as integerNote: The shorthand for the types is: % -integer; & -long; @ -currency; # -double; ! -single; $-string
Beside Dim statement we can use.
- Public StatementPublic StrName as StringNote: Declare at Module level but can be use for the whole project.
- Private StatementPublic StrName as StringNote: Declare at Module level but can be use only procedure inside the same module and same as Dim statement.
- StaticStatic StrName as stringNote: If Static replace Dim in procedure,the declared variable will retain its value between calls to that procedure.
Declaring Variable with object automation.
Set FSO = CreateObject("Scripting.FileSystemObject")
Another Example:
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-Data-type-summary
Other Reference-Data-type-summary
Practice makes perfect. Thank You.
macro enabled excel
excel macro
vba coding
vba code
No comments:
Post a Comment