Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
When to use With - End With?
I have a large set of books and loads of macros - they all work OK, but I've
been trying to tweak some of them to make them go a little faster. I've discovered that sometimes adding the With - End With construct to a macro can make it go faster (as I expected) but othertimes it makes it go slower. What are the rules? Here is an example of a change that made things slower (part of a function)... Original: Set rng = Workbooks("data.xls").Worksheets("students").Range ("1:1") lkcol = Application.WorksheetFunction.Match(lookupkey, rng, 0) rtncol = Application.WorksheetFunction.Match(rtnkey, rng, 0) With Workbooks("data.xls").Worksheets("students") Set rng2 = .Range(.Cells(1, lkcol), .Cells(65535, lkcol)) End With rtnrow = Application.WorksheetFunction.Match(lookupval, rng2, 0) Sinfo = Workbooks("data.xls").Worksheets("students").Cells (rtnrow, rtncol) Revised, and markedly slower: With Workbooks("data.xls").Worksheets("students") Set rng = .Range("1:1") lkcol = Application.WorksheetFunction.Match(lookupkey, rng, 0) rtncol = Application.WorksheetFunction.Match(rtnkey, rng, 0) Set rng2 = .Range(.Cells(1, lkcol), .Cells(65535, lkcol)) rtnrow = Application.WorksheetFunction.Match(lookupval, rng2, 0) Sinfo = .Cells(rtnrow, rtncol) End With -- Adrian D.Bailey, Information and Systems Manager, Dept.Human Sciences Loughborough University, Loughborough Leics, LE11 3TU, UK. Tel: 01509 223007 Fax: 01509 223940 Community Warden, Storer and Burleigh Areas. Out-of-hours Tel: 01509 563263 -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
When to use With - End With?
Adrian,
Apart from the question of whether you feel it improves or degrades readability of code, With block speed code up if they reduce the levels of the hierarchy that the compiler needs to resolve. You can tell this by counting the periods (.). e.g. ThisWorkbook.Worksheets(1).Range("A1").Borders(xlE dgeTop).LineStyle = xlContinuous '4 periods With ThisWorkbook.Worksheets(1).Range("A1").Borders(xlE dgeTop) .LineStyle = xlContinuous End With 'Still 4 periods, so no gain But: ThisWorkbook.Worksheets(1).Range("A1").Borders(xlE dgeTop).LineStyle = xlContinuous ThisWorkbook.Worksheets(1).Range("A1").Borders(xlE dgeTop).Weight = xlThin ThisWorkbook.Worksheets(1).Range("A1").Borders(xlE dgeTop).ColorIndex = xlAutomatic 'Total 12 periods With ThisWorkbook.Worksheets(1).Range("A1").Borders(xlE dgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With 'Total 6 periods You can also define your own variables to help the compiler and cut down the number of levels Dim brder As Border Set brder = ThisWorkbook.Worksheets(1).Range("B2").Borders(xlE dgeTop) With brder .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With 'Although in this case you gain nothing from the With, only from the variable. Not aware of a downside to either method, so at a loss to explain your slower execution. In a related comment, avoid using the generic "Object", unless you need to, e.g. because of late binding. NickHK "Adrian D. Bailey" wrote in message ... I have a large set of books and loads of macros - they all work OK, but I've been trying to tweak some of them to make them go a little faster. I've discovered that sometimes adding the With - End With construct to a macro can make it go faster (as I expected) but othertimes it makes it go slower. What are the rules? Here is an example of a change that made things slower (part of a function)... Original: Set rng = Workbooks("data.xls").Worksheets("students").Range ("1:1") lkcol = Application.WorksheetFunction.Match(lookupkey, rng, 0) rtncol = Application.WorksheetFunction.Match(rtnkey, rng, 0) With Workbooks("data.xls").Worksheets("students") Set rng2 = .Range(.Cells(1, lkcol), .Cells(65535, lkcol)) End With rtnrow = Application.WorksheetFunction.Match(lookupval, rng2, 0) Sinfo = Workbooks("data.xls").Worksheets("students").Cells (rtnrow, rtncol) Revised, and markedly slower: With Workbooks("data.xls").Worksheets("students") Set rng = .Range("1:1") lkcol = Application.WorksheetFunction.Match(lookupkey, rng, 0) rtncol = Application.WorksheetFunction.Match(rtnkey, rng, 0) Set rng2 = .Range(.Cells(1, lkcol), .Cells(65535, lkcol)) rtnrow = Application.WorksheetFunction.Match(lookupval, rng2, 0) Sinfo = .Cells(rtnrow, rtncol) End With -- Adrian D.Bailey, Information and Systems Manager, Dept.Human Sciences Loughborough University, Loughborough Leics, LE11 3TU, UK. Tel: 01509 223007 Fax: 01509 223940 Community Warden, Storer and Burleigh Areas. Out-of-hours Tel: 01509 563263 -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
When to use With - End With?
Like Nick I don't understand why your With - End With construct slows things
down, that has never been my experience. I wonder if your observation is purely related to the worksheet functions in your code. Although in cell formulas they are very fast, when used in VBA they are relatively slow. But I would have thought when using them any difference between w/w-out the With construct would be insignificant. Sometimes it can be faster to convert cell values to an array and simulate the worksheet function in code. Another way if using several in the same function - Dim wfn as WorksheetFunction Set = Application.WorksheetFunction result = wfn.somefunction(args) Regards, Peter T "Adrian D. Bailey" wrote in message ... I have a large set of books and loads of macros - they all work OK, but I've been trying to tweak some of them to make them go a little faster. I've discovered that sometimes adding the With - End With construct to a macro can make it go faster (as I expected) but othertimes it makes it go slower. What are the rules? Here is an example of a change that made things slower (part of a function)... Original: Set rng = Workbooks("data.xls").Worksheets("students").Range ("1:1") lkcol = Application.WorksheetFunction.Match(lookupkey, rng, 0) rtncol = Application.WorksheetFunction.Match(rtnkey, rng, 0) With Workbooks("data.xls").Worksheets("students") Set rng2 = .Range(.Cells(1, lkcol), .Cells(65535, lkcol)) End With rtnrow = Application.WorksheetFunction.Match(lookupval, rng2, 0) Sinfo = Workbooks("data.xls").Worksheets("students").Cells (rtnrow, rtncol) Revised, and markedly slower: With Workbooks("data.xls").Worksheets("students") Set rng = .Range("1:1") lkcol = Application.WorksheetFunction.Match(lookupkey, rng, 0) rtncol = Application.WorksheetFunction.Match(rtnkey, rng, 0) Set rng2 = .Range(.Cells(1, lkcol), .Cells(65535, lkcol)) rtnrow = Application.WorksheetFunction.Match(lookupval, rng2, 0) Sinfo = .Cells(rtnrow, rtncol) End With -- Adrian D.Bailey, Information and Systems Manager, Dept.Human Sciences Loughborough University, Loughborough Leics, LE11 3TU, UK. Tel: 01509 223007 Fax: 01509 223940 Community Warden, Storer and Burleigh Areas. Out-of-hours Tel: 01509 563263 -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
When to use With - End With?
typo
Dim wfn as WorksheetFunction Set = Application.WorksheetFunction result = wfn.somefunction(args) Dim wfn as WorksheetFunction Set wfn = Application.WorksheetFunction result = wfn.somefunction(args) Peter T "Peter T" <peter_t@discussions wrote in message ... Like Nick I don't understand why your With - End With construct slows things down, that has never been my experience. I wonder if your observation is purely related to the worksheet functions in your code. Although in cell formulas they are very fast, when used in VBA they are relatively slow. But I would have thought when using them any difference between w/w-out the With construct would be insignificant. Sometimes it can be faster to convert cell values to an array and simulate the worksheet function in code. Another way if using several in the same function - Dim wfn as WorksheetFunction Set = Application.WorksheetFunction result = wfn.somefunction(args) Regards, Peter T "Adrian D. Bailey" wrote in message ... I have a large set of books and loads of macros - they all work OK, but I've been trying to tweak some of them to make them go a little faster. I've discovered that sometimes adding the With - End With construct to a macro can make it go faster (as I expected) but othertimes it makes it go slower. What are the rules? Here is an example of a change that made things slower (part of a function)... Original: Set rng = Workbooks("data.xls").Worksheets("students").Range ("1:1") lkcol = Application.WorksheetFunction.Match(lookupkey, rng, 0) rtncol = Application.WorksheetFunction.Match(rtnkey, rng, 0) With Workbooks("data.xls").Worksheets("students") Set rng2 = .Range(.Cells(1, lkcol), .Cells(65535, lkcol)) End With rtnrow = Application.WorksheetFunction.Match(lookupval, rng2, 0) Sinfo = Workbooks("data.xls").Worksheets("students").Cells (rtnrow, rtncol) Revised, and markedly slower: With Workbooks("data.xls").Worksheets("students") Set rng = .Range("1:1") lkcol = Application.WorksheetFunction.Match(lookupkey, rng, 0) rtncol = Application.WorksheetFunction.Match(rtnkey, rng, 0) Set rng2 = .Range(.Cells(1, lkcol), .Cells(65535, lkcol)) rtnrow = Application.WorksheetFunction.Match(lookupval, rng2, 0) Sinfo = .Cells(rtnrow, rtncol) End With -- Adrian D.Bailey, Information and Systems Manager, Dept.Human Sciences Loughborough University, Loughborough Leics, LE11 3TU, UK. Tel: 01509 223007 Fax: 01509 223940 Community Warden, Storer and Burleigh Areas. Out-of-hours Tel: 01509 563263 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|