0

I have a large codebase built in C# (classes and functions), compiled to .NET 4.7.2 in VS2019 and need to call to the compiled C# assembly (compiles to .dll) from VBA in excel. What is the path of least resistance to achieve this, considering I need to deploy the final VBA + C# dll (and whatever wrapper layers I may need) to client machines that won't have admin rights.

I can recompile the C#, if necessary. If I cannot call directly to C#, could I go via a C++ native wrapper, and then on to C#? (or do I need a C++/CLI layer too?).

I have tried the recommendation here but access denied (admin rights required)

I also tried this Side-By-Side COM Interop with C# and VBA. But the .NET framework that excel targets is of an older version than that which I build my C# on. Not sure how I can fix this... Would it require registry edits? because if so, I can't use that method as I don't have admin rights.

Lastly I looked at this option https://pragmateek.com/using-c-from-native-c-with-the-help-of-ccli-v2/. But it appears to be going via C++ -> C++/CLR -> C# source. considering I start in VBA, that's two intermediate steps... surely there is a better way?

stuartd
  • 70,509
  • 14
  • 132
  • 163
puredopamine
  • 1
  • 1
    This sounds like an exercise in suffering. Depending on your scenario, consider going the other way and driving editing or generating the necessary Excel data from a C# application. This can even be done without having to automate Excel, though that is an option. – Jeroen Mostert Feb 17 '23 at 16:05
  • 2
    Your first link seem like a good tutorial as to how to make a COM visible assemby (which is the way to approach calling .Net code from Excel) what specifically was the problem you encountered? Perhaps create a new class library that interfaces with your existing codebase and make that the COM visible intermediary to keep things simple. – Alex K. Feb 17 '23 at 16:17
  • I think what you want is Roberts UnmanagedExports (or Repack, or DLLExport). This could be as simple as adding `[DllExport]` to your C# calls and Importing them into VBA. https://stackoverflow.com/questions/70373749/using-unmanagedexports-package-dllexport-to-call-c-sharp-dll-in-vba-triggers – clamchoda Feb 17 '23 at 16:41
  • 1
    What about Excel DNA: https://excel-dna.net/ I personally like it, but deploying i have not tested yet. It produces .xll for both 32 and 64 bit versions. – Red Hare Feb 17 '23 at 17:58
  • Edit, I managed to build my c# in .NET 4.5 instead of 4.7.2 and it worked! Thanks for the link @clamchoda. Next I will be exploring c++ to c++CLR to c# and seeing how robust it feels. – puredopamine Feb 17 '23 at 20:21
  • @puredopamine No problem! I actually use this with c#/c++ in a production environment with over 300++ installs. I haven't seen any problems, just make sure your targeting platforms specifically. Only thing I've found is you can't compile for `Any CPU`. – clamchoda Feb 17 '23 at 21:00
  • @clamchoda to be clear I don't actually know if I did use the method in your link in it's entirety, but the switch to 4.5 from 4.7.2 was mentioned there so it helped. Here is what I copied step by step https://stackoverflow.com/questions/1903220/side-by-side-com-interop-with-c-sharp-and-vba – puredopamine Feb 18 '23 at 08:17
  • Using COM interop from C# to VBA is the normal way to do this. Its not very painful even though it probably sounds like it in the documentation. `DLLExport` might be easier for simple situations. I would not mess around with C++ unless absolutely necessary or you're just very curious. – StayOnTarget Feb 18 '23 at 12:59
  • @StayOnTarget And that required admin rights to compile right? and again for registration on user's machines? I do not have reliable access to admin rights hence trying to avoid using them. – puredopamine Feb 19 '23 at 11:18
  • @puredopamine yes for the developer there is no good way around needing to run the IDE as admin in order to register COM DLLs. (You could register them manually but still would need to be admin). However for the end user no registration is needed whatsoever if you setup an application manifest. (See https://github.com/wqweto/UMMM for example). – StayOnTarget Feb 21 '23 at 12:49
  • Note that one of the main advantages of COM is that it exposes an object oriented interface to your assembly/DLL. Using a straight "DLLExport" style approach can't do that. Maybe you can get by that way but you'd have to invent some wrapper layer to make it work except in very simple cases. – StayOnTarget Feb 21 '23 at 12:51
  • Thanks @StayOnTarget I have successfully done the wrapper method. As for using a manifest file, where would that manifest live in relation to my c#dll? – puredopamine Feb 23 '23 at 07:27
  • The application manifest has to be setup for the .EXE, not for the DLLs. https://learn.microsoft.com/en-us/windows/win32/sbscs/application-manifests#file-location – StayOnTarget Feb 23 '23 at 18:34

0 Answers0