Excel as an IDE

I’ve often found, especially when starting at a new client, that real development tools aren’t provided for testers (even automation testers) as standard and have to be requested from the helpdesk. This can take a week or longer to sort out, depending on efficiency. It’s worse in financial institutions who seem to think Admin rights are golddust, who lock down online storage and usb ports, who scan and validate every email. That’s their prerogative, but if you’re going to pay me to do my job, maybe make it so I actually can rather than putting security in the way. A lot of it is foolish, I ask for read access to a customer database in a test environment. Now as long as you’re not using live customer data, there’s virtually no risk to granting that, it’s a 5-minute job – so why does it alway’s take a week and 3 managers to approve it?

In any case, my workaround for this is MS Excel.

There are a number of reasons for this:

  • Because of the structure of a workbook, and a worksheet, into tabs, rows and columns, it’s easy to visualise data structures like lists and arrays.
  • Because rows and columns provide an easy structure to navigate around in code
  • Because I have used Visual Basic for a long time, and Excel’s VBA is probably the easiest variant of this to use
  • Because every client I’ve ever been to has Excel.
  • Because you don’t need admin rights to run macros’ typically.

This article is the first in a planned series showing how to use Excel to perform some tasks which would take a long time when done manually, or just be incredibly boring (You’ll know by now that I can’t handle tedious repetitive tasks I expect).

Getting Started
To begin with the VBA development environment is accessed through Alt + F11, or the Tools >> Macro >> Visual Basic Editor menu items.
then: Insert >> Module.

I generally don’t prepare functions, preferring to use subroutines or “sub”s.

The main difference between a VBA function and a VBA subroutine is that a function returns a result, whereas a subroutine does not.

Ok so let’s look first at reading values in cells into a variable. This is my dataset

Bob Jenkinson Mr Mr.
Edgar Ross Monsieur Mr.
Ross Neville De heer Mr.
Jason Shearer Mr Mr.
Charles Shilton Monsieur Mr.
Joey Ramone De heer Mr.
Paul Hartley Mr Mr.
Richard Edwards Monsieur Mr.
Edward Richards De heer Mr.
Jacob Wolf Mr Mr.
Tony Tucker Monsieur Mr.
Dean Chester De heer Mr.

I had that stored in an excel spreadsheet, and I need to navigate through it to build the excel table in HTML so I can paste it here. Which is handy since it also illustrates my point.

The code:

Sub build_table_html()

strHeader = "<tr>"

strLeft = "<td>"
strRight = "</td>"
strFooter = "</tr>"

For i = 1 To 12  '12 Rows
For j = 1 To 4  '4 Columns

strItem = Cells(i, j).Value 'A single cell item at position i,j - row first, column second
'Append the cell boundaries and add to the strOutput string.
strOutput = strOutput & strLeft & strItem & strRight & vbCrLf

Next j 'column
'At the end of each column, build the output for that entire row of columns
strRowOutput = strRowOutput & strHeader & strOutput & strFooter & vbCrLf
strOutput = "" 'Reset strOutput

Next i

'This dumps the output into the debug log
Debug.Print strRowOutput

End Sub

From the debug log I can just select all and copy paste into my html editor, and it’s done. It’s a fairly trivial example I’d admit, but as a performance tester I often work with vast test datasets and the ability to traverse through them extracting and manipulating them is hugely beneficial in both timesaving and boredom-reduction. More on this later…

You can leave a response, or trackback from your own site.

Leave a Reply

Powered by WordPress and ThemeMag