with VSTO Add-ins

Create a class with your functions in VSTO
<System.Runtime.InteropServices.ComVisible(True)> _
Public Class MyManagedFunctions
Public Function GetNumber() As Integer
Return 42
End Function
End Class

2. Wire up your class to VBA in VSTO

Private Sub ThisWorkbook_Open() Handles Me.Open
Me.Application.Run("RegisterCallback", New MyManagedFunctions)
End Sub

3. Create Hook for managed code and a wrapper for the functions in VBA

In a VBA module in your spreadsheet or document
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

Now you can enter =GetNumberFromVSTO() in a cell, when excel starts the cell value should be 42.

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext