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

Find next comment — Excel doesn't offer any way to find an embedded comment. One is able to make all comments visible; but, on a large multi-hundred or thousand line worksheet it is a tedious and error prone exercise to attempt to find the cell containing a comment that is causing auto-filtering to generate an error about moving objects. This short code snippet will allow you to find the "next" comment on the active worksheet:

Sub FindNextcomment()
'
' FindNextcomment() Macro
' Find the next comment.
'
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    If ActiveSheet.Comments.Count > 0 Then
        ActiveSheet.Comments(1).Visible = True
        ActiveSheet.Comments(1).Parent.Select
    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.
Merge Conditional Formatting — As you edit a worksheet, the conditional formatting can become a complete mess as data is copied around the worksheet. This add-in scans all the conditional formatting on a worksheet and merges rules that are identical. Note, it only does this for conditional formatting rules that use a formula. I rarely use the other types and so I haven't bothered to implement code to merge them.
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.
Personal Addin — Add some extra items to the Home tab.
Project Budget to Actual Tracking — workbook I use to track budgetted to actual hours and expenses on my consulting engagements.
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. Note that the Excel 2007 version allows you to configure how files are opened and saves (i.e., you can always save and/or files in a specific addressing mode).

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

Worksheet Name — An oft' requested Excel feature is the ability to use the name of the current worksheet in a formula (without resorting to VBA). Recently, Nick (who operates ExcelExperts.com) pointed out on his blog that the CELL() function can be used to obtain the sheetname. Here is the appropriate formula needed to retrieve the name of the worksheet on which the formula appears (I've shown this in R1C1 notation; so, if you use A1 notation then replace "RC" with a reference to the cell containing the formula). Note, this formula will fail in a new workbook until that workbook has been saved at least once.

=RIGHT(CELL("filename",RC),LEN(CELL("filename",RC))-FIND("]",CELL("filename",RC),1))

Other Resources

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