Call Excel UDF from VBScript in MS12?

[KenHadley]

I know that I can call an Excel workbook macro using XLRun. I believe I can also call such a macro from VBScript using something like: xlApp.Run(MacroName) in the midst of the script. I also know I can call an Excel workfunction like Sum or Min from VBScript using xLApp.WorksheetFunction.

My question is if and how I can call a user-defined function from a VBA module in an Excel workbook using VBScript within MS12? I have several such I would like to use and, rather than rewrite them into VBScript syntax, I am hoping I can just call them and return the result to my MS12 VBScript.

Many thanks,

Ken

[KenHadley]

Seems to work with Application.Run from the VBScript portion of an MS12 script:

Code: Select all

Function CallExcelFunction
	a = xlApp.Run("SomeExcelFile.xlsm!SomeUDF")
	CallExcelFunction = a
End Function

I was using parentheses after the function name in the call, which does not work.

Ken