Referencing workbooks and sheets programmatically generates a lot of confusion because there are so many possibilities. No method is superior; they all have their place. The purpose at hand will define which referencing method is the most efficient.
Note: This article is also available as a PDF download.
1: Reference the active workbook
VBA’s ActiveWorkbook property refers to the workbook with the focus. The active workbook may or may not contain the code that’s referencing the active workbook, which is an important distinction. It’s perfectly acceptable to use this property to reference the active workbook from code inside the active workbook. However, it’s invaluable when referencing the active workbook remotely.
For example, after passing data to an active workbook, you’d probably want to save that workbook, which is a simple task for the ActiveWorkbook property. The following procedures use the ActiveWorkbook property to close the active workbook:
Sub CloseActiveWBNoSave() <p> 'Close the active workbook without saving.</p> <p> ActiveWorkbook.Close False</p> End Sub
Sub CloseActiveWBWithSave() <p> 'Close the active workbook and save.</p> <p> ActiveWorkbook.Close True</p> End Sub
Sub CloseActiveWB() <p> 'Close the active workbook.</p> <p> 'Let user choose whether to save.</p> <p> ActiveWorkbook.Close</p> End Sub
Of course, you could just as easily combine all three into a Select Case statement and use a single function to pass a conditional argument that specifies which save to execute.
Figure A
Function GetActiveWB() As String <p> GetActiveWB = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name</p> End Function
Figure A
Use the ActiveWorkbook property to return the active workbook’s full path and filename.
2: Reference the workbook that’s currently running code
VBA’s ThisWorkbook property is similar to the ActiveWorkbook property, but whereas ActiveWorkbook evaluates the workbook with the focus, ThisWorkbook refers to the workbook that’s running the current code. This added flexibility is great because the active workbook isn’t always the workbook that’s running code.
Figure B
Function GetThisWB() As String <p> GetThisWB = ThisWorkbook.Path & "\" & ThisWorkbook.Name</p> End Function
As you can see, HumanResources.xls is the active workbook, but the function is in a workbook named 0908002.xls.
Figure B
Take advantage of ThisWorkbook’s flexibility when you need to refer to the workbook running code when the active workbook isn’t the workbook running code.
3: Reference workbooks in the Workbooks collection
The Workbooks collection contains all the open Workbook objects. Using the Workbooks property, you can refer to open workbooks. For instance, the following subprocedure populates a list box in a user form with the names of all open workbooks:
Private Sub UserForm_Activate() <p> 'Populate list box with names of open workbooks.</p> <p> Dim wb As Workbook</p> <p> For Each wb In Workbooks</p> <p> ListBox1.AddItem wb.Name</p> <p> Next wb</p> End Sub
The resulting user form, shown in Figure C, displays a list of open workbooks. By referencing the Workbooks collection, you can reference all the open workbooks without hard-coding a single workbook name.
Figure C
Use the Workbooks collection to reference open workbooks.
Listing all the open workbooks is an easy enough task, thanks to the Workbooks collection. However, opening all of the workbooks in a specified folder is a bit harder, as you can see in the following subprocedure:
Sub OpenAllWB() <p> 'Open all workbooks in specified folder.</p> <p> Dim i As Integer</p> <p> With Application.FileSearch</p> <p> .LookIn = "C:\Examples"</p> <p> .FileType = msoFileTypeExcelWorkbooks</p> <p> 'There are wb's</p> <p> If .Execute > 0 Then</p> <p> For i = 1 To .FoundFiles.Count</p> <p> Workbooks.Open (.FoundFiles(i))</p> <p> Next i</p> <p> 'There are no wb's</p> <p> Else</p> <p> MsgBox "There are no workbooks to open", vbOKOnly</p> <p> End If</p> <p> End With</p> End Sub
This task isn’t a referencing one in the true sense, but it shows the power of the Workbooks collection. In this case, the code doesn’t cycle through the Workbooks collection; it just takes advantage of one of the collection’s methods — specifically, the Open method. Closing all the open workbooks is a bit easier than opening them, as the following procedure shows:
Sub CloseAllWB() <p> 'Close all open workbooks.</p> <p> Workbooks.Close</p> End Sub
To see a collection’s many methods and properties, press F2 in the VBE to launch the Object Browser.
4: Explicitly reference a workbook
If you know the name of the workbook you want to reference, an explicit reference might be the most efficient method. Although an explicit reference is easy, it does require a stable situation. If the name of the workbook changes, but the possibilities are known, you can still use an explicit reference by passing the workbook’s name. For example, the following subprocedure activates an open workbook, as determined by the passed argument, wbname:
Function ActivateWB(wbname As String) <p> 'Open wbname.</p> <p> Workbooks(wbname).Activate</p> End Function
To execute it, you simply pass the name of the workbook you want to activate as follows:
ActivateWB("HumanResources.xls")
(You must include the .xls extension.)
The following function also uses the Workbooks property to determine whether a specific workbook is currently open:
Function IsWBOpen(wbname As String) As Boolean <p> 'Open workbook.</p> <p> Dim wb As Workbook</p> <p> On Error Resume Next</p> <p> Set wb = Workbooks(wbname)</p> <p> IsWBOpen = Not wb Is Nothing</p> End Function
If wbname is open, the function returns True. When not open, the function returns False. These procedures also rely on the Workbooks property, but instead of cycling through the collection, they specify a workbook by name.
5: Reference workbooks by index
Perhaps the least stable method for referencing a workbook is to use its index value. Excel assigns index values to workbooks as you open them. The first workbook opened has an index value of 1, the second workbook opened has an index value of 2, and so on.
Index values pose a special problem because they change when you delete a Workbook object from the collection; index values slip down a notch, accordingly. For example, suppose you have three open workbooks with the following index values:
ExcelStatisticalFunctions — 3
0908002.xls – 2
HumanResources.xls – 1
If a particular task depends on all three workbooks always being open, using the index values can generate mistakes. For instance, the statement
Workbooks(1).Activate
activates HumanResources.xls as long as it’s open. If you close HumanResources.xls, ExcelStatisticalFunctions and 0908002.xls both move down a notch: ExcelStatisticalFunctions becomes 2 and 0908002.xls becomes 1. As a result, the above statement activates 0908002.xls, not HumanResources. That may or may not be what you want. Using index values to reference workbooks isn’t wrong, but you must understand its inherent behaviors to avoid errors that can be difficult to troubleshoot.
6: Reference the active sheet
If you don’t specify an object qualifier, the ActiveSheet property defaults to the active sheet in the active workbook. For instance, to retrieve the name of the active sheet, you’d use a function similar to the following:
Function GetActiveSheet() As String <p> GetActiveSheet = ActiveSheet.Name</p> End Function
This property is read-only; you can’t use it to activate a sheet.
7: Reference Worksheet objects
The Worksheets collection contains all the sheet objects in a workbook. Using a simple For Each loop, you can cycle through the collection. For example, the following code populates a list box control with the names of all the sheets in the active workbook:
Private Sub UserForm_Activate() <p> 'Populate list box with names of sheets</p> <p> 'in active workbook.</p> <p> Dim ws As Worksheet</p> <p> For Each ws In Worksheets</p> <p> ws.Select</p> <p> ListBox1.AddItem ws.Name</p> <p> Next ws</p> End Sub
The Sheets and Worksheets collections both contain Worksheet objects, but the Sheets collection contains both worksheets and chart sheets.
8: Explicitly reference sheets
Use the Worksheets property to explicitly reference a sheet. For example, use this type of reference to delete a specific sheet as follows:
Function DeleteSheet(shtname As String) <p> 'Delete shtname.</p> <p> Application.DisplayAlerts = False</p> <p> Worksheets(shtname).Delete</p> <p> Application.DisplayAlerts = True</p> End Function
9: Reference sheets by index
Index values come in handy when you don’t care about specific sheets, but only their number or order. Granted, that’s not going to be a common task, but occasionally, referencing by index values can come in handy. The following procedure adds and deletes sheets based on the number of sheets you want:
Function ControlSheetNumber(intSheets As Integer) <p> 'Add or delete sheets to equal intSheets.</p> <p> Application.DisplayAlerts = False</p> <p> 'Delete sheets if necessary</p> <p> While Worksheets.Count > intSheets</p> <p> Worksheets(1).Delete</p> <p> Wend</p> <p> 'Add sheets if necessary</p> <p> While Worksheets.Count < intSheets</p> <p> Worksheets.Add</p> <p> Wend</p> <p> Application.DisplayAlerts = True</p> End Function
Use caution when executing this function because it deletes the first Sheet object in the collection, even if that sheet contains content. It simply adds and deletes sheets, depending on the value you pass. This function is useful when creating new workbooks programmatically.
10: Refer to a sheet’s code name property
Code that refers to a Worksheet object by the name on the sheet’s tab runs the risk of generating an error. That’s because you must remember to update the code when you change the sheet’s name. Not only is that a lot of trouble, users are apt to change a sheet’s name. One way to safeguard code that refers to specific sheets by name is to use the CodeName property.
The code name is the sheet’s default name , which Excel assigns when you create it — Sheet1, Sheet2, and so on. Changing the sheet’s name, as displayed on the sheet’s tab, does not change its code name, as you can see in Figure D. The names in parentheses are the sheet names (as shown on the sheet tabs). Notice that the default names, the code names, remain the same even if you change the sheet’s name.
Figure D
A sheet’s code name property is stable; its sheet name is subject to change.
To change a sheet’s code name, use the (Name) property, as shown in Figure E. You must use the Visual Basic Editor (VBE), as you can’t change this property programmatically. There are two similar properties, so don’t confuse them. The Name property (without parentheses) toward the bottom of the properties list represents the name Excel displays on the sheet tab. (Code name values must start with a letter character.)
Figure E
Change the code name using the VBE.
Also see
- How to add a drop-down list to an Excel cell (TechRepublic)
- How to become a cloud engineer: A cheat sheet (TechRepublic)
- 50 time-saving tips to speed your work in Microsoft Office (free PDF) (TechRepublic download)
- Cost comparison calculator: G Suite vs. Office 365 (Tech Pro Research)
- Microsoft Office has changed, how you use it should too (ZDNet)
- Best cloud services for small businesses (CNET)
- Best to-do list apps for managing tasks on any platform (Download.com)
- More must-read Microsoft-related coverage (TechRepublic on Flipboard)
Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.
