How To Automate Repetitive QA Tasks Using Excel Macros [Examples]

This In-depth Guide About QA’s Macro Mindset Explains How To Automate Repetitive QA Tasks Using Excel Macros with Examples And Screenshots of Excel Sheets:

The need for automation in today’s world is one of the essential and growing demands. Today everyone is seeking something that eases his/her work and the hassles involved in the routine work.

Nowadays, nobody wants to visit a mobile store or stand in queues to recharge their phone. It is now convenient not to physically visit anywhere but to recharge the phone online within a fraction of a second.

Starting from paying your utility bills through net banking, booking and tracking your cab online, buying things online on various eCommerce sites to using different automated appliances like washing machines, microwave, geyser and the ever-evolving smartphones etc., the world today is really craving for smart and automated products in the form of services or gadgets.

QA’s Macro Mindset

In this article, we are not going to discuss the usage of any automation tool or anything about artificial intelligence or machine learning but the focus shall be on mere usage of Excel for automating the daily routine tasks of a QA.

Manual testers usually have a mindset that being a manual tester has nothing to do with anything technical or in other words, “coding know-how is not my cup of tea”. This is probably the wrong mindset that could stop our growth as engineers.

A manual QA has many daily tasks that could be repetitive following the same workflow. QA can very well identify such tasks and have them automated using Excel Macros and/or formulas.

This could help in two ways:

• Reduces the time consumed in manual efforts.
• Also, inspires the QA to learn and adapt to logical thinking and upskilling on the technical front.

Things can be automated for all the Testing Phases listed below:

• Test planning and estimation.
• Test design and test case preparation.
• Test execution status update.
• Test report and metrics creation.

Quick Context Settings

#1) An excel saved with. xlsxm extension is a macro-enabled Excel.

#2) In order to enable the macro on an Excel, select File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> Select Radio Button “Enable all macros” and check the checkbox “Trust access to the VBA project object model”. Click OK.

#3) Select the menu View ->Macros ->Record Macros -> Perform some activity and Stop macros. You may edit the macro and view the VBA code recorded in the VBA editor window. Similarly, you can add your code to create a macro.

#4) We will take a look at certain examples of such repetitive tasks that could be good candidates for automation via Excel macros/formulae.

Example Usage #1

At the end of every functional phase, there needs to be some test cases identified for regression from the functional suite. This is a process that needs to be followed in every sprint or iteration.

The Solution

To reduce the effort of identifying and manually creating a regression suite, the below innovative process can be implemented.

1. You can create a sheet with Functional Test cases with an additional column to mark the test cases/ scenarios as Yes / No for regression.
2. Then create a macro for the same, such that when the macro is run, all the test cases/scenarios with regressions marked as “Yes” get copied to another sheet.
3. Thus, this will avoid an additional exercise of going through all functional test cases at the end of the sprint and then putting extra effort into getting each test case extracted into the regression suite.

Demo

• The first sheet, “FunctionalTestScenarios”, contains all the Functional test scenarios/test cases.
• Is there an additional column “Include in Regression suite?” added at the end of the test columns to mark . Yes or No by identifying if each of the test scenarios must be included in the regression suite.
• There is a second sheet created as “RegressionSuite” and this will automatically fetch all the regression test scenarios through the macro.
• CTRL+SHIFT+S is set as the shortcut for the macro to execute.

Functional Test Scenarios

Regression Suite 

VBA (Macro) Code

Sub RegressionSuite()
               ' Keyboard Shortcut: Ctrl+Shift+S
		Sheets("FunctionalTestScenarios").Activate
                Rows("1:1").Select
                Selection.AutoFilter
		Sheet1.Range("$A$1:$D$38").AutoFilter Field:=4, Criteria1:="Yes"
                Lastrow=Sheets("FunctionalTestScenarios")
               .Cells(Sheets("FunctionalTestScenarios").Rows.Count, "A").End(xlUp).Row
		Range("A2:C2" & Lastrow).Select
		Selection.Copy
		Sheets("RegressionSuite").Select
		Range("A2").Select
		Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
                SkipBlanks _:=False, Transpose:=False
End Sub

Explanation of Code

1. Sheets(“FunctionalTestScenarios”). Activate: When there are multiple sheets in Excel, the Activate method activates the first sheet and hence the focus is explicitly set to the first sheet.      
2. Rows(“1:1”).Select: This statement selects the first row for all columns of the first sheet.
3. Selection.AutoFilter: This statement applies to the filter on the selected first row.
4. Sheet1.Range(“$A$1:$D$38″).AutoFilter Field:=4, Criteria1:=”Yes”: This statement applies the auto filter on column number 4 i.e. “Include in Regression suite?” and filters out the records for value “Yes”.
5. Lastrow=Sheets(“FunctionalTestScenarios”).Cells(Sheets(“FunctionalTestScenarios”) .Rows.Count,  “A”).End(xlUp).Row: This statement gets the last row count of the first sheet.
6. Range(“A2:C2” & Lastrow).Select: This statement selects all the rows and columns of the first sheet with Include in Regression suite?=Yes.
7. Selection.Copy: This statement copies all the selected records.
8. Sheets(“RegressionSuite”).Select: This statement opens up the 2^nd sheets.
9. Range(“A2”).Select: This statement selects cell A2 of the 2^nd sheets.
10. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False:  This statement copies the source selected cell value to the destination sheet. The destination cell range contains only the calculated values using the formulas. Alternatively, Paste:=xlValues can also be used. No blanks are skipped. The source cells are not transposed and no mathematical operations are done.

Example Usage #2

There are times when you need to have test cases created for say 100 states or 1000 entities (like 1000 insurance forms). The test steps for this huge list of states and entities could be the same and repetitive.

Despite that, test case preparation could be one of the most time-consuming activities, that having the test case document in place is very important to buy-in the required estimates from the client and have the QA team test each of the test cases within the correctly estimated timelines.

The Solution

In such cases, long hours of test case preparation efforts can be reduced to a fraction of seconds using the macro.

• You can create a sheet wherein you can just provide the input as a test case id and a unique list of entities.
• Create a macro which when executed, adds repetitive lines of test steps for each of these entities and the test case document is ready in a few seconds without having to spend the substantial amount of manual efforts required to prepare the test cases.

Demo

• The first sheet “GetTestcasesASAP” is the only macro-driven sheet here which is blank initially.
• The other sheet “Sample input data” plays no role in the macro. It merely holds the sample input data for demo purposes.
• Copy-paste data from Sample Input data ranging from A2 to B12 into the first sheet.
• You can copy-paste the input data in the format – Column A =<testcase id1>……. <testcase idn> and Column B =<unique entity1……. unique entityn>.
• Once the input data is ready, press CTRL+SHIFT+T as the shortcut keys for the macro to execute.
• Test steps are automatically added for each test case id and the test case document is prepared in just a few seconds.

GetTestcasesASAP

Sample Input Data

Input Data Is Pasted In The First Sheet.

Image While Macro Execution By Pressing CTRL+SHIFT+T.

VBA (Macro) Code

Sub GetTestcasesQuick()
' GetTestcasesQuick Macro
' Keyboard Shortcut: Ctrl+Shift+T
Sheets("GetTestcasesASAP").Activate
lastrow =Sheets("GetTestcasesASAP").Cells(Sheets("GetTestcasesASAP").Rows.Count, "A").End(xlUp).Row

Dim AddRows As Integer
AddRows = 5
Dim i As Integer
i = lastrow
Do While i <> 1
			Rows(i & ":" & i + AddRows).Insert
			i = i - 1
Loop
ScreenUpdating = True
lastrow = Sheets("GetTestcasesASAP").Cells(Sheets("GetTestcasesASAP").Rows.Count, "A").End(xlUp).Row

i = 2
While i <= lastrow + 6
                     Sheets("GetTestcasesASAP").Cells(i, 3) = "Validate rates-factor combinations"
	             Sheets("GetTestcasesASAP").Cells(i + 1, 3) = "Batch job schedules and runs. "
                     Sheets("GetTestcasesASAP").Cells(i + 2, 3) = "Commissioning calculations settlements"
	             Sheets("GetTestcasesASAP").Cells(i + 3, 3) = "Quick and detailed quote"
		     Sheets("GetTestcasesASAP").Cells(i + 4, 3) = "Benefit illustration "
		     Sheets("GetTestcasesASAP").Cells(i + 5, 3) = "Benefit summary validation"
		i = i + 7
Wend
End Sub

Explanation of Code

(a) Sheets(“GetTestcasesASAP”).Activate: This statement activates the first sheet.         

(b) lastrow = Sheets(“GetTestcasesASAP”).Cells(Sheets(“GetTestcasesASAP”).Rows.Count, “A”).End(xlUp).Row: This statement gets the last row count. This time you will get the total number of row counts for the initial test case ids just added as part of the input data before the execution of the macro.

(c) DimAddRows As Integer: This statement declares the AddRows variable as an integer data type.

(d) AddRows = 5: This statement initializes AddRows with 5. The variable is used to insert a few lines after each test case id so that static test steps can be added for each test case.

(e) Dim i As Integer: This statement declares variable i as the integer.     

(f) i = lastrow: This statement assigns the initial last row count to the variable i.    

(g) Do While i <> 1: This do while loop inserts 6 blank rows after every test case id. The looping starts with the last test case id progressing till the first test case id.
Rows(i & “:” & i + AddRows).Insert
i = i – 1
Loop 

(h) ScreenUpdating = True: This statement makes every screen update visible. Hence, the performance of the macro might be slightly slow. In order to optimize the performance of the macros, you may also set ScreenUpdating=False.            

Setting ScreenUpdating to False improves the macro performance and fastens its execution, however, you won’t be able to see the intermittent changes that take place during the execution of the macro. Instead, the changes will only show up once the execution of the macro is complete.

(i) lastrow = Sheets(“GetTestcasesASAP”).Cells(Sheets(“GetTestcasesASAP”).Rows.Count, “A”).End(xlUp).Row: This statement calculates the last row count again after addition of new 6 rows for each testcase.

(j) i = 2

(k) While i <= lastrow + 6
Sheets(“GetTestcasesASAP”).Cells(i, 3) = “Validate rates-factor combinations “
Sheets(“GetTestcasesASAP”).Cells(i + 1, 3) = “Batch job schedules and runs. “
Sheets(“GetTestcasesASAP”).Cells(i + 2, 3) = “Commissioning calculations settlements”
Sheets(“GetTestcasesASAP”).Cells(i + 3, 3) = “Quick and detailed quote”
Sheets(“GetTestcasesASAP”).Cells(i + 4, 3) = “Benefit illustration “
Sheets(“GetTestcasesASAP”).Cells(i + 5, 3) = “Benefit summary validation”
i = i + 7
Wend

This snippet of the while loop adds 6 steps of static test steps for each testcase by setting these lines subsequently after each testcase id.

The Static Test Steps Added For Each Test Case Are As Follows:

• Step 1: Validate rate-factor combinations.
• Step 2: Batch job schedules and runs.
• Step 3: Commissioning calculations of settlements.
• Step 4: Quick and detailed quote.
• Step 5: Benefit Illustration.
• Step 6: Benefit summary validation.

It also pushes the next test case Id in the sequence to 6+2 i.e. 8^th rows.

E.g., if TC1 is in the first row, the steps are added from the second to seventh rows and TC2 is pushed to the eighth row. A similar fashion is followed for the rest of the test case ids too.

Example Usage #3

There are cases when a huge data file somewhere close to 1000 records or more is provided by the client and writing test cases for these humongous records and mapping each data against each test step is a tedious job. Macro can cut down the days of effort to a few minutes thereby saving the test case preparation time.

The Solution

The client provided data file can be copied to the macro-enabled sheet. Upon executing the macro, the test cases are auto-created in the second sheet by automatically mapping the test data against each test step for each test case.

Demo

• The first sheet, “InputFile” and the second sheet, “ReadyTestCases,” are the main contributors in the macro.
• The other sheet “Sample-InputFile” plays no role in the macro. It merely holds the sample input data file for demo purposes.
• Once the data from the “Sample-InputFile” or client-provided data file is copied into the first sheet i.e. ‘InputFile’,
• Execute the macro and the test cases are automatically created for each test case where the order ids are set as test case ids (Note: Remember to place the unique identifier placed as the first column in the InputFile).
• As of now, we have no shortcut key set for the macro run, instead we have to run it through the run option on the View -> Macros -> Select the Macro -> Edit -> VBA Window.
• The macro code handles the test steps addition dynamically. It does a check on its end to see if any of the field values are blank, if yes, it deletes the test step for the same as the step would not be required for a blank field. For example, if you look at the input file below, the 2^ndrecord has no value for Ship Date and the third row has no value for Region. Hence, after the macro execution, the test cases that are auto-created will not have test steps corresponding to these blank values.

Sample – InputFile

InputFile: After Performing Copy Paste Data From Sample InputFile To InputFile

Note:

• Do not paste the column header from the “Sample-InputFile” sheet.
• The yellow highlighted fields are blank hence the test steps corresponding to these blank values should not be created via the macro.

Image of ReadyTestcase Sheet after Macro Run.

VBA (Macro) Code

Sub Macro1()
ScreenUpdating = False
'-------- Assign the first column value of  Inputfile sheet to first column of  ReadyTestCases sheet' This is the TC Id --

ThisWorkbook.Sheets("InputFile").Activate
nrow = ThisWorkbook.Sheets("InputFile").Cells(Rows.Count, 1).End(xlUp).Row
ncol = ThisWorkbook.Sheets("InputFile").Cells(1, Columns.Count).End(xlToLeft).Column
i = 1
j = 1
For i = 1 To nrow
ThisWorkbook.Sheets("ReadyTestCases").Cells(i, 1) = ThisWorkbook.Sheets("InputFile").Cells(i, 1)

Next i

' ---------------------Inserting 20 buffer blank rows for each row in InputFile sheet---------
lastrow = Sheets("InputFile").Cells(Sheets("InputFile").Rows.Count, "A").End(xlUp).Row
ThisWorkbook.Sheets("InputFile").Activate

AddRows = 21
i = lastrow
Do While i <> 1
			Rows(i &; ":" & i + AddRows - 1).Insert
			 i = i - 1
Loop
' Inserting 21 buffer blank rows for each row in  ReadyTestCases sheet-----------
lastrow = Sheets("ReadyTestCases").Cells(Sheets("ReadyTestCases").Rows.Count, "A").End(xlUp).Row

ThisWorkbook.Sheets("ReadyTestCases").Activate

AddRowsTC = 21
j = lastrow

Do While j <> 1 ' 1 because we dont need extra blank lines after last row
			Rows(j & ":" & j + AddRowsTC - 1).Insert
			j = j - 1
Loop
	
'------- Input values into ReadyTestCases sheet from each row of inputfile sheet
lastrow = Sheets("ReadyTestCases").Cells(Sheets("ReadyTestCases").Rows.Count, "A").End(xlUp).Row

Dim a
a = 1
ThisWorkbook.Sheets("ReadyTestCases").Activate
For a = 1 To lastrow
ThisWorkbook.Sheets("ReadyTestCases").Cells(a,3) = ThisWorkbook.Sheets("InputFile").Cells(a, 2)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 1, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 3)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 2, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 4)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 3, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 5)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 4, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 6)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 5, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 7)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 6, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 8)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 7, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 9)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 8, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 10)	
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 9, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 11)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 10, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 12)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 11, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 13)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 12, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 14)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 13, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 15)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 14, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 16)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 15, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 17)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 16, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 18)
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 17, 3) = ThisWorkbook.Sheets("InputFile").Cells(a, 19)
a = a + 21
Next a
'------- Add verbiages reserved for each row
lastrow = Sheets("ReadyTestCases").Cells(Sheets("ReadyTestCases").Rows.Count, "C").End(xlUp).Row

a = 1
ThisWorkbook.Sheets("ReadyTestCases").Activate
For a = 1 To lastrow
ThisWorkbook.Sheets("ReadyTestCases").Cells(a, 2) = "Verify Order Date"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 1, 2) = "Verify Ship Date"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 2, 2) = "Verify Ship Mode"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 3, 2) = "Verify Customer Id"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 4, 2) = "Verify Customer Name"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 5, 2) = "Verify Segment"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 6, 2) = "Verify City"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 7, 2) = "Verify State"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 8, 2) = "Verify Postal Code"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 9, 2) = "Verify Region"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 10, 2) = "Verify Product Id"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 11, 2) = "Verify Category"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 12, 2) = "Verify Sub-Category"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 13, 2) = "Verify Product Name"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 14, 2) = "Verify Sales"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 15, 2) = "Verify Quantity"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 16, 2) = "Verify Discount"
ThisWorkbook.Sheets("ReadyTestCases").Cells(a + 17, 2) = "Verify Profit"
a = a + 21
Next a
'------Second last step- remove blank rows from ReadyTestCases and InputFile-----

Deleteblankrows ("ReadyTestCases") 'call Delete blank row function for TC sheet
ScreenUpdating = True
End Sub
‘--------------------------------------------------------------------------------
Sub Deleteblankrows(ByVal Sheet As String)
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets(Sheet)
Dim i As Long
wks.Activate
lastrow = wks.Cells(Sheets(Sheet).Rows.Count, "C").End(xlUp).Row
	
With ActiveSheet
		For i = 1 To lastrow
 'check each row of column B , if any row is empty then countA=0, delete that entire row
			If WorksheetFunction.CountBlank(Range(Cells(i, 2), Cells(i, 3))) = 1 Then
				Selection.Rows(i).EntireRow.Delete
			End If
		Next i
	End With
End Sub

Explanation of Code

#1) ScreenUpdating = False: Screen updates will not be visible by setting ScreenUpdating to False.

#2) ThisWorkbook.Sheets(“InputFile”).Activate: This statement activates the sheet ‘InputFile’.

#3) nrow = ThisWorkbook.Sheets(“InputFile”).Cells(Rows.Count, 1).End(xlUp).Row: This statement gets the count of the total rows.

#4) ncol = ThisWorkbook.Sheets(“InputFile”).Cells(1, Columns.Count).End(xlToLeft).Column: This statement gets the count of the total columns.

#5) i = 1: This statement initializes i with 1.      

#6) j = 1: This statement initializes j with 1.

#7) For i = 1 To nrow
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(i, 1) = ThisWorkbook.Sheets(“InputFile”).Cells(i, 1)
Next i : This snippet of For loop copy pastes the first column value (Order Id in this case) of the ‘InputFile’ sheet into each row of the sheet ‘ReadyTestCases’ as the testcase id.

#8) lastrow = Sheets(“InputFile”).Cells(Sheets(“InputFile”).Rows.Count, “A”).End(xlUp).Row: This statement recalculates the last row count of the InputFile.

#9) AddRows = 21: This statement assigns 21 to AddRows variable. This variable has been assigned 21 with an intention to insert 21 additional blank rows for each test case for addition of test steps.

#10) i = lastrow: This statement assigns the latest last row count to i.

#11) Do While i <> 1
Rows(i & “:” & i + AddRows – 1).Insert
i = i – 1
Loop : This statement adds 20 rows for each testcase starting from the bottom to the top.

#12) lastrow = Sheets(“ReadyTestCases”). Cells(Sheets(“ReadyTestCases”).Rows.Count, “A”).End(xlUp).Row : This statement recalculates the latest total row count.

#13) ThisWorkbook.Sheets(“ReadyTestCases”). Activate: The second sheet is activated through this statement.

#14) Dim a : This statement declares variable a.    

#15) a = 1: This statement assigns 1 to a.           

#16) For a = 1 To lastrow

ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a,3)=ThisWorkbook.Sheets(“InputFile”).Cells(a, 2)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 1, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 3)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 2, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 4)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 3, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 5)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 4, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 6)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 5, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 7)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 6, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 8)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 7, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 9)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 8, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 10)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 9, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 11)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 10, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 12)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 11, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 13)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 12, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 14)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 13, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 15)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 14, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 16)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 15, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 17)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 16, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 18)
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 17, 3) = ThisWorkbook.Sheets(“InputFile”).Cells(a, 19)
a = a + 21
Next a: A snippet of for loop maps the values from each column of InputFile sheet against each test step for each test case id.

#17) lastrow = Sheets(“ReadyTestCases”).Cells(Sheets(“ReadyTestCases”).Rows.Count, “C”).End(xlUp).Row : This statement recalculates the total row count ReadyTestCases sheet after addition of 21 rows for each test case id.

#18) a = 1: This statement assigns 1 to variable a.   

#19) ThisWorkbook.Sheets(“ReadyTestCases”).Activate:  This statement activates the ReadyTestCases sheet.

#20) For a = 1 To lastrow

ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a, 2) = “Verify Order Date”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 1, 2) = “Verify Ship Date”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 2, 2) = “Verify Ship Mode”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 3, 2) = “Verify Customer Id”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 4, 2) = “Verify Customer Name”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 5, 2) = “Verify Segment”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 6, 2) = “Verify City”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 7, 2) = “Verify State”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 8, 2) = “Verify Postal Code”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 9, 2) = “Verify Region”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 10, 2) = “Verify Product Id”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 11, 2) = “Verify Category”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 12, 2) = “Verify Sub-Category”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 13, 2) = “Verify Product Name”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 14, 2) = “Verify Sales”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 15, 2) = “Verify Quantity”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 16, 2) = “Verify Discount”
ThisWorkbook.Sheets(“ReadyTestCases”).Cells(a + 17, 2) = “Verify Profit”
a = a + 21
Next a – This snippet of For loop adds static test steps for each test case.

