Issue No. 02 (April 1, 1999)

[Item URL]

**********************************

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