My Own Excel Framework

Part1

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()
    Run_Manager
    End Sub

  • 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
    Exit For
    End If
    Log_Test strexecmd

    Application.Run strexecmd

    Next

    End Sub

  • 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

    End Sub

 

 

 

PART  2

Advertisements

8 responses to “My Own Excel Framework

  1. This is -really- cool. Great work!

    Liked by 1 person

  2. m4i3

    Thank you for your encouragement Michael

    Like

  3. We developed an Excel based test automation framework many many years ago which did a similar thing and it was widely used in our company.
    First, everything was built with Excel even the operations to drive the Internet Explorer. That was invisible for the test engineer. The testers just needed to learn a couple of keywords and there were lots of cross-references that allowed the testers to use the output of one step as the input for another.
    One thing we struggled with was the acceptance in managers. They just couldn’t understand why someone would use Excel to do automated UI testing this was true especially because we added code to Excel. So, if I can give you an advice then this is the following: Having Excel to serve as the main source for automated UI testing is a great idea, but keep the code out of Excel. Either use a professional tool that serves as an executing engine or develop something on yourself, but Excel should be there only to store the instructions. It should be a slave only and not the master. This allows you to easily exchange the Excel sheets among the testers. As soon as you start coding and linking libraries you build dependencies and soon have to provide disclaimers like…”only supports version xy..”, etc.

    Liked by 1 person

    • m4i3

      Excellent feedback, pearls of wisdom with an hindsight of experience,

      will start on this.

      Like

      • I completely love your website and find almost all of your post’s to be what just I’m looking for. Would anyone offer invitee writers to create content available for you? I wo17&nd#82ul;t mind developing a post as well as elaborating on almost all of the subjects you write about here. Again, awesome weblog!

        Like

      • m4i3

        sure thing..all good content is welcome.

        Like

  4. Aniruddha

    This is really good work.
    Can u Pl do it for selenium bcoz now a days every one using that.

    Liked by 1 person

  5. Mawabo

    Wow… I like this. Congratulations!!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s