Calling vba function(with parameters) from vbscript and show the result

Question

I have the following vba function ( in a module in Excel file)

Public Function validate_fncname(strFncname As String) As Boolean
.
.
.
validate_fncname  = True
End Function

and I wrote the following vbscript to call it :

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook =  objExcel.Workbooks.Open("C:\Users\uidu8611\Desktop\CAM0500040F10_SW_Quality_Assurance_Report_Template(new_version).xlsm")

objExcel.Application.Visible = True

Dim str
str ="hello"

Dim validate_fncname 

 validate_fncname =  objExcel.Application.Run("'C:\Users\uidu8611\Desktop\CAM0500040F10_SW_Quality_Assurance_Report_Template(new_version).xlsm'!validate_fncname", str)

Wscript.Echo validate_fncname

however when I run the script, it gives me type mismatch error for the line:

objExcel.Application.Run("'C:\Users\uidu8611\Desktop\CAM0500040F10_SW_Quality_Assurance_Report_Template(new_version).xlsm'!validate_fncname", str)

Despite the fact that the type is correct (String)

furthermore if I change it to :

objExcel.Application.Run("'C:\Users\uidu8611\Desktop\CAM0500040F10_SW_Quality_Assurance_Report_Template(new_version).xlsm'!validate_fncname(5)")

It doesn't give me an error, although 5 is integer!

Where is my mistake please?

Does your second version actually return a value? It doesn't look like it would work at all, since that's not how you pass arguments using run. — Tim Williams
– Tim Williams, Commented Nov 5, 2012 at 17:46

Rick Sharp · Accepted Answer · 2015-10-22 03:04:36Z

2

I played with this for a while and came up with the same error.
In my case I was just trying to pass a string back and forth.

Then I looked up the dim statement for VBScript and it says "All VBScript variables are variants" so I changed the function on the spreadsheet to as Variant and that worked for me. Hope this helps others that come across this thread.

answered Oct 22, 2015 at 3:04

Rick Sharp

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kevin Pope · Accepted Answer · 2012-11-05 20:01:46Z

1

You already have your validate_fncname method, which looks fine (except setting the return value to True should probably be done in an if then statement to avoid setting everything to True).

When calling this function, you should just be able to do the following:

Dim validateResult As Boolean
Dim str as String
str = "hello"
validateResult = validate_fncname(str)
Debug.Print validateResult

answered Nov 5, 2012 at 20:01

Kevin Pope

2,9825 gold badges35 silver badges49 bronze badges

Collectives™ on Stack Overflow

Calling vba function(with parameters) from vbscript and show the result

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related