VBA Function IsNumeric

IsNumeric function is to check whether variables is number or not and return result in Boolean either True of False. For example IsNumeric(10) result is True and IsNumeric(ABC123) of course False. If we combine with Not then the result in reverse way. For example Not IsNumeric(10) then result is False.

Syntax : IsNumeric(expression) , Return : True/False

1) Below example for True case and mostly when data type are Byte, Integer, Long, Double, Decimal, Currency and Boolean.

VBA Vode:

Option Explicit
Sub Examples_IsNumeric_True()

    Dim BytNum As Byte
    Dim CurNum As Currency
    Dim IntNum As Integer
    Dim LngNum As Long
    Dim DblNum As Double
    Dim DecNum As Variant
    Dim BlnTxt As Boolean
    
    BytNum = 5
    CurNum = 12000000
    IntNum = 25000
    LngNum = -50000
    DblNum = 5.25
    DecNum = CDec(7 / 8)
    BlnTxt = True
    
    Debug.Print IsNumeric(BytNum)
    Debug.Print IsNumeric(CurNum)
    Debug.Print IsNumeric(IntNum)
    Debug.Print IsNumeric(LngNum)
    Debug.Print IsNumeric(DblNum)
    Debug.Print IsNumeric(DecNum)
    Debug.Print IsNumeric(BlnTxt)

End Sub

Note: 

As you can see Boolean data type become True because when False is refer to 0 and True is -1.

2) Below example for False case and mostly when data type are String, Date and Other than Number.

VBA Vode:

Option Explicit
Sub Examples_IsNumeric_False()
    
    Dim StrTxt As String
    Dim DteTxt As Date
    
    StrTxt = "Abc879"
    DteTxt = "21/10/2022"
    
    Debug.Print IsNumeric(StrTxt)
    Debug.Print IsNumeric(DteTxt)
    
End Sub

Note: 

This function is very useful to avoid calculation error because when we try to calculate other than number then system will generate error.

Microsoft Reference-Isnumeric-function
Other Reference-Isnumeric-function

Practice makes perfect. Thank You.

excel training beginners
coding in vba
excel training online
visual basic for applications

VBA Function IsNull

IsNull Function is to check whether an expression contains no valid data (Null). The return value in Boolean either True or False. If we assigned specific variables with Null then the result is True else False. 

Syntax : IsNull(expression) , Return : True/False

Refer below example:

VBA Vode:

Option Explicit
Sub Examples_IsNull()

    Dim StrVarA, StrVarB, StrVarC
    
    StrVarB = ""
    StrVarC = Null
    
    Debug.Print IsNull(StrVarA)    ' Returns False.
    Debug.Print IsNull(StrVarB)    ' Returns False.
    Debug.Print IsNull(StrVarC)    ' Returns True.

End Sub

Note: StrVarA we don't assigned anything, StrVarB as empty and StrVarC is Null.

Microsoft Reference-Isnull-function
Other Reference-Isnull-function

Practice makes perfect. Thank You.

excel training beginners
coding in vba
excel training online
visual basic for applications

VBA Function IsMissing

IsMissing function is to check whether Option Variant has been past through from procedure to called procedure or not. The return value is Boolean either True or False. If really missing then True else False.

Syntax : IsMissing(Variables) , Return True/False

1) Below example Main procedure Called Sub procedure but did not assigned StrVar.

VBA Vode:

Option Explicit
Sub Examples_IsMissingCalling_True()

    Examples_IsMissingCalled 'Cause IsMissing is True
    
End Sub
Public Sub Examples_IsMissingCalled(Optional ByVal StrVar As Variant)

    Debug.Print IsMissing(StrVar) 'Result is True
    
End Sub

Note: The above case StrVar is Missing then result is True.

2) Below example Main procedure Called Sub procedure but assigned StrVar = StrVarA.

VBA Vode:

Option Explicit
Sub Examples_IsMissingCalling_False()
    
    Dim StrVarA As Variant
    Examples_IsMissingCalled StrVar:=StrVarA
    
End Sub
Public Sub Examples_IsMissingCalled(Optional ByVal StrVar As Variant)

    Debug.Print IsMissing(StrVar) 'Result is False
    
End Sub

Note: The above case StrVar is Not Missing then result is False.

Microsoft Reference-Ismissing-function
Other Reference-Ismissing-function

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

VBA Function IsError

IsError function is to check any expression contain error value or not and return in Boolean either True or False. 

