## A real-life example

I developed some universal clever macros and use them for quite a while now finding them to be very useful. They are stored in an add-in (download) that adds an Extra tab to the ribbon so that they are available in all workbooks.
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.
They have the following characteristics in common:
• 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
```
This function restricts the selected range(s) based on the Range.SpecialCells(xlLastCell) method. This returns the cell selected when you press [End] and [Home] consecutively. The rows below this cell are not in use neither are the columns to it's right. So there will be nothing to process there. How does it work?

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

Did you ever tried to highlight a range in a filtered worksheet and issue the commands copy and then paste special/values? Well it doesn't work. You get an error. The Formula's to values macro is a macro that does just that and even in one operation and it works on multiple selections and/or acts only on visible cells in a filtered sheet which both aren't possible with Excel's paste/special command. Simply select a cell or range(s) you want and run the 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
```
How does it work?
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 Then Cell.Value = Cell.Value : replaces the formula with its value if the row isn't hidden.

### The Change case macro

Unlike Word, Excel doesn't have a Change Case command. You can only do it with a formula. This Change Case macro changes the text in a range of cells directly to UPPER CASE, lower case, Title Case or Sentence case. As for the other macros you can use it on a single cell, a range off cells or multi-range selections and on a filtered sheet.
```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
```
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 for every loop Select Case executes the statement based on the Control.ID variable. This variable is passed as an argument to the Sub procedure by the Ribbon. It is "U" if you clicked UPPER CASE, "L" if you choose lower case, "T" if you selected Title Case (every word capitalized) and "S" if you clicked Sentence case (only first word capitalized).

### The Formula... macro

The Formula... macro is a macro that lets you manipulate the contents of a range of cells directly. If you entered for example the amounts in a range off cells without the decimal period, you can use this macro to divide those cells by 100. Simply highlight the range, invoke the macro, enter the formula =a1/100 in the input box and click OK. That's all.

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)
Although this macro is very powerful, it can also be very dangerous. As you can see by the last example, those formulas can become rather complex, so the slightest mistake can generate unexpected results. So it is strongly advised to save your work before using it.
```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
```
How does it work?
Remember, since this macro resides in Module1 from the add-in, the cell references refer to the add-in's active worksheet.

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.