#21) Deleteblankrows (“ReadyTestCases”): Call the “DeleteBlankrow” function for a “Ready TestCases” sheet.

#22) ScreenUpdating = True: This allows you to visualize the screen updates happening as a part of macro execution.

#23) Sub Deleteblankrows(ByVal Sheet As String): The below code is for the function DeletBlankrows which will be dynamically checking if there are any blank values in the data fields. If yes, the test steps created for the blank data will in turn be deleted by deleting such rows.

#24) Dim wks As Worksheet: This statement declares wks variable as Worksheet.

#25) Set wks = ThisWorkbook.Worksheets(Sheet): This statement assigns the wks sheet provided as input parameter.

#26) Dim i As Long: This liner declares I as Long.

#28) wks.Activate: This statement activates the sheet.

#29) lastrow = wks.Cells(Sheets(Sheet).Rows.Count, “C”).End(xlUp).Row: This statement gets the count of the last row on the sheet.

#30) For i = 1 To last row: The For loop iterates from the first row till the last row.

#31) If WorksheetFunction.CountBlank(Range(Cells(i, 2), Cells(i, 3))) = 1 Then: This condition checks if for each row, the second and third column counts to the blank value as 1.

#32) Selection.Rows(i).EntireRow.Delete: This statement-deletes the selected row if the condition is true.

Example Usage #4

The Excel formula in the Test Estimates template can be run using a macro too. A chart is also created using the macro.

Demo

• A sheet, “Estimates,” that will enable users to add estimated hours for testing phases starting from understanding business requirements to creating, executing, and reworking on updating test scripts is maintained.
• The green highlighted cells are enabled for user entry. These are the input fields.
• The blue highlighted ones are auto derived.
• The second sheet includes Rework metrics and the third sheet includes Complexity metrics.
• The user enters the complexity size in terms of H, L, and M in column D, and column E auto fetches the factor for complexity using the VLOOKUP from the “Complexity metrics” sheet.
• The user enters the rework size in terms of H, L, and M in column F, and column G auto-fetches the factor for complexity using the VLOOKUP from the ‘Rework metrics’ sheet.
• The factors are then used in column H to fetch the total effort for estimation.
• Another macro when executed prepares the chart and adds it on the fourth sheet ‘Chart’.
• The formulas included are driven via the macro using the shortcut key CTRL+SHIFT+E.

Estimates

Rework Metrics       

Complexity Metrics

Chart

VBA (Macro) Code For Estimates Sheet

' Keyboard Shortcut: Ctrl+Shift+E
ThisWorkbook.Sheets(1).Activate
Sheets("Estimates").Cells(4, 8) = Sheets("Estimates").Cells(4, 2) * Sheets("Estimates").Cells(4, 3) * Sheets("Estimates").Cells(4, 5) * Sheets("Estimates").Cells(4, 7)
Sheets("Estimates").Cells(5, 8) = Sheets("Estimates").Cells(5, 2) * Sheets("Estimates").Cells(5, 3) * Sheets("Estimates").Cells(5, 5) * Sheets("Estimates").Cells(5, 7)
Sheets("Estimates").Cells(6, 8) = Sheets("Estimates").Cells(6, 2) * Sheets("Estimates").Cells(6, 3) * Sheets("Estimates").Cells(6, 5) * Sheets("Estimates").Cells(6, 7)
Sheets("Estimates").Cells(7, 8) = Sheets("Estimates").Cells(7, 2) * Sheets("Estimates").Cells(7, 3) * Sheets("Estimates").Cells(7, 5) * Sheets("Estimates").Cells(7, 7)
Sheets("Estimates").Cells(8, 8) = Sheets("Estimates").Cells(8, 2) * Sheets("Estimates").Cells(8, 3) * Sheets("Estimates").Cells(8, 5) * Sheets("Estimates").Cells(8, 7)
Sheets("Estimates").Cells(9, 8) = Sheets("Estimates").Cells(9, 2) * Sheets("Estimates").Cells(9, 3) * Sheets("Estimates").Cells(9, 5) * Sheets("Estimates").Cells(9, 7)
Sheets("Estimates").Cells(10, 8) = Sheets("Estimates").Cells(10, 2) * Sheets("Estimates").Cells(10, 3) * Sheets("Estimates").Cells(10, 5) * Sheets("Estimates").Cells(10, 7)
Sheets("Estimates").Cells(11, 8) = Sheets("Estimates").Cells(4, 8) + Sheets("Estimates").Cells(5, 8) + Sheets("Estimates").Cells(6, 8) + Sheets("Estimates").Cells(7, 8) + Sheets("Estimates").Cells(8, 8) + Sheets("Estimates").Cells(9, 8) + Sheets("Estimates").Cells(10, 8) 
‘---------------------------------------------------

