Sunday, December 01, 2013

Using Selenium with Excel VBA

We had to change the names of 700 students in the student information system that I'm the product manager for at work. Instead of requesting that our support team write a database script, I figured it would be a good opportunity to try using Selenium (http://www.seleniumhq.org to rename the students using the browser user interface. In the past I have written VBA (Visual Basic for Applications) scripts to control Internet Explorer and I wanted to extend that approach to control Firefox and/or Chrome.

Excel VBA provides a very capable environment and the spreadsheet as an underlying model works very well for a class of problems. In this case we have a list of students already in the system and I added a couple of columns to contain the new first and last names. A simple loop through the rows using Selenium calls to manipulate the user interface and then record the results back in the spreadsheet is a straight-forward VBA macro.

Of course, without a VBA wrapper for Selenium this project would not go anywhere. I'd like to thank Florent Breheret for creating a wrapper and making it available as open source:

Viewing the source for the wrappers is important because some Selenium features are not implemented and other, non-standard, but helpful features are available (WindowHandles).

Selenium IDE for Firefox was invaluable. For my purposes, it's a script snippet recording solution that you then have to build your full scripts around using your environment of choice.

Stack Overflow was a helpful repository of answers for questions related to Selenium.

I ran into a problem with model dialogs, and ultimately didn't have to solve it because the script would complete what it needed to do with a simple On Error Resume Next. I may dig into this more at a later date. If I really get ambitious then I'll figure out how to edit/build the wrapper (requires C#).

After some debugging of the VBA/Selenium script (at the staging site first, and then with the production site), I was able to accomplish the renaming of 700 students without tedious manual effort. Creating Selenium scripts requires sleuthing the UI for the system you're trying to automate using Selenium IDE and the browser's tools. Of course if you already know the website and how it is coded it will be easier. If you have a site with frames and especially popups, you can expect it to take some time to have a script that works well. Also, plan to spend time making the script less fragile and smart about checking to make sure it is changing what you expect. For example, when changing the student names, I made sure to check the field that verified I was in the expected school district.

I did encounter one bug where the script would timeout. This happened after about four iterations of renaming students. The solution was to have the script logout of the system and then login every four renames. Definitely undesirable and if this continues to come up on future scripts I'll have to dig in more, but probably not without digging into C#.

I've just scratched the surface with Selenium for automation and of course it has great potential use for website testing. A future project will be to blend it with XCode to experiment with browser automation / testing from a mobile platform.

Saturday, June 15, 2013

Tips for DIY Websites for Non-Profit Organizations

With smaller non-profit organizations, often the primary owner of the website is a founder or key board member who has taken on the job of creating and maintaining the website as a Do-It-Yourself (DIY) effort. Often this decision is made because of lack of funding or perhaps because the person has a passion for communication and the web. The website (social media too, but a topic for another time) is a critical resource for a non-profit organizations to leverage.

This is an "evergreen" posting so web searches are suggested with some links that are current now. The intended audience is the current or potential website owner within the non-profit organization.

Design of the Website

There are good resources available on the web for this topic. Search "designing websites for non-profit organizations". This is a good article to review:
I suggest you lay out the organization of the site in rough form on paper first before jumping into building the website. Run the rough design of the website by key personnel in the organization to get feedback early before investing time building a website.

A rough site design - nothing fancy, but a big time saver!
Building the Website

There are many good tools for creating websites. I recommend choosing one that is completely web-based and does not require any installation on a local computer. The chances are that you will want to delegate creating/maintaining at least some parts of the website or pass on responsibility for the website some day. Having it with a service that can be shared with others easily will facilitate the process.

Search "website builder". Wikipedia has an article on them and would be a good place to start:
Searching "how to choose a website builder" does not turn up anything that I can recommend today. Many of the links are sponsored. Some of the "Top 10 Website Builders" may involve paid placement. My best advice is to stick with the ones that are popular and offer the features you want. Word of mouth recommendations are also a good source since you may be able to start a mutual support group with others using the same website builder.

I've used Google Sites in the past and am working with a non-profit that is using Wix now. The Wikipedia article referenced above is a good place to find other ideas. I'd stay away from professional level hosting solutions like Amazon Web Services and Rackspace. These are aimed at businesses with more technical savvy and resources than you will likely have either now or as you pass on the reigns to others in a small non-profit organization.

To help evaluate options make a checklist. My list looks like this:
  • Easy to use
  • Features (e.g. Templates, Calendar, Contact Form, PayPal Integration, etc.)
  • Affordable (<$15 per month, <$10 is better)
  • Allows others to share in posting/editing materials
  • Opinions of others who will help with the website
  • Good reviews by current users similar to your organization
  • Statistics (which pages are being visited, with what browsers, what search terms were used, etc.)
  • Platform support (does it look good on various browsers, desktop and mobile)
  • Backup (can I copy the contents to another location)
Once selected, try implementing the most complex portion of the site to see if the website builder will handle it well. See Testing the Website below for tips on checking the features with different platforms. Don't be concerned about revisiting the choice of the website builder, but once you've moved forward with one, learn it well to maximize the benefits. Understand the work-arounds for features that might not work exactly as you'd like them to work and share that knowledge regularly with any others using the website builder.

Testing the Website

So you've got your website looking pretty good on your system and you're ready to release it to the world! Today you can expect your site to be visited by people using a wide variety of "platforms" including desktop and mobile browsers. If you don't check how the site looks and works on some of the major platforms you'll be in for a rude surprise when your site is not visited.

First, an attitude adjustment. Your site may be the greatest, but don't expect a user to go to any trouble if it doesn't work reasonably well on their platform. The responsibility for a usable experience with your site rests on your shoulders. The site that looks wonderful in Windows Internet Explorer, but isn't usable on an iPad or iPhone, etc. could be a major problem depending on the target audience for your website. Mobile has become a critical platform to support now.

Search for "browser market share" to find out which browsers are popular. The Wikipedia article on the subject provides a great overview of browser market share and may be all that you'll need:
The following are the platforms that I would recommend testing on today. You can download and install the browsers by searching as indicated.

  • iPad / iPad Mini
  • Android Tablets (e.g. Nexus 7)
  • iPhone
  • Android Phones
  • Blackberry 10?
  • Windows Phone?
For Mobile testing you'll probably turn up any big problems by testing on an iPhone and Android phone. You can search "testing mobile websites" to find more resources available to consider. Certainly asking some others to check the website on their mobile phones is a good start.

Updating or Moving from an Existing Website

If your organization already has a website up and running and you plan to replace it with a new version of the website, consider running a "beta" version of the site for a while and ask others to try it out before doing a switchover of the public site. A common practice is to use a "subdomain" to point to your beta website until it is ready to be switched over to your public website. For example, if  your website is:
You might publish the beta of the website at:
This will make it easy for others that you tell to try out the beta without impacting the current public website.

What's Next?

Once you have the website up and running and working acceptably well on common platforms that potential or current members are using, you'll want to keep the site updated with fresh content and leverage it for various organizational goals including outreach for new members, retention of existing members, providing a resource for the community at large, processing donations, etc. Some of these will be topics for future postings.

Thursday, April 11, 2013

PLATO Access Lists

At work today we discussed the need for a relatively sophisticated permissions system for controlling access to various features at a site we are building. I realized that something similar to what we were talking about was designed and built way back in the early 1980s on the PLATO system. Here's an Antiques Roadshow tour of PLATO Access Lists using a game I worked on in college as an example.... (if you want the PDF version contact me).

Avatar: A Multi-Player Dungeons and Dragons Game

An Access List is a system-provided capability on PLATO that allows authors (developers) to create custom flags that can then be used to control access to various features of a lesson (program). The example shown here is for Avatar, a popular RPG on PLATO. Keep in mind that this was created in the early 1980s and 512 x 512 plasma (orange) panel screens were state-of-the-art at the time.

Thank you to for providing a working PLATO system!

In an Access List there are roles (e.g. Author, Operator) that have sets of individual permissions. In this screen the roles can be set for the individual indicated (mike / s or mike@s in more modern lingo) by pressing a capital letter (A, B, C, D, E) or individual permissions can be set by pressing 1,2,3,4.

These are the individual options listed under the Operator Options (#2 above). So you could set a user to have Operator access and then enable/disable individual flags for that user.

Individual options and sets of options (Author, Operator, etc.) can be custom defined by the Developer without any programming (next four screens). The Developer of course still needs to put hooks in their code to look at the Access List flags.

The Developer can edit the sets of options….

And the individual options…

That’s pretty much it! This is the main entry screen for Access Lists and may give you some other ideas.