function call parameter??? I need help

[check_it_now]

hello an god day,

I try to call a function in excel from a vb script. here is the example


in excel there is a function like this

--------------------------------------------------------------------------
Public Function test_func(test_cs As String) As Variant

Dim ws As Worksheet
Dim wbmodelxl As Workbook

Set wbmodelxl = Application.Workbooks("Calling Code.xls")

Set ws = wbmodelxl.Sheets(test_cs)

ws.Activate 'activate the proper sheet

test_func = test_cs

End Function

-----------------------------------------------------------------------

test_cs is the name from the sheet which I like to activate in a open excel application


here is my function call from vbs

=================================================

sheet = "sheet2" ' name from excelsheet

call appxl.run("VBAProject.testmod.test_func", trim(sheet))

or this is also running well

call appxl.run("VBAProject.testmod.test_func", "sheet2")


the next call is the same (for me) but it is not running - errormessage is typemismatch

call appxl.run("VBAProject.testmod.test_func", sheet)

========================================================

I don't understand why I can't give the variable alone. looks like that vbs can not see what is this for an variable type.



ok but my main problem is an others


I like to give an excel function as parametertype the worksheet as object

as example the code part in vbs

=====================================================

filename = "Calling Code.xls"
sheet = "Sheet3"

excelsheet = current_path & "\" & filename
workbook = filename

' open Excel master application
Set appxl = CreateObject("Excel.Application")
appxl.Application.Visible = True

appxl.workbooks.Open excelsheet
Set wbmodelxl = appxl.workbooks(filename)
Set wsmodelxl = wbmodelxl.sheets(sheet)

call appxl.run("VBAProject.testmod.test_func_ws", wsmodelxl )
========================================================

here I get the same error as in the example with string.


I have checked the variables an the variables types. that ist not the problem :-).


excelcode for worksheet call
-------------------------------------------------------------------------------------------------

Public Function test_func_ws(work_s As Worksheet) As Variant

Dim ws As Worksheet
Dim wbmodelxl As Workbook

Set wbmodelxl = Application.Workbooks("Calling Code.xls")
Set ws = work_s

ws.Activate 'activate the proper sheet

test_func_ws = "ok"

End Function

------------------------------------------------------------------------------------------------

I have not tested the worksheet excel function in case the script is not running well. :-(. so can be there are also an error.


so I hope here is somebody they can help me and can give me good tipps to solve my problem.

thanks and nice day


by the way - sorry for my bad english

[check_it_now]

ok here it is the solution.

VBS can just give parameters with type VARIANT

for my opject types there is no function (like trim for STRING types) to convert this in to the rigth type form for excel.

that means it looks like it is not possible to give object typs as parameter to (as example) an excel application.

how I solved this problem?

I write the special code in VBA and than I compiled this to an exe file.

this file I can call from a vbs file.

every thing is running well now.


thats my way. there are some better ideas???

cu