A real-life exampleI 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.
To accomplish that the following function is used. The AdjustedSelection functionPrivate 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 FunctionThis 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: The Formulas to values macroDid 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 SubHow 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 macroUnlike 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 SubFor 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... macroThe 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:
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 SubHow 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. The add-inYou can download the add-in here: General_add-in.zip. It contains the three macro's explained above and the Extra tab with the necessary commands.
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.
| |