Parent page: Service Functions – Excel (QTP, VBScript)

Answers

1. To pass-in parameters to an Excel VBA function

A function must be public and belong to a Macro Module.
QTP/VBScript source code

Set XLHandle =  CreateObject("Excel.Application")
XLHandle.DisplayAlerts = False
   
Set XLBook = XLHandle.WorkBooks.Open("c:\temp\1.xls")

On Error Resume Next
Call XLHandle.Run("ArgFunction", 5)
boolRC = Err.Number <> 0
On Error GoTo 0

If boolRC Then
 Reporter.ReportEvent micFail, "Run Excel Macro", "Failed to call macro | Incorrect or not existing macro"
Else
 Reporter.ReportEvent micDonel, "Run Excel Macro", "Successfully invoked macro"
End If

XLBook.Save
XLBook.Close

XLHandle.Quit

Set XLBook = Nothing
Set XLHandle = Nothing

Excel/VBA function source code

Public Function ArgFunction(ByVal intRow)
 Sheet1.Cells(intRow, 1) = "Changed Row " & intRow
End Function

2. To retrieve parameters from an Excel VBA function

You need to reserve cells on a worksheet for that purpose.

Excel/VBA function source code

Public Function RetParamFunction()
 Sheet1.Cells(1, 1) = "Returned value1"
 Sheet1.Cells(1, 2) = "Returned value2"
End Function

QTP/VBScript Source Code 

Set objUsedRange = XLBook.Worksheets(1).UsedRange()
RetValue1 = objUsedRange.Cells(1,1)
RetValue1 = objUsedRange.Cells(1,2)