Referencing Name Property Using An Array

Question

I am working on a project that requires me to select worksheets depending on the value of a position in an array. I have populated desArr() (of type String) with values and desArr(0) contains the name of the sheet I want to reference. In order to avoid the problem with users changing the tab names and messing up the code, I am trying to reference the CodeName of the worksheet.

Usually, I would be able to do this:

ThisWorkbook.Worksheets(Import.Name) 

where Import is specified under the (Name) property in VB Editor. However, I would now like to reference that name variably, as in input depending on the value of desArr(0). I tried doing the following but to no avail:

ThisWorkbook.Worksheets(desArr(0).Name)

ThisWorkbook.Worksheets(desArr(0) & ".Name")

Does anyone have any idea on how to solve this problem?

Answer 1

Just use:

Import

That's the way to reference a sheet object without needing to know its Name property. Just use its variable/object name. The above is equivalent to, but much less convoluted than,

ThisWorkbook.Worksheets(Import.Name) 

What you refer as the "CodeName" and the (Name) "property" isn't a property. It's just the name of the object variable. From what I can infer from your question, your sheet object is called Import so just use that.

---

EDIT:

It's hard to understand what you are asking; to clarify a bit:

If I understand correctly, you have a sheet called "Import", and you want to be able to guard against this scenario:

Ok, some user decided it was a good idea to rename the sheet. Note that this changed the sheet object's Name property's value to "User-input crap". The Name property and the thing written in the tab are always the same; they're the same thing; they're linked one-to-one.

However, the sheet object itself is still called Import, or whatever it was earlier. (By default it is Sheet1, but maybe you changed that in the Properties window.) See:

Regardless of user-input crap, I can still say, for instance:

Import.Cells(1, 1).Value = "frog"

I can also say this, which is uselessly complicated but perfectly equivalent:

ThisWorkbook.Worksheets(Import.Name).Cells(1, 1).Value = "frog"

I could also say this:

ThisWorkbook.Worksheets("User-input crap").Cells(1, 1).Value = "frog"

but for that I'd have to know what the user typed in the sheet tab, which I don't.