Out here I will try to share my own Excel automation frame work, and If I do get support then we can host this on my blog and welcome everyone to share their functions and collaborate.
I will desist from architectural consideration as I would like to increase trial and allow user to decide on his comfort with architecture. Before I jump into the code just 3 lines of background, currently there is a lot of conversation in the industry about the different Automation tools and their implicit frameworks. My contention is that using VBA you can create ur own framework which can automate test execution without a framework using best practices from the modularity, data driven and keyword driven world.
The best way to illustrate this is to follow the following steps.
(Pls. note I have used office 2010 to create the below instruction set)
- Create a blank new workbook and name it “MyOwnExcelFramework.” and save it as a .xlsm file.
- Rename Sheet 1 as “RunManager”
- Rename Sheet 2 as “TestCase Library”
- Rename Sheet3 as “TestLog”
- Rename Sheet4 as “ErrorLog”
- In the worksheet “RunManager” Click the Developer tab in Excel, from the Insert Control add a command button in the Right hand side.
- In the Developer tab Click Design Mode ,right click the command button and select properties to display Properties Window . In the properties window, change the Name of command button to “cmdRunManager”.
- In the properties window, change the Caption of command button to “RUN”.
- In the developer tab click Visual Basic , It will open the VBA module.
- In the sub cmdRunmanager_Click(), rewrite the sub as below.
Private Sub cmdRunmanager_Click()
- The code for Sub Run_Manager as below.
Private Sub Run_Manager()
Dim strexecmd As String
For m = 10 To 250
strexecmd = Worksheets(“RunManager”).Cells(m, 3)
If strexecmd = vbNullString Then
- The code for sub Log_Test as under.
Private Sub Log_Test(strTestcase As String)
Dim lngLastRow As Long
lngLastRow = Worksheets(“TestLog”).Cells(Worksheets(“TestLog”).Rows.Count, “A”).End(xlUp).Row
lngLastRow = lngLastRow + 1
Worksheets(“TestLog”).Cells(lngLastRow, 1) = lngLastRow – 1
Worksheets(“TestLog”).Cells(lngLastRow, 2) = Date
Worksheets(“TestLog”).Cells(lngLastRow, 3) = Time
Worksheets(“TestLog”).Cells(lngLastRow, 4) = “Executing command ” & strTestcase