Piecing it all together (Part 3)

Part 2

Step 3 – The Add-In

One nice thing I like about Microsoft® Office® is the ability to write code behind the application. This allows for the creation of complex macros and coding in VBA. You can write [virtually] anything that you can with Visual Basic®, with one big differences being the inability to make stand alone applications.
The next step takes us into Microsoft® Excel®. Once it is running open up the Visual Basic® Editor (Alt+F11) and Insert a Module.

The easiest way for an end user to call our DLL is via a menu item. Seeing how we are creating an Add-in, our menu item should be created when loaded and removed when unloaded. For this we use the Auto_Open and Auto_Close procedures. I also like to put version information in my menus. We also need to declare our DLL function so that our VBA ‘application’ knows it exists.
[Here comes the VB part]

Option Explicit

Const strVersion As String = "Rev 1.00A"

Declare Function DoEmpty Lib "emptydll" (ByVal thestring As String, _
ByVal base As Single, ByVal newbase As Single, ByRef ooutsingle As Single) As Boolean

Sub Auto_Open()
Dim MyMenu As Menu

Set MyMenu = MenuBars(xlWorksheet).Menus.Add("BPStuff", 10)
MyMenu.MenuItems.Add "CallIt", "CallMyEmptyDll"
MyMenu.MenuItems.Add "---"
MyMenu.MenuItems.Add strVersion
End Sub

Sub Auto_Close()
End Sub

Sub CallMyEmptyDll()

Dim sometext As String * 30
Dim returnsingle As Single
Dim theresult As Boolean

sometext = ActiveCell.Value
While ((sometext Empty) And Not (IsEmpty(ActiveCell)))
theresult = DoEmpty(sometext, 400, 450.56, returnsingle)
If theresult Then
Selection.Offset(0, 1).Select
ActiveCell.Value = returnsingle
Selection.Offset(1, -1).Select
sometext = ActiveCell.Value
End If
End Sub

This simple example looks at the current cell and checks for a value and then ‘performs a calculation’ and places the result in the adjacent cell. To allow for an undetermined number of items this procedure goes through a list and stops when it finds a blank cell or value. The only thing left is to save the application as an Excel® Add-in (*.xla) and place it in the Add-in directory (or any other directory). From within Excel® select Tools --> Add-ins and activate your newly saved add-in. Once active it will load each time.

Well, I think we’re all done with our project. At this point you should be able to select a cell and call our function from a menu.

Reading Stuff

Information in this document subject to change without notice.
All Software source code published is for demonstration and knowledge sharing purposes only. The Code is supplied "as is" without warranty as to result, performance or merchantability. Use at your own risk.
The opinions expressed herein are the opinions of the author and do not reflect those of any other entity.