**********************************
COMMENTS
Welcome to the second issue of the Excel Experts E-letter (or EEE), by
David Hager. EEE is a semi-monthly publication. Feel free to distribute
copies of EEE to your friends and colleagues.
I am overwhelmed by the positive responses I received about the 1st issue
of EEE and I appreciate your kind words. The mailing list for EEE is up
above 170 now. I have had to look far and wide on the Internet to find
you, and frankly it has taken more time to do this than writing this
newsletter. I had some trouble with the stability of my mailing list on
the 1st issue, and many people that I believed were on the list were not.
If you would like a copy of the 1st issue, send me a note and I will be
happy to pass it along.
Many of you have indicated that you would like to contribute your ideas.
I look forward to seeing them!
**********************************
TOP EXCEL WEB SITES
Do you need an Excel formula?
http://home.gvi.net/~cpearson/excel.htm
**********************************
WORKSHEET FORMULA TIPS
Created by David Hager
In a lab a test is performed in triplicate. If 2 of the values are the
same, those 2 values are averaged. Otherwise, all 3 of the values are
averaged. An array formula that returns a result based on this protocol is:
=AVERAGE(IF(SUM(COUNTIF(Rng,Rng))=3,Rng,IF(COUNTIF(Rng,Rng)>1,Rng,"")))
where Rng is a three cell range containing those values.
Created by Chip Pearson
The IF function has a limit of 7 nested arguments. Chip's formula
circumvent that inherent limitation.
Define this formula as OneToSix:
=IF($A$4=1,11,IF($A$4=3,22,IF($A$4=5,33,IF($A$4=7,44,IF($A$4=9,55,IF
($A$4=11,44,IF($A$4=13,55,IF($A$4=15,66,FALSE))))))))
and this formula as SevenToThirteen:
=IF($A$4=17,77,IF($A$4=19,88,IF($A$4=21,99,IF($A$4=23,100,IF($A$4=25,110,
IF($A$4=27,120,IF($A$4=29,130,"NotFound")))))))
The combined formula looks like this:
=IF(OneToSix,OneToSix,SevenToThirteen)
**********************************
POWER FORMULA TECHNIQUE
Created by Shane Devenshire and David Hager
The problem - to change a link in a formula without changing the
formula. This can be done with the INDIRECT function by creating a
concatenated string with input from several worksheet cells which
contain workbook (in A1) and worksheet (in B1) names.
=INDIRECT("'["&A1&"]"&B1&"'!A1")
Unfortunately, this type of formula will only work if the referenced
workbook is open. Shane came up with part of the solution to this
problem when he discovered that the INDEX function can return a linked
cell value from a hard-coded link range. For example, if you define a
range as "ref1", where the linked range formula is:
=[Book1.xls]Sheet1!$1:$65536
(A smaller range starting at A1 can also by used.)
then you can use the formula:
=IF(ISERR(INDEX(ref1,ROW(),COLUMN())),"",INDEX(ref1,ROW(),COLUMN()))
in any cell and the returned value will be from the same cell in Book1.xls
on Sheet1. Then, variable links to this formula can be made by changing the
link range as referred to in a named formula. This formula is of the form:
=CHOOSE(Sheet2!$A$1-29*INT((Sheet2!$A$1-1)/29),ref1,ref2,...,ref29)
where Sheet2!$A$1 is an input cell for values from 1 to n which represent
a particular link stored as a defined name. As you are probably aware, the
CHOOSE can only accept 29 arguments. However, there is a workaround for
this limitation, and the formula in the 1st argument is part of that
process. It converts the value in Sheet2!$A$1 into a number between 1 and
29. Then, if you define the preceding formula as oref1 (and other similar
formulas as oref(n)), you can use the following master formula:
=CHOOSE(INT((Sheet2!$A$1-1)/29)+1,oref1,oref2,...,oref29)
Now, if you give this formula a defined name (say mref), then the resulting
"omnireference" can be used in place of ref1 in Shane's formula to produce
an "omnilink" that is capable of returning values from 29 x 29 (841)
different links. This formula is the one that is finally entered in a
worksheet cell.
=mref
Note: This technique works great as long as the linked files are not moved,
renamed or deleted.
**********************************
VBA CODE EXAMPLES
Created by Bill Manville
To create a blinking cell:
If you define a new Style (Format / Style / Flash/ Add ) and apply
that style to the cells you want to flash, paste the following code
into a module sheet and run the procedure Flash from Auto-Open if
desired you will get the text flashing alternately white and red.
Dim NextTime As Date
Sub Flash()
NextTime = Now + TimeValue("00:00:01")
With ActiveWorkbook.Styles("Flash").Font
If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
End With
Application.OnTime NextTime, "Flash"
End Sub
Sub StopIt()
Application.OnTime NextTime, "Flash", schedule:=False
ActiveWorkbook.Styles("Flash").Font.ColorIndex = xlAutomatic
End Sub
Created by Myrna Larson
An UDF that returns values "between" the points in the lookup table.
Function InterpolateVLOOKUP(x As Single, Table As Range, _
YCol As Integer)
Dim TableRow As Integer, Temp As Variant
Dim x0 As Double, x1 As Double, y0 As Double, y1 As Double
Dim d As Double
On Error Resume Next
Temp = Application.WorksheetFunction.Match(x, Table.Resize(, 1), 1)
If IsError(Temp) Then
InterpolateVLOOKUP = CVErr(Temp)
Else
TableRow = CInt(Temp)
x0 = Table(TableRow, 1)
y0 = Table(TableRow, YCol)
If x = x0 Then
InterpolateVLOOKUP = y0
Else
x1 = Table(TableRow + 1, 1)
y1 = Table(TableRow + 1, YCol)
InterpolateVLOOKUP = (x - x0) / (x1 - x0) * (y1 - y0) + y0
End If
End If
End Function
**********************************
POWER PROGRAMMING TECHNIQUE
By Laurent Longre
There is a way for an auto-start macro to read the arguments on the
command line (with Excel 97).
Assume that you want to read the command line argumentsfrom an Auto_open
sub in the workbook "c:\temp\test.xls" opened by a batch file (or by a
Win95 shortcut).
1. Your command line should look like this one:
start excel c:\temp\test /e/param1/param2/.../paramN
i.e. : after excel.exe, the name of the workbook containing the
Auto_open, then the switch /e **immediately** followed by your own
arguments. These arguments should be separated by "/" and form a
continuous string without spaces.
For instance, if you want to pass the arguments "c:\temp\file1.dbf",
"all" and "exclusive" to Excel, your command-line should look like:
start excel c:\temp\test /e/c:\temp\file1.dbf/all/exclusive
2. In Test.xls, use the API function GetCommandLine (alias
GetCommandLineA in Win95) to get the contents of this command-line
string.
You should then parse the string returned by GetCommandLineA, search for
the separators "/" and store each argument in an array. Here is an
example of a such Auto_open sub:
Option Base 1
Declare Function GetCommandLineA Lib "Kernel32" () As String
Sub Auto_open()
Dim CmdLine As String 'command-line string
Dim Args() As String 'array for storing the parameters
Dim ArgCount As Integer 'number of parameters
Dim Pos1 As Integer, Pos2 As Integer
CmdLine = GetCommandLineA 'get the cmd-line string
On Error Resume Next 'for the wksht-function "Search"
Pos1 = WorksheetFunction.Search("/", CmdLine, 1) + 1 'search "/e"
Pos1 = WorksheetFunction.Search("/", CmdLine, Pos1) + 1 '1st param
Do While Err = 0
Pos2 = WorksheetFunction.Search("/", CmdLine, Pos1)
ArgCount = ArgCount + 1
ReDim Preserve Args(ArgCount)
Args(ArgCount) = Mid(CmdLine, Pos1, _
IIf(Err, Len(CmdLine), Pos2) - Pos1)
MsgBox "Argument " & ArgCount & " : " & Args(ArgCount)
Pos1 = Pos2 + 1
Loop
End Sub
If you use the command-line above, this Auto_open sub will automatically
store the three arguments ("c:\temp\file1.dbf", "all" and "exclusive")
in the Args() array and display them.
Again, be sure that you don't insert any space between /e and each
argument in the command-line, otherwise it could fail (Excel can believe
that these "pseudo-arguments" are the names of workbooks to open at
startup...).
**********************************
EXCEL 2000 TIP
An intriguing property was added to the Range object in Excel 2000. It
is the ID property. In the normal scheme of things, it assigns a string
to a worksheet cell, which is used in a HTML tag when the worksheet is
saved as a web page. If the worksheet is saved in a normal manner, the
ID does not appear to be persistent. However, if ID's of cells are set
when a workbook is opened, they can be used in some interesting ways.
As an example, consider the following:
Sub Auto_Open()
With Sheets(1)
.Range("a1").ID = "Test"
End With
End Sub
'in Sheet1 module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address <> Target.Address Then Exit Sub
If Target.ID <> "" Then
MsgBox Target.ID
End If
End Sub
In this case, if A1 is selected, the MsgBox dialog will appear with
the text "Test". Thus, this provides a method of creating cell comments
that do not have to be stored with the cells. Further, if the assignment
of ID strings to cells is criteria-based rather than address-based, then
this provides a dynamic method of documenting cells of varying properties.
Another use of the ID property would be to apply the Collection class
for the Tag property developed by Ken Getz and Mike Gilbert (shown in the
Nov '98 issue of MOD magazine, p.36) to this system. I leave that as an
exercise to the reader.
**********************************
DID YOU KNOW?...
that you can create a hyperlink from an object to a VBA procedure. In
Excel 97, make a text box on a worksheet and right-click its edge. Then,
select Insert, Hyperlink from the menu and type the name of the procedure
in the Named Location in File box.
NOTE: The manual setting of a procedure name as a hyperlink subaddress
apparently cannot be done in Excel 2000. Not only does the dialog box not
allow that option, it does not allow the use of names that do not yet exist,
unlike Excel 97. In my opinion, there was no reason to add an extra
validation step to see if the name exists, since the option to add the name
at a later time increases the flexibility of this feature. However, you can
still set/change the SubAddress of the hyperlink programatically in Excel
2000 for VBA procedures.
**********************************
Issue No.2 OF EEE (PUBLISHED 01Apr1999)
Next issue scheduled for 16APR1999.
BY David Hager
dchager@compuserve.com