Comparison Operator Is in Excel VBA

Comparison operator Is basically consist of 3 type but type 2 is commonly use.

1) Direct compare 2 object with variables reference for example Ranges, Worksheets and etc.

Base on below table we try to compare 2 variables assign to different range.



VBA Code:

Option Explicit
Sub ComparisonOperator_Is()
    
    Dim i As Integer
    Dim RngA As Range
    Dim RngB As Range
    Dim RngC As Range
     
    With ActiveSheet
        Set RngA = .Range("A2:A4")
        Set RngB = .Range("B2:B4")
        Set RngC = RngA
        
        .Range("C2") = RngA Is RngB
        .Range("D2") = RngA Is RngC
        .Range("E2") = RngB Is RngC
        
        For i = 1 To 3
            If .Cells(2, 2 + i).Text = "TRUE" Then
                .Range(Cells(2, 2 + i), Cells(4, 2 + i)). _
                Interior.ColorIndex = 4
            Else
                .Range(Cells(2, 2 + i), Cells(4, 2 + i)). _
                Interior.ColorIndex = 6
            End If
        Next i
    End With
    
End Sub

2) Comparison with Is Nothing

Below example we try to find certain keyword (Ex. Score) inside any active sheet and get row and column index.

VBA Code:

Option Explicit
Sub ComparisonOperator_IsNothing()
    
    Dim StrKeyword As String
    Dim FndKeyword As Range
    Dim MyRng As Range
    
    StrKeyword = "Score"
    Set MyRng = ActiveSheet.UsedRange
    
    Set FndKeyword = MyRng.Find(StrKeyword, LookIn:=xlValues, _
    LookAt:=xlWhole)
    If FndKeyword Is Nothing Then
        MsgBox "Sorry the keyword" & StrKeyword & " was not found"
    Else
        MsgBox "The keyword found at Row index = " & FndKeyword.Row & _
        " Column index = " & FndKeyword.Column
    End If

End Sub

3) Comparison with Is Nothing with Intersect.

Below example to check either our selection is inside our designated range.

VBA Code:

Option Explicit
Sub ComparisonOperator_IsNothingInteSect()
    
    Dim MyRng As Range
    Dim MySelect As Range
    
    With ActiveSheet
        Set MyRng = .Range("A1:D10")
        Set MySelect = Selection
    
        If Not Intersect(MySelect, MyRng) Is Nothing Then
            MsgBox "Your Selected cell is in range A1 to D10."
        Else
            MsgBox "Your Selected cell is Not in range."
        End If
    End With
    
End Sub

Microsoft Reference-Is-operator

Practice makes perfect. Thank You.

Comparison Operator in Excel VBA

 Basically 6 types of comparison operator as below table.

No.			Operator			Compare Value
1			<			Less Than
2			<=			Less than or equal to
3			>			Greater than
4			>=			Greater than or equal to
5			=			Equal to
6			<>			Not equal to

Comparison operator to be use either compare 2 value or checking certain condition example empty or null but mostly to check numeric value as below either True or False. Inside Green and Yellow cell we have formula =Num1 Operator Num2 example inside Range("E3") =B3<D3 then answer is TRUE.

 

VBA Code:

Option Explicit
Sub ComparisonOperator()
    Dim i As Integer
    Dim Cell As Range, MyRng As Range
     
    With ActiveSheet
        i = 3
        Do
            'For < Less Than
            .Range("E" & i) = .Range("B" & i) < .Range("D" & i)
            'For <= Less than or equal to
            .Range("F" & i) = .Range("B" & i) <= .Range("D" & i)
            'For > Greater than
            .Range("G" & i) = .Range("B" & i) > .Range("D" & i)
            'For >= Greater than or equal to
            .Range("H" & i) = .Range("B" & i) >= .Range("D" & i)
            'For = Equal to
            .Range("I" & i) = .Range("B" & i) = .Range("D" & i)
            'For <> Not equal to
            .Range("J" & i) = .Range("B" & i) <> .Range("D" & i)
            i = i + 1
        Loop While .Range("A" & i) <> ""
        Set MyRng = .Range("E3:J5")
        For Each Cell In MyRng
            If Cell.Text = "TRUE" Then
                Cell.Interior.ColorIndex = 4
            Else
                Cell.Interior.ColorIndex = 6
            End If
        Next
    End With
    
