Passing custom objects from VBA to VBScript

Question

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

Alex K. · Accepted Answer · 2011-12-06 16:18:12Z

3

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

answered Dec 6, 2011 at 16:18

Alex K.

177k32 gold badges276 silver badges299 bronze badges

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

2 Comments

Achraf Over a year ago

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

Alex K. Over a year ago

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

Collectives™ on Stack Overflow

Passing custom objects from VBA to VBScript

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related