Introduction to Collections in Excel VBA

Home/Excel VBA/Introduction to Collections in Excel VBA

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

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates



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:

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:

mongopono.ru – Collections

LIMITED TIME OFFER
By |August 25th, 2013|Excel VBA|1 Comment

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

One Comment

  1. Sunil BS July 19, 2017 at 9:39 PM - Reply

    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,

Leave A Comment


Related pages


msgbox syntax in vbexcel vba applicationprotect password excelupdate query in vbaunprotect excel workbook 2013active x checkvba variable in rangeexcel sheet formulas with exampleeasy vbainsert row on exceltop oop interview questionsmacro commands in excelmacro excel for dummieslearn vba from scratchautofit excel 2013excel formula operatorsexcel macro range cellshow to merge cells on excelwhat does vlookup meandelete duplicate recordsvba activeworksheetvba recordsetsql basic questions and answersexcel vba macrosvba cell interior colormacros vba excelexcel select rowsduplicate sheet in excelfind duplicates in excel listvb.net interview questions and answers pdfvba progress bar exampleexcel vba unhide all sheetsexcel findbvba excel copy rangesoftware project plan template excelplanning templates excelexcel msgbox yes noexcell shortcutsrgb vba excel3d excel graphexcel vba last cellvba commandshow to create a vlookup in excel 2007combining chart types in excelshow developer tab in word 2010how to start macro in excelexcel remove doublesmicrosoft office project management templatehyperlink excel formulaexcel macro for vlookupprocedure writing worksheetvba create tablesql data mining tutorialvba copy worksheet to another workbookhow to use countifs in exceldashboard templates exceldeveloper tool in excel 2007fso.deletefilehow to delete a sheet in excelexcel vba current worksheetunmerge cell excelhow do you delete cells in excelhow to create a spider web chart in excelremove blank rows in excelhlookup function in excel with examplehlookup formula in excel with exampleclose word document vbaexcel vba moduleexcel downloaderexcel vba end of columnms excel 2007 formulas with examples pdf in hindipassword protect excel 2010 spreadsheetsql interview questions and answers pdf downloadvba msgbox inputselect sheet excel vbadashboard report excelaccess vba sqlexcel vba sheet name