How to create an array of hyperlinks (Excel VBA)?

Question

Is it possible to create a list of hyperlinks in an array, so that the array elements are all hyperlinks?

I am working on a more efficient macro to create a hyperlinked index. I have all of the worksheet names in an array, but would like to convert the array elements to hyperlinks before printing it on my Index tab.

So far I have tried the following (this is just the portion of code where I've tried to turn array elements into hyperlinks)...

Attempt 1:

For Each xElement In xArr
    xElement = FormulaR1C1 = "=Hyperlink(""xWB.Sheets(xElement)!A1"", xWB.Sheets(xElement).Name)"
Next xElement

Result for Attempt 1: The macro runs without error, but the resulting list is not hyperlinked. My Index is the same as if I left out this code all together.

Attempt 2:

For Each xElement In xArr
    xElement = .Hyperlinks.Add _
        anchor:="", _     'Compile error: Expected: end of statement
        Address:="", _
        SubAddress:="'" & xWB.Sheets(xelement).Name & "'!A1", _
        TextToDisplay:=xWB.Sheets(xelement).Name
Next xElement

Result for Attempt 2: "anchor" is highlighted as causing an error. The error message is "Compile error: Expected: end of statement"

Is it possible to do what I'm attempting?

Answer 1

Your first attempt has a few problems.

1. FormulaR1C1 does not appear to be qualified, so it's either a typo or an empty variant
2. xElement = FormulaR1C1 = ... assigns a boolean (the result of the second comparison: FormulaR1C1 = ... to xElement
3. And finally, to modify an array in-place, you can't For Each, you have to use indexed assignment, otherwise the xElement is changed without modifying the xArray!

 

Dim a as Long
For a = LBound(xArray) to UBound(xArray)`
    xArray(a) = "something..."
Next

Your second attempt fails because the Anchor argument cannot be an empty string. Per the dox, it must be a shape or a range object.

Is it possible to do what I'm attempting?

Strictly, no. You can't "convert the array elements to hyperlinks before printing it on my Index tab" because there is no constructor for the individual Hyperlink which is exposed to you (i.e., you can't simply create or instantiate a Hyperlink on its own, it can only be created via the Hyperlinks.Add method, which requires the Anchor argument).

So the solution is to simply create them using the .Hyperlinks.Add method during your array iteration, e.g.:

Dim wsIndex as Worksheet
Set wsIndex = Worksheets("Index")
Dim x as Long
With wsIndex
    For x = LBound(xArr) To UBound(xArr)
        .Hyperlinks.Add _
            wsIndex.Cells(x + 1, 1), _
            xArr(x).Name & "!A1", _
            TextToDisplay:=xArr(x).Name
    Next
End With

Update

You can store hyperlinks in an array or collection, but not until they've been created first. You could do something like:

ReDim links(LBound(xArr) to UBound(xArr))
Dim h as Hyperlink
Dim wsIndex as Worksheet
Set wsIndex = Worksheets("Index")
Dim x as Long
With wsIndex
    For x = LBound(xArr) To UBound(xArr)
        Set h = .Hyperlinks.Add _
            wsIndex.Cells(x + 1, 1), _
            xArr(x).Name & "!A1", _
            TextToDisplay:=xArr(x).Name
        ' Store the link in an array for later use, if needed
        Set links(x) = h
    Next
End With