Slick Tricks in No-Cost "Ultimate Excel Add-In"

Tired of trying to find Excel features hidden among the options pages and dialog boxes? Putting all those options up front is just one of the many benefits of Ultimate Add-in, a collection of over 80 utilities and 20 custom functions (nearly 7000 lines of code) is available. The utility adds lots of smart shortcuts, from swapping cells and ranges to manipulating sheets and formulas.
Among the user-interface changes available: You can show and hide the formula bar, status bar, gridlines, and horizontal or vertical scroll bars, plus quickly enable AutoFilter, check worksheet protection settings, reveal hidden sheets or cells, or find unlocked cells.
UAI adds a host of features for working with individual cells or ranges. You can select cells by content (all equal to 100, for example) or format property (font name, font color, bold or italic, among others), find those with errors, find (and remove) cells with links, or reverse the signs in all numeric cells (turn positive values to negatives, and vice versa). Text manipulation includes removing spaces (leading, trailing, excess, or all) or characters (leading, trailing, all alpha, or all numeric).
You can create ranges or change their dimensions and location. For quickly switching between relative and absolute addressing, UAI has an option to switch between the two methods for selected cells, or to change formulas to relative rows and absolute columns or to relative columns and absolute rows. One of our favorites: converting a long column into several columns (you tell the program how many elements in each column and the new starting location; it does the rest).
Working with more than one sheet is also easier. Imagine sorting sheets by name or a custom order you define, add serial sheets (Mon, Tues, Wed ... or Jan, Feb, Mar ...). You can also insert sheets using a custom naming convention; you specify the leading text, starting number, and increment, plus how many sheets you want; UAI then creates sheets, such as “Region 2, Region 4, Region 6, and so on. If you’d prefer, UAI can create sheets using values already entered in a cell range. Pretty slick.
You can insert the current date in a variety of formats, find and delete conditional formats or data validation, use the full file path or the contents of a cell as the header or footer when printing a worksheet, or reset what Excel thinks is the last cell in a worksheet.
A Range Info toolbar is a handy feature, which includes the ability to select a range and with a single click view the average, numeric count, minimum, maximum, total cell count, and sum of values in that range. Select a new range, click on the numeric-keypad icon, and UAI recalculates the values.
Among the custom functions are some to show the full path of the current file, the ability to insert text into other text (you supply the starting position as well), and calculating the sum of the user-defined top values in a range.

COMPUTING WORK DAYS IN A DATE RANGE

How to find the number of weekdays between two dates, excluding holidays. Since he didn’t specify which Office application he was using, I’ll start with Excel, since that’s the easiest. Believe it or not, the Excel Analysis ToolPak contains a Visual Basic for Applications (VBA) function designed for this very purpose!

In a cell, enter: =NetWorkDays(“01/01/2001”,”03/31/2001”) and tab out of the cell. It should show 65, the number of weekdays in the first quarter of 2001. Changing your function to =NetWorkDays(“01/01/01”,”03/31/01”,{“01/01/01”,”02/19/01”}) eliminates New Year's Day and President's Day, yielding 63 work days.

To eliminate a longer list of holidays, replace the third function parameter with a range of cells containing the dates to be excluded. For example, =NetWorkDays(“01/01/01”,”12/31/01”,E1:E12) where column E contains a list of holidays in rows 1 through 12.

If the NetWorkDays function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu. More information on the NetWorkDays function can be found in Excel’s help file.

            Public Function WorkDays(D1 As Date, D2 As Date) As Long
            Dim vDate As Date, vHolidays(2) As Date, I As Integer
            'Initialze variables
            vHolidays(1) = "01/01/01"
            vHolidays(2) = "02/19/01"
            WorkDays = 0
            vDate = D1
            'Loop through the rnge of dates
            While vDate <= D2
            If Weekday(vDate) > 1 And Weekday(vDate) < 7 Then
            WorkDays = WorkDays + 1
            End If
            For I = 1 To 2
            If vDate = vHolidays(I) Then
            WorkDays = WorkDays - 1
            End If
            Next I
            vDate = DateAdd("d", 1, vDate)
            Wend
            End Function
            

The array v Holidays could be expanded to hold more dates and it could be loaded from a table if you wish.

SECURITY WARNING: SELF-EXECUTING WORD MACROS

Microsoft has issued a security bulletin (and program patch) about a flaw that lets macros automatically execute in Word (versions 97 through 2002) documents. The potential for hacker mayhem is significant.
In typical use, Word's security mechanism guards against automatically running macros. In Word 97, for example, a pop-up window appears whenever Word opens a file and discovers a macro if that's the security setting you've chosen.
Here's how Microsoft describes it: "By default in Word 2000 and 2002, only macros that are signed by a trusted party are enabled; all others are disabled. In Word 97, if the document contains macros, the user is prompted regarding whether to enable them or disable them."
Unfortunately, it turns out that it is possible to modify a Word document so the security scanner won't recognize an embedded macro -- and lets the macro execute no matter what. (Microsoft doesn't give any details about what that modification is.) Given the power of the macro language, that's a huge security hole.
The company has posted patches for Word (for both Windows and the Mac) at: 
While we don't know how widely such hacked files may be circulating, we suggest you apply the fix right away.