25 May 2010

QTP Help Guide: Translating Excel VBA in Quick Test Professional (QTP) vbscript

Translating Excel VBA (Visual Basic Editor) code is a tricky task sometimes. Here are a few tips that you may find helpful. If you have an automation task that you wish to do in an excel sheet via QTP, the best way is to record it first in an excel sheet in the form of a macro. This makes it much easier to get a rough architecture of the final code. You can then copy the macro VBA code in QTP and start translating it and generalizing it.


Example:
Recorded Macro as in Visual Basic Editor


Sub Macro1()
'
' Macro1 Macro
'

'
    Range("C2:G2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("C2:G2").Select
    ActiveCell.FormulaR1C1 = "Learn QTP is a Great site for learning"
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Lets Modify it for QTP

First you need to convert all these constant from the macro code into actual numeric form.

xlCenter
xlBottom
xlContext
xlAutomatic
xlSolid

For this you need the VBA Reference Guide which has all the numeric interpretations for the constants.
It is free download from microsoft. Get the VBA Reference Guide.Sometimes, you may find certain constants which are not there in this reference guide. You may want to google a little to get it or leave a comment here and i will try and help you.

Translated code for QTP
'**************************************
'Create an object of Excel Application API
'Open a new excel sheet
'Make it visible
'**************************************
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Add
objExcel.Visible = True   
 
'Select the collection of cells
objExcel.ActiveSheet.Range("C2:G2").Select

'The below code format the selected  cells
With objExcel.Selection
     .HorizontalAlignment = -4108
     .VerticalAlignment = -4107
     .WrapText = False
     .Orientation = 0
     .AddIndent = False
     .IndentLevel = 0
     .ShrinkToFit = False
     .ReadingOrder = -5002
     .MergeCells = False
End With

'Merge the selected cells. Remember that once you 
'do a ".Select " a particular range of  cells, they remain 
'selected for the rest of the code  unless you do a ".Select"
' another range of cells
objExcel.Selection.Merge

'Key in the text in the merged cell
objExcel.ActiveCell.FormulaR1C1 = "Learn QTP is a Great site for learning"

'Make the text in the selected range as Bold
objExcel.Selection.Font.Bold = True

'Here you are formatting the appearance of the seleced cell
With objExcel.Selection.Interior
     .Pattern = 1
     .PatternColorIndex = -4105
     .Color = 65535
     .TintAndShade = 0
     .PatternTintAndShade = 0
End With

The above code can be directly copy and pasted and run in QTP. Please find below a list of all the excel constants that are frequently used in excel VBA and their interpretations.

xl3DBar -4099
xl3DEffects1 13
xl3DEffects2 14
xl3DSurface -4103
xlAbove 0
xlAccounting1 4
xlAccounting2 5
xlAccounting3 6
xlAccounting4 17
xlAdd 2
xlAll -4104
xlAllExceptBorders 7
xlAutomatic -4105
xlBar 2
xlBelow 1
xlBidi -5000
xlBidiCalendar 3
xlBoth 1
xlBottom -4107
xlCascade 7
xlCenter -4108
xlCenterAcrossSelection 7
xlChart4 2
xlChartSeries 17
xlChartShort 6
xlChartTitles 18
xlChecker 9
xlCircle 8
xlClassic1 1
xlClassic2 2
xlClassic3 3
xlClosed 3
xlColor1 7
xlColor2 8
xlColor3 9
xlColumn 3
xlCombination -4111
xlComplete 4
xlConstants 2
xlContents 2
xlContext -5002
xlCorner 2
xlCrissCross 16
xlCross 4
xlCustom -4114
xlDebugCodePane 13
xlDefaultAutoFormat -1
xlDesktop 9
xlDiamond 2
xlDirect 1
xlDistributed -4117
xlDivide 5
xlDoubleAccounting 5
xlDoubleClosed 5
xlDoubleOpen 4
xlDoubleQuote 1
xlDrawingObject 14
xlEntireChart 20
xlExcelMenus 1
xlExtended 3
xlFill 5
xlFirst 0
xlFixedValue 1
xlFloating 5
xlFormats -4122
xlFormula 5
xlFullScript 1
xlGeneral 1
xlGray16 17
xlGray25 -4124
xlGray50 -4125
xlGray75 -4126
xlGray8 18
xlGregorian 2
xlGrid 15
xlGridline 22
xlHigh -4127
xlHindiNumerals 3
xlIcons 1
xlImmediatePane 12
xlInside 2
xlInteger 2
xlJustify -4130
xlLast 1
xlLastCell 11
xlLatin -5001
xlLeft -4131
xlLeftToRight 2
xlLightDown 13
xlLightHorizontal 11
xlLightUp 14
xlLightVertical 12
xlList1 10
xlList2 11
xlList3 12
xlLocalFormat1 15
xlLocalFormat2 16
xlLogicalCursor 1
xlLong 3
xlLotusHelp 2
xlLow -4134
xlLTR -5003
xlMacrosheetCell 7
xlManual -4135
xlMaximum 2
xlMinimum 4
xlMinusValues 3
xlMixed 2
xlMixedAuthorizedScript 4
xlMixedScript 3
xlModule -4141
xlMultiply 4
xlNarrow 1
xlNextToAxis 4
xlNoDocuments 3
xlNone -4142
xlNotes -4144
xlOff -4146
xlOn 1
xlOpaque 3
xlOpen 2
xlOutside 3
xlPartial 3
xlPartialScript 2
xlPercent 2
xlPlus 9
xlPlusValues 2
xlReference 4
xlRight -4152
xlRTL -5004
xlScale 3
xlSemiautomatic 2
xlSemiGray75 10
xlShort 1
xlShowLabel 4
xlShowLabelAndPercent 5
xlShowPercent 3
xlShowValue 2
xlSimple -4154
xlSingle 2
xlSingleAccounting 4
xlSingleQuote 2
xlSolid 1
xlSquare 1
xlStar 5
xlStError 4
xlStrict 2
xlSubtract 3
xlSystem 1
xlTextBox 16
xlTiled 1
xlTitleBar 8
xlToolbar 1
xlToolbarButton 2
xlTop -4160
xlTopToBottom 1
xlTransparent 2
xlTriangle 3
xlVeryHidden 2
xlVisible 12
xlVisualCursor 2
xlWatchPane 11
xlWide 3
xlWorkbookTab 6
xlWorksheet4 1
xlWorksheetCell 3
xlWorksheetShort 5

Please post your comment and views about the post. If you would like more from any specific topic on QTP or vbscript in general.

4 comments:

  1. great work dude.. keep it up...

    ReplyDelete
  2. Excellent reference for Excel VBA!
    I'm still looking for xlDot (dotted line)
    Thanks for the help:)

    ReplyDelete
  3. xlPatternLinearGradient=
    xlThemeColorDark1=
    xlThemeColorAccent1=
    xlThemeFontMinor=

    ReplyDelete
  4. xlPatternLinearGradient= 4000



    xlThemeColorDark1= 1


    xlThemeColorAccent1= 4


    xlThemeFontMinor= 1

    ReplyDelete