In VBA (Visual Basic for Applications), the Workbooks object represents a collection of all Excel workbooks that are currently open. Think of it as a list that contains every open Excel file. Within this collection, each individual workbook is represented by a Workbook object (note the singular form, without the “s”).
There are several ways to refer to and work with a specific workbook, depending on your needs:
- ThisWorkbook: This refers to the workbook in which the currently executing VBA code resides. No matter which workbook is active on the screen, ThisWorkbook always points to the file containing the running code. In this context, it is often referred to simply as this workbook.
- ActiveWorkbook: This refers to the workbook that is currently active or in focus—in other words, the one that the user is interacting with at the moment. Be aware that the ActiveWorkbook might be different from ThisWorkbook if the user switches to another file while code is running.
- Workbooks(Index): This method accesses a workbook by its position in the Workbooks collection. The Index is a numeric value indicating the workbook’s order in the list, starting from 1 (VBA uses 1-based indexing). For example, Workbooks(1) returns the first opened workbook. You can use the Count property (e.g., Workbooks.Count) to find out how many workbooks are currently open, which also tells you the maximum valid index.
- Workbooks(« Name »): This lets you refer to a specific workbook by its name, written as a string (in double quotation marks). For instance, Workbooks(« Sales2024.xlsx ») accesses the workbook named “Sales2024.xlsx”, assuming it is currently open.