|
Basic VBA questions....
1. Do you have to "select" a worksheet or cell (or range of cells) before you
can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! |
Basic VBA questions....
other more knowledgeable will probably respond, but to answer all 3 questions,
no. in fact, it's preferable not to,. make sure you turn off screen updating and set calculation to manual while your code executes and then set calculation back to automatic when it's finished. -- Gary "Eric @ BP-EVV" wrote in message ... 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! |
Basic VBA questions....
Hi
No to all three. If tou use the macro recorder you typically get Something.Select Selection.DoSomething You can always cut out the middle bit Something.DoSomething Have a look at your code and see how many of these selections you can remove. regards Paul On Sep 16, 3:21*pm, Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. *I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. *What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! |
Basic VBA questions....
Gary - thanks for the reply.... I have already got screen updating turned
off, as well as having set calculation to manual. I'm sure I can elminate some code by not selecting cells first, which I would think can only help the speed of this routine. Does anyone else have other suggestions for improving speed or efficiency ? Thanks ! "Gary Keramidas" wrote: other more knowledgeable will probably respond, but to answer all 3 questions, no. in fact, it's preferable not to,. make sure you turn off screen updating and set calculation to manual while your code executes and then set calculation back to automatic when it's finished. -- Gary "Eric @ BP-EVV" wrote in message ... 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! |
Basic VBA questions....
#1. Nope.
dim RngToCopy as range dim DestCell as range set rngtocopy = worksheets("Sheet999").range("x1:z99") set destcell = worksheets("sheet888").range("u71") rngtocopy.copy _ destination:=destcell #2. Nope. worksheets("sheet999").range("z99").value = "what you want here" #3. Nope. worksheets("sheet999").rows(17).delete ========== I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! -- Dave Peterson |
Basic VBA questions....
#2. Nope.
worksheets("sheet999").range("z99").formula = "=sum(a:a)" (using a formula) Dave Peterson wrote: #1. Nope. dim RngToCopy as range dim DestCell as range set rngtocopy = worksheets("Sheet999").range("x1:z99") set destcell = worksheets("sheet888").range("u71") rngtocopy.copy _ destination:=destcell #2. Nope. worksheets("sheet999").range("z99").value = "what you want here" #3. Nope. worksheets("sheet999").rows(17).delete ========== I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! -- Dave Peterson -- Dave Peterson |
Basic VBA questions....
Based on your reply, Dave, along with those of Paul and Don, it's
unanimous....I'm an amateur at this VBA coding stuff ! :-) None the less, this forum has always been of great help to me and I am grateful for the responses I get to my questions. I will implement the suggestions from y'all and see how my routine's performance changes. One more question: what does the "Option Explicit" do / mean ? Thanks again ! "Dave Peterson" wrote: #1. Nope. dim RngToCopy as range dim DestCell as range set rngtocopy = worksheets("Sheet999").range("x1:z99") set destcell = worksheets("sheet888").range("u71") rngtocopy.copy _ destination:=destcell #2. Nope. worksheets("sheet999").range("z99").value = "what you want here" #3. Nope. worksheets("sheet999").rows(17).delete ========== I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! -- Dave Peterson |
Basic VBA questions....
Option Explicit means that all of your variables have to be declared. If
they are not, the first one you try to use that is not declared will generate an error. It also helps you to catch typos because VBA will see the typo as a new undeclared variable and send you a message. For short procedures it is not that important, but it can save you a lot of grief in long and complex procedures. "Eric @ BP-EVV" wrote: Based on your reply, Dave, along with those of Paul and Don, it's unanimous....I'm an amateur at this VBA coding stuff ! :-) None the less, this forum has always been of great help to me and I am grateful for the responses I get to my questions. I will implement the suggestions from y'all and see how my routine's performance changes. One more question: what does the "Option Explicit" do / mean ? Thanks again ! "Dave Peterson" wrote: #1. Nope. dim RngToCopy as range dim DestCell as range set rngtocopy = worksheets("Sheet999").range("x1:z99") set destcell = worksheets("sheet888").range("u71") rngtocopy.copy _ destination:=destcell #2. Nope. worksheets("sheet999").range("z99").value = "what you want here" #3. Nope. worksheets("sheet999").rows(17).delete ========== I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! -- Dave Peterson |
Basic VBA questions....
Thanks for that information....it sounds like I need to be using "Option
Explicit" for future coding projects. "JLGWhiz" wrote: Option Explicit means that all of your variables have to be declared. If they are not, the first one you try to use that is not declared will generate an error. It also helps you to catch typos because VBA will see the typo as a new undeclared variable and send you a message. For short procedures it is not that important, but it can save you a lot of grief in long and complex procedures. "Eric @ BP-EVV" wrote: Based on your reply, Dave, along with those of Paul and Don, it's unanimous....I'm an amateur at this VBA coding stuff ! :-) None the less, this forum has always been of great help to me and I am grateful for the responses I get to my questions. I will implement the suggestions from y'all and see how my routine's performance changes. One more question: what does the "Option Explicit" do / mean ? Thanks again ! "Dave Peterson" wrote: #1. Nope. dim RngToCopy as range dim DestCell as range set rngtocopy = worksheets("Sheet999").range("x1:z99") set destcell = worksheets("sheet888").range("u71") rngtocopy.copy _ destination:=destcell #2. Nope. worksheets("sheet999").range("z99").value = "what you want here" #3. Nope. worksheets("sheet999").rows(17).delete ========== I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! -- Dave Peterson |
Basic VBA questions....
I think it's very useful for short code, too.
As well as making typos easier to find, if you declare your variables correctly: Dim wks as worksheet not dim wks as variant 'or as object Then you'll get VBAs helpful intellisense--that's the popup that you see after typing the dot in: wks. You'll see a list of all the properties and methods that you could use. JLGWhiz wrote: Option Explicit means that all of your variables have to be declared. If they are not, the first one you try to use that is not declared will generate an error. It also helps you to catch typos because VBA will see the typo as a new undeclared variable and send you a message. For short procedures it is not that important, but it can save you a lot of grief in long and complex procedures. "Eric @ BP-EVV" wrote: Based on your reply, Dave, along with those of Paul and Don, it's unanimous....I'm an amateur at this VBA coding stuff ! :-) None the less, this forum has always been of great help to me and I am grateful for the responses I get to my questions. I will implement the suggestions from y'all and see how my routine's performance changes. One more question: what does the "Option Explicit" do / mean ? Thanks again ! "Dave Peterson" wrote: #1. Nope. dim RngToCopy as range dim DestCell as range set rngtocopy = worksheets("Sheet999").range("x1:z99") set destcell = worksheets("sheet888").range("u71") rngtocopy.copy _ destination:=destcell #2. Nope. worksheets("sheet999").range("z99").value = "what you want here" #3. Nope. worksheets("sheet999").rows(17).delete ========== I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! -- Dave Peterson -- Dave Peterson |
Basic VBA questions....
You can turn it on automatically for all your code windows by clicking
Tools/Options on the VB editor's menu bar and then putting a check mark in the Code Setting checkbox labeled "Require Variable Declaration". Note, this only affects new (empty) code windows that you call up... if you have existing code windows with code already in them, you have to add the Option Explicit manually to them. -- Rick (MVP - Excel) "Eric @ BP-EVV" wrote in message ... Thanks for that information....it sounds like I need to be using "Option Explicit" for future coding projects. "JLGWhiz" wrote: Option Explicit means that all of your variables have to be declared. If they are not, the first one you try to use that is not declared will generate an error. It also helps you to catch typos because VBA will see the typo as a new undeclared variable and send you a message. For short procedures it is not that important, but it can save you a lot of grief in long and complex procedures. "Eric @ BP-EVV" wrote: Based on your reply, Dave, along with those of Paul and Don, it's unanimous....I'm an amateur at this VBA coding stuff ! :-) None the less, this forum has always been of great help to me and I am grateful for the responses I get to my questions. I will implement the suggestions from y'all and see how my routine's performance changes. One more question: what does the "Option Explicit" do / mean ? Thanks again ! "Dave Peterson" wrote: #1. Nope. dim RngToCopy as range dim DestCell as range set rngtocopy = worksheets("Sheet999").range("x1:z99") set destcell = worksheets("sheet888").range("u71") rngtocopy.copy _ destination:=destcell #2. Nope. worksheets("sheet999").range("z99").value = "what you want here" #3. Nope. worksheets("sheet999").rows(17).delete ========== I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! -- Dave Peterson |
Basic VBA questions....
Just for the record, I have added "Option Explicit" and the compiler found 3
variables I had not defined...I fixed that...I commented out all the unnecessary select statements, and revamped my delete, copy and paste type statements. I timed the performance with a stop watch before and after making these changes, with the same "other applications" open on my computer and the performance did improve a little....went from 9 minutes 40 seconds down to 9 minutes 20 seconds, which equates to almost a 3.5 % increase in speed....I was hoping for more, so now I am going to study hard on the use of calculation statements in the routine. I have it turned to manual at the beginning...I need to make sure I am only forcing the recalculation when absolutely necessary. "Dave Peterson" wrote: I think it's very useful for short code, too. As well as making typos easier to find, if you declare your variables correctly: Dim wks as worksheet not dim wks as variant 'or as object Then you'll get VBAs helpful intellisense--that's the popup that you see after typing the dot in: wks. You'll see a list of all the properties and methods that you could use. JLGWhiz wrote: Option Explicit means that all of your variables have to be declared. If they are not, the first one you try to use that is not declared will generate an error. It also helps you to catch typos because VBA will see the typo as a new undeclared variable and send you a message. For short procedures it is not that important, but it can save you a lot of grief in long and complex procedures. "Eric @ BP-EVV" wrote: Based on your reply, Dave, along with those of Paul and Don, it's unanimous....I'm an amateur at this VBA coding stuff ! :-) None the less, this forum has always been of great help to me and I am grateful for the responses I get to my questions. I will implement the suggestions from y'all and see how my routine's performance changes. One more question: what does the "Option Explicit" do / mean ? Thanks again ! "Dave Peterson" wrote: #1. Nope. dim RngToCopy as range dim DestCell as range set rngtocopy = worksheets("Sheet999").range("x1:z99") set destcell = worksheets("sheet888").range("u71") rngtocopy.copy _ destination:=destcell #2. Nope. worksheets("sheet999").range("z99").value = "what you want here" #3. Nope. worksheets("sheet999").rows(17).delete ========== I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! -- Dave Peterson -- Dave Peterson |
Basic VBA questions....
One of the best things that comes out of your clean up isn't the (slightly)
improved execution speed. I think that you'll find the code easier to understand and easier to update. Those worksheets().select and range().selects can get really confusing really fast. And some things that may improve speed: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Eric @ BP-EVV wrote: Just for the record, I have added "Option Explicit" and the compiler found 3 variables I had not defined...I fixed that...I commented out all the unnecessary select statements, and revamped my delete, copy and paste type statements. I timed the performance with a stop watch before and after making these changes, with the same "other applications" open on my computer and the performance did improve a little....went from 9 minutes 40 seconds down to 9 minutes 20 seconds, which equates to almost a 3.5 % increase in speed....I was hoping for more, so now I am going to study hard on the use of calculation statements in the routine. I have it turned to manual at the beginning...I need to make sure I am only forcing the recalculation when absolutely necessary. "Dave Peterson" wrote: I think it's very useful for short code, too. As well as making typos easier to find, if you declare your variables correctly: Dim wks as worksheet not dim wks as variant 'or as object Then you'll get VBAs helpful intellisense--that's the popup that you see after typing the dot in: wks. You'll see a list of all the properties and methods that you could use. JLGWhiz wrote: Option Explicit means that all of your variables have to be declared. If they are not, the first one you try to use that is not declared will generate an error. It also helps you to catch typos because VBA will see the typo as a new undeclared variable and send you a message. For short procedures it is not that important, but it can save you a lot of grief in long and complex procedures. "Eric @ BP-EVV" wrote: Based on your reply, Dave, along with those of Paul and Don, it's unanimous....I'm an amateur at this VBA coding stuff ! :-) None the less, this forum has always been of great help to me and I am grateful for the responses I get to my questions. I will implement the suggestions from y'all and see how my routine's performance changes. One more question: what does the "Option Explicit" do / mean ? Thanks again ! "Dave Peterson" wrote: #1. Nope. dim RngToCopy as range dim DestCell as range set rngtocopy = worksheets("Sheet999").range("x1:z99") set destcell = worksheets("sheet888").range("u71") rngtocopy.copy _ destination:=destcell #2. Nope. worksheets("sheet999").range("z99").value = "what you want here" #3. Nope. worksheets("sheet999").rows(17).delete ========== I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Basic VBA questions....
|
Basic VBA questions....
Don,
Thanks for the offer to review my workbook/code...I appreciate it...and I may take you up on in shortly. I'm down to the last three days on my current contract, and I would hate to ask for your help with such a short deadline, and since the application I have developed works, just slower than I would like, I'm willing to leave it as is as I depart, and should you be able to help with improvements after the fact, I'll email the company and give them the benefits of the improvements....heck...that may land me another contract with them ! I will email you the workbook from my personal email....it will be coming from Thanks again ! Eric "Don Guillett" wrote: There could be other reasons. If desired, you may send your workbook to my address below and I'll take a look. Please make sure I know who you are and what you are talking about. -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric @ BP-EVV" wrote in message ... Just for the record, I have added "Option Explicit" and the compiler found 3 variables I had not defined...I fixed that...I commented out all the unnecessary select statements, and revamped my delete, copy and paste type statements. I timed the performance with a stop watch before and after making these changes, with the same "other applications" open on my computer and the performance did improve a little....went from 9 minutes 40 seconds down to 9 minutes 20 seconds, which equates to almost a 3.5 % increase in speed....I was hoping for more, so now I am going to study hard on the use of calculation statements in the routine. I have it turned to manual at the beginning...I need to make sure I am only forcing the recalculation when absolutely necessary. "Dave Peterson" wrote: I think it's very useful for short code, too. As well as making typos easier to find, if you declare your variables correctly: Dim wks as worksheet not dim wks as variant 'or as object Then you'll get VBAs helpful intellisense--that's the popup that you see after typing the dot in: wks. You'll see a list of all the properties and methods that you could use. JLGWhiz wrote: Option Explicit means that all of your variables have to be declared. If they are not, the first one you try to use that is not declared will generate an error. It also helps you to catch typos because VBA will see the typo as a new undeclared variable and send you a message. For short procedures it is not that important, but it can save you a lot of grief in long and complex procedures. "Eric @ BP-EVV" wrote: Based on your reply, Dave, along with those of Paul and Don, it's unanimous....I'm an amateur at this VBA coding stuff ! :-) None the less, this forum has always been of great help to me and I am grateful for the responses I get to my questions. I will implement the suggestions from y'all and see how my routine's performance changes. One more question: what does the "Option Explicit" do / mean ? Thanks again ! "Dave Peterson" wrote: #1. Nope. dim RngToCopy as range dim DestCell as range set rngtocopy = worksheets("Sheet999").range("x1:z99") set destcell = worksheets("sheet888").range("u71") rngtocopy.copy _ destination:=destcell #2. Nope. worksheets("sheet999").range("z99").value = "what you want here" #3. Nope. worksheets("sheet999").rows(17).delete ========== I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! -- Dave Peterson -- Dave Peterson |
Basic VBA questions....
Just an FYI for anyone interested....I put in several breakpoints to my code
and used a stopwatch to time things....the sections of code that are slowing down this process are the sections where Excel is calculating. I'm working on trying to modify this application so that all the work is done on the AS/400 and only launch the execution and have the reporting in Excel for the users. The AS/400 queries took about 2 minutes in total for 6 different queries, while Excel took over 7 minutes to do the rest of the work when 17 facilitles were selected simultaneously....about 120,000 records in Excel with between 8 and 10 columns being calculated for each record in order to complete the analysis. "Eric @ BP-EVV" wrote: Don, Thanks for the offer to review my workbook/code...I appreciate it...and I may take you up on in shortly. I'm down to the last three days on my current contract, and I would hate to ask for your help with such a short deadline, and since the application I have developed works, just slower than I would like, I'm willing to leave it as is as I depart, and should you be able to help with improvements after the fact, I'll email the company and give them the benefits of the improvements....heck...that may land me another contract with them ! I will email you the workbook from my personal email....it will be coming from Thanks again ! Eric "Don Guillett" wrote: There could be other reasons. If desired, you may send your workbook to my address below and I'll take a look. Please make sure I know who you are and what you are talking about. -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric @ BP-EVV" wrote in message ... Just for the record, I have added "Option Explicit" and the compiler found 3 variables I had not defined...I fixed that...I commented out all the unnecessary select statements, and revamped my delete, copy and paste type statements. I timed the performance with a stop watch before and after making these changes, with the same "other applications" open on my computer and the performance did improve a little....went from 9 minutes 40 seconds down to 9 minutes 20 seconds, which equates to almost a 3.5 % increase in speed....I was hoping for more, so now I am going to study hard on the use of calculation statements in the routine. I have it turned to manual at the beginning...I need to make sure I am only forcing the recalculation when absolutely necessary. "Dave Peterson" wrote: I think it's very useful for short code, too. As well as making typos easier to find, if you declare your variables correctly: Dim wks as worksheet not dim wks as variant 'or as object Then you'll get VBAs helpful intellisense--that's the popup that you see after typing the dot in: wks. You'll see a list of all the properties and methods that you could use. JLGWhiz wrote: Option Explicit means that all of your variables have to be declared. If they are not, the first one you try to use that is not declared will generate an error. It also helps you to catch typos because VBA will see the typo as a new undeclared variable and send you a message. For short procedures it is not that important, but it can save you a lot of grief in long and complex procedures. "Eric @ BP-EVV" wrote: Based on your reply, Dave, along with those of Paul and Don, it's unanimous....I'm an amateur at this VBA coding stuff ! :-) None the less, this forum has always been of great help to me and I am grateful for the responses I get to my questions. I will implement the suggestions from y'all and see how my routine's performance changes. One more question: what does the "Option Explicit" do / mean ? Thanks again ! "Dave Peterson" wrote: #1. Nope. dim RngToCopy as range dim DestCell as range set rngtocopy = worksheets("Sheet999").range("x1:z99") set destcell = worksheets("sheet888").range("u71") rngtocopy.copy _ destination:=destcell #2. Nope. worksheets("sheet999").range("z99").value = "what you want here" #3. Nope. worksheets("sheet999").rows(17).delete ========== I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. Eric @ BP-EVV wrote: 1. Do you have to "select" a worksheet or cell (or range of cells) before you can copy a cell (or range of cells) from that worksheet ? 2. Do you have to "select" a worksheet or cell before you can assign a formula to a cell in that worksheet ? 3. Do you have to "select" a row in a worksheet before you delete that row ? These may seem like very basic VBA questions....and in reality they are....what I am trying to get at is efficiency. I have some code in a workbook that pulls data from a SQL table on an AS/400 system and them performs several calcuations, sorts, etc. on that data. When I run this routine for all 17 locations at one time, it takes nearly 10 minutes to run to completion. What I'm really after here is suggestions on how to make my code more efficient....what can I do to speed up this process ? Thanks ! -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 10:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com