End Sub

Microsoft Reference-Comparison-operators

Practice makes perfect. Thank You.

To Create Hyperlink with Excel VBA

 In Microsoft Excel to insert Hyperlink just click at Insert Tab and click at Link world icon with chain. Key in Text to display, add ScreenTip if you wish and select link or location. To convert into HTML we have save this selection link as webpage and publish. We also can use VBA code to convert this link into HTML code. For example we have table as below.

Run below code to get HTML code inside notepad.

VBA Code:

Option Explicit
Sub ToCreate_HyperLinkListing_Reference()
        
    Dim OpenShell As Variant
    Dim FSO, fs As Object
    
    Dim MstWS As Worksheet
    Dim StrPath As String
    Dim Rw As Long
    
    Dim BrktCls As String, Qto As String
    Dim HpLinkOpn As String, HpLinkCls As String
    Dim TtlTag As String, StrTgt As String
    Dim BrTag As String
    Dim pTgOpn As String, pTgCls As String
    
    Dim StrTitle As String, StrAchText As String, StrLink As String
    
    'Assign Variables
    Set MstWS = ActiveSheet
    BrktCls = ">": Qto = """"
    HpLinkOpn = "<a href=": HpLinkCls = "</a>"
    TtlTag = " title="
    StrTgt = "target=" & Qto & "_blank" & Qto
    BrTag = "<br />"
    pTgOpn = "<p>": pTgCls = "</p>"
    
    'To Create new Text File
    StrPath = VBA.Environ("UserProfile") & "\Desktop\MacroReadMe.txt"
    
    'Create Object to open Notepad
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'To Check files existance and delete old file
    On Error Resume Next
    If Dir(StrPath) <> "" Then Kill StrPath

    'To write into NotePad
    If Dir(StrPath) = "" Then
        Set fs = FSO.CreateTextFile(StrPath, 2)
        With fs
            Rw = 3
            Do While MstWS.Range("B" & Rw) <> ""
            
                StrTitle = MstWS.Range("C" & Rw)
                StrAchText = MstWS.Range("B" & Rw)
                StrLink = MstWS.Range("D" & Rw)
                
                .WriteLine HpLinkOpn & Qto & StrLink & Qto & TtlTag & _
                Qto & StrTitle & Qto & " " & StrTgt & BrktCls & _
                StrAchText & HpLinkCls & BrTag
                
                StrTitle = "": StrAchText = "": StrLink = ""
                Rw = Rw + 1
            Loop
        End With
    End If

    'Just To Open NotePad Created
    If Err = 0 Then
        OpenShell = Shell("C:\Windows\System32\notepad.exe " & _
        StrPath, vbNormalFocus)
    End If
    On Error GoTo 0
    
    'Reset Variables
    StrPath = ""
    BrktCls = "": Qto = ""
    HpLinkOpn = "": HpLinkCls = ""
    TtlTag = "": BrTag = ""
End Sub

The result in HTML as below:

Microsoft Excel VBA
Computer Resources
Digital Camera Resources
Computer Games Collection

Practice makes perfect. Thank You.

Basic Operator for Excel VBA

Before start your 1st code it is better to understand basic operator purpose and function as below table. Certain operator can be use as different function for example + and =.

No.

Category

Operator

Purpose

Example

Result

1

Numeric Variables

+

Sum

2 + 5

7

2

Numeric Variables

-

Minus

5 - 2

3

3

Numeric Variables

*

Multiply

5 * 5

25

4

Numeric Variables

/

Divide

5.5 / 2

2.75

5

Numeric Variables

^

Exponential

5.5 ^ 2

30.25

6

Numeric Variables

=

Equivalent

2 + 5 = 7

7

String/Variables

=

Assign

MyVar = "C:\MyFiles"

8

Property/Object

=

Assign

Set MyVar = ActiveSheet

9

String/Variables

+

Concatenation

"Hello " + "World"

Hello World

10

String/Variables

&

Concatenation

"Hello " & "World"

Hello World

11

Numeric Integer

\

Divide

5.5 / 2

2

The above table just a basic operator not comparison operator.

Microsoft Reference-Operator-summary
Microsoft Reference-Operator (* Operator)
Microsoft Reference-Caret-operator (^ operator)
Microsoft Reference-Plus-operator (+ operator)
Microsoft Reference-Ampersand-operator (& operator)
Microsoft Reference-Equals-operator (= operator)
Microsoft Reference-Minus-operator (- operator)
Microsoft Reference-Forwardslash-operator (/ operator)
Microsoft Reference-Backslash-operator (\ operator)

Practice makes perfect. Thank You.

Working with Select Case Statement

Select case statement is more readable compare with If Then Else statement if the condition to many level for example below table:

No.

Condition

Grade

Bg Color

1

Score More Than >80

A

Green

2

Score More Than >60

B

Yellow

3

Score More Than >40

C

Blue

4

Other Score

D

Red

Base on the above table we have 4 conditions to test and give different result into below table.

VBA Code:

Option Explicit
Sub SelectCaseExample()

    Dim i As Integer
    Dim IntScore As Integer
    With ActiveSheet
        i = 2
        Do
            IntScore = .Range("C" & i)
            Select Case IntScore
                Case Is > 80
                    .Range("D" & i) = "A"
                    .Range("D" & i).Interior.ColorIndex = 4
                Case Is > 60
                    .Range("D" & i) = "B"
                    .Range("D" & i).Interior.ColorIndex = 6
                Case Is > 40
                    .Range("D" & i) = "C"
                    .Range("D" & i).Interior.ColorIndex = 5
                Case Else
                    .Range("D" & i) = "D"
                    .Range("D" & i).Interior.ColorIndex = 3
            End Select
            i = i + 1
        Loop While .Range("A" & i) <> ""
    End With

End Sub

Result after run above code.

Microsoft Reference - Select Case Statement

Practice makes perfect. Thank You.

Convert Excel Table into HTML table with VBA code

Actually to convert Excel table into HTML table is to use Save As web page either whole sheet or selection but we also can use VBA code below to convert by selecting table range example below and run the code. New pop up window will open notepad with HTML code inside and immediately can paste into your blog or webpage.

Run below code to get HTML code inside notepad.

VBA Code:

Option Explicit
Sub ToCreateTableForSelectedRange()
    Dim OpnShell As Variant
    Dim FSO, fs As Object
    Dim StrPath As String
    
    Dim iRow As Integer, iCol As Integer
    Dim Cell As Range, Rng As Range
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, x As Integer
    
    Dim DbWitdh() As Double, DbWitdhTot As Double
    Dim DbWitdhP() As String, DbWitdhAcc As Double
    Dim iColSp As Byte
    
    Dim Qto As String, ClEnd As String
    Dim BdOpn As String, BdCls As String
    Dim TdOpn As String, TdCls As String
    Dim TrOpn As String, TrCls As String
    Dim TblOpn As String, TblCls As String
    Dim StrStyle As String, StrTblWd As String
    Dim StrCellSp As String, StrCellPd As String
    Dim StrBdrCol As String, StrBdr As String
    Dim TBdOpn As String, TBdCls As String
    Dim StrCS As String, StrWd As String, StrAl As String
    
    Qto = """": ClEnd = ">"
    BdOpn = "<b>": BdCls = "</b>"
    TdOpn = "<td": TdCls = "</td>"
    TrOpn = "<tr>": TrCls = "</tr>"
    StrStyle = " style=" & Qto & "border-collapse: collapse;"
    StrTblWd = " width: 500px;" & Qto
    StrCellSp = " cellspacing=" & Qto & "0" & Qto
    StrCellPd = " cellpadding=" & Qto & "3" & Qto
    StrBdrCol = " bordercolor=" & Qto & "#000000" & Qto
    StrBdr = " border=" & Qto & "1" & Qto
    TblOpn = "<table" & StrStyle & StrTblWd & StrCellSp & _
    StrCellPd & StrBdrCol & StrBdr & ClEnd
    TblCls = "</table>"
    TBdOpn = "<tbody>": TBdCls = "</tbody>"
    StrCS = " colspan=" & Qto: StrWd = " width=" & Qto
    StrAl = " align=" & Qto & "Center" & Qto
    
    Set Rng = Selection
    If Rng.Rows.Count < 2 Or Rng.Columns.Count < 2 Then
        MsgBox "Please select range with table!": GoTo Line1
    End If
    
    'To Create new Text File
    StrPath = VBA.Environ("UserProfile") & "\Desktop\TableGenerator.txt"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'To Check files existance and delete
    On Error Resume Next
    If Dir(StrPath) <> "" Then Kill StrPath
    
    k = 1
    For Each Cell In Rng
        If Cell.Row > iRow And iRow > 0 Then Exit For
        If iRow = 0 Then iRow = Cell.Row
        If iCol = 0 Then iCol = Cell.Column
        ReDim Preserve DbWitdh(k): DbWitdh(k) = Cell.ColumnWidth
        DbWitdhTot = DbWitdhTot + DbWitdh(k)
        iColSp = k
        k = k + 1
    Next

    For x = LBound(DbWitdh) + 1 To UBound(DbWitdh)
        ReDim Preserve DbWitdhP(x)
        If x < UBound(DbWitdh) Then
            DbWitdhP(x) = Round((DbWitdh(x) / DbWitdhTot) * 100, 0)
            DbWitdhAcc = DbWitdhAcc + DbWitdhP(x)
        Else
            DbWitdhP(x) = 100 - DbWitdhAcc
        End If
    Next x
    
    k = 1
    If Dir(StrPath) = "" Then
        Set fs = FSO.CreateTextFile(StrPath, 2)
        With fs
            .WriteLine TblOpn & TBdOpn
            For i = iRow To iRow + Rng.Rows.Count - 1
                l = 1
                .WriteLine TrOpn
                For j = iCol To iCol + iColSp - 1
                    If k = 1 Then
                        .WriteLine TdOpn & StrCS & iColSp & Qto & _
                        StrWd & DbWitdhP(l) & "%" & Qto & StrAl & _
                        ClEnd & BdOpn & Cells(i, j) & BdCls & TdCls
                    Else
                        .WriteLine TdOpn & StrCS & iColSp & Qto & _
                        StrWd & DbWitdhP(l) & "%" & Qto & StrAl & _
                        ClEnd & Cells(i, j) & TdCls
                    End If
                    l = l + 1
                Next j
                .WriteLine TrCls
                k = k + 1
            Next i
            .WriteLine TBdCls & TblCls
        End With
    End If
    
    'Just To Open NotePad Created
    If Err = 0 Then
        OpnShell = Shell("C:\Windows\System32\notepad.exe " & _
        StrPath, vbNormalFocus)
    End If
    On Error GoTo 0
    
