Objects are simply collections of colon-separated key/value pairs with key-value entries separated from each other by commas. Objects can be arbitrarily complex storing values ranging from simple scalars to other objects. Objects can also store functions where functions attached to objects in this way are known as methods. This storage of data together with the functions that operate on that data is reminiscent of classes in more conventional object oriented languages.
To demonstrate some of the power of objects, consider the following frequently encountered scenario:
- How can one count the occurrences of distinct cell value in an input range?
- A secondary consideration might be how to filter out just those cell values that occur more than a certain number of times in an an input range.
In Excel, this can be done easily with pivot tables while in a database one can employ the COUNT() function in conjunction with a GROUP BY clause. Scripting languages such as Perl and Python have built-in objects known as hashes and dictionaries, respectively, to perform this sort of task. Excel VBA can be extended to include a dictionary object by referencing the Microsoft Scripting Runtime library through the Tools|References menu in the VBA editor.
Counting Occurrences Of Values
To run the following code as is:
- Create a Google spreadsheet
- Re-name or create a sheet and call it “input”
The code adds a new sheet called “output” and writes the output into columns A and B of the is sheet starting in the first cell “A1”. The code comments should clarify what is going on
- It introduces the for...in loop to enumerate the keys. The standard for loop is used to iterate over arrays but the for...in loop is required for objects.