Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
To Set or not to Set that is the question
Hi all, Thx to some very nice and briliant folks here, i have a workbook that's working great. But in my quest to learn, i am courious about this. I have several (6+) blocks of code like below. All of the source workbook and worksheets references are the same, and all of the destination workbook and worksheet references are the same. The ranges are the only difference. 'Copy site data Workbooks("UCPSITE-06.xls").Sheets("UCP SITE - Totals").Range("D227:BD303").Copy 'Paste site data Workbooks("3140UCP2006WithShell.xls").Sheets("3140 UCP Totals 2006").Range("D3").PasteSpecial _ Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Clears Clipboard Application.CutCopyMode = False So if i set the WorkBook at the begining of the code, Something like this: 'Source workbook Set swb = Workbooks("UCPSITE-06.xls").Sheets("UCP SITE - Totals") 'Destination workbook Set dwb = Workbooks("3140UCP2006WithShell.xls").Sheets("3140 UCP Totals 2006") then in my blocks of code use something like: swb.Range("D227:BD303").Copy dwb.Range("D3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Clears Clipboard Application.CutCopyMode = False Soo what are the pros and cons of something like this? -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=572376 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
To Set or not to Set that is the question
The biggest plus IMO is that it makes writing and modifying you code a whole
pile easier. By setting objects you can very quickly and easily refer to Workbooks, sheets and ranges in a very simple manner. The code is easier to read and manage. This is especialy true if you are flipping back and forth between workbooks, sheets and ranges. Finally it can help to organize your code. Normally I will set all of my object (that I can) right at the beginning of my code. If at some point one of these things changes it is very easy to update my code as all of the objects are initialized right up front. Depending what you are up to it can also have a marginal improvement in the speed of your application. -- HTH... Jim Thomlinson "Desert Piranha" wrote: Hi all, Thx to some very nice and briliant folks here, i have a workbook that's working great. But in my quest to learn, i am courious about this. I have several (6+) blocks of code like below. All of the source workbook and worksheets references are the same, and all of the destination workbook and worksheet references are the same. The ranges are the only difference. 'Copy site data Workbooks("UCPSITE-06.xls").Sheets("UCP SITE - Totals").Range("D227:BD303").Copy 'Paste site data Workbooks("3140UCP2006WithShell.xls").Sheets("3140 UCP Totals 2006").Range("D3").PasteSpecial _ Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Clears Clipboard Application.CutCopyMode = False So if i set the WorkBook at the begining of the code, Something like this: 'Source workbook Set swb = Workbooks("UCPSITE-06.xls").Sheets("UCP SITE - Totals") 'Destination workbook Set dwb = Workbooks("3140UCP2006WithShell.xls").Sheets("3140 UCP Totals 2006") then in my blocks of code use something like: swb.Range("D227:BD303").Copy dwb.Range("D3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Clears Clipboard Application.CutCopyMode = False Soo what are the pros and cons of something like this? -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=572376 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
To Set or not to Set that is the question
I think I can explain the advantage in this way.
Do you think people would like to call you "Desert" every time, or call you "The first son of the second daughter of the third son of the 5th daughter of the .... of the first guy who live in this town" everyday? "Desert Piranha" wrote: Hi all, Thx to some very nice and briliant folks here, i have a workbook that's working great. But in my quest to learn, i am courious about this. I have several (6+) blocks of code like below. All of the source workbook and worksheets references are the same, and all of the destination workbook and worksheet references are the same. The ranges are the only difference. 'Copy site data Workbooks("UCPSITE-06.xls").Sheets("UCP SITE - Totals").Range("D227:BD303").Copy 'Paste site data Workbooks("3140UCP2006WithShell.xls").Sheets("3140 UCP Totals 2006").Range("D3").PasteSpecial _ Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Clears Clipboard Application.CutCopyMode = False So if i set the WorkBook at the begining of the code, Something like this: 'Source workbook Set swb = Workbooks("UCPSITE-06.xls").Sheets("UCP SITE - Totals") 'Destination workbook Set dwb = Workbooks("3140UCP2006WithShell.xls").Sheets("3140 UCP Totals 2006") then in my blocks of code use something like: swb.Range("D227:BD303").Copy dwb.Range("D3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Clears Clipboard Application.CutCopyMode = False Soo what are the pros and cons of something like this? -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=572376 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
To Set or not to Set that is the question
I agree with what Jim wrote, but I would have used:
swk, not swb. It represents a worksheet, not a workbook <bg. And what's even nicer if you declare your variables nicely: Dim swk as worksheet dim dwk as worksheet You'll get the VBE's intellisense to pop up. Type swk. (include the dot) and you'll see a list of all the properties/methods that you can use. Desert Piranha wrote: Hi all, Thx to some very nice and briliant folks here, i have a workbook that's working great. But in my quest to learn, i am courious about this. I have several (6+) blocks of code like below. All of the source workbook and worksheets references are the same, and all of the destination workbook and worksheet references are the same. The ranges are the only difference. 'Copy site data Workbooks("UCPSITE-06.xls").Sheets("UCP SITE - Totals").Range("D227:BD303").Copy 'Paste site data Workbooks("3140UCP2006WithShell.xls").Sheets("3140 UCP Totals 2006").Range("D3").PasteSpecial _ Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Clears Clipboard Application.CutCopyMode = False So if i set the WorkBook at the begining of the code, Something like this: 'Source workbook Set swb = Workbooks("UCPSITE-06.xls").Sheets("UCP SITE - Totals") 'Destination workbook Set dwb = Workbooks("3140UCP2006WithShell.xls").Sheets("3140 UCP Totals 2006") then in my blocks of code use something like: swb.Range("D227:BD303").Copy dwb.Range("D3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Clears Clipboard Application.CutCopyMode = False Soo what are the pros and cons of something like this? -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=572376 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
To Set or not to Set that is the question
Dave Peterson Wrote: I agree with what Jim wrote, but I would have used: swk, not swb. It represents a worksheet, not a workbook <bg. And what's even nicer if you declare your variables nicely: Dim swk as worksheet dim dwk as worksheet You'll get the VBE's intellisense to pop up. Type swk. (include the dot) and you'll see a list of all th properties/methods that you can use.Hi, Jim, Thanks for your explanation and input, It's easy for an old geezer lik me to understand. salut, Thanks also Dave, (swk, not swb) hee hee (declare your variables) Yeah i had to do that because of "Optio Explicit", but i never know what to "Dim as". I will make another thread on that. (VBE's intellisense to pop up). I will make another thread on thi also. Thanks very muc -- Desert Piranh ----------------------------------------------------------------------- Desert Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2893 View this thread: http://www.excelforum.com/showthread.php?threadid=57237 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|