Hi all €“

Excel 2003/WinXP Pro SP2. Im 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).

Im 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, its 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 doesnt appear to be related to gross memory
resources because Ive 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 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. Im 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).

Im 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, its 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 doesnt appear to be related to gross memory
resources because Ive 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. Im 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).

Im 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, its 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 doesnt appear to be related to gross memory
resources because Ive 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

"Jay" 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.
---------------------------
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