Customizing the Office 2007 Ribbon

A real-life example

1. The Formula's to values macro is a macro that does just that but in one operation.
2. The Change Case macro is a macro that changes the text in a range of cells to UPPER CASE, lower case, Title Case or Sentence case.
3. The Formula... macro is a macro that lets you manipulate the contents of a range of cells directly.

• You can use the macros on a single cell, a range off cells or on multiple selections in one operation.
• When your sheet is filtered, the macros will only act on the visible cells.
• You can select entire row(s) and/or column(s) or even the entire sheet. The macros will automatically restrict the range to the Lastcell in use to prevent it to run "forever" especially in Excel 2007 with its 1 048 576 rows and 16 384 columns.

To accomplish that the following function is used.

The AdjustedSelection function

Private Function AdjustedSelection() As Range
Dim Area As Range, Lcc As Integer, Lcr As Long, C As Integer, R As Long
Set AdjustedSelection = Selection.Cells(1)      '}
Lcc = Selection.SpecialCells(xlLastCell).Column '}initialize
Lcr = Selection.SpecialCells(xlLastCell).Row    '}
For Each Area In Selection.Areas                'loop through all selected areas
   R = Area.Rows(Area.Rows.Count).Row           'area's bottommost row
   If Lcr < R Then R = Lcr                      'if LastCell's row is less: adjust
   C = Area.Columns(Area.Columns.Count).Column  'area's rightmost column
   If Lcc < C Then C = Lcc                      'if LastCell's column is less: adjust
   Set AdjustedSelection = Union(AdjustedSelection, Range(Area.Cells(1), Cells(R, C)))
Next Area
End Function

For Each Area In Selection.Areas : loops through all the selected areas. It determines for each area the rightmost column and bottommost row and compares it with the column and row of LastCell. If those are less, the area is adjusted.

Set AdjustedSelection = Union(AdjustedSelection, Range(Area.Cells(1), Cells(R, C))) : For each loop the Union method adds the area being processed to the list. When finished, AdjustedSelection refers to all the selected areas but with the ranges restricted to the active part of the worksheet. So you can select entire row(s) or column(s) or even the entire sheet without risking your macro will run "forever".

This function is used by all the following macros. These macros all start with:
If TypeName(Selection) <> "Range" Then Exit Sub : terminates the macro if the selection is not a range to prevent VBA to generate an error.

The Formulas to values macro

Sub Convert2values(Control As IRibbonControl)
If TypeName(Selection) <> "Range" Then Exit Sub
Dim Cell As Range
For Each Cell In AdjustedSelection()
   If Not Rows(Cell.Row).Hidden Then Cell.Value = Cell.Value
Next Cell
End Sub

The Change case macro

Sub ChangeCase(Control As IRibbonControl)
If TypeName(Selection) <> "Range" Then Exit Sub
Dim Cell As Range
For Each Cell In AdjustedSelection()
   If Not Rows(Cell.Row).Hidden Then
      Select Case Control.ID
         Case "U": Cell.Value = UCase(Cell.Value)
         Case "L": Cell.Value = LCase(Cell.Value)
         Case "T": Cell.Value = StrConv(Cell.Value, 3)
         Case "S": Cell.Value = UCase(Left(Cell.Value, 1))&LCase(Mid(Cell.Value, 2))
      End Select
   End If
Next Cell
End Sub

The Formula... macro

Normally you would enter the formula =a1/100 in an empty column, copy it to the other corresponding cells, then highlight the range, click copy, highlight the destination range, click paste/special/values and then delete the formulas. This is a rather tedious procedure.

You can even use the macro on multiple selections in one operation which Excel doesn't allow you to do with the paste/special command. When your sheet is filtered, the macro will only act on the visible cells.

Other examples:

• append a text to the contents of a range of cells: =a1&"some text"
• convert a range off cells from inches to centimeters: =a1*2.54
• increase a price list with i.e. 7%: =round(a1*1.07,2)
• convert a range of negative numbers to positive or vice versa: =a1*-1
• reverse two words in a range of cells: =mid(a1,find(" ",a1)+1,len(a1))&" "&left(a1,find(" ",a1)-1)

Sub Formule(Control As IRibbonControl)
Dim Cell As Range, X As String
If TypeName(Selection) <> "Range" Then Exit Sub
With ThisWorkbook.Sheets(Sheet1.Name)
   X = InputBox("Enter formula referencing cell A1", "Formula macro", .[A3])
   If InStr(1, X, "a1", 1) = 0 Then Exit Sub
   If Left(X, 1) <> "=" Then X = "="&X
   .[A2] = X
   .[A3] = "'"&X
   For Each Cell In AdjustedSelection()
      If Not Rows(Cell.Row).Hidden And Cell.Value <> "" Then
         .[A1] = Cell
         .[A2].Calculate
         If Not Application.IsError(.[A2]) Then Cell.Value = .[A2]
      End If
   Next Cell
End With
End Sub

X = InputBox("Enter formula referencing cell A1", "Formula macro", .[A3]) : stores your formula in the variable X.

If InStr(1, X, "a1", 1) = 0 Then Exit Sub : terminates the macro if X is empty (in case you pressed [Esc] or clicked Cancel) or if the formula doesn't contain "A1" in which case an error would occur.

If Left(X, 1) <> "=" Then X = "="&X : adds an equal sign before the formula in case you forgot it.

.[A2] = X : puts the formula in cell A2 (remember [A2] is a shortcut for Range("a2")).

.[A3] = " ' "&X : stores the formula in as a label in cell A3 for later use as default in input box.

For Each Cell In AdjustedSelection() first calls the AdjustedSelection function that restricts the selection(s) to the active part of the worksheet. Then it loops through all the cells of all selections and executes:

If Not Rows(Cell.Row).Hidden And Cell.Value <> "" Then : skips hidden rows and empty cells.

.[A1] = Cell : copies the value of the currently processed cell to cell A1.

.[A2].Calculate : calculates cell A2 containing your formula referencing the just changed cell A1.

If Not Application.IsError(.[A2]) Then Cell.Value = .[A2] : copies the result to the currently processed cell if it is not an error.

The add-in

How to install an add-in is explained at the end of Example 1 in What about Personal.xlsm?.

Of course you can add macro's of your own. How to add commands for them in the Ribbon is explained in Example 1.