Create Hyperlinks with Excel VBA

To create hyperlinks in Excel using VBA, you can use the Hyperlinks.Add method. Below is a detailed code that demonstrates how to create hyperlinks programmatically in Excel using VBA, along with an explanation of each part of the code.

VBA Code to Create Hyperlinks

Sub CreateHyperlinks()
    Dim ws As Worksheet
    Dim cell As Range
    Dim hyperlinkText As String
    Dim hyperlinkAddress As String   
    ' Set the worksheet where hyperlinks will be created
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Loop through the range where you want to create hyperlinks (e.g., A2:A10)
    For Each cell In ws.Range("A2:A10")
        ' Check if the cell has a value (i.e., should create a hyperlink only if there is text)
        If cell.Value <> "" Then
            ' Define the text that will be displayed as a hyperlink
            hyperlinkText = cell.Value           
            ' Define the address of the hyperlink (this can be any URL or file path)
            ' Here we assume the hyperlinks point to "http://www.example.com/" and the text from the cell is appended to it.
            hyperlinkAddress = "http://www.example.com/" & cell.Value           
            ' Add the hyperlink to the current cell
            ws.Hyperlinks.Add _
                Anchor:=cell, _
                Address:=hyperlinkAddress, _
                TextToDisplay:=hyperlinkText
        End If
    Next cell
End Sub

Detailed Explanation

1. Setting the Worksheet:
   • Set ws = ThisWorkbook.Sheets(« Sheet1 »): This line sets the variable ws to refer to the worksheet « Sheet1 » of the current workbook. You can modify « Sheet1 » to the actual name of the sheet where you want the hyperlinks to be created.
2. Defining the Range:
   • For Each cell In ws.Range(« A2:A10 »): This line specifies the range of cells (A2:A10) where the code will create hyperlinks. You can adjust this range to fit your needs.
3. Checking for Empty Cells:
   • If cell.Value <> «  » Then: Before creating a hyperlink, the code checks whether the cell contains any value. This ensures that no hyperlinks are created for empty cells.
4. Defining Hyperlink Text and Address:
   • hyperlinkText = cell.Value: The hyperlink text displayed in the cell is set to the value in the current cell (cell.Value).
   • hyperlinkAddress = « http://www.example.com/ » & cell.Value: This line constructs the full URL for the hyperlink by appending the cell value to a base URL. For example, if the cell contains the word « VBA », the final hyperlink address will be http://www.example.com/VBA.
5. Adding the Hyperlink:
   • ws.Hyperlinks.Add _: The Hyperlinks.Add method is used to create a hyperlink. The method’s parameters are:
     • Anchor:=cell: This specifies the cell where the hyperlink will be added.
     • Address:=hyperlinkAddress: This is the destination URL or file path of the hyperlink.
     • TextToDisplay:=hyperlinkText: This is the text that will be displayed as the hyperlink.
6. Looping Through the Range:
   • The For Each loop iterates through each cell in the specified range (A2:A10). If the cell contains a value, a hyperlink will be created in that cell.

Customization Options:

• Different Range: You can adjust the range (e.g., ws.Range(« A2:A10 »)) to fit the actual range where you want to create hyperlinks.
• Hyperlink Address: The code constructs the hyperlink address by concatenating the cell value with a base URL (« http://www.example.com/ »). You can customize this to create links to different websites, local files, or even internal Excel ranges.
• Text to Display: The hyperlink text is taken directly from the cell value. You can customize this by setting hyperlinkText to any text or value you prefer.

This VBA code is a flexible and dynamic way to create hyperlinks in Excel based on the values in a specified range of cells.