Line1:
    'Reset Variables
    iRow = 0: iCol = 0
    Set Cell = Nothing: Set Rng = Nothing
    Erase DbWitdh: DbWitdhTot = 0
    Erase DbWitdhP: DbWitdhAcc = 0
    iColSp = 0
End Sub

The result in HTML as below:

No.				Subject				Score				Grade
1				English				100
2				Mathematics				65
3				Science				30
4				Physics				55
5				History				20
6				Chemistry				48
7				Biology				70
8				Geography				90

Practice makes perfect. Thank You.

Debugging with Debug print method

Debug print is very useful for debugging instead of using message box because we can run without clicking OK button when we use message box. Debug print is utilizing immediate window below module under Visual Basic Editor (VBE) as below:

Base on above Table subject vs score we can print this value under immediate window as below:

The above example we use direct Debug.print value1,value2,value3,....

The purpose is to print value especially variables when our code is getting complicated and we wish to verify our code run without error later on.

Below example we use operator to connect or joint value1 & "-" & Value2 & "=" & Value3 but still using the same table above.

VBA Code:

Option Explicit
Sub PrintMethodExample()

    Dim i As Integer
    Dim StrSubject As String
    Dim IntScore As Integer
    With ActiveSheet
        i = 2
        Do
            'Assign Variables
            StrSubject = .Range("B" & i)
            IntScore = .Range("C" & i)
            
            'Print to immediate window for debugging
            Debug.Print i - 1 & "-" & StrSubject & "=" & IntScore
            
            'Reset Variables
            StrSubject = ""
            IntScore = 0
            i = i + 1
        Loop While .Range("A" & i) <> ""
    End With
    
