Excel VBA Tutorial – #6 Collections of Workbooks, Sheets and Cells
Last updated on November 26, 2018 By Victor Chan
Summary: If you followed our Excel VBA tutorials 1 – 5, you’ll now have a good grasp of macro programming basics. It’s time to go deeper into the Excel VBA world to understand collections.
Let’s find out how collections of objects like Workbooks and Sheets relate to each other. We’ll also code actions on objects inside collections, like open a workbook and activate a sheet. This stuff is very important… don’t skip this tutorial if you want to learn VBA properly!
Difficulty: Beginner
Download the Sample Workbook
Download the sample file with VBA code
Excel-VBA-Collections.xlsm (99 Kb)
#1 – The Importance of Objects
When we use a computer we are typically working with objects such as icons, menus, files, folders and applications in the operating system.
These things are all objects of the system and they live inside a multi-level hierarchy. Let’s think about the Microsoft Excel application and hierarchy…
… when you open Microsoft Excel, the main active object will be the Excel application itself. The application is the highest level in the hierarchy, and every other object is at lower levels.
How many levels can we find in an application? It depends on the complexity of the program. Most programs have toolbars, menus, and in-built functions.
Additionally in Excel there are workbooks, worksheets, cells, and more.
Note: Workbooks are the files you see in the file manager on your HDD/SSD with extensions like “.xlsx”, or hopefully more often macro-enabled “.xlsm” now that you’re learning Excel VBA.
Imagine a house
Let’s picture a typical brick house. It has rooms with walls that are built with bricks.
Now let’s imagine an Excel workbook and compare it to the brick house.
The workbook is like the house, the sheets are like the rooms, and the cells are like the bricks.
Cells (bricks) are arranged in rows and columns, like bricks are arranged in an ordered way to make walls.
- House = Workbook
- Rooms = Sheets
- Bricks = Cells
You can walk through the house and go into any room (unless it’s locked) just like you can open an Excel workbook and browse through any sheet (unless it’s protected).
he active sheet is like the room you’re currently standing in. And if you’re a normal Excel user who clicks to get around in Excel, you can only work on the active sheet and the active cells or range.
But… if you are a VBA programmer you don’t have to personally enter the room and touch the wall to work on it. Moreover, you don’t even need to be in the house!
Are you intrigued?
The Magic of VBA
Can you imagine seeing the whole neighborhood from above, and instantly changing the color of the wall paint in any house you want?
And can you imagine doing that without personally walking into that house to find the room to paint the wall while standing in front of it?
Basically, that is what programmers can using Excel macros when they know about objects and collections.
They can make change to workbooks, sheets and ranges without personally clicking on cells and entering data. It’s just like magic!
In the rest of this tutorial we’ll look at the collections of Workbooks, then Sheets and finally Cells (known as ranges).
#2 – Workbooks
To be able to do this “magic”, we use “collections”. Let’s start with one of the collections, the workbooks!
As you can see, the noun “workbooks” is in plural form. Many objects in Excel can only be referenced in plural form: Sheets, Cells, Rows, etc. These type of objects are the so called collections.
Plural collections cannot be referenced in singular form – it is a syntax error, and will give you an error message.
Here is a list of the main collections of Excel:
If you want to activate an open workbook called “Task”, then you do it with the following command:
Workbooks("Task").Activate
This is a reference to the item named “Task” in the collections of Workbooks. All open workbooks are stored in the memory addressed to the Application.
Opening a closed workbook with macros
What happens when the workbook you need is not open yet? You don’t have to search in the file manager and open it manually!
Here is an example of how to do it with a macro:
Sub OpenWB() Dim sPath As String, sFile As String Dim wb As Workbook sPath = "C:\User\Documents\Macros\" sFile = sPath & "TestOpenWB.xlsm" Set wb = Workbooks.Open(sFile) End Sub
This can be helpful when your macro should open another workbook and take some info from that external file.
It’s excellent for repetitive tasks – I use a similar macro to get the currency exchange rate from a “currency.xlsx” file on the server.
Note: You must of course modify the path and filename in the code to suit your needs!
So, Workbooks are the collection of Excel files containing sheets and cells objects. Let’s get to the sheets now!
#3 – Sheets
Declaring constants
Sheets are the “walls” of our Excel “house”. The collection of “sheets” contains both “chart sheets” and “worksheets”. Chart sheets can only hold charts, whereas worksheets have rows and columns for data and can also hold charts and drawing objects.
Older Excel versions, like 2010 and 2013 have always started with 3 worksheets in every blank document you open.
Now, in version 2016, you typically get 1 worksheet when you open a new document. (Note: you can also change the default number of worksheets in Excel options)
You can of course add almost as many sheets as you just need – the limit is the amount of RAM memory in your computer.
Worksheets contain cells, and therefore they are more often used in VBA programming than the higher lever workbooks. Workbooks can be open or closed, or activated, but with worksheets you can do a lot more.
You can activate sheets using their name:
Sheets("Sheet1").Activate
Or you can reference a sheet using its index number:
Sheets(2).Activate
Sheets are a great example of collections, because of the numerous actions you can do with them: count, rename, delete, add, change order, export, copy, move, etc…
You can find a list of actions and properties for sheets at this Microsoft support article on the Excel Sheets Object. And you can find a list of actions and properties for worksheets in this article on the Excel Worksheets Object.
#4 – Cells
Cells are the “bricks” of our Excel “house”. They build up every sheet, and they store the information you type in. You can reference them using their address, which is defined by rows and columns:
Cells("A1").Activate
Note that I only activated one specific cell, but the collection is still in plural form “Cells” – because it is a collection, and I need only one item from it.
You can of course select a group of cells as well:
Cells("A1:B5").Select
This way the defined range will be selected – so they can be copied or they can have values written into them.
But the most efficient way to use cells in the code does not need us to select them at all:
Range("A1:B5").Value = 1
or
Range(Cells(1, 1), Cells(5, 2)).Value = 1
With this command you write a “1” in every cell of the specified range without selecting it. This is executed much faster by the computer.
Why isn’t the word “Range” in plural form? Because it is a special collection, and it is used in singular form.
One more interesting thing in the second line of code – I did not reference columns as letters, but as numbers. I normally prefer this alternative because you can easily add variables in place of the numbers. When working with loops and variables, this feature comes in very handy!
“Range” is also used often in coding because most of the time you don’t work with single cells, but groups of them. Using the Range collection, you can specify your own desired range of cells to run macro commands.
#5 – Rows and Columns
These are the two dimensions that specify the cell address on the sheet. But this is not their only function. These collections can be used alone as well, if you want to work with every cell in a row or in a column.
They can be referenced like this:
Rows(3).Select Columns(3).Select
Here is how to delete rows 1-5 and columns B-C:
Rows("1:5").Select
Selection.Delete Shift:=xlUp
Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
And the shorter, more professional version without the selection:
Rows("1:5").Delete
Columns("B:C").Delete
These are basic examples you can use in your macro writing routines.
If you ever wondered, how many rows and columns are in Excel, you do not have to count it manually (or with a macro) There are 1,048,576 rows and 16,384 columns in Excel version 2016. And you can find more in this Microsoft support article on Excel specifications and limits.
#6 – Summary
In this tutorial we got familiar with the most often used collections of Excel VBA. We now have a basic knowledge of their hierarchy (Application > Workbooks > Sheets > Cells).
Always make sure you use the proper syntax when referencing objects!
And from now on, please try to stop using the “Select” command, and instead work directly with the actual objects in collections. This is a big step in VBA programming and now you know enough to start using this method on collections of workbooks, sheets and cells.
#7 – About the Author
Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)
To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.
Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!
Related Posts:
Hey, I'm Victor Chan
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.