Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Draw VERY Slow (lots of lines)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Draw VERY Slow (lots of lines)
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...ve-projection/ Doug On Nov 5, 2:16*pm, MikeZz wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Draw VERY Slow (lots of lines)
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 "MikeZz" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lots of REM lines an easy way? | Excel Programming | |||
VBA to draw lines of SPC | Charts and Charting in Excel | |||
Can I line up lots of controls on a sheet at once? | Excel Programming | |||
How do I draw lines..?? | Excel Programming | |||
Slow Draw with Excel VBA 2000 | Excel Programming |