Pass a parameter to an excel function using vbscript

[irGed]

Hi,

I'm trying to pass a parameter to an VBA Excel function but I always got this error:

Type Mismatch error

This is my sample Excel function:

Public Function OpenAndRepairWorkbook(X As String) As String
Dim oWB As Workbook
On Error GoTo Err_Open
Application.DisplayAlerts = False
Set oWB = Workbooks.Open(Filename:="D:\UnitTest.xlsm", CorruptLoad:=XlCorruptLoad.xlRepairFile)
oWB.SaveAs ("D:\UnitTest.xlsm")
OpenAndRepairWorkbook = "VB-00"
oWB.Close
Exit Function
Err_Open:
OpenAndRepairWorkbook = "VB-98"
Err.Clear
End Function

And this is my sample vbscript code:

On Error Resume Next
Dim ArgObj, var1, var2, var3
Dim X
X = "Sample"
Set ArgObj = WScript.Arguments 
var1 = ArgObj(0) 
var2 = ArgObj(1) 
var3 = ArgObj(2)
Set objExcel1 = CreateObject("Excel.Application")
	objExcel1.DisplayAlerts = 0
Set objWorkbook1 = objExcel1.Workbooks.Open(var1)
	result = objExcel1.Run("UnitTest.xlsm!OpenAndRepairWorkbook"(X))
	if err.Number<>0 then
	WScript.Echo "VB-98," + err.Description
	else
	WScript.Echo result
	end if
	objWorkbook1.Save
	objExcel1.Quit
Set	objExcel1 = Nothing
set ArgObj = Nothing

Given these codes, I'm confused why I got "type mismatch" error even though the required parameter is String.

Is it possible to pass a parameter from vbscript upto the VBA function?

Thanks.

[irGed]

Problem solved!

For future reference.

My VB script code:

On Error Resume Next
Dim ArgObj, var1, var2, var3 
Set ArgObj = WScript.Arguments 

'First parameter target file
var1 = ArgObj(0) 
'Second parameter validation file
var2 = ArgObj(1) 
'Third parameter macro
var3 = ArgObj(2)

Set objExcel1 = CreateObject("Excel.Application")
	objExcel1.DisplayAlerts = 1
Set objWorkbook1 = objExcel1.Workbooks.Open (var1)
result = objExcel1.Run (var2, var3)
	if err.Number<>0 then
	WScript.Echo "VB-98," + err.Description
	else
	
	WScript.Echo result
	end if
	objExcel1.Quit
Set	objExcel1 = Nothing
Set ArgObj = Nothing

and my VBA function:

Public Function OpenAndRepairWorkbook(filePath) As String

Dim oWB As Workbook

On Error GoTo Err_Open

Application.DisplayAlerts = False

Set oWB = Workbooks.Open(Filename:=filePath, CorruptLoad:=XlCorruptLoad.xlRepairFile)
oWB.SaveAs (filePath)

oWB.Close

OpenAndRepairWorkbook = "VB-00"

Exit Function


Err_Open:

OpenAndRepairWorkbook = "VB-99" + "," + Err.Description

Err.Clear


End Function