Excel in Testing

As excel is the de facto tool of any tester, usage of excel starts from test planning, test case creation, test execution and most importantly test reporting, even if we use other tools like MS -Word, web forms, rich text email, or other customized sophisticated technologies, testers may directly or indirectly use MS-excel in test reporting and thus, knowing excel or excelling in excel is one of the key responsibilities of tester. Usually even commercial tools have the option of exporting the test execution status to spreadsheets.

Test reporting without any functions or conditions: This is just copy paste or data entry operations of entering pass or fail or blocked against tests and filling corresponding bug numbers against failed test cases. Though this method is simple, it is time consuming and definitely is not the smarter way of working.

Test reporting with functions and conditions: In this way, we can write macros for copying the cell data across spreadsheets in a workbook, change cell colour based on test status,

refresh the whole workbook on periodical basis, summarize test results using aggregate functions and if conditions--all using single macro click.

Macro programming starts with simple formula entering in the cells and it goes up to complex function calling using VBA.

Personally I have used formula like,

=COUNTIF (Test_Area_Login! C3:C100,"P")

Here Test_Area_Login is the name of the worksheet (which obviously contains Login related test cases), this formula tells that count the number of 'P' entry between the cells C3 and

C100, so between C3 and C100 (which is 97 test cases, too high for login :-)) if tester passes 40 test cases by entering 'P' and fails 50 testcases by entering 'F' and 7 testcases are

blocked (entered as 'B'), now the above formula gives 40.

In the same way, failed test cases can be found by

=COUNTIF (Test_Area_Login! C3:C100,"F")

Suppose let’s assume some test cases are marked as N/A which stands for Not applicable. So in calculating total number of test cases, we should subtract these test cases, which can be done by

=COUNTA (Test_Area_Login!C$3:C$100)-COUNTIF(Test_Area_Login!C$3:C$100,"NA")

Moving forward, we can access and control the cell values.

Worksheet ("Test_Area_Login").Range ("C1").Value=10 -->Assigns 10 the cell C1

We can change the active cell from one cell to another cell by,

ActiveCell.Offset (1,0)=1 (Places 1 on the next row)

ActiveCell.Offset (0, 1) =2 (Places 2 on the next column)

We can also change the background colour of the cell based on our own condition (if passed, then green, if failed, then magenta etc.)

We can do so by,

If (Worksheet (Test_Area_Login).Range ("C1").Value="P") then

Worksheet (Test_Area_Login).Range ("C1").Interior.ColorIndix=3

The interior property references the colour and style of the shading; it uses an index, rather than real colour. What is the colour of magenta? I don’t know, we can find it , by the following

VBA procedure,

Sub DisplayPallet()

Dim N As Long

For N = 1 To 56

Cells(N, 1).Interior.ColorIndex = N

Next N

End Sub

The options through the macro and VBA are countless, what I have touched here is just a tip of ice berg. Usage of macro and VBA programming will definetely rise the bar in test reporting and make it more sophasticated and smart.