Microsoft KB Archive/830467

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

Article ID: 830467

Article Last Modified on 2/3/2006

-

APPLIES TO


 * Microsoft Visual Studio Tools for the Microsoft Office System version 2003
 * Microsoft Office Excel 2003

-





SUMMARY
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
 Start Microsoft Visual Studio .NET 2003. On the File menu, click New, and then click Project. Under Microsoft Office System Projects, click Visual Basic Projects, and then click Excel Workbook. Click OK to start the Microsoft Office Project Wizard. Click Create new document, and then click Finish.  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(&quot;RegisterCallback&quot;, Me) End Sub   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 &quot;Error!&quot; End Try End Function </li> Press F5 to build and then run the project.</li></ol>

Modify the Excel workbook
<ol> Make sure that the workbook is open in Microsoft Excel.</li> On the Tools menu, point to Macro, and then click Security.</li> Click the Security Level tab, click Medium, and then click OK.</li> On the Tools menu, point to Macro, and then click Visual Basic Editor.</li> On the Insert menu, click Module.</li>  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 </li> On the File menu, click Close and Return to Microsoft Office Excel.</li> Save your changes to the workbook, and then quit Microsoft Excel.</li></ol>

Test the callback function
<ol> Start Microsoft Excel, and then open the workbook.</li> In the security warning dialog box, click Enable Macros.</li>  In one of the cells of the workbook, enter the following code, and then press ENTER: =UserName </li> The user name is then returned to the cell in the workbook.</li></ol>

Keywords: kbhowto KB830467

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.