What's Hot at TechRepublic • • • • 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. Figure A 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.
With Excel, your Office moves. Create, duplicate, hide, and unhide sheets easily. Nicktoons. Tell a story with your dataInsert common charts to bring your data to life.
Figure B 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.
Collaborate for free with an online version of Microsoft Excel. Save spreadsheets in OneDrive. Share them with others and work together at the same time. Excel offers myriad options for referring to workbooks and sheets in your VBA code. See which methods make sense in which situations.
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. Vcd Cutter. 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 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).