Syntax : IsError(expression) , Return : True/False

Below example Range D2~D6 contain formula which create error because of formula.When we test this range with IsError function cells contain #NULL error show TRUE result and others FALSE.

VBA Vode:

Option Explicit
Sub Examples_IsError()
    
    Dim i As Integer
    i = 2
    With ActiveSheet
        Do
            .Range("E" & i) = IsError(Range("D" & i))
            i = i + 1
        Loop Until .Range("A" & i) = ""
    End With

End Sub

Note: 

The error generate in Excel included #DIV/0,#N/A,#NAME?,#NULL!,#NUM!,#REF!,#VALUE! and #####. May be circular error also included.

Microsoft Reference-Iserror-function
Other Reference-Iserror-function.htm
Other Reference-Iserror.php

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

VBA Function IsEmpty

IsEmpty function is to check whether a variable has been initialized or assigned any value or not. The result is Boolean either True or False. Basically the variable is variant for example Dim StrVar as Variant, then we assigned StrVar = Null or anything then this variable no longer empty (False). 

Syntax : IsEmpty(expression), Return : True/False

1) Example IsEmpty for True.

VBA Vode:

Option Explicit
Sub Examples_IsEmptyTrue()
    
    Dim StrVar
    Dim MyTest As Boolean
    
    MyTest = IsEmpty(StrVar)
    Debug.Print MyTest 'Result is True

End Sub

Note: The variables (StrVar) is not yet assigned or initialized.

2) Example IsEmpty for False.

VBA Vode:

Option Explicit
Sub Examples_IsEmptyFalse()
    
    Dim StrVar As Variant
    Dim MyTest As Boolean
    
    StrVar = Null
    MyTest = IsEmpty(StrVar)
    Debug.Print MyTest 'Result is False
    
End Sub

Note: The variables (StrVar) already assigned or initialized as Null.

Microsoft Reference-Isempty-function
Other Reference-Variant-empty.htm

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

VBA Function Isdate

Isdate function is to check whether variables is valid date or not and the return is Boolean either True or False.

Syntax : IsDate(expression), Return : True/False

1) Below example for True in Result.

VBA Vode:

Option Explicit
Sub Examples_IsDateTrue()
    Dim MyDate() As Date
    
    ReDim Preserve MyDate(1): MyDate(1) = "01/07/2022"
    ReDim Preserve MyDate(2): MyDate(2) = "01/99" '= 1000000 True?
    ReDim Preserve MyDate(3): MyDate(3) = "01/13/2022"
    ReDim Preserve MyDate(4): MyDate(4) = "13/01/2022"
    ReDim Preserve MyDate(5): MyDate(5) = "12-01-2022"
    ReDim Preserve MyDate(6): MyDate(6) = CDate(42565) '14/7/2016
    
    Debug.Print IsDate(MyDate(1))   '= True
    Debug.Print IsDate(MyDate(2))   '= True
    Debug.Print IsDate(MyDate(3))   '= True
    Debug.Print IsDate(MyDate(4))   '= True
    Debug.Print IsDate(MyDate(5))   '= True
    Debug.Print IsDate(MyDate(6))   '= True
    
End Sub

Note: With variables original declare as date may different result compare with direct test. For example direct test IsDate(1) False but declare as date MyDate(1) = 1, IsDate(MyDate(1)) become True. Even with number 1 to 1,000,000 still true but more than that become error "Run-time error '6': Overflow".

2) Below example for False in Result.

VBA Vode:

Option Explicit
Sub Examples_IsDateFalse()

    Debug.Print IsDate(42565)          '= False
    Debug.Print IsDate("8")            '= False
    Debug.Print IsDate(15)             '= False
    Debug.Print IsDate("24")           '= False
    Debug.Print IsDate("String")       '= False

End Sub

Note: Without Dim declaration the result will be different.

Microsoft Reference-Isdate-function
Other Reference-Isdate-function.htm

Practice makes perfect. Thank You.

excel training beginners
coding in vba
excel training online
visual basic for applications

VBA Function Isarray

Isarray function is to check whether a variable is an array form or not and the return is Boolean which is True or False.

Syntax : IsArray(Variables), Return : True/False

1) Below example is declare as variant but we assigned as array. The return is true.

VBA Vode:

Option Explicit
Sub ExamplesIsArray()

    Dim StrVar As Variant
    Dim i As Integer

    StrVar = Array(1, 2, 3)
    If IsArray(StrVar) Then 'The answer is True
        For i = LBound(StrVar) To UBound(StrVar)
            Debug.Print StrVar(i) ' The answer 1,2,3
        Next
    Else
        Debug.Print "This variables is not an array"
    End If

