If you didn’t use collections or arrays you would need to create a hundred variables – one variable to store the mark for each student.Īnother problem is that you have to use these variables individually. Imagine you want to store the marks of 100 students. However most of the time you will have more than one student to deal with.

If you were storing the marks of one student then you can easily do this using a single variable Dim mark As Long I will briefly recap this information here. In my post on arrays, I explained in simple terms what arrays are and why they are so useful. a list of student marks or country names. Using a collection or array allows you to quickly and easily manipulate a large number of items. They both store a set of similar items e.g.

( Note: Website members have access to the full webinar archive.)Ĭollections and arrays are both used to group variables. If you are a member of the website, click on the image below to view the webinar.

' Print the full name of the workbook that was opened second Debug.Print Workbooks(2).FullNameĬollections are similar to arrays so it is important to understand what they are and how the differ to arrays. ' Print the full name of the workbook called Example.xlsm Debug.Print Workbooks( 'Example.xlsm').FullName The following code shows some examples of using the VBA Workbooks collection: ' Workbooks is a collection of all open workbooks ' Count is the number of workbooks in the collection Debug.Print Workbooks.Count The most common ones are the Workbooks, Worksheets, Range and Cells collections. If you have used the language for any length of time then you will have used Collections. 16.3 Returning a Collection From a FunctionĬollections are a very important part of VBA.16.1 Passing a Collection to a Sub/Function.

16 Using Collections with Functions and Subs.13 Check if an item exists in a Collection.12.2 The Shortcoming of Using Keys in Collections.10.1 Are Items in a Collection Read-Only?.7.1 Minor Difference Between These Methods.6.2 Example Where a Collection is Better.

Visual basic for excel cheat sheet