VBA & Beyond: March 2012

I am getting familiar with Google Spreadsheets and Google Apps Script.

For a sheet used for expense tracking I needed a findFromRight function in Google Apps Script.

I let myself be inspired from the Excel VBA world and found something from Chris Rae's site that could be modified:

	// inspired by Chris Rae's VBA site: http://chrisrae.com/vba/

	function findRight(findIn, findWhat) {
	var findLoc=0;
	for(findLoc=(findIn.length - findWhat.length + 1);findLoc>=1;findLoc--)
	{
	if (findIn.substring(findLoc,(findLoc + findWhat.length)) === findWhat) {return findLoc;}
	}
	return -1;
	}

view raw findFromRight.js hosted with ❤ by GitHub

To add testing to this Google Apps Script, I decided to try out the GAS-Unit testing framework.

	var q = 'http://gas-unit.googlecode.com/svn/trunk/gas-unit/gasUnit.js';
	var s = UrlFetchApp.fetch(q).getContentText();

	eval(s);

	function runFindRightAssertions() {

	var testFixture = new TestFixture('Tests on findRight function');

	testFixture.addTest(
	"third space character in string returns 2",
	function () {
	this.assertAreEqual(2,findRight("VB ", " "));
	});

	testFixture.addTest(
	"not containing character returns -1",
	function () {
	this.assertAreEqual(-1,findRight("VB ", "A"));
	});

	testFixture.addTest(
	"searching empty string returns -1",
	function () {
	this.assertAreEqual(-1,findRight("", "A"));
	});

	testFixture.addTest(
	"flipped order search returns -1",
	function () {
	this.assertAreEqual(-1,findRight("BA", "AB"));
	});

	testFixture.addTest(
	"searching partially matched string returns -1",
	function () {
	this.assertAreEqual(-1,findRight("BAQ", "BAQH"));
	});

	testFixture.addTest(
	"complete match returns 0",
	function () {
	this.assertAreEqual(-1,findRight("BAQH", "BAQH"));
	});

	var retVal = testFixture.runTests().getResultsSummary();

	Logger.log(testFixture.createTextTestReport());
	//MailApp.sendEmail("myname@example.com", "test report", Logger.getLog(), {htmlBody: testFixture.runTests().createHtmlTestReport() });

	return retVal;

	}

view raw FindRightTests.js hosted with ❤ by GitHub

There are two ways to view the test results. The code above only prints to the log view (View-> Logs in the menus). It is also possible to receive the test result as a formatted report per mail (by including the outcommented line and updating the email address).

All in all, I find that using the testing framework can be of good help to establish that the functions are working as expected (and keep working as expected when redesigned).

I am getting comfortable with changing from Excel 2003 to Excel 2010.

One difference that I have noticed in the two versions, is how the array formula entry behaves on already existing arrays (pressing ctrl-shift return).

In Excel 2003, after array formula entry, the current array gets selected.
In Excel 2010, after array formula entry, the current cell gets selected.

Please see illustrated example below:

Excel 2003

A basic array formula is entered