For i = 3 To 10
Sheets("Chart").Cells(i - 2, 1) = Sheets("Estimates").Cells(i, 1)
Sheets("Chart").Cells(i - 2, 2) = Sheets("Estimates").Cells(i, 8)
Next i
End Sub

Explanation of Code

(a) ThisWorkbook.Sheets(1).Activat: The first sheet .Estimates’ activate.

(b) Sheets(“Estimates”).Cells(4, 8) = Sheets(“Estimates”).Cells(4, 2) * Sheets(“Estimates”).Cells(4, 3) * Sheets(“Estimates”).Cells(4, 5) * Sheets(“Estimates”).Cells(4, 7): This statement calculates column B * column C * column E* column G and assigns to column H for row 4.

(c) Sheets(“Estimates”).Cells(5, 8) = Sheets(“Estimates”).Cells(5, 2) * Sheets(“Estimates”).Cells(5, 3) * Sheets(“Estimates”).Cells(5, 5) * Sheets(“Estimates”).Cells(5, 7):  This statement calculates column B * column C * column E* column G and assigns to column H for row 5.

(d) Sheets(“Estimates”).Cells(6, 8) = Sheets(“Estimates”).Cells(6, 2) * Sheets(“Estimates”).Cells(6, 3) * Sheets(“Estimates”).Cells(6, 5) * Sheets(“Estimates”).Cells(6, 7):  This statement calculates column B * column C * column E* column G and assigns to column H for row 6.

(e) Sheets(“Estimates”).Cells(7, 8) = Sheets(“Estimates”).Cells(7, 2) * Sheets(“Estimates”).Cells(7, 3) * Sheets(“Estimates”).Cells(7, 5) * Sheets(“Estimates”).Cells(7, 7): This statement calculates column B * column C * column E* column G and assigns to column H for row 7.

(f) Sheets(“Estimates”).Cells(8, 8) = Sheets(“Estimates”).Cells(8, 2) * Sheets(“Estimates”).Cells(8, 3) * Sheets(“Estimates”).Cells(8, 5) * Sheets(“Estimates”).Cells(8, 7): This statement calculates column B * column C * column E* column G and assigns to column H for row 8.

(g) Sheets(“Estimates”).Cells(9, 8) = Sheets(“Estimates”).Cells(9, 2) * Sheets(“Estimates”).Cells(9, 3) * Sheets(“Estimates”).Cells(9, 5) * Sheets(“Estimates”).Cells(9, 7):  This statement calculates column B * column C * column E* column G and assigns to column H for row 9.

(h) Sheets(“Estimates”).Cells(10, 8) = Sheets(“Estimates”).Cells(10, 2) * Sheets(“Estimates”).Cells(10, 3) * Sheets(“Estimates”).Cells(10, 5) * Sheets(“Estimates”).Cells(10, 7): This statement calculates column B * column C * column E* column G and assigns to column H for row 10.

(i) Sheets(“Estimates”).Cells(11, 8) = Sheets(“Estimates”).Cells(4, 8) + Sheets(“Estimates”).Cells(5, 8) + Sheets(“Estimates”).Cells(6, 8) + Sheets(“Estimates”).Cells(7, 8) + Sheets(“Estimates”).Cells(8, 8) + Sheets(“Estimates”).Cells(9, 8) + Sheets(“Estimates”).Cells(10, 8) : This statement sums up cell H2 to H10 and assigns the final value to H11. This value provides total effort (in hours).

