65532-65536:Hyperlinks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all –

Excel 2003/WinXP Pro SP2. I’m trying to convert a column of cell address
strings into hyperlinks. The column has a header row, but is otherwise
completely full with 65535 cell address strings down to row 65536. The code
below loops through all but the last 5 rows of the column. It will not
convert the address strings in rows 65532-65536 to hyperlinks (it stalls with
a runtime error ‘1004’ when trying to convert the cell in row 65532).

I’m curious to know if anyone can repeat this behavior or if it is isolated
to my system.

---
Thanks in advance for taking a look,
Jay

Note1: When the loop index is altered to arbitrarily operate on the last 50
or so rows of the worksheet, all 50 cell addresses convert properly to
hyperlinks with no runtime error. So, it’s not the row number that precludes
conversion.

Note 2: Also, the i=1 to 65535 loop stalls in the same place (at i=65531)
regardless of the number of other programs I have open or the number of
sheets in the workbook. It doesn’t appear to be related to gross memory
resources because I’ve run the procedure with a lot of programs open and
after a reboot with just a single-sheet workbook open with equivalent error
results.
---------------------------
A. The Code
Sub hyptest()
'Convert cell addresses to hyperlinks
idim = 65535
With Worksheets(1)
If idim > 0 Then
For i = 1 To idim
lnk_address = ""
lnk_SubAddress = "'" & .Cells(i + 1, 2).Text & "'!" & _
.Cells(i + 1, 4).Text
lnk_display = .Cells(i + 1, 4).Text
ActiveSheet.Hyperlinks.Add Anchor:=.Cells(i + 1, 4), _
Address:=lnk_address, _
SubAddress:=lnk_SubAddress, TextToDisplay:=lnk_display
Next i
End If
End With
End Sub

B. To recreate the workbook:
1. Make a header row in Worksheet(1) of a blank workbook with some header
labels:
A1: “H1†B1: “H2†C1: “H3†D1: “H4â€

2. In Column A, fill rows 2-65536 with consecutive integers, 1-65535.

3. Fill Column B with the arbitrary string “DGSheet†(same entry for all
65535 cells).

4. Leave Column C blank (except for the header label).

5. Fill Column D with an arbitrary cell address using the following formula
in D2:
=Address(A2,7)
Copy/Paste this formula downward to the last row of the worksheet. Convert
the entire column to values with Copy/PasteSpecial Values.

6. Run Sub hyptest() and see what happens.
 
Jay,
I think it may be a case of adding the straw that finally collapsed the cart.
You need to think very seriously of replacing the hyperlink object with
the hyperlink formula.
See Chip Pearson's comments here... http://tinyurl.com/386ufm
(about the middle of the page)

Replacing them made a difference in my List Files Excel add-in.
(it is a free download from the link below)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Jay"
<[email protected]>
wrote in message
Hi all –
Excel 2003/WinXP Pro SP2. I’m trying to convert a column of cell address
strings into hyperlinks. The column has a header row, but is otherwise
completely full with 65535 cell address strings down to row 65536. The code
below loops through all but the last 5 rows of the column. It will not
convert the address strings in rows 65532-65536 to hyperlinks (it stalls with
a runtime error ‘1004’ when trying to convert the cell in row 65532).

I’m curious to know if anyone can repeat this behavior or if it is isolated
to my system.
---
Thanks in advance for taking a look,
Jay

Note1: When the loop index is altered to arbitrarily operate on the last 50
or so rows of the worksheet, all 50 cell addresses convert properly to
hyperlinks with no runtime error. So, it’s not the row number that precludes
conversion.

Note 2: Also, the i=1 to 65535 loop stalls in the same place (at i=65531)
regardless of the number of other programs I have open or the number of
sheets in the workbook. It doesn’t appear to be related to gross memory
resources because I’ve run the procedure with a lot of programs open and
after a reboot with just a single-sheet workbook open with equivalent error
results.

-SNIP-
 
Hi Jim -

Thank you for the information, suggestion, and the lead. The application I
was building did work fine during initial construction with a moderate number
of hyperlink objects. Once I tested it with a high volume of hyperlinks, one
workbook became irreversibly corrupted and others showed the unpredictable
extended load time symptom. As you and Chip Pearson warn, users should be
aware of this.

I'm replacing the hyperlink objects (added by the code in my first post)
with the hyperlink function. At the moment, the hyperlink function sounds
like more of a 'best practice' than a work-around when a high volume of
hyperlinks are needed. I'll post the results here after I make the change.
 
Hi Jay,

Just for the hang of it, I ran your test and it stopped at exactly the same
place. I am running Excel 2002. Obviously there is some limitation.

Regards,

OssieMac
 
Hi OssieMac -

Thank you for the test results. Interesting. I guess there is something
magical about the number 65530 in this case; it's like the 'end of the
rainbow' for some reason.

In additional testing, I experienced the excessive load time again and was
barely able to recover some minor changes I made to the VB code in the
workbook. I had to reboot and navigate directly to the VB Editor and export
the code (with no unnecessary keystrokes). Otherwise, the application would
hang and a reboot was required. The take-home message stands; favor the
hyperlink function instead of the hyperlink object when adding large numbers
of hyperlinks in a workbook.

I refashioned the code as per the recommendations posted by Jim (citing his
work and Chip Pearson's warning). Here is the new procedure using the
hyperlink function and it has worked properly in all my testing so far. It
should work on the workbook you built to test the dysfunctional procedure.

Sub lnk_by_function()
'Convert a list of cell addresses to hyperlinks using the hyperlink function
'The list has a single header row (hence the 'i+1' offset in the formulas)
'Cells in column 2 contain sheet names
'Cells in column 4 contain a cell address as a string, e.g., "$G$22"
(without quotes)
idim = 65535
With Worksheets(1)
For i = 1 To idim
lnk_address = "[" & ThisWorkbook.Name & "]" & "'" & _
.Cells(i + 1, 2).Text & "'!" & .Cells(i + 1, 4)
lnk_display = Chr(34) & .Cells(i + 1, 4).Text & Chr(34)
.Cells(i + 1, 4).Formula = _
"=Hyperlink(""" & lnk_address & """, " & lnk_display & ")"
Next i
End With
End Sub
 
Besides the other comments, why not just use a worksheet_doubleclick event
such as
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub
 
Back
Top