Most of the time you do three things with collections: All you have to do is add an item or remove it.Ĭollections are much easier to use than arrays especially if you are new to programming. You don’t have to specify the size or allocate new spaces. When you add or remove an item to a collection VBA does all the resizing for you. ' Declare Dim coll As New CollectionĬoll.Add "Pear" ' remove item - VBA looks after resizing So for this example using a collection would be better. This is very inefficient and quite messy to do. You could also resize the array for each item as it is added. If you read 50 students from a max of 1000 then you would have 950 unused array slots. The problem is you would have a lot of empty slots and would have to add code to deal with these. You could create an array of the biggest possible size. Therefore you do not know what size array to create. So in this example the number of students is not fixed and changes a lot. Here you do not know the number of students in advance. Imagine also that students can be added or removed from the list as the application runs. In other words, you will not know how to select a student until you read their details from the worksheet. For example, only the students from the USA or England that study Maths or History. In this example we have the same student worksheet but this time we only want students with a given criteria. Let us now look at a second example where we don’t know the number of items in advance ' Create array of correct size Dim arr() As Long ReDim arr(1 To lStudentCount) LStudentCount = Sheet1.Range( "A" & Rows.Count).End(xlUp).Row We can then use this to create an array of the correct size: ' Get last row - this is the number of students Dim lStudentCount As Long In the example code you can see that we get the number of students by counting the rows. In other words, you know the number of items in advance. In this example, you can easily count the number of rows to get the number of students. You want to store information about each student. Imagine you have a worksheet of student marks with one student per row: This means that you know the size before you start adding elements. The main difference is that with an array you normally set the size once. So what is the difference and why use one over the other? We have looked at what collections and arrays have in common. ' This line is used to add all the values ' Read 100 values to collection Dim c As Range If we rewrite the above example using a collection then we only need a few lines of code: ' Create collection Dim collMarks As New Collection Using a loop with a collection or with arrays means you only need one line for add or reading values. When you use a collection or array you only need to declare one variable. ' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Longĭim mark100 As Long ' Store the marks from the worksheet in a variableĪs you can see in the above example, writing code like this would mean hundreds of lines of repetitive code. If you want to store 100 marks then you need a line of code each time you want to store a value to a variable. 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. Collections are a very important part of VBA.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |