RSS
 

Posts Tagged ‘VBA’

#25: Differences of Funtion that returns array in VBScript and VBA

17 Nov

To create and call a Function that returns array in VBScript and VBA are slightly different (QTP uses VBScript and TestPartner uses VBA):

In VBA,

Function GetExcelSheetData(FilePath As String, SheetName As String)
Dim CellArray() As String 'A dynamic array that is not sized in the Dim statement
... Calculate RowCount and ColumnCount
ReDim CellArray(RowCount, ColumnCount) As String
... Populate data in CellArray
GetExcelSheetData = CellArray()
End Function

Sub Main()
Dim SheetData() As String 'Dynamic array
.. Calculate RowCount and ColumnCount
ReDim SheetData(RowCount, ColumnCount) As String
SheetData() = Function_GetExcelSheetData.GetExcelSheetData(ExcelFilePath_MeasurementTab, "Package")

In VBScript,

Function Function_ImportExcelToArray(FilePath, SheetName)
Dim CellArray 'A dynamic array that is not sized in the Dim statement
... Calculate RowCount and ColumnCount
ReDim CellArray(RowCount, ColumnCount)
... Populate data in CellArray
Function_ImportExcelToArray = CellArray
'Note: if write "Function_ImportExcelToArray = CellArray()",
'then will get error "Subscript out of range: 'CellArray'".
End Function

Sub Sub_LoginRedirect()
Dim PageURL
'DO NOT write "PageURL()",
'otherwise, will get error "type mismatch".
.. Calculate RowCount and ColumnCount
ReDim PageURL(RowCount, ColumnCount)
PageURL = Function_ImportExcelToArray(InputExcel, "Sub_LoginRedirect")
'Note: if write "PageURL() = Function_ImportExcelToArray(InputExcel, "Sub_LoginRedirect")",
'then will get error "Subscript out of range: 'PageURL'".

Conclusion, in VBScript, do not use Array with empty brackets, such as Array(), but you can use not-empty brackets, such as Array(1, 2).

===========================================================

More information about Array in VBScript:

Much of the time, you only want to assign a single value to a variable you have declared. A variable containing a single value is a scalar variable. Other times, it is convenient to assign more than one related value to a single variable. Then you can create a variable that can contain a series of values. This is called an array variable. Array variables and scalar variables are declared in the same way, except that the declaration of an array variable uses parentheses ( ) following the variable name. In the following example, a single-dimension array containing 11 elements is declared:

Dim A(10)

Although the number shown in the parentheses is 10, all arrays in VBScript are zero-based, so this array actually contains 11 elements. In a zero-based array, the number of array elements is always the number shown in parentheses plus one. This kind of array is called a fixed-size array.

You assign data to each of the elements of the array using an index into the array. Beginning at zero and ending at 10, data can be assigned to the elements of an array as follows:

A(0) = 256
A(1) = 324
A(2) = 100
. . .
A(10) = 55

Similarly, the data can be retrieved from any element using an index into the particular array element you want. For example:

. . .
SomeVariable = A(8)
. . .

Arrays aren’t limited to a single dimension. You can have as many as 60 dimensions, although most people can’t comprehend more than three or four dimensions. You can declare multiple dimensions by separating an array’s size numbers in the parentheses with commas. In the following example, the MyTable variable is a two-dimensional array consisting of 6 rows and 11 columns:

Dim MyTable(5, 10)

In a two-dimensional array, the first number is always the number of rows; the second number is the number of columns.

You can also declare an array whose size changes during the time your script is running. This is called a dynamic array. The array is initially declared within a procedure using either the Dim statement or using the ReDim statement. However, for a dynamic array, no size or number of dimensions is placed inside the parentheses. For example:

Dim MyArray()
ReDim AnotherArray()

To use a dynamic array, you must subsequently use ReDim to determine the number of dimensions and the size of each dimension. In the following example, ReDim sets the initial size of the dynamic array to 25. A subsequent ReDim statement resizes the array to 30, but uses the Preserve keyword to preserve the contents of the array as the resizing takes place.

ReDim MyArray(25)
. . .
ReDim Preserve MyArray(30)

There is no limit to the number of times you can resize a dynamic array, although if you make an array smaller, you lose the data in the eliminated elements.

 
No Comments

Posted by Jia Qi in Quick Test Professional (QTP), TestPartner (TP)

 

#23: Error handling in VBA (TestPartner)_Part2

01 Nov

In post#13, we talked about there are 3 error handling statements in Visual Basic of Application (VBA), which can be used in TestPartner. However, NOT all of these statements can be used in QTP, since QTP uses the Visual Basic Scripting Edition (VBScript) scripting language (refer to post#26 for error handling in VBScript).

The 3 error handling statements in VBA are:

1, On Error GoTo line
This statement has been used in post#9.
This statement enables the error-handling routine that starts at line specified in the required line argument. The line argument is any line label or line number. If a run-time error occurs, control branches to line, making the error handler active. The specified line must be in the same procedure as the On Error statement; otherwise, a compile-time error occurs.

