|
|
|
| Title | Use a Visual Basic DLL file in an Excel spreadsheet |
|---|
| Keywords | DLL, Excel |
|---|
| Categories | Office, Software Engineering |
|---|
|
|
|
Before you start this, you should consider implementing the DLL's features within Excel using VBA. That is a lot less complicated although a DLL may give you better performance for very complicated calculations.
In Visual Basic:
- Create a new ActiveX DLL project.
- Change the name of the default class to the name you want your server class to have. For this example, TestClass.
- In the Project menu, select the Properties command at the bottom.
- Set the Project Name to the name you want the server DLL to have. For this example, MyTestServer.
- Set the Project Description to the string you want to see when you select the DLL reference in Excel. For this example, My Test Server.
- In the File menu, select the Make command to compile the DLL.
In the Excel workbook:
- Open Tools\Macro\Visual Basic Editor.
- In the Tools menu, select References. Locate the server class you created in Visual Basic and select it. If it isn't in the list, click Browse and select the DLL.
- Add a module to the workbook. Create a function that makes an instance of the server class and uses the instance's methods. The following example creates an instance of the TestClass class in the DLL project named MyTestServer. It then calls the object's SquareMe function.
|
|
' Execute the MyTestServer.TestClass.SquareMe method.
Public Function CallSquareMe(ByVal v As Single) As Single
Dim obj As MyTestServer.TestClass
Set obj = New MyTestServer.TestClass
CallSquareMe = obj.SquareMe(v)
End Function
|
|
- Use the function in cell formulas as in:
=CallSquareMe(C7)
|
| |
|
| |
|
|
