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 likely 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); 

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 hierarchy and print out various things

  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.