End Sub

Note: When the return is true we can loop thru this array from lower to upper bound.

2) Below example originally declare as array even we don't assigned any array the result still True.

VBA Vode:

Option Explicit
Sub Examples_IsArray()
    
    Dim MyArray() As String
    If IsArray(MyArray) Then
        Debug.Print True 'The answer is True
    Else
        Debug.Print False
    End If
    
End Sub

Note: For the above example we have to use redim statement to assigned array.

Microsoft Reference-Isarray-function
Other Reference-Isarray-function.htm

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

Why and How to declare Array

Array declare same as any other variables by using the Dim, Static, Private, or Public statements. Data type also same for examples Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String, or Variant. The only different array is the size can be specify or dynamic for example in variables we use Dim iNum1 as integer, Dim iNum2 as integer,... and etc, but with array we use Dim iNum(5) as integer then we can use in our code iNum(0),iNum(1),... until 5. Array can be devided into:

• Fixed Array
  Example: Dim iNum(10) as integer
                  Dim iNum(1 to 10) as integer (Override default for LBound)
  
• Rectangular Array or 2 Dimension Array
  Example: Dim iNum(3,4) as integer
                  Dim MyArray(1 To 10, 5 To 15, 10 To 20) (Override default for LBound)
• Dynamic Array
  Example: Dim iNum() as integer
  Note: We must use Redim iNum(1) or Redim Preserve iNum(1) in code
  

Default starting number is 0 unless we use Option Base statement at module level, meaning on top of module we put Option Base 1. For Example Dim iNum(3) as integer then we can use iNum(0), iNum(1), iNum(2) and iNum(3) but with Option Base 1 we only can use iNum(1), iNum(2) and iNum(3). If your code still assigned 0 to array then error code "Run-time error '9': Subscript out of range" will appeared.

VBA Vode: (Without Option Base 1)

Option Explicit
Sub Examples_ArrayFixed()
    Dim i As Integer
    Dim IntNum(5) As Integer
    
    IntNum(0) = 20
    IntNum(1) = 15
    IntNum(2) = 23
    IntNum(3) = 18
    IntNum(4) = 28
    IntNum(5) = 35
    
    For i = LBound(IntNum) To UBound(IntNum)
        Debug.Print IntNum(i)
    Next
End Sub

Note: Starting number is 0 (Lower Bound)

VBA Vode: (With Option Base 1)

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_ArrayFixed()
    Dim i As Integer
    Dim IntNum(5) As Integer

    IntNum(1) = 15
    IntNum(2) = 23
    IntNum(3) = 18
    IntNum(4) = 28
    IntNum(5) = 35
    
    For i = LBound(IntNum) To UBound(IntNum)
        Debug.Print IntNum(i)
    Next
End Sub

Note: Starting number is 1 (Lower Bound)

VBA Vode: (Example for Rectangular Array)

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_RectangularArray()
    Dim i As Integer, j As Integer
    Dim IntNum(2, 3) As Integer

    IntNum(1, 1) = 15
    IntNum(1, 2) = 23
    IntNum(1, 3) = 18
    IntNum(2, 1) = 28
    IntNum(2, 2) = 35
    IntNum(2, 3) = 15
    
    For i = LBound(IntNum, 1) To UBound(IntNum, 1) 'Row Index
        For j = LBound(IntNum, 2) To UBound(IntNum, 2) 'Column Index
            Debug.Print IntNum(i, j)
        Next j
    Next i
End Sub

Note: Both Starting point for Row and Column index =1

VBA Vode: (Example for Dynamic Array with Redim)

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_DynamicArray()
    Dim i As Integer
    Dim IntNum() As Integer

    ReDim IntNum(1): IntNum(1) = 15
    ReDim IntNum(2): IntNum(2) = 23
    ReDim IntNum(3): IntNum(3) = 18
    ReDim IntNum(4): IntNum(4) = 28
    ReDim IntNum(5): IntNum(5) = 35
    
    For i = LBound(IntNum) To UBound(IntNum)
        Debug.Print IntNum(i) 'Answer = 0,0,0,0,35
    Next i
End Sub

Note: The answer for you get is all 0 except the last one which is IntNum(5) = 35. Meaning every time we Redim the array the previous data store in previous array will be deleted.

