How to use VBA Collections in Excel office 365? – Quick Steps!!
Tags: tousevbacollections, vbamacros
Published: 2021-09-13 14:14:04

---
title: How to use VBA Collections in Excel office 365? – Quick Steps!!
published: true
date: 2021-09-08 09:20:38 UTC
tags: ToUseVBACollections,VBAMacros
canonical_url: https://geekexcel.com/how-to-use-vba-collections-in-excel-office-365-quick-steps/
---

In this article, you will learn  **how to use VBA Collections in Excel Office 365**. Let’s see them below!! Get the official version of  **MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

## Collections

- A **Collection is a data type** that holds **stores objects** just like arrays (but different).
- While writing basic **VBA codes** , you must have used **Workbooks, Worksheets, Ranges** etc.
- You can **add, find, fetch and delete items** from collections.
- Now, the collection has **4 properties or methods** associated with it.
  - Collection.Add
  - Collection.Item
  - Collection.Count
  - Collection.Remove

## Create a Collection in VBA

- Firstly, in the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- Now, you have to  **copy and paste the code**  given below code step-by-step.

### Instant Creation

**Syntax:**

```
 Dim items as new Collection 
```

The object is **instantly created** , and you can **start using this collection** for your requirements immediately.

### Delayed Creation

**Syntax:**

```
Dim items as Collection
Set items = New Collection

```

- In this method, you have to create a **reference of collection** and when required you need to **initialize with a collection object**.
- Using this method, you need to **create the**  **collection based** on some criteria.

### Adding Items to a Collection (Collection.Add)

- Here, you have to use **Add method** to **add new items** to a collection.

```
 items.Add "Apples" 
```

- Then, the above **code** will **add string “Apples”** in the collection.

```
 items.Add 1.4 
```

- Now, the above **code** will **add double 1.4** to the **end of collection**.
- After that, you can also **define keys** to these items and these **keys** can be used to **access the items** in collection.

### Inserting Items in Collection Before or After an Existing Item

- Here, the Add method adds new items in the end of the collection.
- Then, the add method has four parameters.

```
 Add(item,[Key],[Before],[After]) 
```

- Now, if you want to **add some item before or after** some particular **index/key** , then you have to use **Before and After parameters of Add method**.

```
items.Add True, "b", "cnst"

'Or

items.Add True, Before:="cnst"

```

- After that, both of the **lines** will **insert a boolean value** before key **cnst** in the VBA Collection items.
- To **insert an item** after a particular **index/item/key** , you need to use **After parameter of Add method**.

```
'inserting "Mango" with key "m" after key "cnst"

items.Add "Mango", Key:="m", after:="b"

```

- Now, the above code will insert **“Mango” with key “m” after key “b”**.
- After that, you need to  **save the code**  by selecting it and then  **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Save-the-code-23.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- You need to choose the  **Macros option**  in the Code section.

![Choose Macro option](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- Then, you have to make sure that your  **macro name is selected**  and click the  **Run ** button.

![](https://geekexcel.com/wp-content/uploads/2021/09/Run-the-Code-11.png)<figcaption>Run the Code</figcaption>

**NOTE:** The keys of items should be unique. Any key that already exists in the collection will not be accepted. VBA will return an error that “the key is already associated with an element of this collection” or you will get “Subscript out of range”.

- Finally, you will receive **output** as a dialog box.

![](https://geekexcel.com/wp-content/uploads/2021/09/Dialog-Box.png)<figcaption>Dialog box</figcaption>

### Access Items of Collection

- There are two methods of accessing the items of a collection.
  - CollectionName (“index/key”)
  - CollectionName.Item(“index/key”)
- For example, to **access a member** in a collection item at **index 1 and key “apl”,** you have to use any of below **VBA codes**.

```
Debug.Print items.Item(1)

--

Debug.Print items(1)

--

Debug.Print items.Item("apl")

--

Debug.Print items("apl")

```

## How to get count of items in a collection?

- To get the **total number of objects or items** in a collection, you need to use **.Count function** of collection.
- The **.Count method** returns **total number of items** in collection.

```
 items.Count 
```

- Now, the above line will give the **count of objects in the collection**.

## How to access all the items of a collection?

- Firstly, the only way to **access all the items** of the collection in VBA is **loops**.
- You have to iterate through **each item of collection** to access them.

### Looping Through a Collection in VBA

- There are various ways to **loop** through the collection in VBA.
- You need to use most elegant **for each loop to iterate** through the collection.
- To loop through **each item of a collection** , you have to use this line of VBA code.

```
 
For Each itm In items
Debug.Print itm
Next itm

```

### Removing Items From a Collection

- To **remove an item** from a VBA collection, you need to use **Remove** method.
- The remove method takes **one argument** , and it can be the **index or the key of item** (if available).
- Now, you have to **copy-paste** the following the code.

```
items.Remove (1) 'using index
--
items.Remove 1
--
items.Remove ("apl") 'using key
--
items.Remove "apl"

```

### Deleting Collection in VBA

- You need to **delete a collection** in VBA.
- Then, you have to **remove all the items** of that collection by setting it again as a new collection.
- Now, you need to copy-paste the following the code.

```
 Set items = New Collection 
```

- Finally, this **line of code** will **create a new collection** on the address of items.

## End of the Era

In the above article, you can learn the simple steps on **how to use VBA Collections in Excel Office 365**. Kindly, share your  **feedback**  in the below comment section. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!

**Read Next:**

- **[How To Use VBA Class Modules in Excel office 365?](https://geekexcel.com/how-to-use-vba-class-modules-in-excel-office-365/)**
- **[How to Delete Column in Microsoft Excel 365?](https://geekexcel.com/how-to-delete-column-in-microsoft-excel-365/)**
- **[How to Hide Sheet in Microsoft Excel 2007?](https://geekexcel.com/how-to-hide-sheet-in-microsoft-excel-2007/)**
- **[Options in Quick Access Toolbar in Microsoft Excel 365](https://geekexcel.com/options-in-quick-access-toolbar-in-microsoft-excel-365/)**
- **[How to Create an Array of Numbers in Excel Office 365?](https://geekexcel.com/how-to-create-an-array-of-numbers-in-excel-office-365/)**