VBA collection in javaScript

javaScript does not really have the equivalent of a VBA collection. Since the use of collections is very significant in this project, the best approach is  to emulate a collection object in javaScript so that the original code can remain largely as is. Note that a few other hacks are used by the code here, so you need a few of the other pieces of utility code covered throughout From VBA to Google Apps Script.You can find the code and test data in the VBA to Google Apps Script Roadmapper project download

This is now part of a Google Apps Script shared library you can incorporate in your own project

Usage

Here is a Google Apps Script/ javaScript procedure to test the collection object.
function myCollection() { 
  var co = new collection();
  
  co.add ("my dog","sushi");
  co.add ("my other dog","kenji");
  co.add ("my cat","ginger");
  co.add ("something with no key");
  co.add ("my monkey","chuckles");
  co.add ("my snake","viper");
  co.add ("something else with no key");
  
  // should fail as already exists
  //co.add ("my dog","sushi",true);
  
  // enumerate items by index number (note base 1 default like VBA)
  DebugPrint ('---',co.count(),'items by position');
  for (var i=1; i<= co.count(); i++) DebugPrint('item',co.item(i));
  // enumerate keys and data
  DebugPrint ('---',co.count(),'key,items by key');
  for (var k in co.keys()) DebugPrint(k , co.item(k));
  
  // delete some things
  co.remove("viper");
  co.remove("kenji");
  //delete the last one
  co.remove(co.count());

  DebugPrint('----','item, index presort by foreach');
  co.forEach ( 
    function(cItem,cIndex) {
        DebugPrint (cItem , cIndex);
    }
  )
  // sort it
  co.sort();
  DebugPrint('----','item, index postsort by foreach');
  // enumerate items by index number (note base 1 default like VBA)
  DebugPrint ('---',co.count(),'items by position');
  for (var i=1; i<= co.count(); i++) DebugPrint('item',co.item(i));
  // enumerate keys and data
  DebugPrint ('---',co.count(),'key,items by key');
  for (var k in co.keys()) DebugPrint(k , co.item(k));
  // sort it descending
  co.sort(function (a,b) { return (b>a); });
  DebugPrint('----','item, index postsort descending by foreach');
  co.forEach ( 
    function(cItem,cIndex) {
        DebugPrint (cItem , cIndex);
    }
  );
}  

Approach

A javaScript object has some kind of indexing of its properties, and one would assume this would be pretty efficient. Clearly then, if we could leverage that by making each key in the collection an object property, a big challenge would be solved. However, we also need to store the data object as well as the key, and we need to be able to access it by both key and index number, as per the VBA collection object.  We will also need to be able to  generate unique keys for items added to the collection with no key. Finally, we will need to (optionally, but by default) unobtrusively allow for the base to be 1 as per VBA.

