Excel VBA Tutorial – #6 Collections of Workbooks, Sheets and Cells

[IMAGE] Excel CollectionsSummary: 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

«« Previous … | … Next »»

Download the Sample Workbook

[Image] Download Workbook Download the sample file with VBA code
Excel-VBA-Collections.xlsm (99 Kb)

#1 – The Importance of Objects

» Back to contents

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…

[IMAGE] 2-excel-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.

[IMAGE] 3-brick-house-reduced-fileCells (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).

The 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

» Back to contents

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:

[IMAGE] 4-excel-main-collections

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

» Back to contents

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

» Back to contents

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.

[IMAGE] 5-cells-selectBut 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

» Back to contents

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

» Back to contents

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

» Back to contents
[IMAGE] Daniel LajosbanyaiDaniel 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!

Victor Chan
Victor runs Launch Excel to help you master Excel and VBA. He has used Excel a lot since 2002. He's a Chartered Accountant (Fellow of the ICAEW) with a Masters in Manufacturing Engineering from the University of Cambridge... and has a passion for teaching others. Enjoy his tutorials!
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recent Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.