Скачать или смотреть How to Use CLR Classes from COM Addins in Excel VBA

Learn how to effectively utilize complex `C# classes` from your Excel VSTO COM add-in in Excel VBA with this step-by-step guide.
---
This video is based on the question https://stackoverflow.com/q/62916800/ asked by the user 'Ralf Stauder' ( https://stackoverflow.com/u/4008142/ ) and on the answer https://stackoverflow.com/a/62946111/ provided by the user 'Dmitry Streblechenko' ( https://stackoverflow.com/u/332059/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Use CLR classes from COM addin in Excel VBA?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Use CLR Classes from COM Addins in Excel VBA

If you're developing a VSTO COM add-in for Excel using C# , you might find yourself needing to access complex classes from your add-in within Excel VBA. This can prove challenging, especially when trying to invoke class methods or properties in VBA, which may lead to confusion and errors like "Object required."

In this post, we will tackle this issue by providing a clear breakdown of how to make your C# classes accessible and usable within Excel VBA.

Understanding the Problem

You're able to exchange basic types (like strings) between Excel VBA and C# , but things get trickier with complex classes. Let's say you have a C# class that looks like this:

[[See Video to Reveal this Text or Code Snippet]]

Your goal is to expose this class through your add-in’s interface and make it callable from VBA.

In your interface, you may have similar functionality:

[[See Video to Reveal this Text or Code Snippet]]

You successfully set up the interface, and it works perfectly for basic data types. However, when you try to access methods from your TestClass in VBA, you encounter an error indicating that VBA doesn't recognize the object, as it does not know its structure.

The Solution: Implementing a Proper Interface

The key to resolving this issue lies in ensuring that your TestClass also implements a public IDispatch-derived interface, just as your Utilities class does. Here’s how to do it:

Define an Interface for Your Class

Create an interface specifically for your TestClass that defines the methods and properties you want to expose:

[[See Video to Reveal this Text or Code Snippet]]

Implement the Interface in Your Class

Update your TestClass to implement the new interface:

[[See Video to Reveal this Text or Code Snippet]]

Modifying VBA Code to Access the Class

Once your TestClass implements the interface, you can access it in your VBA code without encountering the "Object required" error. Use the following code snippet in the VBA editor:

[[See Video to Reveal this Text or Code Snippet]]

Key Changes to Note

Defining tc as ITestClass: This informs VBA about the structure of your TestClass.

Direct Method Calls: You can now directly call tc.GetTextFunc() without errors.

Conclusion

By ensuring that your complex C# classes implement a public interface, you can seamlessly integrate them with Excel VBA. This enhances the capabilities of your COM add-in and allows for a richer user experience without running into object recognition issues.

Feel free to adapt this method for your other classes and methods as needed, and happy coding!