MS Excel

These are MS Excel-specific VBA tips:

Close Reviewing toolbar; simple method — often when I open a spreadsheet someone has emailed me, Excel has insisted upon posting the Reviewing toolbar. I find this annoying, and so the following macro, if put into your Excel PERSONAL.XLS file (located in your %USERPROFILE%\Application Data\Microsoft\Excel\XLSTART folder) will cause Excel to close the Reviewing toolbar each time Excel is started. It is worth noting that Microsoft has deprecated the Auto_Open() method and so this is not an ideal solution; also this method only closes the toolbar when Excel initially starts.

Sub Auto_Open()
'
' AutOpen Macro
' Actions to perform each time a workbook is opened.
'
    Application.CommandBars("Reviewing").Visible = False        ' Close the Reviewing toolbar.
End Sub

Close Reviewing toolbar; robust method — to completely address the Reviewing toolbar problem it is necessary to use Application Events (see Chip Pearson's excellent information on this subject); also, since you may not always want the toolbar to disappear any Reviewing toolbar manager needs to be configurable. I've written an Excel Add-In to properly address this problem. If you want to see how this is accomplished, download the Reviewing Toolbar Helper Add-In and inspect the source code. To install the Add-In: (1) Exit Excel; (2) Put the .xla file in your %USERPROFILE%\Application Data\Microsoft\AddIns folder; (3) Start Excel; (4) Enable the Add-In via [Tools]à[Add-Ins…]; and (5) Restart Excel. You can open the Reviewing Toolbar Helper Options panel from a new menu that the Add-In installs.
Conditional formatting of a cell if it contains (or doesn't contain) a formula — recently, I wanted to change the shading of a cell if the cell had its formula replaced with a value. I found the answer elsewhere on the web (Identify formulas using Conditional Formatting); but, I've also capture the essence of it here for my own future reference. The trick is to use Excel's GET.CELL function in a defined name. So, create a defined name CellHasFormula with a value of =GET.CELL(48,INDIRECT("rc",FALSE)) and then use CellHasFormula in the conditional formatting rule.
Delete hidden columns — I recently had a need to remove the hidden columns from a set of Excel workbooks before providing them to a client. The following macro, if put into your Excel PERSONAL.XLS file (located in your %USERPROFILE%\Application Data\Microsoft\Excel\XLSTART folder) will cause Excel to delete any hidden columns that exist within a set of selected cells (you can select the entire worksheet or only a few cells/columns); or download the Delete_Hidden_Columns Add-In.

Sub DeleteHiddenColumns()
'
' DeleteHiddenColumns() Macro
' Within the selection, delete any columns that are marked as hidden.
'
' The following code will *not* work because we are removing objects
' from the collection and VBA's OO model is not robust enough to cope
' with this situation:
'        Dim myCol As Range
'        For Each myCol In Selection.Columns
'            If True = myCol.EntireColumn.Hidden Then
'                myCol.EntireColumn.Hidden = False
'                myCol.Delete Shift:=xlToLeft
'            End If
'        Next myCol
'
    If 1 <> Selection.Areas.Count Then
        MsgBox "Error: the selection must be contiguous."
    Else
        Dim i As Integer
        Dim firstCol As Integer
        Dim lastCol As Integer
        
        firstCol = Selection.Item(1).Column
        lastCol = firstCol + Selection.Columns.Count - 1
        
        For i = lastCol To firstCol Step -1
            With Selection.Worksheet.Columns(i)
                If True = .EntireColumn.Hidden Then
                    .EntireColumn.Hidden = False
                    .Delete Shift:=xlToLeft
                End If
            End With
        Next i
    End If
End Sub

FormatInterval() — Recently, for an availability chart I was creating, I needed to take an elapsed time (a server outage) and format that time into the form of "n days n hours n minutes n seconds" of time. Excel doesn't offer that as a native function, and I was also unable to find anything on the net; so, I wrote something. This function is the result.
IsColorN() — I regularly receive an activity status chart wherein completed actions are marked with a Blue background color; where I must extract dates from only the cells with a Blue background. Unfortunately, Excel doesn't offer an IsXXX() function for testing cell colouration. Chip Pearson's excellent website has some VBA code that exposes Excel's ColorIndex property; however, it was not in a form I could easily utilise. So, I have taken Chip's key function and packaged in into an Excel Add-In which introduces a function called IsColorN() that can be easily used in if() functions. The Add-In also provides a function for determining a particular cell's ColorIndex values. See the Close Reviewing Toolbar Robust tip, above, for instructions on how to install an Add-In.
Menu manager — After writing several Excel add-in modules I wanted to add code to have all the modules appear on a single menu on Excel's menu bar. I was unable to find any code others had posted to manage a menu, but with the help of some information posted on Chip Pearson's website I was able to make one myself.
Multireplace — Replace a string in a collection of workbooks. This Add-In adds a new menu item MyàReplace string in files… that prompts you for strings and a folder and then opens all of the workbooks in that folder and the folders below it and performs a Find-Replace (including within the worksheet headers and footers). To install, go to Excel's Add-In panel (ToolsàAdd-Ins…) and add this file.
RCtoA1() — Code to convert RC numerical notation to A1 letter-number notation (that is, convert from R1C1 to A1 addresses).
Set vertical alignment to top (Excel) — for no explicable reason, Excel defaults to a vertical alignment of "bottom". Since I usually want cells aligned to top, I put together this code. Place the following code snippet in your Excel PERSONAL.XLS file (located in your %USERPROFILE%\Application Data\Microsoft\Excel\XLSTART folder); then assign that macro to a button on your toolbar.

Sub SetVerticalAlignmentToTop()
'
' SetVerticalAlignmentToTop() - Change the vertical alignment to Top for all selected cells.
'
    With Selection
        .VerticalAlignment = xlTop
    End With
End Sub

Toggle between RC and A1 addressing — When I edit a spreadsheet I think in RC mode; that is, Row X Column Y format (e.g., "R34C2"). I do not cope well with Excel's default Column N Row Y (e.g., "B34") mode. When someone sends me a spreadsheet, I sometimes have to toggle it back into RC addressing mode, so I attached the following code to a button on my toolbar; which allows me to easily toggle between the two modes. Here's the code offered as an Excel Add-In (drop it into your Add-In folder): for Excel 2003 and earlier; for Excel 2007 and later.

Sub RC_A1_Toggle()
'
' RC_A1_Toggle Macro
' Toggle between RC and A1 addressing modes.
'
    If Application.ReferenceStyle = xlR1C1 Then
        Application.ReferenceStyle = xlA1
    Else
        Application.ReferenceStyle = xlR1C1
    End If
End Sub

Other Resources

See the page that linked to this one for other VBA resources I have found helpful.