Question regarding variables when working with objects

[supermom28115]

I found this thread and wanted to ask about the use of a variable to hold the sheet name.
Is this the way the code should be written when using variables for objects?
I was under the impression that I should be using this code:

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Sheet 1")

I ask because this is not working in my code and I can not figure out why it will not work. I have tried multiple variations of this code; ie long hand the workbook name within the set code, using worksheet, sheet, sheets, using the index for the sheet. I have even tried changing the variable name in case the ws was inappropriate. I continually get the same ws1= Nothing, object variable or with variable not set error.
So how am I supposed to declare and set a variable correctly for my workbook and worksheets to use in my code?

ThisWorkbook.Sheets with variable

Hi folks, I am trying to create a generic code that could use the sheet name, which is stored in a cell. Dim wsName As String Dim ws As Worksheet Dim targetCell As Range ' Set the target cell (N3 in this case) for the active sheet Set targetCell = ActiveSheet.Range("N3")...

www.mrexcel.com

 

[Scott Huish]

Is it not working because your sheet is named Sheet1 not Sheet 1?

 

[supermom28115]

Is it not working because your sheet is named Sheet1 not Sheet 1?

Really? You mean I can't use spaces in my sheet name? If I rename my sheet1 to Sheet 1 this code will work? It was just an example, I could have just as easily said sheet name was "Bunny Rabbits".

 

[BigBeachBananas]

Really? You mean I can't use spaces in my sheet name? If I rename my sheet1 to Sheet 1 this code will work? It was just an example, I could have just as easily said sheet name was "Bunny Rabbits".

Scott is saying "Bunny Rabbits" is not the same as "BunnyRabbits". You can use spaces, but make sure your code matches exactly what you named the sheet.

 

[supermom28115]

Scott is saying "Bunny Rabbits" is not the same as "BunnyRabbits". You can use spaces, but make sure your code matches exactly what you named the sheet.

I got that part, my point was that there was no reason to assume Sheet 1 wasn't the name of my sheet. The actual name of my sheet is "PO Journal" and believe me I have checked the spelling multiple times even checked the sheet tab spelling to verify no spaces before or after. I used sheet 1 example because I have spaces in my sheet names and even though I have seen spaces in many examples on the web and in books, that doesn't mean that microsoft won't decide to make spaces a no-no and then create a 5 page spec sheet that leaves the average reader more confused.
Why do some examples use the long version and some use the

 set ws1 = sheets("Bunny Rabbits")

do they do the same thing or do I need to choose which to use based on my task I am coding?

 

[BigBeachBananas]

In the link you provided, there are advantages to declaring your sheet name and storing it in a variable. The biggest one is that if you use it in multiple places throughout the code and you need to change the sheet name, you would only have to change it in 1 spot rather than go through my entire code and replace them.

By long version, do you mean this? Where you call the ThisWorkBook?

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Sheet 1")

It's good practice to be specific as possible, in case you're working with multiple workbooks.

 

[supermom28115]

In the link you provided, there are advantages to declaring your sheet name and storing it in a variable. The biggest one is that if you use it in multiple places throughout the code and you need to change the sheet name, you would only have to change it in 1 spot rather than go through my entire code and replace them.

By long version, do you mean this? Where you call the ThisWorkBook?

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Sheet 1")

It's good practice to be specific as possible, in case you're working with multiple workbooks.

Ok, so the use of ThisWorkbook.Worksheets is for precision in the code. If I wanted to code quickly then I could just use the Sheets("SheetName")
So, when debugging code and stepping through will it always say ws1 = Nothing until the macro is actually run and the variable is given a value? I ask because I am now able to hover over the variable and see ws1 = Nothing but am not getting the error code I was seeing earlier in the day when I hovered over the section naming the sheet on the set code line. 6 hours ago when I would run debug and hover over the set code line I would get ws1 = Nothing and when I hover over ThisWorkbook.Worksheets I would get the error object variable or with variable not set.

 

[BigBeachBananas]

ws1 should be Nothing until you execute the set line.
The object variable or with variable not set indicates you didn't have the keyword "set". The keyword "set" is necessary when you're storing objects like sheets,range, cells, ect...
You probably had ... without set

Dim ws1 As Worksheet
ws1 = ThisWorkbook.Worksheets("Sheet 1")

 

[supermom28115]

ws1 should be Nothing until you execute the set line.
The object variable or with variable not set indicates you didn't have the keyword "set". The keyword "set" is necessary when you're storing objects like sheets,range, cells, ect...
You probably had ... without set

Dim ws1 As Worksheet
ws1 = ThisWorkbook.Worksheets("Sheet 1")

I thought I had checked it but possible. Does the set need to be the next code line after when doing an object variable like this?
Am I right in thinking that the tab settings are for ease in reading the code and don't affect how the code runs?

 

[BigBeachBananas]

I thought I had checked it but possible. Does the set need to be the next code line after when doing an object variable like this?
Am I right in thinking that the tab settings are for ease in reading the code and don't affect how the code runs?

Does not need to be the immediate next line, but must be before you use it somewhere else. So usually we want to declare it as soon as possible, in case you forget.

Correct, indenting the code makes it more readable, but won't impact the execution of the code.