Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clarification need, not just by me
Select is a great method. In VBA it is over-used as experts continually say
"you don't need to use the select statement in accomplishing your task". Also, using Select in VBA can cause an error when one tries a statement (with sheet1 active) like Worksheets("Sheet2").Select. This error is obvious when you stop and think about it as even while in the spreadsheet interface say sheet1 -- one cannot select cell A1 on sheet2, without first activating sheet2, then cell A1. Worksheets("Sheet2").Activate is the same, well nearly€¦ In VBA - with sheet1 active I sometimes activate sheet2 before issuing a statement against a range on sheet2. There are times when the Worksheets ("Sheet2").activate is unnecessary. I just don't know the rules underlying it. So even after years of excel programming these "don't" are not still obvious to me. If these such no-no's or AVOID DOING THESE THINGS were documented it would be a "good-read" for all excel newcomers/want-a-be's. The Excel boioks I have (around 12 or so) don't cover this sufficiently. Can some of you begin assisting me in creating this list of clarification thanks in advance... Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clarification need, not just by me
I looks like you were refering to one of my postings. the are a number of
issues with the SELECT method 1) When debugging large programs with lots of loop trying to debug the code it is often hard to figure out what item is actually selected 2) Adding worksheets and workbooks (also opening workbooks) automatically switches to the new sheet/book. So relying on the select method you have to be aware of the behavior of VBA which isn't well documented. The is why I set objects to each sheet/workbook in my code Set bk = workbooks.open(filename:=abc.xls") Set sht = bk.sheets("Sheet1") with sht end with 3) Selecting a sheet slows down the code because excel has to refresh the worksheet before continuing. Select cells also slows down the code becaue excel has to update the screen. And even if you turn off refreshing excel need to keep track of the active cell so when the program ends it will put the active cell where the macro put the selection. Not the last place you were before the macro ran 4) if you want the selected cell not to change when you run a macro then don't use select. It is a nuisance is ever tim e you run a macro you have to go back to the area of the worksheet where you were working. If you had a worksheet change macro and you changed cell A3 do you want the macro to leave you at cell IV2000. "JMay" wrote: Select is a great method. In VBA it is over-used as experts continually say "you don't need to use the select statement in accomplishing your task". Also, using Select in VBA can cause an error when one tries a statement (with sheet1 active) like Worksheets("Sheet2").Select. This error is obvious when you stop and think about it as even while in the spreadsheet interface say sheet1 -- one cannot select cell A1 on sheet2, without first activating sheet2, then cell A1. Worksheets("Sheet2").Activate is the same, well nearly€¦ In VBA - with sheet1 active I sometimes activate sheet2 before issuing a statement against a range on sheet2. There are times when the Worksheets ("Sheet2").activate is unnecessary. I just don't know the rules underlying it. So even after years of excel programming these "don't" are not still obvious to me. If these such no-no's or AVOID DOING THESE THINGS were documented it would be a "good-read" for all excel newcomers/want-a-be's. The Excel boioks I have (around 12 or so) don't cover this sufficiently. Can some of you begin assisting me in creating this list of clarification thanks in advance... Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clarification need, not just by me
"Selecting a sheet slows down the code because excel has to refresh the
worksheet before continuing." << Is this the same for Activating a sheet? "Joel" wrote: I looks like you were refering to one of my postings. the are a number of issues with the SELECT method 1) When debugging large programs with lots of loop trying to debug the code it is often hard to figure out what item is actually selected 2) Adding worksheets and workbooks (also opening workbooks) automatically switches to the new sheet/book. So relying on the select method you have to be aware of the behavior of VBA which isn't well documented. The is why I set objects to each sheet/workbook in my code Set bk = workbooks.open(filename:=abc.xls") Set sht = bk.sheets("Sheet1") with sht end with 3) Selecting a sheet slows down the code because excel has to refresh the worksheet before continuing. Select cells also slows down the code becaue excel has to update the screen. And even if you turn off refreshing excel need to keep track of the active cell so when the program ends it will put the active cell where the macro put the selection. Not the last place you were before the macro ran 4) if you want the selected cell not to change when you run a macro then don't use select. It is a nuisance is ever tim e you run a macro you have to go back to the area of the worksheet where you were working. If you had a worksheet change macro and you changed cell A3 do you want the macro to leave you at cell IV2000. "JMay" wrote: Select is a great method. In VBA it is over-used as experts continually say "you don't need to use the select statement in accomplishing your task". Also, using Select in VBA can cause an error when one tries a statement (with sheet1 active) like Worksheets("Sheet2").Select. This error is obvious when you stop and think about it as even while in the spreadsheet interface say sheet1 -- one cannot select cell A1 on sheet2, without first activating sheet2, then cell A1. Worksheets("Sheet2").Activate is the same, well nearly€¦ In VBA - with sheet1 active I sometimes activate sheet2 before issuing a statement against a range on sheet2. There are times when the Worksheets ("Sheet2").activate is unnecessary. I just don't know the rules underlying it. So even after years of excel programming these "don't" are not still obvious to me. If these such no-no's or AVOID DOING THESE THINGS were documented it would be a "good-read" for all excel newcomers/want-a-be's. The Excel boioks I have (around 12 or so) don't cover this sufficiently. Can some of you begin assisting me in creating this list of clarification thanks in advance... Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clarification need, not just by me
Yes
"JMay" wrote: "Selecting a sheet slows down the code because excel has to refresh the worksheet before continuing." << Is this the same for Activating a sheet? "Joel" wrote: I looks like you were refering to one of my postings. the are a number of issues with the SELECT method 1) When debugging large programs with lots of loop trying to debug the code it is often hard to figure out what item is actually selected 2) Adding worksheets and workbooks (also opening workbooks) automatically switches to the new sheet/book. So relying on the select method you have to be aware of the behavior of VBA which isn't well documented. The is why I set objects to each sheet/workbook in my code Set bk = workbooks.open(filename:=abc.xls") Set sht = bk.sheets("Sheet1") with sht end with 3) Selecting a sheet slows down the code because excel has to refresh the worksheet before continuing. Select cells also slows down the code becaue excel has to update the screen. And even if you turn off refreshing excel need to keep track of the active cell so when the program ends it will put the active cell where the macro put the selection. Not the last place you were before the macro ran 4) if you want the selected cell not to change when you run a macro then don't use select. It is a nuisance is ever tim e you run a macro you have to go back to the area of the worksheet where you were working. If you had a worksheet change macro and you changed cell A3 do you want the macro to leave you at cell IV2000. "JMay" wrote: Select is a great method. In VBA it is over-used as experts continually say "you don't need to use the select statement in accomplishing your task". Also, using Select in VBA can cause an error when one tries a statement (with sheet1 active) like Worksheets("Sheet2").Select. This error is obvious when you stop and think about it as even while in the spreadsheet interface say sheet1 -- one cannot select cell A1 on sheet2, without first activating sheet2, then cell A1. Worksheets("Sheet2").Activate is the same, well nearly€¦ In VBA - with sheet1 active I sometimes activate sheet2 before issuing a statement against a range on sheet2. There are times when the Worksheets ("Sheet2").activate is unnecessary. I just don't know the rules underlying it. So even after years of excel programming these "don't" are not still obvious to me. If these such no-no's or AVOID DOING THESE THINGS were documented it would be a "good-read" for all excel newcomers/want-a-be's. The Excel boioks I have (around 12 or so) don't cover this sufficiently. Can some of you begin assisting me in creating this list of clarification thanks in advance... Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clarification | Excel Discussion (Misc queries) | |||
Clarification | Excel Programming | |||
clarification | Excel Programming | |||
clarification | Excel Programming | |||
Clarification please... | Excel Programming |