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.

--
Thanks again,
Jay

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"
<(E-Mail Removed)>
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-
--
Thanks again,
Jay

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.
--
Thanks again,
Jay


"Jim Cone" wrote:

> 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"
> <(E-Mail Removed)>
> 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-
> --
> Thanks again,
> Jay
>
>

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

"Jay" wrote:

> 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.
>
> --
> Thanks again,
> Jay

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

--
Thanks again for the test and feedback,
Jay


"OssieMac" wrote:

> 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
>
> "Jay" wrote:
>
> > 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.
> >
> > --
> > Thanks again,
> > Jay

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

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Jay" <(E-Mail Removed)> wrote in message
news:427B2FC7-BBFE-4846-8642-(E-Mail Removed)...
> 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.
>
> --
> Thanks again,
> Jay