CODEWITHSUNDEEP
excelvba
Agustin
Agustin

Reputation: 1526

Set up variable to refer to sheet

Can I set up a variable x = "Sheet1" In order to do: x.Range("A3") instead of Sheet1.Range("A3")?

What type of variable should it be? I tried string and it didn't work.

Thanks

Update: I would like a method that won't be affected by changing the worksheet name. i.e. Sheet1.Range("A3") will always refer to the same worksheet even if I change the worksheet name to "peanuts", at least that's what I thought.

Upvotes: 0

Views: 1214

Answers (4)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

You want a Worksheet variable. The "name" you're referring to is the Name property, and that isn't in code; it's the "tab name" of the sheet, that the user can change on a whim and that, for that reason, you don't want to have to hard-code anywhere if you can avoid it.

But it looks like you're referring to a sheet that exists in ThisWorkbook at compile-time; each sheet has a (Name) property that you can edit in the Properties toolwindow (F4). By default that name is Sheet1, and VBA takes that name and makes it a project-scope identifier you can use anywhere you need to refer to that particular sheet.

So if you change a sheet's (Name) to PeanutsSheet, then you can use PeanutsSheet in your code:

PeanutsSheet.Range("A1").Value = 42 '<~ that's the "CodeName", and users can't easily break it

And that is preferable to referring to that same sheet by its "tab name":

ThisWorkbook.Worksheets("Peanuts").Range("A1").Value = 42 '<~ breaks if tab is ever renamed

Declaring a variable for a sheet that exists in ThisWorkbook at compile-time, is completely redundant:

Dim ws As Worksheet
Set ws = Sheet1 '<~ variable 'ws' is 100% redundant

Using such variables makes the code confusing and harder to follow than it needs to be, because now you have 2 (or more) identifiers referring to the same thing.

But this is worse:

Set ws = ThisWorkbook.Worksheets("Sheet1") '<~ now it's redundant *and* super frail

Upvotes: 0

Vu Hoang
Vu Hoang

Reputation: 5

It should be worksheet. The syntax would be:

Dim x as Worksheet
Set x = Sheet1

after that you can use x.range("A3") to refer to Cell A3 in sheet1

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

You want to declare it a Worksheet Object:

Dim x as WorkSheet

Since it is an object we must Set the sheet:

Set x = WorkSheets("Sheet1")

or if you want to use the code name:

Set x = Sheet1

Then yes you can use it:

x.Range("A3")...

Upvotes: 5

Cyril
Cyril

Reputation: 6829

I think you're mixing two different declarations

Declaring a sheet (must set the sheet)

dim ws as worksheet
set ws = sheets("sheet1")
ws.cells(1,1).value = ""

Declaring a string as the name of a sheet (can utilize the NAME of the sheet as a string)

dim ws_name as string
ws_name = "sheet1"
sheets(ws_name).cells(1,1).value = ""

Additionally you could use the sheet index, which does not utilize the name of the sheet (if you change it later); this is a little different than the previous two, but this example (using a loop) helps more clearly explain how the index can be utilized

dim i as long
for i = 1 to sheets.count step 1
    sheets(i).cells(1,1).value = ""
next i

and a simple use of a sheet index

sheets(1).cells(1,1).value = ""

Upvotes: 1

Related Questions