current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite
1

I have a function in an Excel file that returns a custom class, when i try to call this function from VBScript i get an "Unknown runtime error"

Sample VBA Code:

Function myVBAFunc() As Variant
   Set myClass1 = New Class1
   myClass1.Name = "Test"
   Set myVBAFunc= myClass1
End Function

Calling VBScript code:

Set Excel = CreateObject("Excel.Application") 
Set Workbook = Excel.Workbooks.open("C:\myFile.xls")
Set myRes = Excel.Run("myVBAFunc")
Excel.Quit

I am getting an error when i try to set myRes, I assume this is caused by VBScript not recognizing the object's type. Is there any way to work around this?

Thanks, Achraf

share|improve this question

1 Answer 1

up vote 3 down vote accepted

You need to change the instancing property of the class module (in its property window) from Private to PublicNotCreatable.

(You can also strictly type the function)

Function myVBAFunc() As Class1
   Set myVBAFunc= New Class1
   myVBAFunc.Name = "Test"
End Function
share|improve this answer
    
Thanks, that's much better now, I can access the object and use it. However as soon as the Excel object is closed i can no longer use the object! Is there anyway to copy the object locally to VBS so i can use it without keeping Excel open? thx –  Achraf Dec 6 '11 at 16:33
    
I doubt very much you can do that at all, if you don't need excels functionality at all give the class a method of returning whatever data you need from it and use it in vbs –  Alex K. Dec 7 '11 at 10:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.