Line Draw VERY Slow (lots of lines)

[MikeZz]

Hi,
I have an application that builds a grid map and draws lots of lines between
cells.
The number of lines is currently over 600 but could grow more and each line
contains a hyperlink and screentip text.

The app works reasonably fast for the first 3-400 lines but exponentially
slows to a crawl. By the end it could be a line per second or slower.

I've seen recommendations not to use the "selection." command but I can't
figure out how to use it for my application.

Below is the code that works.
If I try to use LineShape.Hyperlinks.... I get an error.
I've also noticed that excel shows each line plotting even though I have
sreenupdating off.... am I missing something on that?

Thanks, MikeZz

Set LineShape = ActiveSheet.Shapes.AddLine(x1, y1, x2, y2)
LineShape.Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(lineName), _
Address:= "", SubAddress:=linkAdd, ScreenTip:=screenTipText

 

[Dougaj4]

I think that's just the way it is. There seems to be something in the
Excel drawing routines that causes this exponential slowdown. I'm
hoping it will be fixed in 2010, but I haven't seen anything
definitive to say it will be. You might like to have a look at this
post from my blog, which deals with complex drawings in VBA, but I'm
afraid it doesn't offer a solution to the slow speed problem:

http://newtonexcelbach.wordpress.com/2009/01/13/drawing-in-excel-9-perspective-projection/


Doug

 

[Peter T]

No need to select, try this to make 2000 lines, each with a hyperlink.

Sub test()
Dim i As Long
Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
Dim linkAdd As String, screenTipText As String
Dim LineShape As Shape
Dim ws As Worksheet

Set ws = ActiveSheet
ws.Lines.Delete ' << ONLY FOR TESTING

For i = 2 To 2001
With Cells(i, 3)
y1 = Cells(i, 1).Top + .Height / 2
y2 = y1
x1 = .Left + .Width / 2
x2 = x1 + .Width * 3
End With

Set LineShape = ActiveSheet.Shapes.AddLine(x1, y1, x2, y2)

linkAdd = Cells(i, 1).Address(0, 0)
screenTipText = "goto cell " & linkAdd

ActiveSheet.Hyperlinks.Add _
Anchor:=LineShape, _
Address:="", _
SubAddress:=linkAdd, _
ScreenTip:=screenTipText

Next

End Sub

Regards,
Peter T