Issue No. 01 (March 16, 1999)

[Item URL]

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

COMMENTS

Welcome to the first issue of  the Excel Experts E-letter (or EEE), 
by David Hager. My current plan is to make EEE a semi-monthly 
publication. If you received this file as an attachment to an e-mail
message sent on ~ 16Mar1999, that means you are among the 100 or so
people I collected e-mail addresses for as a starting point to send
EEE to the top Excel experts worldwide. I looked in newsgroups, forums,
web pages, books, magazines and e-lists to find you, but I did not 
find everyone. Also, there are quite a few Excel experts out there 
that I have names for, but no e-mail addresses. So, if you know of 
someone whom you consider to be an Excel expert and they are not 
currently receiving EEE, ask them to send their name and e-mail address 
to me at dchager@compuserve.com.

My vision for EEE is for it to be based on meaty content. I have 
collected a lot of material over the last 5 years on Excel, and I will
try to present what I feel is of interest to the majority of people on 
this list. In most cases, I will use tidbits that were gleaned from 
postings on Excel newsgroups, forums and e-lists, and I will attempt 
to give credit to the creator of the tip. If you object to the use of 
your name, let me know. On the flip side, if you have any new and great 
ideas, send them to me and I will include them in a future issue.	
I don't plan to issue any challenges to solve Excel problems in EEE, but 
it is likely that many of you have made some interesting discoveries 
during the course of your work that you have never had the chance to 
share with anyone. Let EEE be the vehicle for the presentation of that 
Excel gem!

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

TOP EXCEL WEB SITES

Tied for the top spot:
www.j-walk.com
www.bmsLTD.co.uk

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

WORKSHEET FORMULA TIPS

Created by Bob Umlas and David Hager
To define a global range name in Excel, go to Insert, Name, Define and, 
as an example, in the Names in Workbook box type "cellA1" and in the Refers
To box type this formula: =OFFSET(!$A$1,,,,). Now, type =cellA1 in a cell 
on any worksheet in the workbook and it will return the value in A1 for 
that worksheet.

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

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

POWER FORMULA TECHNIQUE

Created by Laurent Longre:
The problem - how to create an array of filtered items in a column 
list. The SUBTOTAL function allows you to operate on an array of this
type with a limited number of worksheet functions, but it does not
expose the array for formula manipulation. Laurent came up with an
elegant soution to this problem, based on an obscure behavior of 
the OFFSET function. It turns out that when an array is used as the
2nd argument of OFFSET, such as

=OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)

an array of RANGES is returned. If the array is properly sized, as in
this example, the OFFSET function returns a separate single cell range
for each cell in the original range(Rge). Thus, if this array of arrays
is operated on by the SUBTOTAL function, each single cell range gets
evaluated separately. So, the formula

=SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1))

evaluates as 1 for each cell that is visible and as 0 if the cell is not
visible. The use of 3 as the 1st argument in SUBTOTAL counts the number of
items in the visible range. Since there is only one item in each range, 
the answer can only be 0 or 1. Thus, this formula can be used as an array 
which indicates the rows in the list that are filtered and unfiltered. If
you want to returns an array of items in the column list, then use:

=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,"")

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

VBA CODE EXAMPLES 

Created by Rob Bovey:
Uses path as argument and it returns True if the
path is empty or doesn't exist and False if the path contains files.

Function bIsEmpty(ByVal szPath As String) As Boolean
    Dim bReturn As Boolean
    Dim szTemp As String
    bReturn = True
    If Right$(szPath, 1) <> "\" Then szPath = szPath & "\"
    szTemp = Dir$(szPath & "*.*")
    If szTemp <> "" Then bReturn = False
    bIsEmpty = bReturn
End Function


Created by Jim Rech:
Removes a known directory including all of its files and any/all 
possible sub-directories of unknown quantity & name/s including their
files.

Const FO_DELETE = &h3&
Const FOF_NOCONFIRMATION = &h10&

Private Type SHFILEOPSTRUCT
   hwnd As Long
   wFunc As Long
   pFrom As String
   pTo As String
   fFlags As Integer
   fAnyOperationsAborted As Long
   hNameMappings As Long
   lpszProgressTitle As String
End Type

Private Declare Sub CopyMemory Lib "KERNEL32" Alias "RtlMoveMemory" 
(hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)

Private Declare Function SHFileOperation Lib "Shell32.dll" Alias 
"SHFileOperationA" (lpFileOp As Any) As Long

Sub Test()
    ShellDelete "c:\aaa"
End Sub

Sub ShellDelete(SrcFile As String)
    Dim result As Long
    Dim lenFileop As Long
    Dim foBuf() As Integer
    Dim fileop As SHFILEOPSTRUCT
    lenFileop = LenB(fileop)
    ReDim foBuf(1 To lenFileop)
    With fileop
        .hwnd = 0
       .wFunc = FO_DELETE
       .pFrom = SrcFile & Chr(0) & Chr(0)
        .fFlags = FOF_NOCONFIRMATION
        .lpszProgressTitle = "" & Chr(0) & Chr(0)
    End With
    Call CopyMemory(foBuf(1), fileop, lenFileop)
    Call CopyMemory(foBuf(19), foBuf(21), 12)
    result = SHFileOperation(foBuf(1))
End Sub


Created by Bill Manville:
Checks whether a name exists in a collection.
For example, If IsIn(ActiveWorkbook.Names, "ThisOne") Then ...

Function IsIn(oCollection As Object, stName As String)  As Boolean
  Dim O As Object
  On Error Goto NotIn
  Set O = oCollection(stName)
  IsIn = True	'succeeded in creating a pointer to the object so 
		'must be there
NotIn:
End Function

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

POWER PROGRAMMING TECHNIQUE

Created by Stephen Bullen
The problem - you want to programatically obtain the name of the 
VBComponent that contains a specified procedure. Stephen's solution 
was to look for unique strings, since the VBIDE object model does
not provide functionality for doing this directly.
 
Sub TestIt()

MsgBox fnThisVBComponent(ThisWorkbook, "This Unique String").Name & ", " & _
       fnThisProcedureName(ThisWorkbook, "Another Unique String")

End Sub


Function fnThisVBComponent(oBk As Workbook, sUniqueString As String) 
	As VBComponent

Dim oVBC As VBComponent

'Loop through the VBComponents in the given workbook's VBProject
For Each oVBC In oBk.VBProject.VBComponents

    'Using it's code module
    With oVBC.CodeModule

        'See if we can find the unique string
        If .Find(sUniqueString, 1, 1, .CountOfLines, 1000, True, _ 
	True, False) Then

            'Found it, so return the VBComponent where it was found
            Set fnThisVBComponent = oVBC
            Exit For
        End If
    End With
Next

End Function


Function fnThisProcedureName(oBk As Workbook, sUniqueString As String) 
	As String

Dim oVBC As VBComponent
Dim lStart As Long, sProcName As String, vaProcs As Variant, 
vProcType As Variant

'Specify the row number of where to start the find.  This is set by 
'the Find method to give the (starting) line number where the text 
'was found. lStart = 1

'Loop through the VBComponents in the given workbook's VBProject
For Each oVBC In oBk.VBProject.VBComponents

    'Using it's code module
    With oVBC.CodeModule

        'See if we can find the unique string
        If .Find(sUniqueString, lStart, 1, .CountOfLines, 1000, True, _ 
		True, False) Then

            'We found it, so make an array of the available procedure 
	    'types to check for

            vaProcs = Array(vbext_pk_Proc, vbext_pk_Get, vbext_pk_Let, _ 
	    vbext_pk_Set)

            'Loop throguh the procedure types
            For Each vProcType In vaProcs

                'Get the name of the procedure containing the line we 
		'found above
                sProcName = .ProcOfLine(lStart, CLng(vProcType))

                'Did we get a procedure name?
                If sProcName <> "" Then

                    'We did, so return it
                    fnThisProcedureName = sProcName
                    Exit For
                End If
            Next

            Exit For
        End If
    End With
Next

End Function

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

EXCEL 2000 TIP

If you have had the opportunity to use beta versions of Excel 2000, then
you probably realize the great potential of the web-interactive Excel file
formats and their corresponding Office Web Components (OWC). Something you 
might not realize (or had a chance to play with yet) is that the OWC's can
also be used with UserForms! It opens up a Pandora's Box of possibilities.

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

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