How do I Shorten Object References?

  • Thread starter Thread starter Benjamin
  • Start date Start date
B

Benjamin

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?
 
Dim ws as worksheet
set ws = ThisWorkbook.Sheets("Vessel")

ws.Range("A1").value = "Shorten"
 
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
 
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
Click to expand...

Also, that seems tb (theoretically, at least) faster - since the
code doesn't have to go through the list of worksheets with every
ref.
 
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
 
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
 
Back
Top