VBA Vode: (Example for Dynamic Array with Redim Preserve)

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_DynamicArray()
    Dim i As Integer
    Dim IntNum() As Integer

    ReDim Preserve IntNum(1): IntNum(1) = 15
    ReDim Preserve IntNum(2): IntNum(2) = 23
    ReDim Preserve IntNum(3): IntNum(3) = 18
    ReDim Preserve IntNum(4): IntNum(4) = 28
    ReDim Preserve IntNum(5): IntNum(5) = 35
    
    For i = LBound(IntNum) To UBound(IntNum)
        Debug.Print IntNum(i) 'Answer = 15,23,18,28,35
    Next i
End Sub

Note: To keep your Array data after Redim we must use Redim Preserve statement.

Microsoft Reference-Declaring-arrays
Microsoft Reference-Option-base-statement
Other Reference-Array

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

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

Data type - String

Data type String is any characters in sequence, 2 type of string:

• Variable-length contain up to approximately 2 billion (2^31) characters.
  Declare Dim StrText as String or Dim StrText$
  
• Fixed-length strings contain 1 to approximately 64 K (2^16) characters.
  Declare Dim StrText as String * 3, where 3 is length of string
  

To reset or assign String as empty StrText = "" (Use double-quotation-marks) and to include double-quotation-marks inside string StrText = """" (Indicate single double-quotation-marks inside string).

For more example refer below table and code.

Below example is to get folder name and files name from path.

VBA Vode:

Option Explicit
Sub Examples_StringVariablelength()

    Dim i As Integer
    Dim StrPath As String, StrFldr As String
    
    i = 2
    With ActiveSheet
        Do
            
            StrPath = .Range("B" & i)
            StrFldr = Left(StrPath, InStrRev(StrPath, "\", -1) - 1)
            .Range("C" & i) = Right(StrFldr, Len(StrFldr) - _
            InStrRev(StrFldr, "\", -1))
            .Range("D" & i) = Right(StrPath, Len(StrPath) - _
            InStrRev(StrPath, "\", -1))
            
            StrPath = "": StrFldr = ""
            i = i + 1
        Loop Until .Range("A" & i) = ""
    End With

End Sub

Note: 

Above example include Left function, Right function and InStrRev function.

Below example is to get months name in short form (3 Characters) with fixed length variables and left function.

VBA Vode:

Option Explicit
Sub Examples_StringFixedLength()

    Dim i As Integer
    Dim StrMnth As String * 3
    i = 2
    With ActiveSheet
        Do
            
            StrMnth = .Range("B" & i)
            .Range("C" & i) = Left(.Range("B" & i), 3)
            .Range("D" & i) = StrMnth
            
            StrMnth = ""
            i = i + 1
        Loop Until .Range("A" & i) = ""
    End With

End Sub

Note: 

The variables length string is widely used compare with fixed length string.

Microsoft Reference-String-data-type
Other Reference-String-data-type

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

Data type - Single

Data type Single is short for single precision floating point are stored as IEEE 32-bit (4-byte) ranging for negative values -3.402823E38 to -1.401298E-45 and for positive values 1.401298E-45 to 3.402823E38. Different from Double, Single can support 7 significant figures with 6 decimal places only. Single, Double and Decimal most probably at the same group may be different by range and accuracy of decimal points. Instead of Dim SglNum as Single we can use Dim SglNum!.

For more example refer below table and code. 

Below we try to get area in square meter base on width and length.

Base on result there is huge different in figure especially on decimal points. Which one is more accurate in this case? Double is the same as Direct.

VBA Vode:

Option Explicit
Sub Examples_Single()
    
    Dim i As Long
    
    'Single Declaration
    Dim SglOpnStk As Single, SglInOut As Single
    Dim SglClsStk!
    
    'Double Declaration
    Dim DblOpnStk As Double, DblInOut As Double
    Dim DblClsStk#
    
    i = 2
    With ActiveSheet
        Do
            'Sgleger
            SglOpnStk = .Range("B" & i): SglInOut = .Range("C" & i)
            SglClsStk = SglOpnStk * SglInOut
            
            'Long
            DblOpnStk = .Range("B" & i): DblInOut = .Range("C" & i)
            DblClsStk = DblOpnStk * DblInOut
            
            'Direct input
            .Range("D" & i) = .Range("B" & i) * .Range("C" & i)
            .Range("E" & i) = SglClsStk 'Single variables
            .Range("F" & i) = DblClsStk 'Double variables
            
            SglOpnStk = 0: SglInOut = 0: SglClsStk = 0
            DblOpnStk = 0: DblInOut = 0: DblClsStk = 0
            
            i = i + 1
        Loop Until .Range("A" & i) = ""
    End With

End Sub

Note: 

Single.Double and Decimal at the same group and choose wisely. Same as Integer and Long.

Microsoft Reference-Single-data-type
Other Reference-Single-data-type

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

Data type - Long

Data type Long is short for long integers are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647. Declare as Dim LngNum as Long or Dim LngNum&. Compare with integer, long is more bigger range and required more space in your computer memory.

For more example refer below table and code. 

Below table we try to get balance stock after top up and deduction by using different data type for example direct input, long and integer.

Base on result there is huge different in figure, long is still accurate but integer is totally wrong because the data is out of range for integer itself.

VBA Vode:

Option Explicit
Sub Examples_Integer()
    
    Dim i As Long
    
    'Integer Declaration
    Dim IntOpnStk As Integer, IntInOut As Integer
    Dim IntClsStk%
    
    'Decimal Declaration
    Dim LngOpnStk As Long, LngInOut As Long
    Dim LngClsStk&
    
    i = 2
    With ActiveSheet
        Do
            'Integer
            On Error Resume Next
            IntOpnStk = .Range("C" & i): IntInOut = .Range("D" & i)
            IntClsStk% = IntOpnStk + IntInOut
            
            'Long
            LngOpnStk = .Range("C" & i): LngInOut = .Range("D" & i)
            LngClsStk& = LngOpnStk + LngInOut
            
            'Direct input
            .Range("E" & i) = .Range("C" & i) + .Range("D" & i)
            .Range("F" & i) = LngClsStk& 'Long variables
            .Range("G" & i) = IntClsStk% 'Integer variables
            
            IntOpnStk = 0: IntInOut = 0: IntClsStk% = 0
            LngOpnStk = 0: LngInOut = 0: LngClsStk& = 0
            
            i = i + 1
        Loop Until .Range("A" & i) = ""
    End With

End Sub

Note: 

Please careful choose your data type unless your output is totally wrong after process, if you're lucky then error message will prompt for verification.

Microsoft Reference-Long-data-type
Other Reference-Long-data-type

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

Data type - Integer

Data type integer stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767. One byte is used to represent the sign either positive (+ve) or negative (-ve). Can be declare Dim IntNum as integer or Dim IntNum%. If assigned data is out of range then error "Run time error '6': Over flow.

For more example refer below table and code. 

Below table we try to get balance stock after top up and deduction by using different data type for example direct input, integer and long.

 Base on result there is no different in figure, but long is better unless we are sure the quantity is not out of range.

VBA Vode:

Option Explicit
Sub Examples_Integer()
    
    Dim i As Long
    
    'Integer Declaration
    Dim IntOpnStk As Integer, IntInOut As Integer
    Dim IntClsStk%
    
    'Decimal Declaration
    Dim LngOpnStk As Long, LngInOut As Long
    Dim LngClsStk&
    
    i = 2
    With ActiveSheet
        Do
            'Integer
            IntOpnStk = .Range("C" & i): IntInOut = .Range("D" & i)
            IntClsStk% = IntOpnStk + IntInOut
            
            'Long
            LngOpnStk = .Range("C" & i): LngInOut = .Range("D" & i)
            LngClsStk& = LngOpnStk + LngInOut
            
            'Direct input
            .Range("E" & i) = .Range("C" & i) + .Range("D" & i)
            .Range("F" & i) = IntClsStk% 'Integer variables
            .Range("G" & i) = LngClsStk& 'Long variables
            
            IntOpnStk = 0: IntInOut = 0: IntClsStk% = 0
            LngOpnStk = 0: LngInOut = 0: LngClsStk& = 0
            
            i = i + 1
        Loop Until .Range("A" & i) = ""
    End With

End Sub

Note:

Number of Maximum rows in Excel is 1,048,576 and Maximum columns is 16,384. Therefore loop through used range in rows must used long and columns as integer enough.

Microsoft Reference-Integer-data-type
Other Reference-Integer-data-type

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

Data type - Double

Data type Double is refer to short for double precision floating point are stored as IEEE 64-bit (8-byte) ranging from:

• -1.79769313486231E308 to -4.94065645841247E-324 for negative values
• 4.94065645841247E-324 to 1.79769313486232E308 for positive values

Instead of Dim DbNum as Double we can use Dim DbNum#. The key point here is precision floating point (read more at link below) this can support 15 significant figures with 14 decimal places.

For more example refer below table and code.

Below we try to get area in square meter base on width and length.

VBA Vode:

Option Explicit
Sub Examples_DoubleVariales()
    
    Dim i As Integer
    
    'Double Declaration
    Dim DbWidht As Double, DbLength As Double
    Dim DbSqMtr As Double
    
    'Decimal Declaration
    Dim VarWidht As Variant, VarLength As Variant
    Dim VarSqMtr As Variant
    
    'Integer Declaration
    Dim IntWidht As Integer, IntLength As Integer
    Dim IntSqMtr As Integer
    
    i = 2
    With ActiveSheet
        Do
            'Double
            DbWidht = .Range("B" & i)
            DbLength = .Range("C" & i)
            DbSqMtr = Round(DbWidht * DbLength, 2)
            
            'Decimal
            VarWidht = CDec(.Range("B" & i))
            VarLength = CDec(.Range("C" & i))
            VarSqMtr = CDec(Round(VarWidht * VarLength, 2))
            
            'Integer
            IntWidht = .Range("B" & i)
            IntLength = .Range("C" & i)
            IntSqMtr = Round(IntWidht * IntLength, 2)
            
            'Direct input
            .Range("D" & i) = Round(.Range("B" & i) * _
            .Range("C" & i), 2)
            .Range("E" & i) = DbSqMtr 'Double variables
            .Range("F" & i) = VarSqMtr 'Decimal variables
            .Range("G" & i) = IntSqMtr 'Integer variables
            
            DbWidht = 0: DbLength = 0: DbSqMtr = 0
            VarWidht = 0: VarLength = 0: VarSqMtr = 0
            IntWidht = 0: IntLength = 0: IntSqMtr = 0
            i = i + 1
        Loop Until .Range("A" & i) = ""
    End With

End Sub

Note: The above example we try to compare without variables, Double variables, Decimal variables and Integer variables. Definitely integer is not accurate for this type of data.

Microsoft Reference-Double-data-type
Other Reference-Double-data-type
Other Reference-Floating-point-numbers

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

Data type -Decimal

Data type Decimal is stored as 96-bit (12-byte) and range for data without decimal value (0) between +/-79,228,162,514,264,337,593,543,950,335 and with 28 decimal point between +/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001. Beside Decimal we can use Double which is more straight forward but in term accuracy better use Decimal depend on condition.

VBA Vode:

Option Explicit
Sub Examples_Decimal()
    
    'Integer Variable Declaration
    Dim DecVar As Variant
    
    'Assign value to integer variable
    DecVar = CDec(50555555000.1004)

    'Checking what type of data
    Debug.Print TypeName(DecVar) 'Decimal

End Sub

Result: Data type shown as Decimal

VBA Vode:

Option Explicit
Sub Examples_DecimalVsDouble()

    'Variables with Decimal declaration
    Dim DecVarA As Variant, DecVarB As Variant
    
    'Variables with Double declaration
    Dim DblVarA As Double, DblVarB As Double
    
    DecVarA = CDec(0.2): DecVarB = CDec(0.21) 'Decimal
    DblVarA = 0.2: DblVarB = 0.21 'Double
    
    Debug.Print DecVarA + DecVarB = 0.41    'True
    Debug.Print DblVarA + DblVarB = 0.41    'False
    
End Sub

Note: For comparison the result for Variant/Decimal is True but the result for Double is False. Meaning Variant/Decimal is more accurate compare with Double.

Microsoft Reference-Decimal-data-type
Other Reference-Decimal-data-type.htm

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

Data type - Date and Time

Data type Date are stored as IEEE 64-bit (8-byte) ranging from 1 January 100, to 31 December 9999, and times from 0:00:00 to 23:59:59. The Date uses decimal numbers to represent the date and time together therefore our result must be declare as Double.

VBA Vode: (To get different date in Hours, Days, Months and Years)

Option Explicit
Sub Examples_DateDiffer()
    
    Dim DtNumA As Date, DtNumB As Date
    Dim DtNumHr As Long, DtNumDy As Long
    Dim DtNumMn As Long, DtNumYr As Integer
    
    DtNumA = "16-10-2022": DtNumB = "16-10-2021"
    If DtNumA - DtNumB > 0 Or DtNumA - DtNumB < 0 Then
        DtNumHr = DateDiff("H", DtNumB, DtNumA) 'Different in Hours
        DtNumDy = DateDiff("D", DtNumB, DtNumA) 'Different in Days
        DtNumMn = DateDiff("M", DtNumB, DtNumA) 'Different in Months
        DtNumYr = DateDiff("YYYY", DtNumB, DtNumA) 'Different in Years
    Else
        MsgBox "Sorry! Same date compare.TQ"
        Exit Sub
    End If
     
    Debug.Print "Hours: " & DtNumHr, "Days: " & DtNumDy, _
    "Months: " & DtNumMn, "Years: " & DtNumYr

End Sub

Answer: Hours: 8760   Days: 365     Months: 12    Years: 1

VBA Vode: (To get date after add Days, Months and Years)

Option Explicit
Sub Examples_DateAdded()
    
    Dim DtBefore As Date, DtAfterDy As Date
    Dim DtAfterMn As Date, DtAfterYr As Date
    
    DtBefore = DateValue("Oct 16, 2022")
    DtAfterDy = DateAdd("d", 3, DtBefore) 'Add 3 days after
    DtAfterMn = DateAdd("m", 3, DtBefore) 'Add 3 months after
    DtAfterYr = DateAdd("yyyy", 3, DtBefore) 'Add 3 years after
    
    Debug.Print "Add 3 Days: " & DtAfterDy, "Add 3 Months: " & _
    DtAfterMn, "Add 3 Years: " & DtAfterYr
    
End Sub

Answer: Add 3 Days: 19/10/2022      Add 3 Months: 16/1/2023     Add 3 Years: 16/10/2025

VBA Vode: (To get current date and time)

Option Explicit
Sub Examples_DateCurrent()

    Dim DtNowDef As Date, DtNowCst As String
    
    DtNowDef = Now() 'Default
    DtNowCst = VBA.Format(Now(), "mm.dd.yy hh:mm") 'Custom
    
    Debug.Print "Default: " & DtNowDef, "Custom: " & DtNowCst

End Sub

Answer: Default: 16/10/2022 9:55:07 PM            Custom: 10.16.22 21:55
(The answer base on my own time when run this code)

Note: 

To converts a string to time serial number we use TimeValue function. The time's serial number is a number between 0 and 1. For example, Afternoon (half day) is represented by 0.5.

Microsoft Reference-Date-data-type
Other Reference-Date-data-type

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

Data type - Currency

Data type Currency stored as 64-bit (8-byte) numbers in an integer format, maximum 15 digit number with 4 digit decimal point. Range between -922,337,203,685,477.5808 to 922,337,203,685,477.5807. The error code same as byte if we assigned out range "Run-time error '6' Overflow.

VBA Vode:

Option Explicit
Sub Examples_Currency()
    
    Dim DbNumA As Double, DbNumB As Double
    Dim CurNum As Currency
    
    DbNumA = 10.5678942563: DbNumB = 15.010234201356
    If Not Abs(DbNumA - DbNumB) > 999999999999999# Then
        CurNum = DbNumA - DbNumB
    Else
        MsgBox "Sorry! Data overflow.TQ"
    End If
    
    Debug.Print CurNum

End Sub

Note: 

The answer above example is -4.4423, only 4 decimal points is allow for data type currency even original data is more than that.

Microsoft Reference-Currency-data-type
Other Reference-Currency-data-type.htm

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

Data type - Byte

Data type Byte store as single 8-bit (1-byte) numbers from 0-255. The default value is 0 and no negative value and more than 255. It will generate error when we try to assigned out of range "Run-Time Error '6' over flow.

VBA Vode:

Option Explicit
Sub Examples_Byte()
    
    Dim iNumA As Integer, iNumB As Integer
    Dim BytNum As Byte
    
    iNumA = 10: iNumB = 15
    If Not Abs(iNumA - iNumB) > 255 Then
        BytNum = Abs(iNumA - iNumB) 'Remove negative sign
    Else
        MsgBox "Sorry! Data overflow.TQ"
    End If
    
    Debug.Print BytNum

End Sub

Note: 

Above example we use ABS function to remove negative sign and get absolute value and test the value over 255 or not then only assigned Byte data type.

Microsoft Reference-Byte-data-type
Other Reference-Byte-data-type.htm

Practice makes perfect. Thank You.

excelmacros
macro excel
excel programming
excel vba

Data type - Boolean

Data type Boolean is only True ( -1 ) Or False ( 0 ) and basically stored as 16-bit (2-byte) numbers. The default is False or 0 (zero) but if the statement result other than 0 then it consider True. For example we have numbers A=5 and B=5 then A-B=0 then boolean consider False and vice versa.

VBA Vode:

Option Explicit
Sub Examples_Boolean()

    Dim iNumA As Integer, iNumB As Integer
    Dim BlResult As Boolean
    
    iNumA = 10: iNumB = 5
    BlResult = Not (iNumA < iNumB)
    
    Debug.Print BlResult

End Sub

Note: 

The result for the above code is True? Don't get confuse when we use Not operator, because inside bracket statement is False (10<5) then operator Not reverse the statement become True.

Microsoft Reference-Boolean-data-type
Other Reference-Boolean-data-type

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

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

Variables Data type Summary in Excel VBA

1.       Boolean
Storage size: 2 bytes
Range: True or False
Declaration: Dim BlnVar As Boolean

2.       Byte
Storage size: 1 byte
Range: 0 to 255
Declaration: Dim BytVar As Byte

3.       Collection
Storage size: Unknown
Range: Unknown
Declaration: Dim ColVar As Collection

4.       Currency (scaled integer)
Storage size: 8 bytes
Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Declaration: Dim CurVar As Currency OR Dim CurVar@

5.       Date
Storage size: 8 bytes
Range: January 1, 100, to December 31, 9999
Declaration: Dim DtVar As Date

6.       Decimal
Storage size: 14 bytes
Range1: +/-79,228,162,514,264,337,593,543,950,335 with no decimal point
Range2: +/-7.9228162514264337593543950335 with 28 places to the right of the decimal
Note: Smallest non-zero number is +/-0.0000000000000000000000000001
Declaration: Dim DecVar As Variant follow by
                       DecVar = Cdec(Number)

7.       Double (double-precision floating-point)
Storage size: 8 bytes
Range1: -1.79769313486231E308 to -4.94065645841247E-324 for negative values
Range2: 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Declaration: Dim DblVar As Double OR Dim DblVar#

8.       Integer
Storage size: 2 bytes
Range: -32,768 to 32,767
Declaration: Dim IntVar As Integer OR Dim IntVar%

9.       Long (Long integer)
Storage size: 4 bytes
Range: -2,147,483,648 to 2,147,483,647
Declaration: Dim LngVar As Long OR Dim LngVar&

10.   LongLong (LongLong integer)
Storage size: 8 bytes
Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Note: Valid on 64-bit platforms only.
Declaration: Dim LngLngVar As LongLong OR Dim LngLngVar^

11.   LongPtr (Long integer on 32-bit systems, LongLong integer on 64-bit systems)
Storage size: 4 bytes on 32-bit systems
Range on 32-bit: -2,147,483,648 to 2,147,483,647 on 32-bit systems
Storage size: 8 bytes on 64-bit systems
Range 64-bit: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems
Declaration: Dim LngPtrVar As LongPtr

12.   Object
Storage size: 4 bytes
Range: Any Object reference
Declaration: Dim ObjVar As Object

13.   Single (single-precision floating-point)
Storage size: 4 bytes
Range: -3.402823E38 to -1.401298E-45 for negative values
Range: 1.401298E-45 to 3.402823E38 for positive values
Declaration: Dim SglVar As Single OR Dim SglVar!

14.   String (variable-length)
Storage size: 10 bytes + string length
Range: 0 to approximately 2 billion
Declaration: Dim StrVar As String OR Dim StrVar$

15.   String (fixed-length)
Storage size: Length of string
Range: 1 to approximately 65,400
Declaration: Dim StrVar As String OR Dim StrVar$

16.   Variant (with numbers)
Storage size: 16 bytes
Range: Any numeric value up to the range of a Double
Declaration: Dim VarVar As Variant OR Dim VarVar$

17.   Variant (with characters)
Storage size: 22 bytes + string length (24 bytes on 64-bit systems)
Range: Same range as for variable-length String
Declaration: Dim VarVar As Variant OR Dim VarVar$

18.   User-defined (using Type)
Storage size: Number required by elements
Range: The range of each element is the same as the range of its data type.
Declaration:
Type MyType
    iTem1 as String
    iTem2 as Integer
End type
Dim UdfVar As MyType

Note: Basically we have more than 18 but we are not going to use all. The most frequent use probably String, Integer, Double, Long, Variant and date. To be discuss in detail later on.

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