How can I make use of .NET objects from within Excel VBA?

Question

Can VBA code instantiate and use .NET objects? The specific class I'm interested in is System.IO.Compression.GZipStream.

For Info GAC is the .NET Global Assembly Cache

Answer 1

VBA can make use of any .NET objects that are exposed to COM. I don't know if GZipStream is or not, but I would guess that it would be easier for you to create a separate .NET object that is a wrapper around the functionality of GZipStream that you want to use. You can then expose your object to COM, and then VBA should make use of it.

Note that the assembly containing your COM object (and its type library too, I think, although I'm not positive on that) need to either be in the same directory as the main executable (winword.exe, or whatever) or in the GAC. This is due to the CLR's loading rules for assemblies.

Answer 2

I think Andy nailed this answer, but I'm not certain that the aspect regarding the CLR loading rules is exactly right.

The .NET Assembly that holds the class acting as the wrapper for GZipStream would be exposed to COM and registered just like any other COM project library and class. In this regard, VBA would find the location of the COM-exposed .NET assembly via the registry. It might be smart to put the assembly in the GAC, so that it can't move (since moving the assembly would invalidate the registry info), but so long as the registry points to the right place, it should be fine.

A good beginner's tutorial on the subject is A Beginner’s Guide to calling a .NET Library from Excel (by Rich Newman 2007). (http://richnewman.wordpress.com/2007/04/15/a-beginner’s-guide-to-calling-a-net-library-from-excel)

Hope this helps...