Reputation: 175
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?
Upvotes: 4
Views: 1992
Reputation: 53663
Your first attempt has a few problems.
FormulaR1C1
does not appear to be qualified, so it's either a typo or an empty variantxElement = FormulaR1C1 = ...
assigns a boolean (the result of the second comparison: FormulaR1C1 = ...
to xElement
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
Upvotes: 6