VBA Codename Reference

Question

I wish to reference my worksheets via codenames as part of a longer vba code - the current code I have is:

Works correctly:

If wb.Sheets("stage 1").Visible = xlVeryHidden Or wb.Sheets("stage 1").Visible = xlHidden Then wb.Sheets("Stage 1").Visible = True

Which I want to change to the below - "Stage 1" = Sheet8 within my excel file

If wb.Sheet8.Visible = xlVeryHidden Or wb.Sheet8.Visible = xlHidden Then wb.Sheet8.Visible = True

However upon changing the code it provides a runtime error 438.

Can someone advise how I can overcome this issue or where my code is incorrect?

Thanks in advance

Remove the `wb.` parts. `Sheet8` is a variable and not a property of `wb`. — Rory, Sep 21 '17 at 11:59
Presumably `wb` is defined as `ThisWorkbook`? If so, you can remove it and your code should work. — SJR, Sep 21 '17 at 12:00
You could shorten that code to just `Sheet8.Visible = True`. If the final result is that you want the sheet visible then just make it visible no matter the current state. — Darren Bartrup-Cook, Sep 21 '17 at 12:27

FunThomas · Accepted Answer · 2017-09-21T12:22:20.897

The name of a sheet within VBA is the codeName

If the sheet it part of ThisWorkbook (that is the workbook where you code lives in), you can access it in your code like it is a variable (remove wb.)

   Sheet8.Visible = xlVeryHidden Or Sheet8.Visible = xlHidden Then Sheet8.Visible = True

However, if you want to access a sheet from a different workbook, you have to loop over all sheets to find it. See https://stackoverflow.com/a/25203416/7599798

score -2 · Answer 2 · answered Sep 21 '17 at 11:59

-2

What you will need to do is use:

    Dim Sht8 As Worksheet
    Set Sht8 = wb.Sheets("stage 1")

You should try not use Sheet8 as the sheet objects inside the Excel WorkBook VBA project are using names such as Sheet1, Sheet2 and Sheet3 for the initial sheet name.

answered Sep 21 '17 at 11:59

Jean-Pierre Oosthuizen

2,653
2
10
34

2

I'd agree in part with this - you shouldn't leave the sheets code name as `Sheet8`, the same as you wouldn't leave the tab name as `Sheet8`. Much better to change it in the sheet properties to something like `shtStage1`. But, much better to use the codename rather than the tab name as the tab name can be changed by the user, while the codename can't. – Darren Bartrup-Cook Sep 21 '17 at 12:31
@DarrenBartrup-Cook agreed, we need to be descriptive with our naming of Variable. Do you think was my answer worthy of a #DownVote? – Jean-Pierre Oosthuizen Sep 21 '17 at 13:15
1

I do - although I didn't down vote it. `Sheet8` is the codename for the sheet and as you say in your answer - `sheet objects inside Excel are using names such as Sheet1....`. The OP was trying to reference the sheet object using that name, while your answer was to reference the tab name which can be changed by the user. – Darren Bartrup-Cook Sep 21 '17 at 13:22
2

Ok, I see now. Good Stuff, at least I learnt something, I never thought about a user changing sheet names which will then cause a error when running a bit of code. – Jean-Pierre Oosthuizen Sep 21 '17 at 13:25

VBA Codename Reference

2 Answers2