VBA: How to refer to the correct workbook when providing a worksheet as a function argument?
I have a question regarding the correct address of Workbooks in VBA, which I am fairly new to.
Here's what I've done so far:
I've written a sub that, among other things, creates a worksheet with CodeName "table10".
Then I defined a function that controls the contents of the sheet: this function
Text_To_Numbers(worksheet as worksheet)
expects a worksheet argument. I am calling a function from another element using the following line:
Call Text_To_Numbers(table10)
Now here's my problem:
This works flawlessly when the only open book is the one I want to manipulate with my function. However, when I have multiple books open, the function will try to manipulate another book, resulting in an error.
I'm pretty sure there must be a way to specify the book to be used, but I can't seem to find it. That being said, there is another complication: the title of the workbook 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 filename over and over is not an option.
Can anyone help me resolve this?
source to share
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 the parent workbook, so make sure you indicate that it is derived from the given workbook!
You cannot directly reference worksheets in other workbooks by their codename , this can only be done with an object ThisWorkbook
(a workbook containing VBA code). See the Question Full link to the codename worksheet for details on how to get the codename worksheet from another workbook. I have included the function in the answer and how to use it in this context.
You have created a sheet table10
in one of the following items:
- ActiveWorkbook
- ThisWorkbook
- WB (some workbook object)
So you can access it using this workbook object without the need for a name!
Usage ThisWorkbook.table10
should give the same behavior once table10
, but here are two simpler examples for calling a 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
source to share
Try assigning the workbook and sheet to a variable and then calling it this way when you need to do some work:
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("Table10")
Then you just need to pass a call to the WS variable from your function to perform operations on the specified sheet.
Edit:
Sorry I didn't realize that you were trying to reference the index name in the project editor when I first read your question. The codename can refer to an external workbook with the following example showing how to select a workbook and a sheet codename to 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 this idea.
source to share