Link to sheets in another book by code name using VBA

Question

I am trying to copy data from one workbook to my current workbook using VBA. InputBook is a workbook object related to the file from which I would like to extract data. The main problem is with reference to specific worksheets in the InputBook. In InputBook, I have a worksheet called "Lines", codenamed LINES. I would prefer to reference this worksheet under my codename, for example:

NumItems = WorksheetFunction.CountA(InputBook.LINES.Columns(1))

This obviously does not work, and I know that I can make it a function using one of the following actions:

NumItems = WorksheetFunction.CountA(InputBook.Sheets("Lines").Columns(1))
NumItems = WorksheetFunction.CountA(InputBook.Sheets(2).Columns(1))

I would rather not use any of these methods, as they seem less reliable. Is there a way to refer to the code name of a worksheet object in another open workbook? Thank.

Answer 1

You can “hack” a link to another code name of a book sheet:

Sub UseCodeNameFromOutsideProject()
    Dim WS As Worksheet
    With Workbooks("InputBook .xls")
        Set WS = _
            .Worksheets(CStr(.VBProject.VBComponents("Lines").Properties(7)))
        debug.print WS.Name
    End With
 End Sub

Now your object is WSset on a sheet codenamed "Lines" in this example.

---

Original inspiration here .

Answer 2

I could name the book "I LIKE TEA", but InputBook.I LIKE TEA.Columns(1)would not allow it.

Cut the name with constor:

public enum InputSheets
   LINES = 2,
   GARMENTS = 4
end enum

to allow:

InputBook.Sheets(InputSheets.LINES).Columns(1)

or

MyGetSheetFunction(InputBook, InputSheets.LINES)

You can also take this and use a wrapper class.