End Sub

Result:

Note:
Normally when error occurred we put Debug.print before error line to verify which variables caused error. For example variable declare as integer but actual is long or double.

Microsoft Reference - Print Method

Practice makes perfect. Thank You.

Working with If...Then...Else

If...Then...Else statement is to test certain condition either true or false and can be tested as many level as per required. Basically can be divide into 4 types:

Below example base on Table subject vs score, decide what to do base on score.

1. Single condition with single line return (If...Then)
   
   
   

   No.			Condition			Return
   1			Score more than 50			Range background color to green

   VBA Code:

   Option Explicit
   Sub IFStatemantsSingleConditionSingleReturn()
   
       Dim i As Integer
       With ActiveSheet
           i = 2
           Do
               If .Range("C" & i) > 50 Then _
               Range("A" & i & ":C" & i).Interior.ColorIndex = 4
               i = i + 1
           Loop While .Range("A" & i) <> ""
       End With
       
   End Sub
   
   
2. Single condition with multiple line return (If...Then...End If)
   
   
   

   No.			Condition			Return
   1			Score more than 50			Range background color to green Font text bold

   VBA Code:

   Option Explicit
   Sub IFStatemantsSingleConditionMultipleReturn()
       
       Dim i As Integer
       With ActiveSheet
           i = 2
           Do
               If .Range("C" & i) > 50 Then
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 4
                   Range("A" & i & ":C" & i).Font.Bold = True
               End If
               i = i + 1
           Loop While .Range("A" & i) <> ""
       End With
       
   End Sub
   
   
