We help IT Professionals succeed at work.

VBA for excel

icd
icd asked
on
I am just learning VBA so please be gentle with my stupid question!

I am developing an 'excel application' to assist in the manual parsing of some data. I need to implement a number of functions, such as file import, split columns etc. Generally I have a good handle on what I need to do as far as programming these functions is concerned.

I have developed several macros (VBA procedures). As far as I understand it these macros are stored in the .xls file where I created them. If I refer to these macros in the future in another .xls file then excel loads them from the original .xls file (is this correct?)

I have now created a new tool bar and menu items linked to these macros. My expectation was that the menu items would be stored in the .xls file together with the macros. Not so. If I open a different .xls file the menu items are still visible.

So my question is in two parts.
1. Where are the settings for the excel toolbar and menu items stored?

2. How can I deploy this 'application' along with the toolbar modifications to other people?

Comment
Watch Question

Commented:
To do that the menu being store with the macro you must create the menu using code in events worksheet_active and wroksheets_deactivate to load and unload the menu with the xls
Commented:
Or better: Create an Add-In !
You can distribute that among your users quite easy and maintance is also easier. Plus you've got full control over what is opened, closed and so on....

Read this 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

Commented:
RoverM is on to something here.

If you have something like this that you need to distribute, I would suggest the same.

The other alternative is to save the Excel Workbook with the VBA project as a template - that way each new file will be based on the template and be saved with the vba code attached.

Good Luck!

Wileecoy.
icd

Author

Commented:
Thank you roverm.

I had just come across add-ins when I checked back to see your answer! Your article however was just what I needed.

Thanks
ICD

P.S. I still don't know where the menu and button settings are stored, I presume it is in the registry, but it no longer matters if I can implement a plug-in.

Commented:
You're welcome !

If you need anything more about Addin's just shout!
Did plenty of work with/in them...

D'Mzzl!
RoverM

Explore More ContentExplore courses, solutions, and other research materials related to this topic.