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.

What is a Sound Card?

Sound card

Typical uses of sound cards include providing the audio component for
multimedia applications such as music composition, editing video or
audio, presentation/education, and entertainment (games). Many computers
have sound capabilities built in, while others require these expansion
cards if audio capability is desired.


General characteristics

Close-up of a sound
card PCB, showing electrolytic capacitors (most likely for AC coupling),
SMT capacitors and resistors, and a YAC512 two-channel 16-bit DAC.A
typical sound card includes a sound chip, usually featuring a
digital-to-analog converter, that converts recorded or generated digital
waveforms of sound into an analog format. This signal is led to a
(typically 1/8-inch earphone-type) connector where an amplifier,
headphones, or similar sound destination can be plugged in. More
advanced designs usually include more than one sound chip to separate
duties between digital sound production and synthesized sounds (usually
for real-time generation of music and sound effects utilizing little
data and CPU time).


Digital sound reproduction is usually achieved by multi-channel DACs,
able to play multiple digital samples at different pitches and volumes,
optionally applying real-time effects like filtering or distortion.
Multi-channel digital sound playback can also be used for music
synthesis if used with a digitized instrument bank of some sort,
typically a small amount of ROM or Flash memory containing samples
corresponding to the standard MIDI instruments. (A contrasting way to
synthesize sound on a PC uses "audio codecs", which rely heavily on
software for music synthesis, MIDI compliance and even multiple-channel
emulation. This approach has become common as manufacturers seek to
simplify the design and the cost of the sound card itself).


Most sound cards have a line in connector where the sound signal from
a cassette tape recorder or similar sound source can be input. The
sound card can digitize this signal and store it (controlled by the
corresponding computer software) on the computer's hard disk for editing
or further reproduction. Another typical external connector is the
microphone connector, for connecting to a microphone or other input
device that generates a relatively lower voltage than the line in
connector. Input through a microphone jack is typically used by speech
recognition software or Voice over IP applications.


Connections
Most sound cards since 1999 conform to Microsoft's PC 99 standard for color coding the external connectors as follows:


Color Function
  Pink Analog microphone input.
  Light blue Analog line level input.
  Lime green Analog line level output for the main stereo signal (front speakers or headphones).
  Black Analog line level output for rear speakers.
  Silver Analog line level output for side speakers.
  Orange S/PDIF digital output (sometimes used as an analog line output for a center speaker instead)


Voices vs channels
Another important
characteristic of any sound card is the number of distinct voices
(intended as the number of sounds that can be played back simultaneously
and independently) and the number of channels (intended as the number
of distinct electrical audio outputs).


For example, many older sound chips had three voices, but only one
audio channel (mono) where all the voices were mixed into, while the
AdLib sound card had 9 voice and 1 mono channel.


For a number of years, most PC sound cards had multiple FM synthesis
voices (typically 9 or 18) which were mostly used for MIDI music, but
only one (mono) or two(stereo) voice(s) and channel(s) dedicated to
playing back digital sound samples, and playing back more than one
digital sound sample required performing a software downmix at a fixed
sampling rate. Modern low-cost integrated soundcards using an audio
codec like the AC'97 still work that way, although they may have more
than two sound output channels (surround sound).


Today, a sound card having hardware support for more than the two
standard stereo voices, is likely to referred at as "providing hardware
audio acceleration".