3. Double condition with multiple line return (If...Then...Else...End if)
   
   
   

   No.			Condition			Return
   1			Score more than 50			Range background color to green Font text bold
   2			Others			Range background color to Yellow

   VBA Code:

   Option Explicit
   Sub IFStatemantsDoubleConditionMultipleReturn()
       
       Dim i As Integer
       With ActiveSheet
           i = 2
           Do
               If .Range("C" & i) > 50 Then
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 4
                   Range("A" & i & ":C" & i).Font.Bold = True
               Else
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 6
               End If
               i = i + 1
           Loop While .Range("A" & i) <> ""
       End With
       
   End Sub
   
   
4. Multiple condition with multiple line return (If...Then...Elseif...Then...Else...End if)
   
   
   

   No.			Condition			Return
   1			Score more than 80			Range background color to green Font text bold
   2			Score more than 50			Range background color to Yellow
   3			Others			Range background color to Red

   VBA Code:

   Option Explicit
   Sub IFStatemantsMultipleConditionMultipleReturn()
       
       Dim i As Integer
       With ActiveSheet
           i = 2
           Do
               If .Range("C" & i) > 80 Then
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 4
                   Range("A" & i & ":C" & i).Font.Bold = True
               ElseIf .Range("C" & i) > 50 Then
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 6
               Else
                   Range("A" & i & ":C" & i).Interior.ColorIndex = 3
               End If
               i = i + 1
           Loop While .Range("A" & i) <> ""
       End With
       
   End Sub

Note:
Normally select case statement is more readable instead or using If...Then...Else statement with multiple condition and If...Then...Else statement can be place inside another if statement but be careful to close with End if properly.

Microsoft Reference - If..Then..Else statement

Practice makes perfect. Thank You.

Working with For...Next statements

For...Next statement is another type of loop but without check or condition to test. The loop is fix between numbers. Base on table below we are going to use For...Next statement to get total monthly sales.

VBA Code:

Option Explicit
Sub ForNextStatements()
    
    Dim i As Integer
    For i = 3 To 14
        Range("D" & i) = Range("B" & i) + Range("C" & i)
    Next i
    
End Sub

To do in reverse way:

Option Explicit
Sub ForNextStatementsRev()
    
    Dim i As Integer
    For i = 14 To 3 Step -1
        Range("D" & i) = Range("B" & i) + Range("C" & i)
    Next i
    
End Sub

Note:
After Next without mentioned i also work perfectly but prefer to add i after Next because whenever your code getting complicated then difficult to trace For..Next loop overlapping.

Microsoft Reference - For..Next statement

Practice makes perfect. Thank You.

Working with For Each...Next statements

