After Hours

10 ways to reference Excel workbooks and sheets using VBA

Excel offers myriad options for referring to workbooks and sheets in your VBA code. See which methods make sense in which situations.

Excel offers myriad options for referring to workbooks and sheets in your VBA code. See which methods make sense in which situations.


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()

  'Close the active workbook without saving.

  ActiveWorkbook.Close False

End Sub
Sub CloseActiveWBWithSave()

  'Close the active workbook and save.

  ActiveWorkbook.Close True

End Sub
Sub CloseActiveWB()

  'Close the active workbook.

  'Let user choose whether to save.

  ActiveWorkbook.Close

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.

This save takes place without additional information, such as the workbook's name, path, and so on. However, if you need such information, it's easy enough to glean using ActiveWorkbook. Figure A shows the result of the following procedure, which returns the full path and workbook name for the active workbook:
Function GetActiveWB() As String

  GetActiveWB = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

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 shows the result of executing the following procedure:
Function GetThisWB() As String

  GetThisWB = ThisWorkbook.Path & "\" & ThisWorkbook.Name

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()

  'Populate list box with names of open workbooks.

  Dim wb As Workbook

  For Each wb In Workbooks

    ListBox1.AddItem wb.Name

  Next wb

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()

  'Open all workbooks in specified folder.

  Dim i As Integer

  With Application.FileSearch

    .LookIn = "C:\Examples"

    .FileType = msoFileTypeExcelWorkbooks

      'There are wb's

      If .Execute > 0 Then

        For i = 1 To .FoundFiles.Count

          Workbooks.Open (.FoundFiles(i))

        Next i

      'There are no wb's

      Else

        MsgBox "There are no workbooks to open", vbOKOnly

      End If

  End With

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()

  'Close all open workbooks.

    Workbooks.Close

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)

  'Open wbname.

  Workbooks(wbname).Activate

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

  'Open workbook.

  Dim wb As Workbook

  On Error Resume Next

  Set wb = Workbooks(wbname)

  IsWBOpen = Not wb Is Nothing

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

  GetActiveSheet = ActiveSheet.Name

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()

  'Populate list box with names of sheets

  'in active workbook.

  Dim ws As Worksheet

  For Each ws In Worksheets

    ws.Select

    ListBox1.AddItem ws.Name

  Next ws

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)

  'Delete shtname.

  Application.DisplayAlerts = False

  Worksheets(shtname).Delete

  Application.DisplayAlerts = True

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)

  'Add or delete sheets to equal intSheets.

  Application.DisplayAlerts = False

  'Delete sheets if necessary

  While Worksheets.Count > intSheets

    Worksheets(1).Delete

  Wend

  'Add sheets if necessary

  While Worksheets.Count < intSheets

    Worksheets.Add

  Wend

  Application.DisplayAlerts = True

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.


Check out 10 Things... the newsletter

Get the key facts on a wide range of technologies, techniques, strategies, and skills with the help of the concise need-to-know lists featured in TechRepublic's 10 Things newsletter, delivered every Friday. Automatically sign up today.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
johnywhy
johnywhy

What if you know the worksheet VBobject, but not it's sheet name? How can you delete the sheet? Say the VBobject is Sheet1, and the sheet name is something else UNKNOWN. You cannot say Sheet1.Delete, that's not allowed. So how can you get the sheet name or sheet object, when all you know is the VB object behind the worksheet? Method 1: ThisWorkbook.Worksheets(VBobject.Name).Delete Explanation: VBobject.Name returns the worksheet name. Except this does not always work! SOMETIMES, VBobject.Name does NOT return the sheet name, it returns the VBobject name! Method 2: VBobject.Properties(7) ALWAYS returns the name of the worksheet. Yes, 7. Dim sSheetName as String sSheetName = VBobject.Properties(7) Worksheets(sSheetName).Delete

ilyaskazi
ilyaskazi

Opening workbooks With Application.FileSearch is dropped in Excel 2007 You should provide other alternative for Excel 2007 users as well. Rest are good collection to go with.. Thank you,

ssharkins
ssharkins

I don't have a 2007 alternative -- maybe a reader will supply one.

Laffs2k5
Laffs2k5

Here's my take on an 2007-friendly OpenAllWB alternative. This uses Microsoft Scripting Runtime to look for files: '****************************************************************************** '* Purpose : '* This procedure opens all Excel files found within a given directory. '* Input : '* strFolder, the folder to look for Excel files within '****************************************************************************** Sub OpenAllWBInFolder(strFolder As String) On Error GoTo ErrorHandler: Dim objFSO As Object Dim objDir As Object Dim objFile As Object Dim lngWbCnt As Long Set objFSO = CreateObject("Scripting.FileSystemObject") Set objDir = objFSO.GetFolder(strFolder) lngWbCnt = Workbooks.Count 'Loop all files in given directory, looking for Excel files For Each objFile In objDir.Files If objFile.Type Like "Microsoft Office Excel*Worksheet" Then Workbooks.Open objFile.Path End If Next objFile 'Warn the user if no files was found to open If lngWbCnt = Workbooks.Count Then MsgBox "There are no workbooks to open", vbOKOnly + vbInformation, _ "OpenAllWBInFolder" End If ExitProc: On Error Resume Next Set objFSO = Nothing Set objDir = Nothing Set objFile = Nothing Exit Sub ErrorHandler: Select Case Err.Number Case 5, 76 ' 5 = Invalid procedure call or argument '76 = Path not found MsgBox "Invalid path specified", vbOKOnly + vbInformation, _ "OpenAllWBInFolder" Case 429 'Can't create ActiveX 'Microsoft scripting runtime probably not installed MsgBox "'Microsoft scripting runtime' must be installed in order" _ & vbCr & "for this procedure to work.", _ vbOKOnly + vbInformation, "OpenAllWBInFolder" Case Else MsgBox "[" & Err.Source & "]" & vbCrLf & "An unexpected run-time" _ & " error occured '" & CStr(Err.Number) & "':" & vbCrLf _ & vbCrLf & Err.Description, vbExclamation, "OpenAllWBInFolder", _ Err.HelpFile, Err.HelpContext End Select Resume ExitProc: End Sub