Solved

VB Subroutine to Excel Add-In

Posted on 2001-09-01
12
543 Views
Last Modified: 2009-03-02
I have a number of useful Visual Basic subroutines and functions which I'd like to make available to the Excel users in my office (all are non-VB or VBA users).  I know these could be added as VBA subroutines and functions; however, I'd like to protect the security of my source code.  I'd like to create Excel Add-In modules based upon my VB subroutines or functions.  Are there useful books or web sites where I can get more information on the mechanics of building Excel Add-In, especially for a VB programer.  Thanks in advance for your assistance.
0
Comment
Question by:prionus
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 6

Expert Comment

by:Triskelion
ID: 6448264
All you really need to do is Save As.
Change the type to Add-in,
use a modify password and you're done.
0
 
LVL 6

Expert Comment

by:Triskelion
ID: 6448271
I goofed.
I the code editor, set a password for viewing in the properties dialog box.
0
 
LVL 12

Accepted Solution

by:
roverm earned 200 total points
ID: 6448653
From MSDN:

Creating Add-ins in Microsoft Excel 97
John Clarkson
Microsoft Corporation

April 1999

Summary: Describes the process of creating a simple Microsoft? Excel add-in. (7 printed pages) Provides detailed instructions and complete sample code.

Contents
Introduction
Adding a Command to the Tools Menu
Building the Product Sales Report
Loading, Unloading, and Running the Add-in

Introduction
A Microsoft Excel add-in is a workbook with an .xla extension. Add-ins are an ideal vehicle for storing and distributing complete applications, or custom functions and utilities. Advantages of distributing solutions as add-ins include:

Microsoft Visual Basic? for Applications (VBA) code in an add-in can be compiled at the time the file is saved. Code in a normal workbook isn't compiled until the file is run. Because add-in code is compiled ahead of time, it runs faster.


Add-in code can be hidden, preventing users from either viewing or modifying the code.


Worksheets in an add-in are hidden, allowing you to store formulas or data and keep them hidden from users.


Add-in events can't be bypassed by holding down the SHIFT key when the add-in is opened. This ensures that your initialization code will run.
This article explains how to create an Excel add-in, and describes the code in a sample add-in that does the following:

Adds a Report command to the Tools menu in Excel.


Opens an Excel worksheet file.


Uses an Access 97 query to import data from the Northwind sample database and add it to the Excel worksheet.
Excel add-ins are installed by default in the \Program Files\Microsoft Office\Office\Library folder. Loading an add-in makes the feature available in Excel and adds any associated commands to the appropriate menus.

Clicking the Report command on the Tools menu starts the Product Sales Report add-in described in this article. The next section describes how to add the Report command to the Excel user interface.

Adding a Command to the Tools Menu
This section explains how to create the code that adds and deletes a Report command on the Tools menu. (Please note that this code will not actually run until you install the add-in, as described in a later section.)

In Excel, press ALT+F11 to open the Visual Basic Editor (VBE).


In the project window, double-click ThisWorkbook. Copy the following event procedures to the ThisWorkbook module.
Option Explicit
Private Sub Workbook_AddinInstall()
' This procedure adds an item to the Tools
' menu on the worksheet menu bar.

    ' This will be set to the Tools menu.
    Dim objCmdBrPp As CommandBarPopup
    ' This will be set to the Project menu item.
    Dim objCmdBtn As CommandBarButton
   
    ' Create an object variable referring
    ' to the Tools menu.
    Set objCmdBrPp = Application.CommandBars _
            ("Worksheet Menu Bar").Controls("Tools")
    ' Add a command in the fourth position
    ' on the Tools menu.
    On Error Resume Next
    ' This generates a run-time error if
    ' the menu item is absent.
    Set objCmdBtn = objCmdBrPp.Controls("Report")
    ' If a run-time error is generated,
    ' add the menu item.
    If Err.Number <> 0 Then
        Set objCmdBtn = objCmdBrPp.Controls.Add _
                (Type:=msoControlButton, before:=4)
    End If
    ' Disables the error handler.
    On Error GoTo 0
   
    ' Set properties for the new menu item.
    With objCmdBtn
        .Caption = "Report"
        .OnAction = "AddInCode.Master"
    End With

