How to perform a callback into an assembly from a worksheet function

Certain functionality in the Microsoft Excel object model and in the Microsoft Word object model is tightly integrated with Microsoft Visual Basic for Applications and is not accessible with COM interop. You must use a Visual Basic for Applications wrapper to perform a callback into your assembly to perform the following actions:

• Create Microsoft Excel worksheet user-defined functions (UDFs) to call a function in an assembly.
• Use the OnKey and the OnTime methods of the Microsoft Excel Application object to call a function in an assembly.
• Assign shortcut keys to a function that is exposed in your assembly. To do this, you must use a Visual Basic for Applications wrapper that implements the OnKey method to trap the keystroke and to perform the callback.

This article describes how perform a callback into a Microsoft Visual Studio Tools for the Microsoft Office System assembly to return the value of a function in the assembly by using a Microsoft Excel spreadsheet function. In this example, the function returns the user name to the spreadsheet.

Build the assembly

1. Start Microsoft Visual Studio .NET 2003.
2. On the File menu, click New, and then click Project.
3. Under Microsoft Office System Projects, click Visual Basic Projects, and then click Excel Workbook.
4. Click OK to start the Microsoft Office Project Wizard.
5. Click Create new document, and then click Finish.
6. In the Code window, locate the following code:

     ' This function is called when the workbook is opened.
       Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
           
       End Sub

   Replace this code with the following code:

     ' This function is called when the workbook is opened.
       Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
           ThisApplication.Run("RegisterCallback", Me)
       End Sub

7. In the Code window, paste the following code in the class module:

       Public Function UserName() As Object
           Try
               Return System.Security.Principal.WindowsIdentity.GetCurrent.Name.ToString
           Catch ex As Exception
               Return "Error!"
           End Try
       End Function

8. Press F5 to build and then run the project.

Modify the Excel workbook

1. Make sure that the workbook is open in Microsoft Excel.
2. On the Tools menu, point to Macro, and then click Security.
3. Click the Security Level tab, click Medium, and then click OK.
4. On the Tools menu, point to Macro, and then click Visual Basic Editor.
5. On the Insert menu, click Module.
6. Add the following code to the module:

       Public objManaged As Object
   
       Public Sub RegisterCallback(o As Object)
           Set objManaged = o
       End Sub
   
       Public Function UserName() As Variant
           UserName = objManaged.UserName
       End Function
   

7. On the File menu, click Close and Return to Microsoft Office Excel.
8. Save your changes to the workbook, and then quit Microsoft Excel.

Test the callback function

1. Start Microsoft Excel, and then open the workbook.
2. In the security warning dialog box, click Enable Macros.
3. In one of the cells of the workbook, enter the following code, and then press ENTER:

   =UserName()

4. The user name is then returned to the cell in the workbook.