How do I Shorten Object References?
 

ThisWorkbook.Sheets("Vessel")
I'm always referring to this Sheet and Sheet "Operations"
I've seen code where people shorten the objects...
How exactly do I do that?

How do I Shorten Object References?
 

Dim ws as worksheet
set ws = ThisWorkbook.Sheets("Vessel")

ws.Range("A1").value = "Shorten"
"Benjamin" wrote:

ThisWorkbook.Sheets("Vessel")
I'm always referring to this Sheet and Sheet "Operations"
I've seen code where people shorten the objects...
How exactly do I do that?

How do I Shorten Object References?
 

Benjamin,

Here's a way, you only need to SET the shhet once and thereafter you can
refer to as in my example

Dim MySheet As Object
Set MySheet = ThisWorkbook.Sheets("Vessel")


MySheet.Range("A1").Value = 999

Mike

"Benjamin" wrote:

ThisWorkbook.Sheets("Vessel")
I'm always referring to this Sheet and Sheet "Operations"
I've seen code where people shorten the objects...
How exactly do I do that?

How do I Shorten Object References?
 

Per Mike H:
Here's a way, you only need to SET the shhet once and thereafter you can
refer to as in my example

Dim MySheet As Object
Set MySheet = ThisWorkbook.Sheets("Vessel")


MySheet.Range("A1").Value = 999

Also, that seems tb (theoretically, at least) faster - since the
code doesn't have to go through the list of worksheets with every
ref.
--
PeteCresswell

How do I Shorten Object References?
 

Benjamin,

You can also use the codename of the sheet without assigning it to a variable: change the codename
to something meaningful by selecting the sheet and changing the name in the properties window. For
example, use v as the name of sheet "Vessel"

Then v is an object that you can reference - type v and a period, and the autosense properties for a
worksheet will show up.

HTH,
Bernie
MS Excel MVP


"Benjamin" wrote in message
...
ThisWorkbook.Sheets("Vessel")
I'm always referring to this Sheet and Sheet "Operations"
I've seen code where people shorten the objects...
How exactly do I do that?

How do I Shorten Object References?
 

And if you're working with the object a lot, you can shorten things even
further using the With statement:

With MySheet
.Range("A1").Value = 3
.Name = "MySheetName"
End With

HTH,

Eric

"Mike H" wrote:

Benjamin,

Here's a way, you only need to SET the shhet once and thereafter you can
refer to as in my example

Dim MySheet As Object
Set MySheet = ThisWorkbook.Sheets("Vessel")


MySheet.Range("A1").Value = 999

Mike

"Benjamin" wrote:

ThisWorkbook.Sheets("Vessel")
I'm always referring to this Sheet and Sheet "Operations"
I've seen code where people shorten the objects...
How exactly do I do that?

How do I Shorten Object References?
 

Thanks for all the posts everyone!
Make my coding life a LOT easier!

"Bernie Deitrick" wrote:

Benjamin,

You can also use the codename of the sheet without assigning it to a variable: change the codename
to something meaningful by selecting the sheet and changing the name in the properties window. For
example, use v as the name of sheet "Vessel"

Then v is an object that you can reference - type v and a period, and the autosense properties for a
worksheet will show up.

HTH,
Bernie
MS Excel MVP


"Benjamin" wrote in message
...
ThisWorkbook.Sheets("Vessel")
I'm always referring to this Sheet and Sheet "Operations"
I've seen code where people shorten the objects...
How exactly do I do that?


.