Share Post
Collection in Excel VBA helps to loop through the group of items:
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
Below are the most frequently used Collections in the Excel VBA:
| Collections | Use |
|---|---|
| AddIns Collection | We can loop through the installed and available add-in in your Excel. You can use it for finding all available add-ins |
| ChartObjects Collection | We 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 Collection | It is similar to the above collection |
| Comments Collection | We can loop through the all comments in the workbook and we can change any properties of all or some comments |
| HPageBreaks Collection | We can loop through the Horizontal page breaks and change the settings |
| Hyperlinks Collection | We can loop through the all hyperlinks in the workbook and change the properties |
| Names Collection | We can loop through All names and change the properties |
| OLEObjects Collection | We can loop through all ActiveX controls and change the properties |
| PivotCaches Collection | We can loop through the all pivot caches in the workbook and change the properties |
| PivotTables Collection | We can loop through all the pivot tables and change the formats and other properties |
| Range Collection | We can loop through all ranges |
| Shapes Collection | We can loop through shapes including chart in workbook and change the properties |
| Sheets Collection | We can loop through all sheets in the workbbok |
| Windows Collection | We can loop through all windows |
| Workbooks Collection | We can loop through available or opened workbook and access their data or change its properties |
| Worksheets Collection | We 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:
Related Posts
One Comment
Leave A Comment
You must be logged in to post a comment.
Hi ,
Thanks for sharing your knowledge and helping many people like me.
I am a novice in this.
I would like to know more about Objects in VBA; when to use them and how to invoke them. Could you please help
Thanks,