Calling an Excel/VBA User Created Module from WSH

N

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.
 
N

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
 
K

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 :
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top