Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this code take so long to execute.
' Delete all lines
Dim lin As Line For Each lin In ActiveSheet.Lines If lin.Name Like "Line*" Then lin.Delete End If Next The longer I have worked on the project with this code, the more sluggish this seemed to be. Each time I run the macro with this code It will create 300-500 line segments. This delete code is the 1st thing that is done to clear out all lines from the previous execution and then re-draw the lines based on current data. The code that draws the lines has much more meat to its code, (lots of loops and conversions) but it runs very fast (a matter of seconds). As I step through the code (single step), the cursor seems to hang on the lin.Delete for about 1-3 seconds, Of course watching the execution i'm sure takes significantly longer, but I have seen this macro take about 3-5 minutes to complete it's deleting of all the lines. As the lines are being created I question the autonumber that is generated, Line15023 for example, I am wondering if that might be causing some overhead. Is there a more efficient way to delete all lines? I have also added code to turn off "calculation" before the deletes and turning recalc back on afterwards, but that didnt seem to help. -- Regards, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this code take so long to execute.
Activesheet.Lines.Delete
-- Regards, Tom Ogilvy "John Keith" wrote in message ... ' Delete all lines Dim lin As Line For Each lin In ActiveSheet.Lines If lin.Name Like "Line*" Then lin.Delete End If Next The longer I have worked on the project with this code, the more sluggish this seemed to be. Each time I run the macro with this code It will create 300-500 line segments. This delete code is the 1st thing that is done to clear out all lines from the previous execution and then re-draw the lines based on current data. The code that draws the lines has much more meat to its code, (lots of loops and conversions) but it runs very fast (a matter of seconds). As I step through the code (single step), the cursor seems to hang on the lin.Delete for about 1-3 seconds, Of course watching the execution i'm sure takes significantly longer, but I have seen this macro take about 3-5 minutes to complete it's deleting of all the lines. As the lines are being created I question the autonumber that is generated, Line15023 for example, I am wondering if that might be causing some overhead. Is there a more efficient way to delete all lines? I have also added code to turn off "calculation" before the deletes and turning recalc back on afterwards, but that didnt seem to help. -- Regards, John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does this code take so long to execute.
Excelent! that worked in the blink of an eye,
Thanks Tom! "Tom Ogilvy" wrote: Activesheet.Lines.Delete -- Regards, Tom Ogilvy "John Keith" wrote in message ... ' Delete all lines Dim lin As Line For Each lin In ActiveSheet.Lines If lin.Name Like "Line*" Then lin.Delete End If Next The longer I have worked on the project with this code, the more sluggish this seemed to be. Each time I run the macro with this code It will create 300-500 line segments. This delete code is the 1st thing that is done to clear out all lines from the previous execution and then re-draw the lines based on current data. The code that draws the lines has much more meat to its code, (lots of loops and conversions) but it runs very fast (a matter of seconds). As I step through the code (single step), the cursor seems to hang on the lin.Delete for about 1-3 seconds, Of course watching the execution i'm sure takes significantly longer, but I have seen this macro take about 3-5 minutes to complete it's deleting of all the lines. As the lines are being created I question the autonumber that is generated, Line15023 for example, I am wondering if that might be causing some overhead. Is there a more efficient way to delete all lines? I have also added code to turn off "calculation" before the deletes and turning recalc back on afterwards, but that didnt seem to help. -- Regards, John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making sheets visible & hidden
Hey there,
I'm a HUGE novice with excel programming, through self-teaching I've covered quite a few simple commands but still there are loads to learn - yay. Anyhoo, I want to be able to control what sheets some of my staff @ work have access to. I can easily make the tabs hidden, but unfortunately most of my staff aren't that simple! What I have so far is a simple userform system which checks payroll numbers and passwords - and their access privilege... What I want to be able to do is say... give level 1 users access to a single "staff" sheet... Give level 2 users access to "staff" and "holiday" sheets... give level 3 users access to "staff", "holiday" and "reports" sheets... I am aware of the Visible, Hidden and VeryHidden variables for sheets - how to use them is the issue... I keep coming up with ideas like... If user_payroll.Value = Reference("PAYROLL1") Then password_form.Show Else MsgBox("Payroll not recognised."), , "Oops!" ---------------------------------------------------------------------------------------------- If user_pass.Value = Reference("PASSWORD1") Then Make Sheets("staff").Visible Else MsgBox("Password not recognised."), , "Oops!" Then on logging out/closing the spreadsheet, the sheet(s) would become VeryHidden again... Any ideas how I can make this work - like I said I'm self taught and I can't find any good learning resources for this - the books I have on excel VBA don't mention anything like this. All input _WILL_ be greatly appreciated! Also - any ideas on how I could instead create a similar system by using an excel spreadsheet as a database then taking all the login details from there would be excellent. thanks, Kai |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making sheets visible & hidden
Not sure why you are posting this in this thread, but see a possible
approach in response to your first posting. -- Regards, Tom Ogivy "Xlyr" wrote in message . uk... Hey there, I'm a HUGE novice with excel programming, through self-teaching I've covered quite a few simple commands but still there are loads to learn - yay. Anyhoo, I want to be able to control what sheets some of my staff @ work have access to. I can easily make the tabs hidden, but unfortunately most of my staff aren't that simple! What I have so far is a simple userform system which checks payroll numbers and passwords - and their access privilege... What I want to be able to do is say... give level 1 users access to a single "staff" sheet... Give level 2 users access to "staff" and "holiday" sheets... give level 3 users access to "staff", "holiday" and "reports" sheets... I am aware of the Visible, Hidden and VeryHidden variables for sheets - how to use them is the issue... I keep coming up with ideas like... If user_payroll.Value = Reference("PAYROLL1") Then password_form.Show Else MsgBox("Payroll not recognised."), , "Oops!" -------------------------------------------------------------------------- -------------------- If user_pass.Value = Reference("PASSWORD1") Then Make Sheets("staff").Visible Else MsgBox("Password not recognised."), , "Oops!" Then on logging out/closing the spreadsheet, the sheet(s) would become VeryHidden again... Any ideas how I can make this work - like I said I'm self taught and I can't find any good learning resources for this - the books I have on excel VBA don't mention anything like this. All input _WILL_ be greatly appreciated! Also - any ideas on how I could instead create a similar system by using an excel spreadsheet as a database then taking all the login details from there would be excellent. thanks, Kai |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to execute the fill function down a long column | Excel Discussion (Misc queries) | |||
Execute vba code on cell exit | Excel Discussion (Misc queries) | |||
my excel formulas are too long to execute find and replace | Excel Worksheet Functions | |||
When does Code Execute on a List Box? | Excel Programming | |||
execute code for all 12 months | Excel Programming |