End Sub

Private Sub Workbook_AddinUninstall()
' This procedure deletes an item on the Tools
' menu on the worksheet menu bar.

    ' If the Project command exists, delete it.
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar") _
           .Controls("Tools").Controls("Report").Delete
End Sub

Comments on the Code
The AddinInstall and AddinUninstall events occur when an add-in is installed and uninstalled, and are a convenient location for initialization and clean-up code. In this case, we're adding a Report command, which will be used to launch the Product Sales Report add-in, to the Tools menu.

The Delete method in the AddinUninstall event relies on the item's key, rather than its position, to identify the right item to delete.

    Application.CommandBars("Worksheet Menu Bar") _
        .Controls("Tools").Controls("Report").Delete

This is because, when Excel is closed and restarted, if our custom menu item is left in this position on the Tools menu, it will move up one position. By using the key, we're not relying on the Report command being in the fourth position on the menu. (By the way, you don't need to worry about deleting menu items accidentally. Excel replaces any missing built-in menu items each time it is started. Of course, if you delete the Exit command . . .)

The OnAction property sets the name of the procedure that runs when the Report menu item is clicked.

    With objCmdBtn
        .Caption = "Report"
        .OnAction = "AddInCode.Master"
    End With

The procedure the OnAction property calls must be located in a code module, and you must specify the module name.

Building the Product Sales Report
This section explains how to create the code that builds the Product Sales Report.

In the Visual Basic Editor on the Tools menu, click References.


In the References dialog box, click the checkbox beside Microsoft DAO 3.51 Object Library (or the latest version of DAO available), and then click OK.


On the Insert menu, click Module.


Press F4 to open the Properties window, and set the module's Name property to AddInCode.


Copy the following statements and procedures to the AddInCode module.
Option Explicit
Dim objRecordset As Recordset
Dim objWorkbook  As Excel.Workbook

Sub Master()
' Called by the AddinInstall event.
' This procedure calls other procedures and
' cleans up object variables.
   
    Call DbConnect
    Call GetProductData

    ' Close object variables.
    Set objRecordset = Nothing
    Set objWorkbook = Nothing
   
End Sub


Sub DbConnect()
' Called by Master procedure.
' This procedure opens the database and
' creates the Recordset object that
' contains data for the presentation.

    Dim dbsNorthwind As Database

    ' Edit this path to match your drive, if necessary.
    Set dbsNorthwind = OpenDatabase _
            ("C:\Program Files\Microsoft " _
            & "Office\Office\Samples\Northwind.mdb")

    Set objRecordset = dbsNorthwind. _
            OpenRecordset("product sales for 1995", _
            dbOpenSnapshot)

    Set dbsNorthwind = Nothing
   
End Sub


Sub GetProductData()
' Called by Master procedure.
' This procedure retrieves data
' from the product sales query.

    ' Open the report worksheet.
    ' Store a reference to the workbook
    ' containing the worksheet.
    Set objWorkbook = Excel.Workbooks.Add
   
    ' Get data from the query.
    With objRecordset
        Do
            ' Call the procedure that adds this
            ' data to the worksheet.
            Call AddToSheet _
                    (objRecordset.AbsolutePosition, _
                     objRecordset!ProductName & "", _
                     objRecordset!CategoryName & "", _
                     objRecordset!ProductSales)
            ' Move to the next record.
            .MoveNext
        Loop Until .EOF = True
    End With
End Sub


Sub AddToSheet(lngRowNumber As Long, _
               strProdName As String, _
               strCatName As String, _
               lngProdSales As Long)
' Called by GetProductData procedure.
' This procedure adds data to the worksheet.
    With objWorkbook.Worksheets(1) _
            .Rows(lngRowNumber + 3)
        .Cells(, 1).Value = strProdName
        .Cells(, 2).Value = strCatName
        .Cells(, 3).Value = lngProdSales
    End With