For Each...Next statement is another type of loop but without check or condition to test. The loop is fix between certain range or numbers. Base on table below we are going to use For Each...Next statement to get total monthly sales.

Before proceed we must understand basic offset function.

The formula for offset = Range().Offset ([RowOffset], [ColumnOffset]) for example:
Range("A1") = Range("B1").offest(0,-1) which is refer to same cell or range.

VBA Code:

Option Explicit
Sub ForEachNextStatements()

    Dim RngS As Range
    Dim MyRng As Range
    
    Set MyRng = ActiveSheet.Range("D3:D14")
    For Each RngS In MyRng
        RngS = RngS.Offset(0, -2) + RngS.Offset(0, -1)
    Next
    
End Sub

Another Example to Loop every Sheets:

Option Explicit
Sub ForEachNextStatementsSht()
    
    Dim MySht As Worksheet

    For Each MySht In Worksheets
        'Your code goes here
        Debug.Print MySht.Name
    Next
    
End Sub

Another Example to Loop every Cells:

Option Explicit
Sub ForEachNextStatementsCell()
    
    Dim Cell As Range

    For Each Cell In ActiveSheet.UsedRange
        'Your code goes here
        Debug.Print Cell.Value
    Next
    
End Sub

Microsoft Reference - For Each..Next statement

Practice makes perfect. Thank You.

Working with Do...Loop statement

 Basically Do...Loop statement consist 2 types:

• While Condition
  Either Check First or Check Last
  
• Until Condition
  Either Check First or Check Last

Before proceed we must understand basic operator as below:

Basic Operator
1	<	Less than
2	<=	Less than or equal to
3	>	Greater than
4	>=	Greater than or equal to
5	=	Equal to
6	<>	Not equal to
7	""	Empty

Base on table below we are going to use Do..Loop statement to get total monthly sales.

In case we use Range("A3") to Range("A14"). For Do...Loop While Check whether Not Equal to empty (<> "") but for Do...Loop Until  Check whether Equal to empty (=""). Under certain circumstances the result may be different can cause bug in your code. Please be careful.

VBA Code:

1) While Condition Check first.

Option Explicit
Sub DoWhile_CheckFirst()
    
    Dim i As Integer
    i = 3
    With ActiveSheet
        Do While .Range("A" & i) <> ""
            .Range("D" & i) = .Range("B" & i) + .Range("C" & i)
            i = i + 1
        Loop
    End With

End Sub

2) While Condition Check last.

Option Explicit
Sub DoWhile_CheckLast()
    
    Dim i As Integer
    i = 3
    With ActiveSheet
        Do
            .Range("D" & i) = .Range("B" & i) + .Range("C" & i)
            i = i + 1
        Loop While .Range("A" & i) <> ""
    End With

End Sub

3) Until Condition Check first.

Option Explicit
Sub DoUntil_CheckFirst()
    
    Dim i As Integer
    i = 3
    With ActiveSheet
        Do Until .Range("A" & i) = ""
            .Range("D" & i) = .Range("B" & i) + .Range("C" & i)
            i = i + 1
        Loop
    End With

End Sub

4) While Condition Check last.

Option Explicit
Sub DoUntil_CheckLast()
    
    Dim i As Integer
    i = 3
    With ActiveSheet
        Do
            .Range("D" & i) = .Range("B" & i) + .Range("C" & i)
            i = i + 1
        Loop Until .Range("A" & i) = ""
    End With

End Sub

Note:
Do Loop without counter will create infinite loop and your computer will hang. The counter for above example is i = i + 1.

Microsoft Reference - Do..Loop statement
Microsoft Reference - Using Do..Loop statement

Practice makes perfect. Thank You.

How to force variables declaration before start VBA code?

Variables declaration is very importance before start writing any code in VBA because:

• More organize whenever code is getting complicated or longer otherwise we get lost somewhere and stuck (Normally variables declaration is on top of any procedure).
• Computer memory limitation slowing down the processing because without declaration default is set as variant.
• More easy to write especially on object example Workbook, Worksheet and etc. This is because we can use Tools tip suggestion whenever we key in dot after variables.
• Simplify code because we only assigned this variables one time but can be use many time. For example:
  Dim StrPath As String
  StrPath = "C:\Users\UserName\Desktop\MyFiles.xlxs"
  We use StrPath instead of full path and can be repeated many time.

To force declaration either Dim, Private, Public, ReDim, or Static statements, we use Option Explicit statement on top of module before any procedures. Variables without declaration will be warn as below.

Microsoft Visual Basic for Applications: Compile error: Variable not defined

To set as default whenever module inserted:

• Under Visual Basic Editor Click Tools follow by Option.
  
  
  
  
  
• Under Options window select Editor Tab and tick Require Variable Declaration.
  
  
  
  
  
• Click OK. Done

Note: Every time Module inserted "Option Explicit" always on top.

Microsoft Reference - Option Explicit Statement

Practice makes perfect. Thank You.

How to insert and start writing VBA code.

Before started module must be insert as below.

• Open Visual Basic Editor(VBE) by pressing Alt + F11 or read here for more detail.
• Click insert Tab follow by module.
  
  
  
• Default inserted Module name Module1..2..3 and etc.
  (Module can be rename later by accessing their property)
  
  
  
  
  My default setting every module inserted with Option Explicit declaration will explain later.

There are 2 ways to start writing VBA code.

• Direct type in Sub>Space>Macro Name>Open Bracket>Close Bracket and hit enter
  Ex. Sub My1StVBACode() and hit enter automatically End Sub appeared as below.
  
  
  
  
  Now we can start writing any code or copy code given and paste here.
  
  OR
  
• Click insert Tab follow by Procedure.
  
  
  
• Key in My1StVBACode inside Name Box and click OK.
  
  
  
  
  Let default setting for the rest and will be explain later.
• The procedure appeared as below.
  
  
  

Note:

1. Sub is equal to Public Sub and can be called from anywhere.
   
2. This is Called procedure and listed under macro name when run macro.
   

Microsoft Reference - Sub Statement

Practice makes perfect. Thank You.

To Protect and Unprotect Sheet with VBA Code

To protect worksheet from editing. 

• Go to Review Tab and click at Protect Sheet.
  
  
  
• Under Protect Sheet Key in Password and Tick allow user to do and click OK.
  
  
  
• Key in password to reconfirm and click OK.
  
  
  
  
• Done.

VBA code:

Option Explicit
Sub ProtectSheet()
    
    If ActiveSheet.ProtectContents = False Then
        With ActiveSheet
            .Protect Password:="abc123", AllowInsertingRows:=True, _
             AllowDeletingRows:=True, Contents:=True, _
             AllowFiltering:=True, Scenarios:=True
            .EnableSelection = xlUnlockedCells
        End With
    End If
    
End Sub

To Unprotect worksheet. 

• Go to Review Tab and click at Unprotect Sheet.
  
  
  
• Key in password and click OK.
  
  
  
• Done.

VBA code:

Option Explicit
Sub UnprotectSheet()
    
    If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect Password:="abc123"
    End If
    
End Sub

Microsoft Reference - Protect Worksheet

Practice makes perfect. Thank You.

To Add Hyperlink Formula and VBA code

Hyperlink in Microsoft Excel is to create link to any location example URL, Drive, Range, Cells or etc.

Formula = HYPERLINK(link_location,friendly_name).

• link_location is https://mrvba.blogspot.com/
• friendly_name is Visual Basic For Application.

VBA code:

Option Explicit
Sub InsertingHyperlink()

    With ActiveSheet
        'a) Insert with formula
        .Range("A1").Formula = "=HYPERLINK(""http://mrvba.blogspot.com"",""Visual Basic for Application"")"
        
        'b) Simple Link direct URL
        .Hyperlinks.Add Range("A2"), "http://mrvba.blogspot.com"
        
        'c) With Anchor Text and Screen Tips
        .Hyperlinks.Add Range("A3"), "http://mrvba.blogspot.com", , "To learn VBA", "Click here"
        
        'd) With details Anchor Text and Screen Tips
        .Hyperlinks.Add Anchor:=Range("A4"), Address:="http://mrvba.blogspot.com", _
        SubAddress:="", ScreenTip:="To learn VBA", TextToDisplay:="Click Here"
    End With

End Sub

Microsoft Reference - Hyperlink Method

Done.

To Use Hlookup with formula and VBA code

Hlookup formula in Microsoft Excel is to find something in table or range by columns. In this example we refer to table monthly score in Sheet1.

