Help with dim coding

[Derek]

Hi Guys

A long used macro has suddenly produced a run-time error on this line:

Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets

Can anyone tell me why this code is not acceptable?

Many thanks

Derek

 

[Kamolga]

I always use worksheets instead of sheets, can not remember why (maybe a question of versions?). I also never use ThisWorkbook since by default they are all ThisWorkbook (and I often forget to rename them). So I rather use:

 

[FormR]

Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets

You didn't tell us the error, but maybe the sheet referenced by the for loop isn't a worksheet, but rather a chart sheet or another type of sheet.

Difference between Sheets and Worksheets in VBA - Excel Off The Grid

 

[Legacy 456129]

@Kamolga said "also never use ThisWorkbook since by default they are all ThisWorkbook "

Not in my dreams would I rename ThisWorkbook. To the Module code it simply means "code in this workbook" and when mentioned execute for ThisWorkbook. Changing the name makes portability a potential nightmare. Besides, in testing, renaming TB did not affect the code, it still runs as ThisWorkbook. But teased as I was went to look and someone complained

Renaming Workbook Module

Hi,

I am trying to rename the workbook module from ThisWorkbook to something else through the properties window. I can change the name fine, but when I close the file and reopen it the workbook module name has reverted back to ThisWorkbook. Any ideas?

Thanks

The ideas was replied to

Thisworkbook is a built in reference to the workbook and should not be renamed. Why do you want to rename it? -- Andy Pope, who further expanded why it was a bad idea.

As for Sheets v Worksheets, latter is always a spreadsheet, former can also be the above mentioned Chart sheet, so the a .Sheets is not a good idea if you have that mix. Difference between Sheets and Worksheets in VBA - Excel Off The Grid

But, it's just my opinion, and sticking to it, has as yet never broken my code. Beside Set MyWbMain = ThisWorkbook sure as heck makes the name readable inside code that touches on multiple workbooks.

 

[RoryA]

I also never use ThisWorkbook since by default they are all ThisWorkbook (and I often forget to rename them)

ThisWorkbook is a different thing from ActiveWorkbook. ThisWorkbook always refers to whatever workbook the code is in, whereas ActiveWorkbook refers to whichever workbook happens to be active.

 

[Derek]

Thank you to everyone who replied.

Strangely the same workbook has this coding in a number of places and has been used daily for years without breaking on this line. However, changing Sheets to Worksheets appears to have fixed it.

The assistance of everyone is greatly appreciated

Derek