VBA: How to address the correct workbook when giving a worksheet as an argument to a function?

Question

I have a question regarding the correct address of Workbooks in VBA, which I am fairly new to.

Here is what I have done so far:
I have written a sub that, amongst other things, creates a worksheet with the CodeName "table10".

Then I defined a function to manipulate the contents of said sheet: this function

Text_To_Numbers(worksheet as worksheet)

expects a worksheet argument. I call the function from another sub using the following line:

Call Text_To_Numbers(table10)

Now, here is my issue:
The above works flawlessly when the only open workbook is the one I want to manipulate with my function. However, when I have multiple open workbooks, the function will try to manipulate a different workbook, resulting in an error.

I am quite certain that there must be a way to specify the workbook to be used, but I am unable to find it. That being said, there is another complication: The name of the workbook which I would like to manipulate is machine generated, so it always has a different name. This means that using an explicit reference to the same file name time and again is not an option.

Could anybody help me resolve this?

Answer 1

You need to fully qualify objects in VBA to avoid situations like this where it is ambiguous what the parent is.

In your situation, you want the sheet to be connected to its parent workbook, so make sure you specify that it came from a given workbook!

You cannot directly refer to worksheets in other workbooks by their CodeName, this can only be done to the ThisWorkbook object (the workbook containing the VBA code). See the question Fully reference a worksheet by codename for details on how to get the sheet by its codename from another workbook. I have included the function in the answer and how to use it in this context.

You created the sheet table10 in one of the following:

• ActiveWorkbook
• ThisWorkbook
• WB (some workbook object)

So you can access it using that workbook object without a need for the name!

---

Using ThisWorkbook.table10 should give same behaviour as just table10, but here are two neater examples for calling the function.

' A neater way to call the function:
Text_To_Numbers worksheet:=ThisWorkbook.table10
' You could also call it simply using
Text_To_Numbers ThisWorkbook.table10

---

If your sheet is not within ThisWorkbook

' Get sheet (from the workbook object you are using, WB) and pass to your Text_To_Numbers 
Text_To_Numbers GetSheetWithCodename("table10", WB)

Function GetSheetWithCodename(ByVal worksheetCodename As String, Optional wb As Workbook) As Worksheet
    Dim iSheet As Long
    If wb Is Nothing Then Set wb = ThisWorkbook ' mimics the default behaviour
    For iSheet = 1 To wb.Worksheets.Count
        If wb.Worksheets(iSheet).CodeName = worksheetCodename Then
            Set GetSheetWithCodename = wb.Worksheets(iSheet)
            Exit Function
        End If
    Next iSheet
End Function

Answer 2

Try assigning the workbook and sheet to a variable then calling it in this way when you need to do some work in it:

Dim WB As Workbook

Dim WS As Worksheet

'If you want to open the workbook before doing work
Set WB = Workbooks.Open("/Workbook path name goes here”)

Set WS = WB.Worksheets("Table‌​10")

Then you just need to pass a call to the WS variable from within your function to perform operations within the specified sheet.

Edit:

Apologies, didn't realise you were trying to reference the index name in the project editor when I first read your question. The code name can be referenced from an external workbook with the following example which shows how to select the workbook and sheet codename to perform a copy/paste from one workbook to another:

Sub UseCodeNameFromOutsideProject()
Dim WS As Worksheet
With Workbooks("MyWorkbook.xlsb")
    Set WS = _
        .Worksheets(CStr(.VBProject.VBComponents("Sheet1").Properties(7)))
        WS.Range("A1").Copy
        Selection.Copy
        WS.Range("B1").PasteSpecial
End With
End Sub

Thanks to Enderland for the idea.