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.

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 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.

Working with Microsoft Excel Objects,Properties and Methods

The definition given by Microsoft for:

1. Object An object represents an element of Excel for examples workbooks, worksheets, range, cells and etc.Visual Basic actually object orientation programming language, nearly everything you do in Visual Basic involves modifying objects. There are four Excel objects you will work with more than any others: the Application object, the Workbook object, the Worksheet object, the Range object. The above sequences actually top to down object hierarchy.
2. Properties A property is an attribute of an object or an aspect of its behavior, for example when we refer to Range object it should be border, fill color and etc.
3. Methods A method is an action that an object can perform, for example when we refer to Range object action can perform should copy, cut and paste, delete and etc.

I think the above explanations is clear and easy to understand but don’t worry you will be able to understand when you continue try and error. The more you record the more you understand the coding. To be more clear understand refer below code and try by yourself.

Option Explicit
Sub SelectOjectAndChangeProperties ()

  Range("A1:A2").Select
  Selection.Interior.ColorIndex = 3

End sub

The above code will select (Method) Range A1 and A2 (Object) and change Interior color index (Property) to red. Copy the above into your modules and try it but don’t forget to enable your macro and set your security to medium. Click here if you have any new project! Let us create for u for free. Thanks