Danny's Weblog

2008 Aug 31 [ Sun ]

Adding a new function to an Excel right-click menu

A few months ago I posted a short routine that adds a (hopefully) useful function to Excel's right-click menu when you have a range of cells selected: www.panix.com [http://www.panix.com/~dannyw/weblog/Computers/Programming/Excel/filldown01.html]

At that time I did not explain the tricks for adding that function to the right-click menu. This posting lists those tricks. They aren't rocket science, and I don't go into every detail, but I think it's a better overview than most. You should read through the whole thing before you start trying stuff out.

This information is for Excel 2003. It is probably also good for Excel 2000, but is probably rather misleading for Word, and basically irrelevant for Access. I haven't tried Excel 2007, but I understand MS redesigned the whole menu structure for Excel 2007, so it probably won't work.

The following links may also be useful:

Adding menu items via VBA: www.fontstuff.com [http://www.fontstuff.com/vba/vbatut07.htm]

Creating an Excel addin: www.fontstuff.com [http://www.fontstuff.com/vba/vbatut03.htm]

If you are not very familiar with VBA, note that when you switch to the VBA window many of the top menu items do not work the same way. In particular, you can only get VBA help while you are in the VBA editor, not when you are in normal mode (and vice versa). Also, you cannot do "save as" (including "save as addin") while you are inside the VBA window.

1. The standard term in Windows for what I'm calling "right-click menus" is actually "context menus". However, neither term is relevant to VBA. VBA considers such menus to be simply a type of "commandbar", just like the main menu. In both Word and Excel you can assign a macro to a *non-context* menu via the GUI, but you can only assign a new function to a *context* menu via the GUI in Word; in Excel you need to use VBA, because the GUI doesn't let you (funnily enough).

The term "shortcut menu" usually means a menu accessed by a keystroke combination like ctrl-G.

2. For the sake of trying all this out, here's a very simple function: it makes the text in the currently selected cell (or cells) bold. Starting from a new empty workbook, open the VBA editor with Alt-F11, select Insert Module (to create Module1) and enter the following code in Module1:

Sub MakeBold()
selection.Font.Bold = "True"
End Sub

(When you test this later, remember that the right-click menu while you are actually editing a cell is a different menu from the one which comes up while you have the cell selected; the MakeBold routine will only work in the latter case.)

3. Then the basic code for creating a new right-click menu item to call that routine is like this:

Private Sub Workbook_Open()
Set NewControl = Application.CommandBars("Cell").Controls.Add
With NewControl
  .Caption = "Make bold"
  .OnAction = "Module1.MakeBold"
End With
End Sub

You need to put this in the "ThisWorkbook" module. You can do that by opening the VB editor (if it's not already open) with Alt-F11, double-clicking on "ThisWorkbook" (under "Microsoft Excel Objects" under "VBAProject" in the Project window) to open the "ThisWorkbook (Code)" window, and selecting "Workbook" on the left-hand dropdown menu (which initially said "General").

Then the effect is: when the workbook is (next) opened, the "make bold" function gets added to the context menu for each cell (or range of cells). And that feature will *still be there* after you close the workbook, on any other workbook, even after you close and re-open Excel! (Excel has stored the link from the menu to the workbook with the actual code, and will reopen the workbook as soon as you try to execute the menu item.)

4. Now there are two snags. One is that in some cases the above code can add the menu item *every time* the workbook is opened, accumulating more and more copies... and it can happen that they never get removed. The other snag is that it's a pain having to add utility code like this to each workbook where you need it.

5. There are several ways to get around the first problem.

Whatever you do, you need to do two things: don't add a menu item which already exists; and don't forget to remove the item when you exit. But it turns out that the best way to do those two things depends on what you do about the second problem above.

6. Your decision on how to install code like this (any kind of VBA code, not just context menus) depends on whether you want to distribute the code. The big problem is macro security. Excel by default (at least in Excel 2003) refuses to load macros, giving a scary error message and a misleading prompt about how to enable macros which just creates confusion. Unless your users are very used to receiving workbooks containing macros (which allows you to use the signing mechanism – Google search for selfcert.exe), it seems to me to be impractical to distribute macros inside a regular workbook.

7. Additionally, even if the macros are for your own use, it is very tedious to have to reinstall multiple functions and features in each new workbook, and especially to update an existing workbook with new or improved code. (And you have to re-run the signing mechanism for each change, however small.)

8. For these reasons, I think the only practical way to deploy Excel macros is by using the "Add-in" feature. An add-in is just a regular workbook which you put in a special directory for special handling; under 2003 the location is c:\Documents and Settings\dannyw\Application Data\Microsoft\Addins. This is the default location which Excel automatically switches to when you do "save as" "Microsoft Excel Add-in (.xla)".

Instead of manually copying the file to the addin folder, you can use the Tools - Addins - dialog, which allows you to "browse" for another location. Once you select an addin in another location, it will ask you if you want to copy the addin to the standard addins. Even if you don't, Excel will remember to load the addin from the nonstandard location the next time it starts up.

To be any use, an add-in has to contain VBA code. Also, there's no point including regular worksheet data in an add-in, unless it's used by the VBA code – for example, boilerplate text with copyright information which macros might add to every saved workbook.

Once the user has trustingly installed the addin, the macro code will always be available: for instance, the function names are all global, so workbooks can use the functions without needing to specify a module name. And without causing intrusive prompts about enabling macros. (Which makes me wonder about what happens if you just haplessly click on an .xla file link inside a browser.)

However, the function names for some reason don't appear in the list when you insert a function via the GUI; you have to know the name already.

Because the function names are global, you can rename the addin if desired: for instance, if you are a system administrator, you can check that everyone has now installed the latest version of an addin by checking that the filename is "utils-23.xla", and if anyone still has "utils-19.xla" you can grumble at them.

Also, you only need to update a function in one place, and then the updated function is automatically available in every workbook.

9. MS suggests using the "Personal Macro Workbook" for this kind of purpose. But there's only *one* of those per user. If you have multiple users, one or two of them will be already using theirs, and they won't be happy if you overwrite it. Even if the macros are only ever going to be used by you, you may want to keep different sets of macros in different files.

10. Your users will inevitably be exchanging workbooks all the time. If they include macros, every user will need to trust every other user, and/or spend time checking that any macros are safe. If all the workbooks use macros from a single addin, users need to trust only one source.

Additionally, Excel includes an optional weak encryption feature to prevent users opening an addin to view/edit it. This may be cool if you are *selling* the addin, but of course it prevents the educated user from checking that your macros are safe.

11. Because an addin is loaded once at startup, and the functions it provides are intended to stay in memory until Excel closes, the risk of adding menu items more than once is lessened. However, it is still worth putting in defensive code (especially while you are figuring all this out). Also, I felt I should provide some code in case you do *not* deploy the code as an addin.

12. You can check whether a menu item already exists with code like this:

Function Check_MakeBold()
Check_MakeBold = False
For Each Myitem In Commandbars("Cell").Controls
    If (Myitem.Caption = "Make bold") 
        Check_MakeBold = True
    End If
End For
End Function

If you open the Immediate window, you can run the routine like this:

debug.Print(check_makebold())

The equality test is case-sensitive, although the function name is not.

13. If necessary, you can delete the context menu item when a regular workbook closes like so:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
	On Error Resume Next
	Application.Commandbars("Cell").Controls("Make Bold").Delete
End Sub

By including the "on error" you don't need to check whether the "Make Bold" item exists before attempting to delete it.

The above routine needs to be entered in the "ThisWorkbook" module, using a similar approach as for the code running on workbook load (see above).

Another approach is to reset the entire r-click menu to its default state, ie (search Excel Help for "reset method") all the original items and no new ones:

Application.CommandBars("Cell").Reset

This is good if all of your menu additions are created by a single module, but if you have multiple menu items from different modules you will probably want to handle them individually.

Another approach – the one I hinted at in the previous posting – is to include the "temporary" parameter when you add the menu item:

Set newControl = Application.CommandBars("Cell").Controls.Add(, , , , temporary)

14. Once you have the addin installed and running, how do you debug it? It turns out that even the creator of the addin can't open it directly to edit it while it's running as an addin. But if you load it as a normal workbook instead, the functions aren't globally accessible from any workbook.

There is an interesting workaround that I haven't seen described elsewhere: you simply load the file both as an addin at startup, and then as a regular workbook. It seems like that would cause some sort of namespace conflict, but I haven't run into that. I haven't figured out the details of why.

15. If you try all this stuff out, you will accumulate a bunch of functions and want to implement the install procedure by looping through them. I leave that as an exercise for the reader.

2008 Mar 16 [ Sun ]

Useful subroutine in Excel: "filldown"

This is not the most complex stuff I've done in Excel but it is all my own code and does something which I think is generally useful, so I thought I'd list it.

It's intended to be linked to a right-click; I leave that out as an exercise for the reader... especially as I now realize that I wrote the code for that part incredibly badly, although it does work. Hint: there is a parameter for this: application.CommandBars("Cell").Controls.add which prevents an option being added multiple times. See one of the books (I forget which) in my post: www.panix.com [http://www.panix.com/~dannyw/weblog/Computers/Programming/Excel/books01.html]

It's intended to make it easier to enter data in a typical Excel table where the same values recur multiple times. For instance, if you are entering vehicle expenses in a company garage, you probably need to enter the expenses for each car multiple times with the same info so that you can easily sort the data with a pivot table. With this sub, you can just leave a blank for repeating fields, and when you're ready you can just right-click on the table and select the sub, and a value will fill down into all the empty cells below it for each record and field. In many cases it will halve the entry time and cut out a lot of errors as well (ie ensure that each repeated value is identical).

Sub filldown()
' dW 2007-12-20 Loops through a range
' if a cell is blank, copy from previous row
' (we hope)
Dim cellnum, cellrow, cellcolumn, rowcount, columncount As Long
If TypeName(Selection) <> "Range" Then
  Beep
  MsgBox "This command requires a cell/range to be selected."
  Exit Sub
End If
If Selection.Areas.Count > 1 Then
  Beep
  MsgBox "This command only works on a single range."
  Exit Sub
End If
rowcount = Selection.Rows.Count
' You can't fill down if less than 2 rows
If rowcount < 2 Then
  Exit Sub
End If
'
columncount = Selection.Columns.Count
' MsgBox ("Rows = " & rowcount & " Columns= " & columncount)
'
cellnum = 0
For Each cell In Selection
  cellnum = cellnum + 1
  ' The \ operator does integer division
  ' First row is row 0
  cellrow = (cellnum - 1) \ columncount
  ' Don't do anything on first row
  If cellrow > 0 Then
    cellcolumn = (cellnum - 1) Mod columncount
    ' MsgBox ("Cell: " & cellnum & "Row: " & cellrow & " Col: " & cellcolumn)
    ' I seem to have defined cell locations starting at 0,
    ' but Excel has cell x,y starting at 1,1.
    ' Need to handle cells with formulas separately
    If cell.Value = "" Then
        If Selection.Cells(cellrow, cellcolumn + 1).HasFormula Then
            cell.Formula = Selection.Cells(cellrow, cellcolumn + 1).Formula
        Else
            cell.Value = Selection.Cells(cellrow, cellcolumn + 1).Value
        End If
    End If
  End If
Next cell
End Sub

When you play with this, remember that Excel resets the undo stack every time you run a macro. If someone wants to fix this code so that it implements its own undo feature, that would be cool.

2008 Feb 02 [ Sat ]

The benefits of learning Excel VBA

The following link explains in amusing detail just how unexceptional the Societe Generale trader M. Kerviel is in his skills, or indeed modus operandi: www.theregister.co.uk [http://www.theregister.co.uk/2008/01/31/kerviel/]

From what little I know about the situation in such companies, the article is not misleading.

2008 Jan 29 [ Tue ]

Some books I've used for Excel VBA programming

Recently I've had to use Excel quite a lot. I used to be quite snooty about it (although I recognized that – after SQL Server – it was Microsoft's most respectable product). Now, having gotten into VBA programming, I have to admit you get a rather solid development environment.

Anyhow, I wanted to put in links to a couple of books I can recommend:

1. Excel 2007 VBA Programmming for Dummies - John Walkenbach

I actually read his version for Excel 2000 first. The two books are much the same. I had a feeling that he caught some errors in the new version, or maybe I just understood stuff more the second time through (I was unable to compare them except by memory).

I don't think Walkenbach ever did a version of this book for 2003. I don't think there was much difference between Excel 2000 and 2003, but there are a lot of changes in 2007 for the new menu ("commandbar") structure. And the 2007 book *only* documents the 2007 code - people expecting the book to be backwards-compatible with 2003 are going to be very disappointed.

But in general, I found the book quite ueseful.

Walkenbach recommends this "blog": www.dailydoesofexcel.com [http://www.dailydoesofexcel.com/]

He also recommends reading Microsoft's own newsgroups via Google. Search Google Groups for microsoft.public.excel.programming, as well as several others under microsoft.public.excel.

2. The Absolute Beginner's Guide to VBA

The advantage of this book is it explains how to run multiple Office apps using VBA: for instance, to read your Outlook mailbox (or at least the header fields and a smidgen of the text) into Excel.

Also, it does show various useful examples in non-Excel apps, and I may need to take a shot at Word VBA sometime.

Incidentally, I rather wish the titles of these books weren't so embarrassing. Don't bring them to your client site! I've actually done some fairly complex stuff with Excel, and if clients ever see this blog they'll get the wrong idea. Hmm, then again if they see some of my other posts my goose is cooked anyhow!

---

Debug: hittotal: 19 startban: 0 dancookie: endbandate: banned: 0 tempdate: tert: jse: jsno jsh: 19