feedfasad.blogg.se

1. #What is a macro in excel and how does it work full
2. #What is a macro in excel and how does it work code

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.

#What is a macro in excel and how does it work code

' 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.

#What is a macro in excel and how does it work full

( 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.