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.