0

Are you trying to access .Net DLL from within MS Office Vba macro ? Then you simply need to switch perspective and write your program in Vb.net or C# .Net using Visual Studio and call excel library methods from there. I wasted a lot of time trying to call a .Net library from VBa - see my original message further below. Call the old technology from the new, not the other way round...

Thanks for setting me on the right track Govert !

How to: Access Office Interop Objects by Using Visual C# Features (C# Programming Guide) https://msdn.microsoft.com/en-us/library/dd264733.aspx

-------------------- original cry for help -------------------------------------

Hello awesome people of stackoverflow. Many thanks for all the great QA's I have perused to date. Now is the time for my first question...

I'm unable to figure out how to query an Azure Service Bus Queue from VBa in the most elegant way possible. Please help! I'm losing sleep over it...

What I have tried is compiling azuresblite open source library with COM interop enabled, and using that within VBa. But it's fraught with challenges (constructors not supported by COM, static members not visible etc.) I then tried writing a COM friendly wrapper over Microsoft.ServiceBus.dll but that gets ugly and complicated, and involves some difficulty installing Microsoft.ServiceBus.dll on non-development machine without the Azure SDK, and it's code intensive and too hard to debug etc...

My latest thoughts are around - Can Visual Studio for Office 365 offer me what I need ? (but how does this web app in spreadsheet help me achieve elegant waiting / no cpu usage / no constant refreshes when the message queue is empty ?) - write an Excel add-in using http://exceldna.codeplex.com/ (will it address the problems I had with dependency on Microsoft.ServiceBus.dll and so forth ?)

Thanks for reading.

golfalot
  • 956
  • 12
  • 22

2 Answers2

0

Disclaimer: I develop the Excel-DNA library.

You're probably better off doing the integration in .NET and not trying to do it with VBA. You can either make an Excel add-in that runs inside Excel, or a runner application (an executable) that drives Excel from outside. For your use-case, it's not clear which is better.

For an Excel add-in that you write with .NET, you'll do the development in Visual Studio but have two choices for the add-in framework:

  • VSTO (Visual Studio Tools for Office) - this is the Office development framework built into Visual Studio. You'd make an 'Application add-in' and install that on your Excel machines. It's the standard, but has some limitations (you can't make worksheet functions, need admin permissions to install add-ins, have tight coupling to particular Office versions etc.)

  • Excel-DNA - this is an independent framework for making Excel add-ins with .NET. It's open-source and more light-weight than VSTO but still gives you full access to the Excel functionality. Installation is easy (you can just copy a single file). But it's not well documented. Support and questions about Excel-DNA is best at the Excel-DNA Google group.

For an external application that drives the Excel calculations, you just use the COM interfaces to write stuff to Excel, calculate and read back the results. If you do the writes and reads in big ranges, and not cell-by-cell, this should be fast enough. You have the advantage of managing this as a separate process, so it actually sounds like a good plan for you. You don't need any special framework for this, just reference the COM interop library for Excel.

None of this will work with the web-based Excel or any of the other platform versions. You need to full Excel client on Windows.

Govert
  • 16,387
  • 4
  • 60
  • 70
  • ", so it actually sounds like a good plan for you". You weren't kidding Govert!!! I have written a console app using the Office Interop Objects, and like you said, that works brilliantly for my needs. Thank you again. – golfalot Sep 11 '15 at 21:10
0

That's annoying, I was just looking for a solution to this exact same problem for a proof of concept.

Given what I am seeing so far the only real solution seems to be to create a web-service front end (are we calling them micro-services now?) and call that from excel calling web service using VBA code in excel 2010 You can then write a nice little wrapper/api for the queue without putting the logic into Excel, so it follows good architectural practice, just needs a bit more thought (like all good architecture!)

Community
  • 1
  • 1
Charlie Phillips
  • 1