Table in Sheet1: Base on monthly we need to find score in

• Range("B2:M3)
  
• Row index = 2

Formula = HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).

In Sheet2 we have another table without score, now we have to lookup this value from Sheet1 table by using formula.

Fill this formula into Range("C2") = HLOOKUP(B2,Sheet1!$B$2:$M$3,2,FALSE).

• B2 is lookup_value
• Sheet1!$B$2:$M$3 is table_array but $ sign to fix the table when drag down.
• 2 is row_index_num
• FALSE is range_lookup (FALSE - Exact Match, TRUE - Approximate match)

VBA code:

Option Explicit
Sub HLookUPExample()

    Dim i As Integer
    With ActiveSheet
        i = 2
        Do
            .Range("C" & i) = Application.HLookup(.Range("B" & i), ActiveWorkbook.Sheets("Sheet3").Range("$B$2:$M$3"), 2, False)
        i = i + 1
        Loop While .Range("A" & i) <> ""
    End With
    
End Sub

Note:

• Make sure Sheet2 is selected or activated.
• If your table is located in another workbook then change ActiveWorkbook to Workbooks("MyBookName.xls") but ensure this workbook is open.

Microsoft Reference - Hlookup function

Done.

To Use Vlookup with formula and VBA code

Vlookup formula in Microsoft Excel is to find something in table or range by rows. In this example we refer to table monthly score in Sheet1.

Table in Sheet1 and range = Range("B2:C13)  and base on month we need to find score in column index = 2. 

Formula = VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).

In Sheet2 we have another table without score, now we have to lookup this value from Sheet1 table by using formula.

Fill this formula into Range("C2") = VLOOKUP(B2,Sheet1!$B$2:$C$13,2,FALSE).

• B2 is lookup_value
• Sheet1!$B$2:$C$13 is table_array but $ sign to fix the table when drag down.
• 2 is column_index_num
• FALSE is range_lookup (FALSE - Exact Match, TRUE - Approximate match)

VBA code:

Option Explicit
Sub VLookUPExample()

    Dim i As Integer
    With ActiveSheet
        i = 2
        Do
            .Range("C" & i) = Application.VLookup(.Range("B" & i), ActiveWorkbook.Sheets("Sheet1").Range("$B$2:$C$13"), 2, False)
        i = i + 1
        Loop While .Range("A" & i) <> ""
    End With
    
End Sub

Note:

• Make sure Sheet2 is selected or activated.
• If your table is located in another workbook then change ActiveWorkbook to Workbooks("MyBookName.xls") but ensure this workbook is open.

Microsoft Reference - Vlookup function

Done.

To Go Specific Cells or Range

Below code to is To Go Specific Cells and can be use to scroll any direction.

Option Explicit
Sub ToGoSpecficCells()

    Application.Goto ActiveSheet.Range("A1"), True
    
End Sub

Note: Change cells location in bracket A1-> any cell and Go cells or range will be at top left corner of excel sheet.

To Scroll up entire rows and Scroll left entire columns

Below code to is To Scroll up entire rows and Scroll left entire columns.

Option Explicit
Sub ToScrollUPcompletely()

    With ActiveWindow
        .ScrollColumn = 1
        .ScrollRow = 1
    End With
    
End Sub

Note: Wherever your location in excel sheet it will return to top left.

To Sort Data Ascending Or Descending without Header

Below code to sort data with header ascending or descending. For example data with 3 columns.

Option Explicit
Sub ToSortWithOutHeader()

    Range("A2:C6").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
    Key2:=Range("B2"), Order2:=xlAscending, _
    Key3:=Range("C2"), Order3:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
    DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
    Range("A1").Select
    
End Sub

Note: Selection must include header and for Descending just change xlAscending to xlDescending.

To Sort Data Ascending Or Descending with Header

Below code to sort data with header ascending or descending. For example data with 3 columns.

Option Explicit
Sub ToSortWithHeader()

    Columns("A:C").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
    Key2:=Range("B2"), Order2:=xlAscending, _
    Key3:=Range("C2"), Order3:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
    DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
    Range("A1").Select
    
End Sub

Note: Selection must include header and for Descending just change xlAscending to xlDescending.