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

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

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

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

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