2, On Error Resume Next (see post#13 for using “On Error Resume Next” with “On Error GoTo 0″)
This statement has been used in post#12.
This statement specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred where execution continues. Use this form rather than On Error GoTo when accessing objects.

3, On Error GoTo 0 (see post#13 for using “On Error GoTo 0″ with “On Error Resume Next”)
This statement has been used in post#12.
This statement disables any enabled error handler in the current procedure (It doesn’t specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0.).

============================================

In post#13, I explained about how to use “On Error Resume Next” and “On Error GoTo 0″ together by analyzing the script from post#12.

In today’s post, I will explain how to use “On Error GoTo Line” in a loop with “Resume line“:

We have an array SheetData(1 To 5) which contains 5 data: A1, B22, C3, D4, E55 (B22 and E55 contains typos, they should be B2 and E5 instead.)

The original code was:

Sub Main()
    ...
    For i = 1 To RowCount
        ComboBox("Parent.Caption=Preferences").Select SheetData(i)
    Next
    ...
End Sub

When running the original code, TestPartner will generate run-time errors when try to select SheetData(2)-B22 and select SheetData(5)-E55, since they doesn’t exist in the ComboBox (B2 and E5 does exist in the ComboBox).

The run-time error will interrupt the run, which is unpleasant. I would like to handle it this way: if SheetData(i) doesn’t exist in the ComboBox, then write in the test result that “SheetData(i) doesn’t exist in the ComboBox”, do not pop-up the error, and resume to run next i, i.e., try to select SheetData(i+1).

The final code looks like this:

Sub Main()
    ...
    For i = 1 To RowCount
        On Error GoTo ErrorHandler
        ComboBox("Parent.Caption=Preferences").Select SheetData(i)
Jump:
    Next
    ...
Exit Sub

ErrorHandler:
UserCheck "PackageName", False, "The " & SheetData(i) & " does NOT exist."
Resume Jump

End Sub

Now, we see how to use “On Error GoTo Line” in a loop – use “Resume line” to resume the loop run (The line argument is any line label or line number).

============================================

Below is a more complicated example about how to use “On Error Resume Next”, “On Error GoTo 0″, “On Error GoTo Line“, and “Resume line” together.

Sub Main()
...
'Select different Packages from the ComboBox,
'expand Protocol nodes, and check their measurements are enabled
For i = 1 To ColumnCount
    For j = 1 To RowCount
        If j = 1 Then   'First row in the Measurement sheet is package name
            On Error GoTo 0    'Disables error handling in the current procedure
            'in case SheetData(1, i) doesn’t exist in the application
            On Error GoTo ErrorHandlerA
            'Select the package
            ComboBox("Parent.Caption=Preferences").Select SheetData(1, i)
            Dim Package As String
            Package = SheetData(1, i)
        ElseIf j = 2 Then    'Second row in the Measurement sheet is protocol name
            Dim ProtocolPath As String
            ProtocolPath = "\Measurements\" & SheetData(j, i)
            'Report error if the protocol name read from Excel sheet
            'does NOT match the protocol name displayed in the workstation
            On Error GoTo 0    'Disables error handling in the current procedure
            'in case ProtocolPath doesn’t exist in the application
            On Error GoTo ErrorHandlerB
            'Expand Protocol nodes
            TreeView("Parent.Caption=Preferences").SelectItem ProtocolPath, , tpTreeButton
        Else
            'If the Excel cell is not empty,
            'check all the CheckBoxes beside measurement nodes
            If SheetData(j, i) <> "" Then
            Dim MeasurementPath As String
            MeasurementPath = ProtocolPath & "\" & SheetData(j, i)
                'Report error if the measurement name read from Excel sheet
                'does NOT match the measurement name displayed in the workstation
                On Error GoTo 0    'Disables error handling in the current procedure
                'in case MeasurementPath doesn’t exist in the application
                On Error GoTo ErrorHandlerC
                TreeView("Parent.Caption=Preferences").SelectItem (MeasurementPath)

                'Store the mouse y position after "SelectItem", which is always
                'in the middle of the first charactor of the measurement name
                Dim y As Integer
                y = TreeView("Parent.Caption=Preferences").MouseY

                'Handle the situation when the measurement name is longer than
                'the width of the treeview window
                On Error Resume Next    'Handle the situation when there is no scroll bar
                TreeView("Parent.Caption=Preferences").Scroll 0, tpScrollHorizontal
                On Error GoTo 0    'Disables error handling in the current procedure

                'Search if the CheckBoxInTreeView.bmp (13*13 pixels) exists or not
                'Write the results to result summary.
                If TreeView("Parent.Caption=Preferences").BitmapExists("CheckBoxInTreeView", 58, y - 8, 17, 17) Then
                    UserCheck "CheckBoxInTreeView", True, "The " & Package & MeasurementPath & " CheckBox is checked"
                Else
                    UserCheck "CheckBoxInTreeView", False, "The " & Package & MeasurementPath & " CheckBox is NOT checked"
                End If
            End If
        End If
JumpB:
    Next
JumpA:
Next

'Close application to reset its original state
Window.Close

'Erase array
Erase SheetData

Exit Sub

ErrorHandlerA:
UserCheck "PackageName", False, "The " & SheetData(1, i) & " does NOT exist in the Preference Panel - Measurement Tab."
Resume JumpA

ErrorHandlerB:
UserCheck "ProtocolName", False, "The " & SheetData(1, i) & "\" & SheetData(2, i) & " does NOT exist in the Preference Panel - Measurement Tab."
Resume JumpA

ErrorHandlerC:
UserCheck "ProtocolName", False, "The " & SheetData(1, i) & "\" & SheetData(2, i) & "\" & SheetData(j, i) & " does NOT exist in the Preference Panel - Measurement Tab."
Resume JumpB

End Sub
 
No Comments

Posted by Jia Qi in TestPartner (TP)

 

#20: Test Image View in Windows Application

09 Sep

When I started this blog, I said I will show how to use QTP to test a website, and how to use TestPartner (TP) to test a windows application. I also said the main focus will be QTP instead of TP, since there is place to download QTP for free but no place to download TP, and you will have access to the website but won’t have access to the windows application.

However, beside a few posts (post#4, post#5, post#6)about how to test the login function of the website by QTP, most of my posts (until now) are about TP and testing an image processing work station. Today, I decided to end the testing of the work station, and from tomorrow, I will switch back to use QTP to test the website.

I want to give a summary of the test architecture I designed for the workstation test before I move on to test the website:

The object and scope of the test is to test all the pre-defined measurements and annotations in the image processing workstation.

Compare to measurements, annotations are much easier, so I will only explain how to test the measurements, and you can adopt similar methods to test the annotations.

In the workstation, we organize pre-defined measurements in 3 layers. The top layer are 5 measurement packages, and under each package, there are 1-20 protocols, which is the second layer. Each protocol, includes 5-20 measurements, which is the bottom layer. There are about 1000 measurements in total.

All these 1000 measurements can be divided into 10 different categories, for example, there are linear measurements, area measurements, velocity measurements, and so on. They way to draw measurements from different categories are not the same, for example, linear measurement needs 2 clicks in the image area and area measurement needs 3 clicks.

Measurements under same protocol/package apply to different modes, for example, protocol A has measurement 1, 2, 3, …, 10. Measurement 1, 3, 7 apply to B-Mode, and the rest apply to M-Mode.

Since the workstation software is one of the product from my current company, we have acquired images during manual testing, and these images can be reused for automation testing too. These images are arranged in 3 layers too in order to cope with the 3 layers of the measurements. The top layer of the images is called study, which is named by package name, i.e., each package has 1 study, so there are 5 studies in total. Under each study, there are a few series, and each series is named by protocol name, so if there are 10 protocols in this package, then there will be 10 series in this study. Under each series, there are many images from different modes, and there won’t be any images from the same mode under 1 series. If the measurements under a protocol apply to 5 different modes, then there will be 5 images each from a different mode listed under the series.

As a summary, the data/images look like this:

  • Study A (named with Package A name) (expanded)
    • Series 1 (named with Protocol 1 name) (expanded)
      • image (B-Mode)
      • image (M-Mode)
      • image (other mode)
    • Series 2 (named with Protocol 2 name) (indented)
    • Series 3 (named with Protocol 3 name) (indented)
  • Study B (named with Package B name) (indented)
  • Study C (named with Package C name) (indented)
  • Study D (named with Package D name) (indented)
  • Study E (named with Package E name) (indented)

 

We also developed manual test cases for these packages. The documents contains these test cases are called system validation procedures (SVP). Each SVP covers 1 measurement package, so there are 5 SVPs. In each SVP, test cases are divided into different sections, and each section is for one protocol. In each section, there are sub-sections divided by modes. For example, in the SVP for package A, in section1 which is for protocol 1, there is a few sub-sections. One of the sub-section is to load a B-Mode image, and under this sub-section, there are many test cases to draw measurements, i.e., each test case will ask you to draw one of the measurement within the protocol.

We created the test cases in this way to make it easy for manual testers to execute the test cases, but it is not the right approach to develop automation test script, since there are about 1000 manual test cases to draw measurements, and we should not develop 1000 automation test scripts accordingly.

The way I want to write the scripts is to use an excel sheet as input, i.e., all the measurements are listed in the excel sheet, and they are stored in the following way:

Package A
Protocol 1
B-Mode
Measurement a Category a Unit a Link a
Measurement b Category b Unit b Link b
…

I will also concentrate all the repetitive test cases into one test case, so there will be in total about 10 test scripts:
test script 1: test all the packages are enabled in the preference – measurement panel; (post#10)
test script 2: test all the protocols are enabled in the preference – measurement panel; (post#12)
test script 3: test all the measurements are enabled in the preference – measurement panel; (post#14)
test script 4: draw all the measurements and output the measurements’ labels to excel sheet a; (this post#20)
test script 5: output all the measurements’ value from measurement panel to excel sheet b;
test script 6: output all the measurements’ value from analysis browser to excel sheet c;
test script 7: output all the measurements’ value to csv;
test script 8: rearrange the data in excel sheet a, b, c, and csv and compare the values are the same;
test script 9: use data from excel csv and measurement template (created during manual testing, an excel sheet has build in formulas) to check calculations results;
test script 10: draw all the annotations and out put the annotations’ label to excel sheet d, and check the values.

In the workstation, there are many objects, but they are no more than 4 types, i.e., Treeview, Listview, ImageView, and buttons. We talked about Treeview in post#10, #12, and #14, Listview in post#19, and ImageView in this post#20. Above mentioned scripts 5 to 10 are just reuse of the properties and methods of these objects, plus some logic (such as loops), so I don’t think there is need for me to post the scripts for them here (anyway you won’t have access to the workstation to understand my scripts).

Below is the script for above mentioned “test script 4″. Sub_MakeMeasurement is a test script, and inside Sub_MakeMeasurement, Function_MakeMeasurement is called, which is a module. For ImageView, there is one thing you need to know, that is how to capture the text on the ImageView: ImageView.CaptureText (startX, startY, width, depth)

Sub_MakeMeasurement

'Testing condition: If last "Link" column of the "ModeCategoryUnit" sheet is empty, then add a space in a cell of the column.

'$TPinclude "Declaration_GlobalConstants"
'$TPinclude "Function_MakeMeasurement"

Sub Main()

'Import data from Measurement.xlsx's ModeCategoryUnit sheet to array InputData()
Dim RowCount As Integer
Dim ColumnCount As Integer
Dim InputData() As String 'Dynamic array
Dim OutputData() As String 'Dynamic array

RowCount = Function_ExcelSheetRowCount.ExcelSheetRowCount(ExcelFilePath_Measurement, "ModeCategoryUnit")
ColumnCount = Function_ExcelSheetColumnCount.ExcelSheetColumnCount(ExcelFilePath_Measurement, "ModeCategoryUnit")

ReDim InputData(1 To RowCount, 1 To ColumnCount) As String    'Array is sized dynamically
ReDim OutputData(1 To RowCount, 1 To ColumnCount) As String    'Array is sized dynamically

InputData() = Function_GetExcelSheetData.GetExcelSheetData(ExcelFilePath_Measurement, "ModeCategoryUnit")

'From Study Browser, open Images from different Study and Series
Dim Study_Package As String 'Study names are the Package names
Dim Series_Protocol As String    'Series names are the Protocol names
Dim Image_Mode As String    'Image names are the Mode names
Dim j As Integer
Dim Count As Integer

'Open the workstation
    ProgramManagerWindow("Application=EXPLORER.EXE Caption='Program Manager'").Attach
        ListView("Index=1").Select "the workstation", tpMouseDoubleClick

'Attach to the workstation
    Window("the workstation").Attach
        For j = 1 To ColumnCount Step 4

            'Inside Study Browser, load the Image_Mode from the correct Study and Series
            Study_Package = InputData(1, j)
            Series_Protocol = InputData(2, j)
            Image_Mode = InputData(3, j)
            OutputData(1, j) = InputData(1, j)
            OutputData(2, j) = InputData(2, j)
            OutputData(3, j) = InputData(3, j)

            'If can not find the Series_Protocol, then the Study_Package needs to be expanded
            If ListView("Parent.Caption='Study Browser'").FindItem(Series_Protocol) = 0 Then
                ListView("Parent.Caption='Study Browser'").Select (Study_Package), tpMouseDoubleClick
            End If

            'If the mode column (i.e., the 6th column) of the row after Series_Protocol is empty
            'then the row is either a Series or a Study
            'the Series_Protocol needs to be expanded to see all the Image_Modes
            Count = ListView("Parent.Caption='Study Browser'").FindItem(Series_Protocol)
            If ListView("Parent.Caption='Study Browser'").GetItem(Count + 1, 6) = "" Then
                ListView("Parent.Caption='Study Browser'").Select (Series_Protocol), tpMouseDoubleClick
            End If

            'Search to load the Image_Mode in between the Series_Protocol and next Series/Study
            Do While ListView("Parent.Caption='Study Browser'").GetItem(Count + 1, 6) <> ""
                If ListView("Parent.Caption='Study Browser'").GetItem(Count + 1, 6) = Image_Mode Then
                    ListView("Parent.Caption='Study Browser'").SelectIndex (Count + 1), tpMouseDoubleClick
                    Exit Do
                Else
                    Count = Count + 1
                End If
            Loop

            'Inside the Mode Window, stop playback, then delete existing measurements and make new measurements
            Button("Caption=Forw").Click    'Stop PlayBack
            Button("Caption=Home").Click    'Display the first Frame
            Button("Caption=Msmnt").Click   'Open Measurement panel

            'Delete measurements
            'Do while the measurement side panel is not empty ("#NameValue" is the header of the ListView)
            Do While ListView("Label='Measured Values'").CaptureText <> "#NameValue"
            ListView("Label='Measured Values'").SelectIndex 1   'Select the top item to delete
            ListView("Label='Measured Values'").Type "{ExtDelete}"  'Press [Delete] key
            Loop

            'Draw new measurements
            'Select Measurement Pacakge
            ComboBox("Parent.Caption=ModeRootView").Select Study_Package

            Dim Measurement As String
            Dim Category As String
            Dim i As Integer

            For i = 4 To RowCount
                If InputData(i, j) <> "" And InputData(i, j) <> "(Note 1)" And InputData(i, j) <> "(Note1)" Then
                    If InputData(i, j + 1) <> "" Then
                        Measurement = InputData(i, j)
                        Category = InputData(i, j + 1)
                        OutputData(i, j) = InputData(i, j)
                        OutputData(i, j + 1) = InputData(i, j + 1)
                        OutputData(i, j + 2) = InputData(i, j + 2)
                    Else
                        Measurement = InputData(i, j)
                        Category = InputData(i + 1, j + 1)
                        OutputData(i, j) = InputData(i, j)
                    End If
                    'Call Function_MakeMeasurement to draw the measurement
                    Dim Label As String
                    Label = Function_MakeMeasurement.MakeMeasurement(Image_Mode, Series_Protocol, Measurement, Category)
                    OutputData(i, j + 3) = Label
                ElseIf InputData(i, j) <> "" And InputData(i, j) = "(Note 1)" Then
                    OutputData(i, j) = InputData(i, j)
                    OutputData(i, j + 1) = InputData(i, j + 1)
                    OutputData(i, j + 2) = InputData(i, j + 2)
                ElseIf InputData(i, j) <> "" And InputData(i, j) = "(Note1)" Then
                    OutputData(i, j) = InputData(i, j)
                    OutputData(i, j + 1) = InputData(i, j + 1)
                    OutputData(i, j + 2) = InputData(i, j + 2)
                Else: Exit For
                End If
            Next

            'Switch back to Study Browser
            Button("Caption=B").Click

        Next

'Close application to reset its original state
    Window.Close

'Use shared module "Function_SaveArrayAsExcelSheet" to write data from array OutputData() to Excel
a = Function_SaveArrayAsExcelSheet.SaveArrayAsExcelSheet(OutputData, RowCount, ColumnCount, ExcelFilePath_Measurement, "ImageArea")

'Erase arrays
Erase InputData
Erase OutputData

End Sub

Function_MakeMeasurement

Function MakeMeasurement(Mode As String, Protocol As String, Measurement As String, Category As String)

'Go to an empty frame/section of the loop
script not posted

'Select \Protocol\Measurement
TreeView("Parent.Caption=ModeRootView").SelectItem "\" & Protocol & "\" & Measurement

'Draw the measurement
Dim MeasurementLabel As String

script not posted

'MakeMeasurement Function returns MeasurementLabel
MakeMeasurement = MeasurementLabel

End Function
 
No Comments

Posted by Jia Qi in TestPartner (TP)

 

#18: TestPartner is not compatible with the currently installed VBA

25 Aug

Sometimes, when you try to launch TestPartner, you may get a pop up message saying “TestPartner is not compatible with the currently installed version of VBA (6.5.****)”.

This is because when you updated your windows, your VBA got updated too, and the new VBA version is no longer compatible with your TestPartner version.

There are 2 solutions for you: upgrade your TestPartner, or downgrade your VBA.

To upgrade your TestPartner, please contact Micro Focus/Compuware. This will cost you money.

To downgrade your VBA, please find the correct VBA version for your TestPartner (e.g., TP 6.3 supports VBA 6.5.1040), then find the DLL file for this VBA version (e.g., VBE6.DLL for VBA 6.5.1040), then replace the DLL file at C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6.

Here is the VBE6.DLL for VBA 6.5.1040 for you to download

 
No Comments

Posted by Jia Qi in TestPartner (TP)

 

#17: Reformat data in Excel (sheet A to Array x to Array y to sheet B)

25 Jul



This post is an example for you to practice post#9 and post#16. The 2 images on the right hand side are illustrations to show what I am tyring to do in this post:

In the excel file “MeasurementTab”, I created 2 sheets, one is called “Raw” and the other is called “ModeCategoryUnit”. I copied all the protocol tables from our Software Specifications, and paste them one by one to the “Raw” sheet. There are less than 50 Protocols, so it is okay to copy them manually. However, each Protocol table contains 2 to 20 measurements, so there are about 500 measurements in-total, and I want to rearrange all these measurements in another format/order and save them in the “ModeCategoryUnit” sheet, which will take days if I do it manually.

I decide to automate the data process. I will export the data from “Raw” sheet to an array called Raw(). By using the array Raw() and another small array ModeArray(1 To 9), I will rearrange the data in a new order, and save the data to an array called ModeCategoryUnit(). Finally, data in array ModeCategoryUnit() will be exported to sheet “ModeCategoryUnit” in the Excel file.

'Convert "Raw" sheet (populated directed from software specification - word version) to "ModeCategoryUnit" sheet in "Measurement.xlsx"
'If handle all the Packages/Protocols/Measurments, then need to increase ModeCategoryUnit's size and size parameters in Function_SaveArrayAsExcelSheet

'$TPinclude "Declaration_GlobalConstants"

Sub Main()

'Store Data from the "Raw" Sheet to array Raw()
Dim RowCount As Integer
Dim ColumnCount As Integer
Dim Raw() As String

RowCount = Function_ExcelSheetRowCount.ExcelSheetRowCount(ExcelFilePath_Measurement, "Raw")
ColumnCount = Function_ExcelSheetColumnCount.ExcelSheetColumnCount(ExcelFilePath_Measurement, "Raw")

ReDim Raw(1 To RowCount, 1 To ColumnCount) As String
Raw() = Function_GetExcelSheetData.GetExcelSheetData(ExcelFilePath_Measurement, "Raw")

'Delete extra space (if any) for each cell of array Raw()
Dim TrimRaw() As String
ReDim TrimRaw(1 To RowCount, 1 To ColumnCount) As String
Dim i As Integer    'row number of array Raw()
Dim j As Integer    'column number of array Raw()

For j = 1 To ColumnCount
    For i = 1 To RowCount
    TrimRaw(i, j) = Trim(Raw(i, j))
    Next
Next

'Convert data from array TrimRaw() to array ModeCategoryUnit()
Dim ModeCategoryUnit(1 To 15, 1 To 60) As String    'Size should be increased if handle all the Packages/Protocols/Measurments

'First, write the header section (top 3 rows) of array ModeCategoryUnit()
'Including Package (1st row), Protocol (2nd row) and Mode (3rd row)
Dim Package As String
Dim Protocol As String
Dim ModeArray(1 To 9) As String
Dim ModeList As String
Dim Counter As Integer
Dim Column As Integer

Column = 1
For j = 1 To ColumnCount
    If TrimRaw(1, j) <> "" Then
        Package = TrimRaw(1, j)
        If TrimRaw(2, j) <> "" Then
            Protocol = TrimRaw(2, j)
            'Reset Counter, Clearn ModeList and ModeArray for the FOR loop
            Counter = 1
            ModeList = ""
            Erase ModeArray
            For i = 3 To RowCount
                If TrimRaw(i, j + 3) <> "" Then
                    'If TrimRaw(i, j + 3) doesn't exist in ModeList
                    If InStr(ModeList, TrimRaw(i, j + 3)) = 0 Then
                        ModeArray(Counter) = TrimRaw(i, j + 3)
                        'ModeList looks like "ModeArray(Counter),ModeArray(Counter+1),,,,,,,,"
                        ModeList = Join(ModeArray, ", ")
                        Counter = Counter + 1
                    End If
                Else: Exit For
                End If
            Next
            'Reset Counter for the DO loop
            Counter = 1
            Do While Counter <= UBound(ModeArray) And ModeArray(Counter) <> ""
                ModeCategoryUnit(1, Column) = Package
                ModeCategoryUnit(2, Column) = Protocol
                ModeCategoryUnit(3, Column) = ModeArray(Counter)
                Counter = Counter + 1
                Column = Column + 3
            Loop
        End If
    End If
Next

'Second, write the rest section of array ModeCategoryUnit()
'including Measurement, and its Category and Unit (same row)
Dim Measurement As String
Dim Category As String
Dim Unit As String
Dim Mode As String
Dim k As Integer    'row number of array ModeCategoryUnit()
Dim l As Integer    'column number of array ModeCategoryUnit()

For j = 1 To ColumnCount
    If TrimRaw(1, j) <> "" Then
        Package = TrimRaw(1, j)
        If TrimRaw(2, j) <> "" Then
            Protocol = TrimRaw(2, j)
            For i = 3 To RowCount
                If TrimRaw(i, j) <> "" Then
                    Measurement = TrimRaw(i, j)
                    Unit = TrimRaw(i, j + 1)
                    Catergory = TrimRaw(i, j + 2)
                    Mode = TrimRaw(i, j + 3)
                    'Search all the columns of array ModeCategoryUnit()
                    For l = LBound(ModeCategoryUnit, 2) To UBound(ModeCategoryUnit, 2)
                        If ModeCategoryUnit(1, l) = Package And ModeCategoryUnit(2, l) = Protocol And ModeCategoryUnit(3, l) = Mode Then
                            For k = 4 To UBound(ModeCategoryUnit)
                                If ModeCategoryUnit(k, l) = "" Then
                                    ModeCategoryUnit(k, l) = Measurement
                                    ModeCategoryUnit(k, l + 1) = Catergory
                                    ModeCategoryUnit(k, l + 2) = Unit
                                    Exit For
                                End If
                            Next
                            Exit For
                        End If
                    Next
                Else
                    If TrimRaw(i, j + 3) <> "" Then
                        Mode = TrimRaw(i, j + 3)
                        For l = LBound(ModeCategoryUnit, 2) To UBound(ModeCategoryUnit, 2)
                            If ModeCategoryUnit(1, l) = Package And ModeCategoryUnit(2, l) = Protocol And ModeCategoryUnit(3, l) = Mode Then
                                For k = 4 To UBound(ModeCategoryUnit)
                                    If ModeCategoryUnit(k, l) = "" Then
                                        ModeCategoryUnit(k, l) = Measurement
                                        ModeCategoryUnit(k, l + 1) = Catergory
                                        ModeCategoryUnit(k, l + 2) = Unit
                                        Exit For
                                    End If
                                Next
                                Exit For
                            End If
                        Next
                    Else: Exit For
                    End If
                End If
            Next
        End If
    End If
Next

'Use shared module "Function_SaveArrayAsExcelSheet" to write data from array to Excel
a = Function_SaveArrayAsExcelSheet.SaveArrayAsExcelSheet(ModeCategoryUnit, 15, 60, ExcelFilePath_Measurement, "ModeCategoryUnit")

'Erase arrays
Erase ModeCategoryUnit
Erase TrimRaw
Erase Raw

End Sub

From the above script, we practiced the following:
1, Use a module “”Declaration_GlobalConstants”" to define a globe constants “ExcelFilePath_MeasurementTab”. (for more details see post#10)
2, Call Function_GetExcelSheetData to transfer data from the “Raw” sheet to array Raw(). (for more details see post#9)
3, Use Trim function to remove spaces on both sides of a string.
4, Join array elements to a string (ModeList = Join(ModeArray, “, “))
5, Call Function_SaveArrayAsExcelSheet to transfer data from array ModeCategoryUnit() to “ModeCategoryUnit” sheet. (for more details see post#16)
6, Erase array (e.g., Erase Raw)

There are an potential improvement for the above script. Currently, I hard coded the size of array “ModeCategoryUnit”, so when there are more measurements added, then I need to change the size in the script. However, there is a solution: “Adding New Elements to array on the Fly” . I didn’t do this because that the logic of the script is already complected, and I do not want to confuse you, but you can try this out by yourself!

 
1 Comment

Posted by Jia Qi in TestPartner (TP)

 

#16: Export data from Two-Dimensional Array (2D array) to an Excel Sheet

25 Jul

In Post#9, we talked about how to import data from Excel (with multiple sheets) to an Array.

In this post, we will discuss that how to export data from a Two-Dimensional Array (2D array) to an Excel sheet.

Function SaveArrayAsExcelSheet(ArrayData() As String, ArrayRowCount As Integer, ArrayColumnCount As Integer, ExcelFilePath As String, SheetName As String)

'On error jump to "Error Handler:" line
On Error GoTo ErrorHandler

'Variable declaration
Dim SheetCount As Integer

'Open the Excel file
Set ObjExcel = CreateObject("Excel.Application")
Set ObjWorkBook = ObjExcel.Workbooks.Open(ExcelFilePath)

'Count how many sheets in the Excel file
SheetCount = ObjWorkBook.WorkSheets.Count

'If the sheet named "SheetName" exists, delete it
For i = 1 To SheetCount
    If ObjWorkBook.WorkSheets(i).Name = SheetName Then
        ObjExcel.DisplayAlerts = False  'Disable the delete confirmation message
        ObjWorkBook.WorkSheets(i).Delete
        ObjExcel.DisplayAlerts = True   'Enable the delte confirmation message
        Exit For
    End If
Next

'Create a new sheet in the Excel file and name this sheet as "SheetName"
ObjWorkBook.WorkSheets.Add.Name = SheetName

'Transfer data from an array to a Excel sheet
Dim ObjRange As Excel.Range
For i = 1 To SheetCount
    If ObjWorkBook.WorkSheets(i).Name = SheetName Then
        Set ObjRange = ObjWorkBook.WorkSheets(i).Range(ObjWorkBook.WorkSheets(i).Cells(1, 1), ObjWorkBook.WorkSheets(i).Cells(ArrayRowCount, ArrayColumnCount))
        ObjRange.Value = ArrayData()
    End If
Next

'Save and Close the Excel file
ObjWorkBook.Save
ObjWorkBook.Close

Exit Function

ErrorHandler: ' This is a normal VBA line label
    MsgBox Err.Description
    ObjWorkBook.Close

End Function

The VBA script above solves the problem (it will add or overwrite a sheet in the Excel file to receive data). The script should be saved as an shared module in TestPartner. To make the script work, you need to go to Tools->Reference to check the reference for Microsoft Excel, otherwise, TestPartner will give you an error on line “Dim ObjRange As Excel.Range”. (see Chapter 8 page 96 of “TestPartner Advanced Training Guide.pdf for Test Partner 5.2” listed in post#8 for more details.)

If you want to apply the script in QTP which uses VBScript instead of VBA, you need to make some minor modifications:

A, The function script needs to be created in the QTP Function Library (File – New – Function Library);

B, In the function script, delete anything related to the ErrorHandler (2 paragraphs);

C, In the function script, delete any definition for variable type, e.g., modify “SheetName As String” to “SheetName”, and change “Dim SheetCount As Integer” to “Dim SheetCount”.

D, Create a Test/Action to execute the function script, and the test/action will look like this (compare to TestPartner test script, no “Sub” nor “End Sub”, no need to assign the function to a variable, and no function file name nor brackets, i.e.,
in Testpartner (In TestPartner, if the function does not have a return value, but it has more than 1 input values, then in order to execute the function, you still need to define a variable in your test script, and assign the function to the variable.):

Sub Main()
a = Function_SaveArrayAsExcelSheet.SaveArrayAsExcelSheet(ArrayName, 15, 60, "C:\...\MeasurementTab.xlsx", "Sheet2")
End Sub

in QTP:

SaveArrayAsExcelSheet ArrayName, 15, 60, "C:\...\MeasurementTab.xlsx", "Sheet2")

E, Associate the Function file with the Test/Action file (right click on the Function file script and choose “Associate…” on the pop up menu), then you can run the function by running the test/action.

The script above should be fast enough to handle 10,000 data, but if your data is more than 50,000, then you may experience some slowness (depends on your computer speed too). There are two ways to solve the speed problem:
1, export the data from array to csv, then convert csv to Excel.
2, use ExcelCreator.NET instead of Excel Object, which is more efficient.
For more details, please refer to this website (you need to understand Chinese) .

 
No Comments

Posted by Jia Qi in Quick Test Professional (QTP), TestPartner (TP)

 

#13: Error handling in VBA (TestPartner)_Part1

01 Jul

There are 3 error handling statements in Visual Basic of Application (VBA), which can be used in TestPartner. However, not all of these statements can be used in QTP, since QTP uses the Visual Basic Scripting Edition (VBScript) scripting language (refer to post#26 for error handling in VBScript).

The 3 error handling statements in VBA are:

1, On Error GoTo line (see post#23 for using “On Error GoTo line” in a loop with “Resume Line“)
This statement has been used in post#9.
This statement enables the error-handling routine that starts at line specified in the required line argument. The line argument is any line label or line number. If a run-time error occurs, control branches to line, making the error handler active. The specified line must be in the same procedure as the On Error statement; otherwise, a compile-time error occurs.

2, On Error Resume Next
This statement has been used in post#12.
This statement specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred where execution continues. Use this form rather than On Error GoTo when accessing objects.

3, On Error GoTo 0
This statement has been used in post#12.
This statement disables any enabled error handler in the current procedure (It doesn’t specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0.).

============================================

In the script of post#12, there is a line to move the scroll bar inside the TreeView window horizontally: “TreeView(“Parent.Caption=Preferences”).Scroll 0, tpScrollHorizontal”. This line of script is necessary when a protocol name is longer than the TreeView window width.

However, when all the protocols names are shorter than the TreeView window width, then there will be no scroll bar, which is an expected situation, but since the script can not find the scroll bar, it will pop up an error message to stop the script from running.

In order to handle the above situation, I wrote “On Error Resume Next” right above “TreeView(“Parent.Caption=Preferences”).Scroll 0, tpScrollHorizontal” line. In this way, if there is a scroll bar, then the script will scroll the scroll bar horizontally to position x=0, and if there is no scroll bar, then the script will skip the scroll step, and run the next step.

Everything looks perfect until I found out this: even though the “On Error Resume Next” is right above “TreeView(“Parent.Caption=Preferences”).Scroll 0, tpScrollHorizontal” line, it does NOT only affect the “TreeView(“Parent.Caption=Preferences”).Scroll 0, tpScrollHorizontal” line, it will affect all the lines after “On Error Resume Next”, which means, if there is an error happens after “On Error Resume Next” is executed, then the error will be ignored anyway.

If LOOP is used, when an error happens at a line above “On Error Resume Next”, the error could still be ignored, as long as the error doesn’t happen at the first iteration of the loop and before “On Error Resume Next” is executed. See the example below from post#12:

For i = 1 to Imax
     ...
     TreeView("Parent.Caption=Preferences").SelectItem (ProtocolPath)
     ...
     On Error Resume Next
     TreeView("Parent.Caption=Preferences").Scroll 0, tpScrollHorizontal
     ...
Next

Real/unexpected error may happen at line “TreeView(“Parent.Caption=Preferences”).SelectItem (ProtocolPath)”, and the script needs to alert me about these errors, but since the existence of “On Error Resume Next”, most errors happen at “TreeView(“Parent.Caption=Preferences”).SelectItem (ProtocolPath)” will be ignored, unless it happens when i=1, i.e., when “On Error Resume Next” haven’t been reached.

How to solve this issue? It is simple, you just need to add the “On Error GoTo 0″ line right above “TreeView(“Parent.Caption=Preferences”).SelectItem (ProtocolPath)”. The script will looks like this:

For i = 1 to Imax
     ...
     On Error GoTo 0
     TreeView("Parent.Caption=Preferences").SelectItem (ProtocolPath)
     ...
     On Error Resume Next
     TreeView("Parent.Caption=Preferences").Scroll 0, tpScrollHorizontal
     ...
Next

In conclusion, to ignore expected error, use “On Error Resume Next”; to alert unexpected error, use “On Error GoTo 0″ if “On Error Resume Next” has been used in the script (same level).

 
No Comments

Posted by Jia Qi in TestPartner (TP)

 

#9: Get data from Excel for TestPartner and QTP

11 Jun

QTP and TestPartner provide integrated features for you to test multiple data in one test case. In QTP, the feature is called Data Table, and in TestPartner it is called Active Data. However, both of the features have an disadvantage, which is they only support Data Table or Active Data that has a single sheet.

For example, in TestPartner, to make the Active Data working, first, you need to import an excel file to the Active Data asset. If your excel file has multiple data sheets, and you want to use each of them, this becomes impossible, since in the Active Data asset, you can only select one data sheet (done through the option tab). If you really want to use another data sheet, then you have to rename your excel file, and import it again, and this time, select another data sheet in the option tab.

Someone like me, who wants to save space on the Database and make data easy to maintain, would like to use one Excel file that has multiple sheets instead of use multiple Excel files contain only one sheet per file.

How to do this, we should really get ride of using the Active Data from TestPartner or the Data Table from QTP. We can get data directly from an external Excel file (contains single or multiple work sheets). I have written a function for this, and will show it to you below.

In TestPartner, you may want to save the following code in the Shared Module, since this script can be used across different projects:

Function GetExcelSheetData(FilePath As String, SheetName As String)

'On error jump to "Error Handler:" line
On Error GoTo ErrorHandler

'Variable declaration
Dim SheetCount As Integer
Dim RowCount As Integer
Dim ColumnCount As Integer
Dim CellArray() As String 'A dynamic array that is not sized in the Dim statement

'Open the Excel file
Set ObjExcel = CreateObject("Excel.Application")
Set ObjWorkBook = ObjExcel.Workbooks.Open(FilePath)

'Count how many sheets in the Excel file
SheetCount = ObjWorkBook.WorkSheets.Count

'Store the data from a specific worksheet into an array
For i = 1 To SheetCount
    If ObjWorkBook.WorkSheets(i).Name = SheetName Then

        'Count how many rows and columns in the Excel file (only for the used range)
        RowCount = ObjWorkBook.WorkSheets(i).UsedRange.Rows.Count
        ColumnCount = ObjWorkBook.WorkSheets(i).UsedRange.Columns.Count

        'Array is sized with the ReDim statement
        'after RowCount and ColumnCount are given values.
        ReDim CellArray(1 To RowCount, 1 To ColumnCount) As String

        'Store the data from the specific worksheet into CellArray
        For j = 1 To RowCount
            For k = 1 To ColumnCount
               CellArray(j, k) = ObjWorkBook.WorkSheets(i).UsedRange.Cells(j, k)
            Next
        Next

    End If
Next

'Close the Excel file
ObjWorkBook.Close

'Return CellArray
GetExcelSheetData = CellArray()

Exit Function

ErrorHandler: ' This is a normal VBA line label
    MsgBox Err.Description
    ObjWorkBook.Close

End Function

From the above script, you learned the following:
1, How to write a function with input parameters
2, When deal with external files, you better write something to handle errors
3, How to use “On Error GoTo” (more details see post#13)
4, How to define an array dynamically (Dim and Redim)
5, How to use Excel Object Model, including Excel.Application, Excel.Workbook, Excel. Worksheet, and Excel.Range. (see here)
6, How to return a value in a function

Make some modifications for the Function_GetExcelSheetData Function above, you should be able to write another 2 functions: one is called as Function_ExcelSheetRowCount, and the other one is called as Function_ExcelSheetColumnCount. These 2 functions will return how many rows or columns in a specific Excel sheet, and they will be used together with Function_GetExcelSheetData in my next post.

In order to test the above function, you can write a separate test script to execute it (“Function_GetExcelSheetData” is the file name):

Sub Main()
Dim A
A = Function_GetExcelSheetData.GetExcelSheetData("C:\...\MeasurementTab.xlsx", "Sheet1")
End Sub

In TestPartner, if the function does not have a return value, but it has more than 1 input values, then in order to execute the function, you still need to define a variable in your test script, and assign the function to the variable. For example, you have a function “ActiveExcelSheet(FilePath As String, SheetName As String)”, which has no return value, just to active a specific sheet in the Excel file. To execute the function, your script needs to look like this:

Sub Main()
Dim A
A = Function_ActiveExcelSheet.ActiveExcelSheet("C:\...\MeasurementTab.xlsx", "Sheet1")
MsgBox (A) 'this tells you A is nothing
End Sub

If the test script looks like this:

Sub Main()
Function_ActiveExcelSheet.ActiveExcelSheet("C:\...\MeasurementTab.xlsx", "Sheet1")
End Sub

TestPartner will inform you there is a “Syntax error” and won’t allow you to run the script. However, if the function has only one input variable and no return value, then you can execute it without assign it to a variable.

Now, let’s talk about how to take the above function (e.g., the “ActiveExcelSheet” function) to QTP. Since TestPartner uses VBA, but QTP uses VBScript, you need to make the following changes in the function script:
A, The function script needs to be created in the QTP Function Library (File – New – Function Library);
B, In the function script, delete anything related to the ErrorHandler (2 paragraphs);
C, In the function script, delete any definition for variable type, e.g., modify “(FilePath As String, SheetName As String)” to “(FilePath, SheetName)”, and change “Dim SheetCount As Integer” to “Dim SheetCount”.
D, Create a Test/Action to execute the function script, and the test/action will look like this (compare to TestPartner test script, no “Sub” nor “End Sub”, no need to assign the function to a variable, and no function file name nor brackets, i.e., “Function_ActiveExcelSheet.ActiveExcelSheet(…)” changes to “ActiveExcelSheet …”):

ActiveExcelSheet "C:\...\MeasurementTab.xlsx", "Sheet1"

E, Associate the Function file with the Test/Action file (right click on the Function file script and choose “Associate…” on the pop up menu), then you can run the function by running the test/action.

From this post, you may have a better understanding of QTP and TestPartner… Beside the object identifier, all the other features that you need for automation testing actually can be achieved by writing VB scripts in notepad, and save them as .vbs files…

Ok, these are just theoretically speaking, we will still write VB scripts in QTP and TestPartner instead of in notepad, right? Just for the convenience.

 
2 Comments

Posted by Jia Qi in Quick Test Professional (QTP), TestPartner (TP)

 

#0: What is this blog for?

06 Sep

This blog will show you real projects examples for you to learn the following Automation testing tools:
- Quick Test Professional (QTP),
- TestPartner (TP),
- Extensible Markup Language (XML),
- Selenium (Selenium-IDE),
- Rational Functional Tester (RFT).

QTP and TestPartner are similar GUI automation testing tools, and they are both easy to learn compare to RFT which I used before. RFT uses JavaScript or VB.NET, QTP uses VBScript, and TestPartner uses VBA (more details about VB family and JavaScript, please refer to post#7). Among the three, QTP has the biggest job market. As a QA person, we should master at least one of the automation testing tools and one of the scripting language.

Selenium is a portable software testing framework for web applications. Selenium provides a test domain specific language (DSL) to write tests in a number of popular programming languages, including C#, Java, Ruby, Groovy, Python, PHP, and Perl. In this blog, we will focus on Selenium IDE, which is a complete Integrated Development Environment (IDE) for Selenium tests. It is implemented as a Firefox extension, and allows recording, editing, and debugging tests.

The difference between Selenium and QTP/TestPartner/RFT is Selenium is an open source software, i.e., it is free! See more information at post#29.

If you decide to learn automation testing, then you can follow this blog. I will share detailed test cases/scripts with you while I am learning, and I am looking forward to learn from you too, so don’t be shy to comment on my posts.

My current company uses TestPartner 6.3 to test an Ultrasound work station (a Windows Application, i.e., software, can be installed on any computer). Our major focus is to automate measurement and export testing, since we have thousands measurement types and hundreds export combinations in the app., which make manual testing very very time consuming. We will also use XML scripts (embedded in Engineering mode) to test our real-time Ultrasound machines. The Ultrasound machine supports 10 different types of transducers, we need to repeat hundred test cases for each type of the transducers, so to automate the transducer tests will save us a huge amount of time.

I also test some websites at home, just for learning purpose. In this blog, I will show you how to test a website called YouPlayOff. I will use QTP and Selenium to test this website, and you can download QTP 9.0 from the pirate bay and Selenium-IDE from Selenium website.

After we get familiar with QTP, Selenium and TestPartner, then RFT should not be a problem anymore.

 
No Comments

Posted by Jia Qi in Extensible Markup Language (XML), Quick Test Professional (QTP), Rational Functional Tester (RFT), Selenium, TestPartner (TP)