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:
Recommended IPTV Service Providers
- IPTVGREAT – Rating 4.8/5 ( 600+ Reviews )
- IPTVRESALE – Rating 5/5 ( 200+ Reviews )
- IPTVGANG – Rating 4.7/5 ( 1200+ Reviews )
- IPTVUNLOCK – Rating 5/5 ( 65 Reviews )
- IPTVFOLLOW -Rating 5/5 ( 48 Reviews )
- IPTVTOPS – Rating 5/5 ( 43 Reviews )
The need for automation in as we converse’s world is doubtless one of many essential and rising requires. Today everybody appears to be in search of one factor that eases his/her work or the hassles involved inside the routine work.
Nowadays, nobody must go to a cell retailer, stand in queues to recharge their phone, it is useful to not bodily go to anyplace nevertheless to recharge the phone on-line inside a fraction of a second.
Starting from paying your utility funds by net banking, reserving and monitoring your cab on-line, looking for points on-line on diversified eCommerce web sites to using utterly totally different automated dwelling tools like washing machines, microwave, geyser and the ever-evolving smartphones and so forth., the world as we converse is principally eager for good and automated merchandise inside the kind of providers or units.
In this textual content, we’re not going to debate the utilization of any automation instrument or one thing about artificial intelligence or machine finding out nevertheless the main target shall be on mere utilization of Excel for automating the day by day routine duties of a QA.
What You Will Learn:
QA’s Macro Mindset
Manual testers usually have a mindset that I being a information tester do not have something to do with one thing technical or in several phrases, ‘coding know-how is simply not my cup of tea’. This could be the fallacious mindset that may stop our progress as engineers.
A information QA has many day by day duties which will very effectively be repetitive following the similar workflow. A QA can very successfully decide such duties and have them automated using Excel Macros and/or formulae.
This could help in two strategies:
- Reduces the time consumed in information efforts.
- Also, evokes the QA to review and adapt to logical pondering and upskilling on the technical entrance.
Things could be automated for the entire Testing Phases listed beneath:
- Test planning and estimation.
- Test design or check out case preparation.
- Test execution standing change.
- Test report and metrics creation.
Quick Context Setting
#1) An excel saved with. xlsxm extension is a macro-enabled Excel.
#2) In order to permit macro on an Excel, select File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> Select Radio Button “Enable all macros” and take a look at the checkbox “Trust entry to the VBA mission object model”. Click OK.
#3) Select the menu View ->Macros ->Record Macros -> Perform some train and Stop macros. You may edit the macro and study the VBA code recorded inside the VBA editor window. Similarly, chances are you’ll add your codes explicitly to create a macro.
#4) We will try positive examples of such repetitive duties which will very effectively be good candidates for automation by means of Excel macros/formulae.
Example Usage #1
At the highest of every purposeful half, there have to be some check out cases acknowledged for regression from the purposeful suite. This is a course of to be adopted in every sprint or iteration.
Solution
To reduce the effort of determining and manually making a regression suite, the beneath revolutionary course of could be utilized.
- You could create a sheet with Functional Test cases with an additional column for marking the check out cases/ eventualities as Yes / No for regression.
- Then create a macro for the same, such that when the macro runs, the entire check out cases/eventualities with regression marked as ‘Yes’ get copied to a unique sheet.
- Thus, this may stay away from an additional practice of going by all purposeful check out cases on the end of the sprint after which putting additional efforts into getting each check out case extracted into the regression suite.
Demo
- The first sheet ‘FunctionalTestEventualities’ accommodates the entire Functional check out eventualities/check out cases.
- There is an additional column ‘Include in Regression suite?’ added on the end of the check out columns to mark ‘Yes’ or ‘No’ by determining if each of the check out eventualities ought to be included inside the regression suite.
- There is a second sheet created as ‘RegressionSuite’ and this may mechanically fetch the entire regression check out eventualities by the macro.
- CTRL+SHIFT+S is about as a result of the shortcut for the macro to execute.
FunctionalTestEventualities
RegressionSuite
VBA (Macro) Code
Sub RegressionSuite() ' Keyboard Shortcut: Ctrl+Shift+S Sheets("FunctionalTestEventualities").Activate Rows("1:1").Select Selection.AutoFilter Sheet1.Range("$A$1:$D$38").AutoFilter Field:=4, Criteria1:="Yes" Lastrow=Sheets("FunctionalTestEventualities") .Cells(Sheets("FunctionalTestEventualities").Rows.Count, "A").End(xlUp).Row Range("A2:C2" & Lastrow).Select Selection.Copy Sheets("RegressionSuite").Select Range("A2").Select Selection.PasteSpecific Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False End Sub
Explanation of Code
- Sheets(“FunctionalTestEventualities”). Activate: When there are a selection of sheets in Excel, the Activate methodology prompts the first sheet and subsequently the principle focus is explicitly set to the first sheet.
- Rows(“1:1”).Select: This assertion selects the first row for all columns of the first sheet.
- Selection.AutoFilter: This assertion applies the filter on the chosen first row.
- Sheet1.Range(“$A$1:$D$38″).AutoFilter Field:=4, Criteria1:=”Yes”: This assertion applies the auto filter on column amount 4 i.e. ‘Include in Regression suite?’ and filters out the knowledge for price “Yes”.
- Lastrow=Sheets(“FunctionalTestEventualities”).Cells(Sheets(“FunctionalTestEventualities”) .Rows.Count, “A”).End(xlUp).Row: This assertion will get the ultimate row rely of the first sheet.
- Range(“A2:C2” & Lastrow).Select: This assertion selects the entire rows and columns of the first sheet with Include in Regression suite?=Yes.
- Selection.Copy: This assertion copies all of the chosen data.
- Sheets(“RegressionSuite”).Select: This assertion opens up the 2nd sheet.
- Range(“A2”).Select: This assertion selects cell A2 of the 2nd sheet.
- Selection.PasteSpecific Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False: This assertion copies the provision chosen cell price to the holiday spot sheet. The trip spot cell range accommodates solely the calculated values using the formulation. Alternatively, Paste:=xlValues can be utilized. No blanks are skipped. The provide cells is not going to be transposed and no mathematical operations are achieved.
Example Usage #2
There are events when you might have check out cases created for say 100 states or 1000 entities (like 1000 insurance coverage protection varieties). The check out steps for this large itemizing of states or entities could very effectively be the similar and repetitive.
Despite that, check out case preparation could very effectively be in all probability probably the most time-consuming actions, that having the check out case doc in place is crucial to buy-in the required estimates from the patron and have the QA employees check out each of the check out cases contained in the precisely estimated timelines.
Solution
In such cases, prolonged hours of check out case preparation efforts could be diminished to a fraction of seconds using the macro.
- You can create a sheet whereby you merely current the enter as a check out case id and the distinctive itemizing of entities.
- Create a macro which when executed, offers repetitive strains of check out steps for each of these entities and the check out case doc is ready in few seconds with out having to spend the substantial amount of information efforts required to rearrange the check out cases.
Demo
- The first sheet ‘GetTestcasesASAP’ is the one macro-driven sheet proper right here which is clear initially.
- Another sheet ‘Sample enter data’ performs no operate inside the macro. It merely holds the sample enter data for demo features.
- Copy-paste data from Sample Input data range A2 to B12 into the first sheet.
- You can copy-paste the enter data inside the format – Column A =<testcase id1>……. <testcase idn> and Column B =<distinctive entity1……. distinctive entityn>.
- Once the enter data is ready, press CTRL+SHIFT+T as a result of the shortcut keys for the macro to execute.
- Test steps are mechanically added for each check out case ids and the check out case doc is prepared in only some 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 combos" 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 assertion prompts the first sheet.
(b) lastrow = Sheets(“GetTestcasesASAP”).Cells(Sheets(“GetTestcasesASAP”).Rows.Count, “A”).End(xlUp).Row: This assertion will get the ultimate row rely. This time it could get the general number of row rely for the preliminary check out case ids merely added as a part of enter data sooner than the execution of the macro.
(c) Dim AddRows As Integer: This assertion declares AddRows variable as an integer data variety.
(d) AddRows = 5: This assertion initializes AddRows with 5. The variable is used to insert only a few strains after every check out case id so that static check out steps could be added for each check out case.
(e) Dim i As Integer: This assertion declares variable i as a result of the integer.
(f) i = lastrow: This assertion assigns the preliminary last row rely to the variable i.
(g) Do While i <> 1: This do whereas loop inserts 6 clear rows after every check out case id. The looping begins from the ultimate check out case id progressing till the first check out case id.
Rows(i & “:” & i + AddRows).Insert
i = i – 1
Loop
(h) ScreenUpdating = True: This assertion makes every show change seen. Hence, the effectivity of the macro might be barely gradual. In order to optimize the effectivity of the macro, you may also set ScreenUpdating=False.
Setting ScreenUpdating to False improves the macro effectivity and fastens its execution, however, you obtained’t be able to see the intermittent modifications that occur in the middle of the execution of the macro. Instead the modifications will current up solely as quickly because the execution of the macro completes.
(i) lastrow = Sheets(“GetTestcasesASAP”).Cells(Sheets(“GetTestcasesASAP”).Rows.Count, “A”).End(xlUp).Row: This assertion calculates the ultimate row rely as soon as extra after addition of latest 6 rows for each testcase.
(j) i = 2
(okay) While i <= lastrow + 6
Sheets(“GetTestcasesASAP”).Cells(i, 3) = “Validate rates-factor combos “
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 whereas loop offers 6 steps of static check out steps for every testcase by setting these strains subsequently after each testcase ids.
The Static Test Steps Added For Each Test Case Are As Follows:
- Step 1: Validate rates-factor combos.
- Step 2: Batch job schedules and runs.
- Step 3: Commissioning calculations settlements.
- Step 4: Quick and detailed quote.
- Step 5: Benefit Illustration.
- Step 6: Benefit summary validation.
Besides, it moreover pushes the next check out case Id inside the sequence to 6+2 i.e. 8th row.
E.g: If TC1 is on the first row, the steps are added from the second to seventh rows and TC2 is pushed to the eighth row. An analogous type is adopted for the rest of the check out case ids too.
Example Usage #3
There are cases when an unlimited data file someplace close to 1000 data or additional is provided by the patron and writing check out cases for these humongous data and mapping each data in direction of each check out step is a tedious job. Macro can reduce down the instances of effort to some minutes thereby saving the check out case preparation time.
Solution
The shopper provided data file could be copied to the macro-enabled sheet. On executing the macro, the check out cases are auto-created inside the second sheet by mechanically mapping check out data in direction of each check out step for each check out case.
Demo
- The first sheet ‘InputFile’ and the second sheet ‘ReadyTestCases’ are the first contributors inside the macro.
- Another sheet ‘Sample -InputFile’ performs no operate inside the macro. It merely holds the sample enter data file for demo features.
- Once the knowledge from ‘Sample-InputFile’ or client-provided data file is copied into the first sheet i.e. ‘InputFile’,
- Execute the macro and the check out cases are mechanically created for each check out case the place the order ids are set as check out case ids (Note: Remember to place the distinctive identifier positioned as the first column inside the InputFile).
- As of now, now we have now no shortcut key set for the macro run as a substitute now we have now to run it by the run chance on the View -> Macros -> Select the Macro -> Edit -> VBA Window.
- The macro code handles the check out steps addition dynamically. It does a take a look at, in the long term, to see if any of the sphere price is clear, if positive, it deletes the check out step for the same as a result of the step would not be required for a clear topic. For Example, within the occasion you check out the enter file beneath, the 2ndreport has no price for Ship Date and the third row has no price for Region. Hence, after the macro execution, the check out cases that are auto-created just isn’t going to have check out steps similar to these clear 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 clear subsequently the check out steps similar to these clear values should not be created by means of the macro.
Image of ReadyTestcases Sheet After Macro Run.
VBA (Macro) Code
Sub Macro1() ScreenUpdating = False '-------- Assign the first column price 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 clear 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 clear 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 on account of we dont need additional clear strains 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- take away clear rows from ReadyTestCases and InputFile----- Deleteblankrows ("ReadyTestCases") 'identify Delete clear row carry out 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 EnergeticSheet For i = 1 To lastrow 'take a look at each row of column B , if any row is empty then countA=0, delete that whole row If WorksheetPerform.CountClear(Range(Cells(i, 2), Cells(i, 3))) = 1 Then Selection.Rows(i).TotalRow.Delete End If Next i End With End Sub
Explanation of Code
#1) ScreenUpdating = False: Screen updates just isn’t going to be seen by setting ScreenUpdating to False.
#2) ThisWorkbook.Sheets(“InputFile”).Activate: This assertion prompts the sheet ‘InputFile’.
#3) nrow = ThisWorkbook.Sheets(“InputFile”).Cells(Rows.Count, 1).End(xlUp).Row: This assertion will get the rely of the general rows.
#4) ncol = ThisWorkbook.Sheets(“InputFile”).Cells(1, Columns.Count).End(xlToLeft).Column: This assertion will get the rely of the general columns.
#5) i = 1: This assertion initializes i with 1.
#6) j = 1: This assertion 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 price (Order Id on this case) of the ‘InputFile’ sheet into each row of the sheet ‘ReadyTestCases’ as a result of the testcase id.
#8) lastrow = Sheets(“InputFile”).Cells(Sheets(“InputFile”).Rows.Count, “A”).End(xlUp).Row: This assertion recalculates the ultimate row rely of the InputFile.
#9) AddRows = 21: This assertion assigns 21 to AddRows variable. This variable has been assigned 21 with an intention to insert 21 additional clear rows for each check out case for addition of check out steps.
#10) i = lastrow: This assertion assigns the latest last row rely to i.
#11) Do While i <> 1
Rows(i & “:” & i + AddRows – 1).Insert
i = i – 1
Loop : This assertion offers 20 rows for each testcase starting from the underside to the very best.
#12) lastrow = Sheets(“ReadyTestCases”). Cells(Sheets(“ReadyTestCases”).Rows.Count, “A”).End(xlUp).Row : This assertion recalculates the latest entire row rely.
#13) ThisWorkbook.Sheets(“ReadyTestCases”). Activate: The second sheet is activated by this assertion.
#14) Dim a : This assertion declares variable a.
#15) a = 1: This assertion 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: The snippet of for loop maps the values from every column of InputFile sheet in direction of each check out steps for each check out case id.
#17) lastrow = Sheets(“ReadyTestCases”).Cells(Sheets(“ReadyTestCases”).Rows.Count, “C”).End(xlUp).Row : This assertion recalculates the general row rely ReadyTestCases sheet after addition of 21 rows for each check out case id.
#18) a = 1: This assertion assigns 1 to variable a.
#19) ThisWorkbook.Sheets(“ReadyTestCases”).Activate: This assertion prompts 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 offers static check out steps for each check out case.
#21) Deleteblankrows (“ReadyTestCases”): Call the carry out Deleteblankrow carry out for ‘Ready TestCases’sheet.
#22) ScreenUpdating = True: This allows you to visualize the show updates occurring as a part of macro execution.
#23) Sub Deleteblankrows(ByVal Sheet As String): The beneath code is for the carry out Deletblankrows which could be dynamically checking if there are any clear values inside the data fields. If positive, the check out step created for the clear data could be deleted in flip by deleting such rows.
#24) Dim wks As Worksheet : This assertion declares wks variable as Worksheet.
#25) Set wks = ThisWorkbook.Worksheets(Sheet): This assertion assigns the wks sheet provided as enter parameter.
#26) Dim i As Long: This liner declares I as Long.
#28) wks.Activate: This assertion prompts the sheet.
#29) lastrow = wks.Cells(Sheets(Sheet).Rows.Count, “C”).End(xlUp).Row: This assertion will get the rely of the ultimate row on the sheet.
#30) For i = 1 To lastrow: The For loop iterates from the first row till the ultimate row.
#31) If WorksheetPerform.CountClear(Range(Cells(i, 2), Cells(i, 3))) = 1 Then: This state of affairs checks if for each row, the second and third column counts to the clear price as 1.
#32) Selection.Rows(i).TotalRow.Delete: This assertion deletes the chosen row if the state of affairs is true.
Example Usage #4
The Excel formulae inside the Test Estimates template could be run using a macro too. A chart could be created using the macro.
Demo
- A sheet ‘Estimates’ that may enable prospects in order so as to add estimated hours for testing phases starting from understanding enterprise requirements to creating, executing and reworking on updating check out scripts is maintained.
- The inexperienced highlighted cells are enabled for individual entry. These are the enter fields.
- The blue highlighted ones are auto derived.
- The second sheet incorporates Rework metrics and the third sheet incorporates Complexity metrics.
- The individual enters the complexity dimension on the subject of H, L, and M in column D, and the column E auto fetches the problem for complexity using the VLOOKUP from the ‘Complexity metrics’ sheet.
- The individual enters the rework dimension on the subject of H, L, and M in column F, and the column G auto-fetches the problem for complexity using the VLOOKUP from the ‘Rework metrics’ sheet.
- The parts are then utilized in column H to fetch the general efforts for estimation.
- Another macro when executed prepares the chart and offers it on the fourth sheet ‘Chart’.
- The formulae included are pushed by means of 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 assertion 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 assertion 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 assertion 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 assertion 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 assertion 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 assertion 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 assertion 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 assertion sums up cell H2 to H10 and assigns the last word price to H11. This price gives entire 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 Chart sheet . This is completed so that the knowledge from Chart sheet could be utilized to rearrange a pie chart. There is one different 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 = EnergeticSheet.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.SetPart (msoElementDataLabelCenter) est.Chart.SetPart (msoElementLegendBottom) End Sub
Explanation of Code
- Dim rng As Range: This assertion declares rng as a Range price.
- Dim est As Object: This assertion declares est as Object price.
- This Workbook.Sheets(“Chart”).Activate: This assertion prompts Chart sheet.
- Set rng = EnergeticSheet.Range(“A2:B8”): The range of A2 to B8 of sheet-Chart is about to rng.
- Set est = ThisWorkbook.Sheets(“Chart”).Shapes.AddChart2: This assertion is used to impress the creation of a model new Chart on the sheet-Chart.
- est.Chart.SetSourceData Source:=rng: This assertion gives a data range for contemplation inside the chart.
- est.Chart.ChartType = xl3DPieExploded: The chart variety is about to 3D Pie of Exploded variety. xl3DPieExploded helps to seek out out this chart variety.
- est.Chart.HasTitle = True: This assertion verifies if the chart already has a title.
- est.Chart.ChartTitle.Text = “Test Estimates”: This assertion overrides the chart title to ‘Test estimates’.
- est.Chart.SetPart (msoElementDataLabelCenter): This assertion models the Data Labels and legends for the chart.
- est.Chart.SetPart (msoElementLegendBottom): This assertion models the knowledge labels to the underside of the chart.
Ways To Execute A Macro
A Macro Could Be Executed Using 4 Ways:
- Using the Run icon on the VBA editor window.
- On the Excel file, select the menu chance View -> Macros -> View Macro -> Select the macro determine and select Run.
- Create a shortcut whereas creating the macro, and pressing the shortcut keys will set off the execution of the macro.
- The most user-friendly means is creating an movement button (E.g. Command button) to which a macro could be assigned which when clicked would set off the execution of the macro.
We will see recommendations on tips on 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 on the Excel sheet.
- Input the determine and textual content material price for the Button. The determine of the Button is used inside the VBA coding to acknowledge this command button whereas the Text is what exhibits on the Button.
- Now correct click on on on the command button and select the selection ‘Assign Macro’, the itemizing of macros displays up, select the determine of the macro that you just simply need to assign.
- Once the macro is assigned, clicking on the button triggers the execution of the assigned macro.
- In this occasion, the ‘Get Test Estimates’ button is assigned to ‘Estimates’ macro.
- Similarly, we could be together with a command button for the Chart sheet and assign the macro for Chart to set off the know-how of the chart.
- Clicking on the ‘Create Chart’ button runs the macro for Chart. This improves the usability of the macro.
Conclusion
These have been few real-time examples which will very effectively be a part of the tester’s day by day routine at work which can very effectively be effectively acknowledged for macro know-how and can, subsequently, save an ample time period on irrelevant and repetitive information efforts by automating the obligation.
The associated screenshots, the VBA code and detailed understanding of each line of code have been coated on this text. I hope, this shall give an excellent head start for the QAs on recommendations on tips on how to rework oneself from a information testing mindset to a macro mindset.
For the people who think about information testers’ job is solely a non-technical job, enable us to indicate them fallacious by the required software program of technical data to reinforce productiveness.
Author: This in-depth useful submit 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.