(j) For i = 3 To 10
Sheets(“Chart”).Cells(i – 2, 1) = Sheets(“Estimates”).Cells(i, 1)
Sheets(“Chart”).Cells(i – 2, 2) = Sheets(“Estimates”).Cells(i, 8)

Next I : This For Loop copy pastes data from column 1 and column 8 of the Estimates sheet to the Chart sheet. This is done so that the data from the Chart sheet can be used to prepare a pie chart. There is another macro written for sheet “Chart” which prepares a chart for the same.

VBA (Macro) Code for Charts Sheet

Sub CreateChart()
Dim rng As Range
Dim cht As Object
ThisWorkbook.Sheets("Chart").Activate
Set rng = ActiveSheet.Range("A2:B8")
Set est = ThisWorkbook.Sheets("Chart").Shapes.AddChart2
est.Chart.SetSourceData Source:=rng
est.Chart.ChartType = xl3DPieExploded
est.Chart.HasTitle = True
est.Chart.ChartTitle.Text = "Test Estimates"
est.Chart.SetElement (msoElementDataLabelCenter)
est.Chart.SetElement (msoElementLegendBottom)
End Sub

Explanation of Code

1. Dim rng as Range: This statement declares rng as Range value.
2. Dim est As Object: This statement declares est as Object value.
3. Workbook.Sheets(“Chart”).Activate: This statement activates the Chart sheet.
4. Set rng = ActiveSheet.Range(“A2:B8”): The range of A2 to B8 of sheet-Chart is set to rng.
5. Set est = ThisWorkbook.Sheets(“Chart”).Shapes.AddChart2: This statement is used to initiate the creation of a new Chart on the sheet-Chart.
6. est.Chart.SetSourceData Source:=rng: This statement provides a data range for contemplation in the chart.
7. est.Chart.ChartType = xl3DPieExploded:  The chart type is set to 3D Pie of Exploded type. xl3DPieExploded helps to determine this chart type.
8. est.Chart.HasTitle = True: This statement verifies if the chart already has a title.
9. est.Chart.ChartTitle.Text = “Test Estimates”: This statement overrides the chart title to “Test estimates”.
10. est.Chart.SetElement (msoElementDataLabelCenter): This statement sets the Data Labels and Legends for the chart.
11. est.Chart.SetElement (msoElementLegendBottom): This statement sets the data labels to the bottom of the chart.

Ways To Execute A Macro

A Macro Could Be Executed Using 4 Ways:

1. Use the Run icon in the VBA editor window.
2. On the Excel file, select the menu option View -> Macros -> View Macro -> Select the macro name and select Run.
3. Create a shortcut while creating the macro, and pressing the shortcut keys will trigger the execution of the macro.
4. The most user-friendly way is to create an action button (e.g. Command button) to which a macro would be assigned which when clicked would trigger the execution of the macro.

You will see how to add a command button and assign a macro to the button. The button when clicked will perform execution of the macro.

Add a Forms Control Button in Excel

• Select Menu ‘Developer’ -> Insert -> Form Controls -> Select Button icon and add the button to the Excel sheet.
• Input the name and text value for the Button. The name of the Button is used in the VBA coding to recognize this command button whereas the Text is what displays on the Button.

• Now right click on the command button and select the “Assign Macro” option and the list of macros will show up, select the name of the macro that you wish to assign.
• Once the macro is assigned, clicking on the button will trigger the execution of the assigned macro.
• In this example, the “Get Test Estimates” button is assigned to the “Estimates” macro.
• Similarly, we will be adding a command button for the Chart sheet and assigning the macro for Chart to trigger the generation of the chart.

• Clicking on the “Create Chart” button runs the macro for Chart. This improves the usability of the macro.

Conclusion

These were a few real-time examples that could be a part of the tester’s daily routine at work and smartly identified for macro generation. They can save an ample amount of time on irrelevant and repetitive manual efforts by automating the task.

The relevant screenshots, the VBA code, and a detailed understanding of each line of code have been covered in this article. I hope this has given you a good head start for QAs on how to transform oneself from a manual testing mindset to a macro mindset.

For the people who believe manual testers’ job is purely a non-technical job, let us prove them wrong through the required application of technical knowledge to improve productivity.

Author: This in-depth useful post is written by Shobha D. She works as a Project Lead and has 9+ years of experience in Manual, Automation (IBM RFT and Selenium using Java), and API testing.