StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Concerning whether there is a way around COM or VBA I don't think that it is possible (at least not without any very dirty tricks). The reason is that the only way Office can execute external code (i.e. you add-in) is via COM. Even VSTO is still using the old IDTExtensibility2 COM interface underneath. IDTExtensibility2 is a COM interface that all add-ins for Microsoft Office applications must implement. </p> <p>Before VSTO, Office add-ins had to implement this IDTExtensibility2 interface themselves. In such a COM based add-in (or COM-visible managed add-in) you can simply add your UDF as described <a href="http://blogs.msdn.com/eric_carter/archive/2004/12/01/273127.aspx" rel="noreferrer">here</a>. </p> <p>However, now with VSTO, there is an additional layer of abstraction: VSTO uses a so-called <a href="http://msdn.microsoft.com/en-us/library/bb608603.aspx#UnmanagedLoader" rel="noreferrer">Solution Loader</a> implementing IDTExtensibility2, which is a dll provided by the VSTO runtime. This means that your add-in is no longer COM-visible. Hence, if you added a UDF to your VSTO add-in it won't be visible to Office.</p> <p>Paul Stubbs explains on his blog how to do with VSTO and VBA: <a href="http://blogs.msdn.com/pstubbs/archive/2004/12/31/344964.aspx" rel="noreferrer"><strong>How to create Excel UDFs in VSTO managed code</strong></a></p> <blockquote> <ol> <li><p>Create a class with your functions in VSTO</p> <pre><code><System.Runtime.InteropServices.ComVisible(True)> Public Class MyManagedFunctions Public Function GetNumber() As Integer Return 42 End Function End Class </code></pre></li> <li><p>Wire up your class to VBA in VSTO</p> <pre><code>Private Sub ThisWorkbook_Open() Handles Me.Open Me.Application.Run("RegisterCallback", New MyManagedFunctions) End Sub </code></pre></li> <li><p>Create Hook for managed code and a wrapper for the functions in VBA</p> <p>In a VBA module in your spreadsheet or document</p> <pre><code>Dim managedObject As Object Public Sub RegisterCallback(callback As Object) Set managedObject = callback End Sub Public Function GetNumberFromVSTO() As Integer GetNumberFromVSTO = managedObject.GetNumber() End Function </code></pre></li> </ol> <p>Now you can enter <code>=GetNumberFromVSTO()</code> in a cell, when excel starts the cell value should be 42.</p> </blockquote>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload