Issue No. 12 (October 1, 1999)

[Item URL]

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

COMMENTS

Welcome to the 12th 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. 

Back issues are available for download from the EEE web page located on John
Walkenbach's web site. New issues are normally available on the 1st and
16th of each month. See:

http://www.j-walk.com/ss/excel/eee/index.htm


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

TOP EXCEL WEB SITES

Lots of useful information at David McRitchie's web site.

http://members.aol.com/dmcritchie/excel/excel.htm


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

POWER FORMULA TECHNIQUE

by David Hager

Give a range of values (in this case B1:D3), find the maximum value and
return the corresponding character in the adjacent column (in this case 
A1:A3). For the example shown below the answer is "z".

x	1	4	7
y	2	5	8
z	3	6	9

The following array formula will return the desired result.

=INDEX(A1:A3,MAX((B1:D3=MAX(B1:D3))*ROW(A1:A3)))

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

VBA CODE EXAMPLES 

by David Hager

This procedure works in a similar manner to the Edit Fill Across Worksheets
command in that it operates on a selection and the selected sheets, but 
formulas in the selection containing relative references are filled in a 
sheet-relative manner.

Sub FillSpecial()
    Msg = "Do you want to add the sheet name to all references in your selection ?"
    Style = vbYesNo + vbDefaultButton2
    Title = "Add Sheet Name?"
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
        Application.StatusBar = "Converting references..."
        Add_Sheet_Name_to_Formulas
    End If
    On Error GoTo EOP
    Application.StatusBar = "Starting fill special..."
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim SheetPosNum As Integer
    Dim SSPNum As Integer
    Dim wbrray()
    Dim ssrray()
    Dim m As Integer
    Dim n As Integer
    Dim y As Integer
    Dim z As Integer
    Dim sscount As Integer
    Dim CurSheet As String
    Dim ASName As String
    Dim RSName As String
    Dim errval As Variant
    ReDim wbrray(1 To ActiveWorkbook.Sheets.Count)
    ReDim ssrray(1 To Windows(1).SelectedSheets.Count)
    If Windows(1).SelectedSheets.Count = 1 Then
        Application.StatusBar = False
        Exit Sub
    End If
    n = 1
    For Each s In ActiveWorkbook.Sheets
        RSName = Application.Substitute(s.Name, " ", "")
        RTName = Application.Substitute(RSName, "(", "")
        RUName = Application.Substitute(RTName, ")", "")
        If s.Name <> RUName Then
            Msga = "The sheetname [" & s.Name & "] needs to be " & _
	    "modified to workwith formulas. Is it OK?"
            Stylea = vbYesNo + vbDefaultButton1
            Titlea = "Change Sheet Name?"
            Responsea = MsgBox(Msga, Stylea, Titlea)
            If Responsea = vbYes Then
                Sheets(s.Name).Name = RUName
                wbrray(n) = RUName
            End If
        Else
            wbrray(n) = s.Name
        End If
        n = n + 1
    Next
    sscount = Windows(1).SelectedSheets.Count
    Application.StatusBar = "0 of " & sscount & " worksheets finished."
    ASName = ActiveSheet.Name
    SheetPosNum = Application.Match(ASName, wbrray, 0)
    ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type _
        :=xlContents
    m = 1
    For Each s In Windows(1).SelectedSheets
        ssrray(m) = s.Name
        m = m + 1
    Next
    ActiveSheet.Select
   For t = 1 To sscount
     Application.StatusBar = t & " of " & sscount & " worksheets finished."
     CurSheet = Application.Index(ssrray, t)
     Worksheets(CurSheet).Activate
     SSPNum = Application.Match(ssrray(t), wbrray, 0)
     y = ActiveWorkbook.Sheets.Count
     Selection.Replace What:="=", Replacement:="(/)", LookAt:= _
     xlPart, SearchOrder:=xlByRows, MatchCase:=False
     For Each r In wbrray
         Selection.Replace What:=wbrray(y), Replacement:="ZZZ00" & y, LookAt:= _
         xlPart, SearchOrder:=xlByRows, MatchCase:=False
         y = y - 1
     Next
     z = ActiveWorkbook.Sheets.Count
     For Each q In wbrray
         Selection.Replace What:="ZZZ00" & z + SheetPosNum - SSPNum, _
	 Replacement:=wbrray(z), LookAt:= 
         xlPart, SearchOrder:=xlByRows, MatchCase:=False
         z = z - 1
     Next
     Selection.Replace What:="(/)", Replacement:="=", LookAt:= _
     xlPart, SearchOrder:=xlByRows, MatchCase:=False
     For Each CurCell In Selection
           If IsError(CurCell) Then
             errval = CurCell.Value
            Select Case errval
             Case CVErr(xlErrName)
              CurCell.Formula = ""
             Case CVErr(xlErrRef)
              CurCell.Formula = ""
            End Select
           End If
     Next
    Next
    Worksheets(SheetPosNum).Activate
    Application.StatusBar = False
    Exit Sub
EOP:
    MsgBox "Illegal formula reference attempted. Examine all " & _
    "filled formulas and try again."
    Worksheets(SheetPosNum).Activate
    Application.StatusBar = False
End Sub

Sub Add_Sheet_Name_to_Formulas()
    Dim CurrentSheet As String
    On Error GoTo EOSH
    CurrentSheet = ActiveSheet.Name
    Application.ReferenceStyle = xlR1C1
    Application.ScreenUpdating = False
    With Selection
    .Replace What:="RA", Replacement:="ARZZ"
    .Replace What:="RE", Replacement:="ERZZ"
    .Replace What:="RI", Replacement:="IRZZ"
    .Replace What:="RO", Replacement:="ORZZ"
    .Replace What:="+R", Replacement:="+" & CurrentSheet & "!R"
    .Replace What:="-R", Replacement:="-" & CurrentSheet & "!R"
    .Replace What:="(R", Replacement:="(" & CurrentSheet & "!R"
    .Replace What:=",R", Replacement:="," & CurrentSheet & "!R"
    .Replace What:="/R", Replacement:="/" & CurrentSheet & "!R"
    .Replace What:="~*R", Replacement:="*" & CurrentSheet & "!R"
    .Replace What:="=R", Replacement:="=" & CurrentSheet & "!R"
    .Replace What:=" R", Replacement:=" " & CurrentSheet & "!R"
    .Replace What:="^R", Replacement:="^" & CurrentSheet & "!R"
    .Replace What:="&R", Replacement:="&" & CurrentSheet & "!R"
    .Replace What:="(C[", Replacement:="(" & CurrentSheet & "!C["
    .Replace What:=" C[", Replacement:=" " & CurrentSheet & "!C["
    .Replace What:="=C[", Replacement:="=" & CurrentSheet & "!C["
    .Replace What:="~*C[", Replacement:="*" & CurrentSheet & "!C["
    .Replace What:="/C[", Replacement:="/" & CurrentSheet & "!C["
    .Replace What:="ORZZ", Replacement:="RO"
    .Replace What:="IRZZ", Replacement:="RI"
    .Replace What:="ERZZ", Replacement:="RE"
    .Replace What:="ARZZ", Replacement:="RA"
    End With
    Application.ReferenceStyle = xlA1
    Exit Sub
EOSH:
    MsgBox "Not all references may have converted correctly."
    Application.ReferenceStyle = xlA1
End Sub


by Dana DeLouis 

This procedure converts normal formulas to those that show an empty cell
if an error condition exists in the original formula.

Sub ErrorTrapAddDDL()

' Adds =If(IsError() around formulas

  Dim cel As Range
  Dim rng As Range
  Dim Check As String

  Const Equ As String = "=IF(ISERROR(_x) ,"""", _x)"

  Check = Left$(Equ, 12) & "*" ' Check for =IF(ISERROR(

  On Error Resume Next

  Set rng = Selection.SpecialCells(xlFormulas, 23)
  If rng Is Nothing Then Exit Sub

  With WorksheetFunction
    For Each cel In rng
      If Not cel.Formula Like Check Then
        cel.Formula = .Substitute(Equ, "_x", Mid$(cel.Formula, 2))
      End If
    Next
  End With
End Sub


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

DO YOU KNOW?...

that you can use defined names in a workbook that are defined in another
workbook. For example, if TheValue is defined as 4 in BookB.xls, typing 
=BookB.xls!TheValue in a cell in another workbook will return the value 4.
However, the workbook containing the defined name formua must be open for
this to work. This is NOT true for defined name ranges. These can be used
to communicate with CLOSED workbooks! So, for example, if TheRange is defined 
as Sheet1!A1:A3 in BookB.xls, typing =SUM(BookB.xls!TheRange) in a cell in 
another workbook will return the value 17 (if that range contains the values

1,2 and 14). When the workbook containing the defined name range is closed,
the full path of BookB.xls will be shown in the formula. Recalculation of 
that formula continues to return the value 17. Unfortunately, the range 
cannot be defined with the OFFSET function as an expanding range, such as:

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),)

since this fits into the category of defined name formulas described earlier
which do not work with closed workbooks. However, it works fine when the 
workbook is open.

An important sidenote to the use of defined names is the transport of defined
names to another workbook. If you type =MyIncrediblyComplexDFFormula in a
cell in the same workbook it is defined in, then copy/paste that cell to 
another workbook, the defined name formula associated with that formula 
(along with any dependent defined name formulas) will be copied to that 
workbook as well. This is true even if the workbook and worksheet is 
completely protected. A method of preventing this from occurring is the 
attachment of an xlm function of your choosing to the formula (perhaps one 
that always returns 0).  Since xlm functions cannot be used directly on a 
worksheet, the destination workbook will not accept the paste operation.

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

Issue No.12 OF EEE (PUBLISHED 01Oct1999)
Next issue scheduled for 16Oct1999.
BY David Hager
dchager@compuserve.com

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