End Sub

In Excel on the File menu, click Properties. Feel free to add your own text here, but a good choice for title is "Sales Report," with "Sample add-in" for comments. The title identifies an add-in in the Add-In Manager. The comment is displayed when an add-in is selected in Add-In Manager. Click OK.


In the VBE, save your project to a working folder as Report.xls.
Comments on the Code
Data is retrieved from the Access query and passed to the AddToSheet procedure with the following statements in the GetProductData procedure. The first expression in the procedure call, objRecordset.AbsolutePosition, passes a row number. The next two expressions, objRecordset!ProductName & "" and objRecordset!CategoryName & "", both retrieve string values from the Access query and pad the result with a zero-length string. Adding the zero-length string prevents "Invalid use of Null" errors in case Null values are encountered.

    With objRecordset
        Do
            ' Call the procedure that adds this data
            ' to the worksheet.
            Call AddToSheet _
                    (objRecordset.AbsolutePosition, _
                     objRecordset!ProductName & "", _
                     objRecordset!CategoryName & "", _
                     objRecordset!ProductSales)
           
            ' Move to the next record.
            .MoveNext
        Loop Until .EOF = True
       
    End With

These statements in the AddToSheet procedure add data to the report worksheet. The current record number in the Recordset object is passed in by the lngRowNumber argument and used to set the row number in the With...End With statement. With the row number already set, the Cells property works fine with just a column argument.

    With objWorkSheet.Rows(lngRowNumber + 3)
        .Cells(, 1).Value = strProdName
        .Cells(, 2).Value = strCatName
        .Cells(, 3).Value = lngProdSales
    End With

Loading, Unloading, and Running the Add-in
This section explains how to install and run the Product Sales Report add-in.

With Report.xls (the file created in the previous section) open in Excel, on the File menu click Save As.


Select the \Program Files\Microsoft Office\Office\Library folder, set the file type to Microsoft Excel Add-In, and click Save. (This creates a file named Report.xla in the folder.)


Close Report.xls and open a blank workbook. (This isn't strictly necessary, but for your own sanity it helps to get the .xls version of the add-in out of the way.)


On the Tools menu, point to Add-Ins and click Add-In Manager. Check the box indicating the Product Sales Report add-in (Report.xla) and click OK.


On the Tools menu, click Sales Report.


After the report is built, on the Tools menu click Add-Ins. In Add-In Manager, clear the box indicating the Product Sales Report add-in and click OK to uninstall the add-in.


D'Mzzl!
RoverM
0
 
LVL 12

Expert Comment

by:roverm
ID: 6448654
I've build some Add-Ins myself. The most important sections are:

Private Sub Workbook_AddinInstall()
End Sub

Private Sub Workbook_AddinUninstall()
End Sub

These are for loading and unloading the Add-In.
You can place up to anything in here. Add menu's, calls to functions and so on.
In the above example a new item is created in the menu.
Just can also do this and refer to your own functions.

D'Mzzl!
RoverM

0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6448838
Hearing...
0
 
LVL 12

Expert Comment

by:roverm
ID: 6448885
Richie: You've got some kind of text2speech converter for EE ? <LOL>
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:prionus
ID: 6449266
Thanks to roverm for plenty to work with.  I also want to thank triskelion for their suggestion.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6449321
Sorry, roverm but no.
Anyway, there is some examples on net regarding it.
Maybe in HD, let me see...
By the way, congratulations for this one ;)
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6449325
0
 
LVL 12

Expert Comment

by:roverm
ID: 6449836
Richie: I was just kidding for you comment "hearing..." Thanks anyway! :>>

Prionus:
Thanks for the points!

D'Mzzl!
RoverM
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6451029
Gee...
0
 
LVL 12

Expert Comment

by:roverm
ID: 6451052
Richie:
I downloaded the example from planet-source-code.
Just one word: WOW!!!!!!!

This is a great OCX. Thanks!

D'Mzzl!
RoverM
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now