Here are the private properties we'll need.
var collection = function(base) {
  var pBase = fixOptional ( base , 1);
  var pItems =[];
  var pKeys ={};
  var pLastUsed =-1;

Note that pItems[] is an array that will store the items added to the collection, whereas pKeys {} is an object we will add keys to, and they will become the properties of that object. The values associated with each property of pKeys will be the index of pItems associated with that key like this
pkeys = { key1 : 2, key2 : 3 , key5: 1 .... }

pLastUsed is to help quickly generate a unique key, and pBase stores the expected base (lower bound) for this collection , and is by default 1. See Dealing with optional arguments for usage of fixOptional.

Public properties & methods

In order to maintain the integrity of the collection, these are all private properties. Below are the subset of the exposed methods you are likey to need. In each case, just like the VBA collection, the key can be specified as a string corresponding to the key you provided when you added the item in the first place, or a number indicating its position in the collection. 

Note that this position starts at 1 by default as per VBA, but you can change the lower bound with an argument to the constructor, for example co = new collection (0);

 method  notes
 .item(key,complain) Return the item associated with given key (or index) or null if not found. Complain is Optional (true by default) and will generate a complaint dialog if item is not found.
 .count() Returns the number of items in the collection
 .add(item,key,complain) Add an item to the collection and associate it with given key, returning the item or null on failure. Key is optional, and if not given, will cause an obscure unique key to be generated. Complain is Optional (true by default) and will generate a complaint dialog if item already exists. Note there is no before/after option as in VBA.
 .keys() Unlike VBA, which does not allow you to access the keys in a collection, this will return the keys object which you can use to iterate through the collection.
 .remove(key,complain) Remove the item associated with the key (or index) from the collection, returning the index it was found at, or undefined on failure. Complain is Optional (true by default) and will generate a complaint dialog if item does not exist.
.forEach ( 
function(cItem,cIndex) {...})

This will execute your function once for every member of the collection, passing to it the item and the index of each item. For example

co.forEach ( 
    function(cItem,cIndex) {
        //do what you need to do in here
        Logger.log (cItem + ':index' + cIndex);
    }
  )

 .swap(a,b)  You can swap the position of the items a and b. As usual, a & b can be either keys or index numbers. This is not available in VBA
 .sort(yourComparison)  This will sort your collection. You need to provide a comparison function, if you are doing complex sort or if your collection is of objects. By default a comparison function will do an ascending sort.
Here is the default comparison function. 
this.sort = function (yourNeedSwap) {
  var swap = yourNeedSwap;
  if (!swap) swap = function (a,b) { return (a>b) };

Here is a simple example of how to provide a comparison function that causes a  descending sort.

co.sort(function (a,b) { return (b>a); });


 .findKey(k,complain)  Find the key of item k. Accessing the key of a collection item is not available in VBA.

Enhancements over VBA

In some cases there were things that wouldn't force fit to be the same as VBA, for example, sorting a collection needs some access to the internals of the collection structure that I didn't want to expose. I built in a few additional basic capabilities, as mentioned above, that anyway I believe are missing from VBA collections. Specifically

.sort ()
.findKey()
.swap()
.foreach()

On the other hand, I did not include the before and after option for .add(). These could be implemented later if needed - I never use them personally.


Enumeration

There are three ways - here's an example of logging their values and keys. Note that the sequence of enumeration by keys might vary on different browsers - or maybe not. The first method is likely to be the most predictable and efficient, and the third ( the magic of javaScript) will allow minimal changing of your VBA logic.


// enumerate items by index number (note base 1 default like VBA)
  for (var i=1; i<= co.count(); i++) Logger.log(co.item(i));
  // enumerate keys and data
  for (var k in co.keys()) Logger.log(k+":" + co.item(k));
  // or even better .. like this
  co.forEach ( 
    function(cItem,cIndex) {
        //do what you need to do in here
        Logger.log (cItem + ':index' + cIndex);
    }
  )

Here's how it is applied to run through the complete cDataSets heirarchy and print out various things
cDataSets.prototype.logIt=function(){
  
  DebugPrint('datassets',this.name(),this.dataSets().count() + ' datasets');
  this.dataSets().forEach(                // for each dataset
    function(dsItem,dsIndex) {
      dsItem.logIt();
    }
  )
};

cDataSet.prototype.logIt = function(){
  DebugPrint('datasset',this.name(),sad(this.where()));
  this.rows().forEach ( 
    function(drItem,drIndex) {         // for each row
      DebugPrint('row',drItem.row(),sad(drItem.where()));
      drItem.columns().forEach ( 
        function(dcItem,dcIndex) {     // for each cell
          DebugPrint ('item r,c',dcItem.row(),dcItem.column(),dcItem.value(),sad(dcItem.where()));
        }
      )
    }
  )

  this.columns().forEach ( 
    function(doItem,doIndex) {         // for each column
      DebugPrint('column',doItem.column(),sad(doItem.where()));
    }
  )  
  this.headings().forEach ( 
    function(dhItem,dhIndex) {         // for each heading
      DebugPrint('heading',dhItem.value(),sad(dhItem.where()));
    }
  )
  
};

Complete code

For more like this, see  From VBA to Google Apps Script . Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. Much of  the code referred to here is the mcpher library. You can find out how to include it in your project here


Transitioning is covered more comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly.








Comments