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.

Try,

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


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





"None" wrote in message
...
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" wrote in news:eE1stOGQFHA.1096
@tk2msftngp13.phx.gbl:

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

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 :

"Chip Pearson" wrote in news:eE1stOGQFHA.1096
@tk2msftngp13.phx.gbl:

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