Collection in Excel VBA helps to loop through the group of items:

Collections in Excel VBA
In This Section:

Introduction to Collections in Excel VBA?

Collection is an object contains group of objects having similar characteristics (with same properties and methods). For example,if you want to loop through all worksheets in a workbook, you can refer worksheets collection of the workbook and do whatever you want to do with that particular worksheet.

Collections in Excel VBA

Introduction to Collections in Excel VBABelow are the most frequently used Collections in the Excel VBA:

CollectionsUse
AddIns CollectionWe can loop through the installed and available add-in in your Excel. You can use it for finding all available add-ins
ChartObjects CollectionWe can loop through the all the charts in a Worksheet or workbook. You can use it for performing some thing with all or some of the available Charts in the Workbook or Worksheet
Charts CollectionIt is similar to the above collection
Comments CollectionWe can loop through the all comments in the workbook and we can change any properties of all or some comments
HPageBreaks CollectionWe can loop through the Horizontal page breaks and change the settings
Hyperlinks CollectionWe can loop through the all hyperlinks in the workbook and change the properties
Names CollectionWe can loop through All names and change the properties
OLEObjects CollectionWe can loop through all ActiveX controls and change the properties
PivotCaches CollectionWe can loop through the all pivot caches in the workbook and change the properties
PivotTables CollectionWe can loop through all the pivot tables and change the formats and other properties
Range CollectionWe can loop through all ranges
Shapes CollectionWe can loop through shapes including chart in workbook and change the properties
Sheets CollectionWe can loop through all sheets in the workbbok
Windows CollectionWe can loop through all windows
Workbooks CollectionWe can loop through available or opened workbook and access their data or change its properties
Worksheets CollectionWe can loop through all worksheets in the workbook and change its properties

Examples Macro File On Collections

Example 1: This code will loop through the Worksheets in a Workbook and display the names of the worksheets
Private Sub CommandButton1_Click()
Dim sh
For Each sh In ThisWorkbook.Worksheets
MsgBox sh.Name
Next
End Sub
Example 2: This code will loop through the Chart objects in a Worksheet and display the names of the Chart Objects
Private Sub CommandButton2_Click()
Dim cht
For Each cht In ActiveSheet.ChartObjects
MsgBox cht.Name
Next
End Sub

Download File:

ANALYSIS TABS – Collections

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!