Piecing it all together (Part 3)
Part 2Step 3 – The Add-InOne 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()
MenuBars(xlWorksheet).Menus("BPStuff").Delete
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
Wend
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.
Labels: Apps, Code, VB, WIN32
AddrMon
October 2005
Subscribe!