Using .NET remoting in an Excel Workbook?

[junfanbl]

Hey Everyone,　
I am hoping that somebody can steer me in the right direction here. I have been utilizing an application I built in Visual Studio that does some remoting between Excel (client) and another application. It utilizes the Worksheet Selection Change event to send data to the server. However, as of late I am not allowed to use the application in my workplace because they don't want users to compile or install there own apps. However losing the functionality really stinks, as it was a big help in my everyday job. I am however able to write macros in Excel. I would like to port the functionality over to one of my Excel workbooks. Can somebody tell me how I might do this? Please let me know if you need any extra information.

 

[JohnH]

You can create a class library project (dll) and export it as COM, then use it in VBA by adding reference to it. See for example HOW TO: Call a Visual Basic .NET Class Library from Visual Basic for Applications in Microsoft Office
If you don't have admin rights to install the COM library then it will be difficult.

For pure VBA solution if that is possible you will have to ask in a VBA forum.

 

[junfanbl]

Hmm, yea I don't think I will be able to install a COM library. Is there a work around?

 

[JohnH]

Yes, but difficult as I understand. Search for "regasm without admin" or the like, this involves manual registry "hacks" to install the COM library only for user without admin rights.

 

[junfanbl]

Okay, so as I understand it, installing a COM library is the same as registering your DLL file? Also, in my .DLL file, if I reference other namespaces in my code for my desired functions, will Excel be able to query those namespaces?

I haven't been able to access certain functions inside a DLL file in Excel by using the following syntax:

[Public | Private] Declare Function name Lib "libname" [Alias "aliasname"] [([arglist])] [As type]


So if I build my own DLL file with the proper references and code to use the function, will Excel be able to use it when I add my DLL via the References Menu?

 

[JohnH]

Yes, COM library is installed by registering it, that is what regasm does.

ComVisible public types and their public members are available for VBA to use. The "Register for COM Interop" that was mentioned in article does two things, (A) it checks "Make assembly COM-Visible" in Assembly Information dialog (project settings, Application tab), which in turn sets ComVisible(True) in AssemblyInfo.vb project file and makes this the default setting for public types in assembly, and (B) it register the assembly when built (ie regasm).

 

[junfanbl]

Hi, so I have been doing more research on COM and I found some resources that may or may not solve my problem. I wanted to run it by you guys to see what you think. I did that registration free COM search online and found that you can register a DLL by using a manifest file instead of the registry itself. Here are some links on the topic:

windows vista - Can I use RegFree Com with an application written in Excel VBA? - Stack Overflow

Functional Fun: A quick guide to Registration-Free COM in .Net?and how to Unit Test it

https://msdn.microsoft.com/en-us/library/fh1h056h(v=vs.110).aspx

In this particular case I would like Excel to be able to reference a .NET Class library of mine by using a manifest as described in the links above. However, even these links do not completely explain how to implement this functionality in Excel. In the first link, it is explained that you can use the Microsoft.Windows.ActCtx Object to reference your manifest file in Excel. However it isn't described in detail how to accomplish this. Can somebody explain to me how I might be able to do this?

 

[bodHOST]

If you need to integrate .NET functionality into an Excel Workbook, you can consider using add ins or COM components instead.