Calling an Excel/VBA User Created Module from WSH

[None]

Hello

Suppose I have an Excel workbook named Book1.xls. Within this workbook,
there is a Module1 and within it, is the following function:

function getValue(sym as String)
begin
//processing code..


getValue = 10
end

How can I call this function from a Windows Scripting Host (WSH) script?

I have tried the following but I receive an error with the message
"Subscript out of range"

Dim ObjXL
Dim tmp
Dim Range

Set ObjXL = WSCript.CreateObject("Excel.Application")


ObjXL.Visible = true

objXL.WorkBooks.Open("C:\Book1")

Set tmp = ObjXL.Modules("Module1").getValue("hello")

//I have also used Modules(0) and Modules(1)


WScript.Echo tmp

Any help or hints is greatly appreciated.

 

[Chip Pearson]

Try,

Dim Result
Result = objXL.Run("test")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[None]

Try,

Dim Result
Result = objXL.Run("test")

Hi Chip

Thanks for the reply. Unfortunately this won't work because a parameter must
be passed to this function. After many hours of trial and error, I moved the
function under the 'Sheet1' object and called the function in this manner:

tmp = ObjXL.WorkSheets("Sheet1").getValue("Hello")

It worked.

Take care

 

[keepITcool]

It looks like your function's code is located in the sheet's object
code module. Do you know the difference between object code modules and
normal modules?

if you dont..

stick to following as a general guideline:

move all subs and functions to normal code module(s)
with the exception of eventhandlers, which must stay
in the object modules (This Workbook, Sheet1,Sheet2 etc..)

buy and study a decent book. VBA is a lot more fun in you know the
basics.

hmm... that sounds harsh.. but it's true


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


None wrote :