Issue No. 05 (May 15, 1999)

[Item URL]

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

COMMENTS

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

Starting with issue #6, EEE will no longer be directly mailed. Instead,
it will be 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.

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

It is becoming difficult to continue to credit the originator of the 
techniques I present in EEE, but I will continue to assign credit when I
know the source. Please continue to submit your best tips, techniques
and procedures so that all can benefit from your creations.

At the end of this issue is a cumulative index for EEE #1-5. The next 
cumulative index will appear in EEE #10.

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

TOP EXCEL WEB SITES

Visit Dave Steppan's web page at:

http://www.geocities.com/SiliconValley/Network/1030/ExcelTop.html

for some great tips and downloadable files.

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

WORKSHEET FORMULA TIPS

Created by David Hager

Here is an array formula that will return TRUE if all of the characters
in a string (in A1 in this example) are unique and return FALSE if not.

=SUM(N(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),MID(A1,ROW(INDIRECT
("1:"&LEN(A1))),1),0)=ROW(INDIRECT("1:"&LEN(A1)))))=LEN(A1)


Created by David Hager

This array formula reverses the digits in a number.

=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*10^(ROW(INDIRECT
("1:"&LEN(A1)))-1))

Created by Laurent Longre

This formula reverses the characters in a string. Note that the CALL
function is disabled by the Microsoft Excel CALL patch.

Under Windows, text length < 256 characters :

=CALL("Msvcrt40","_strrev","1F","String to reverse")


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

POWER FORMULA TECHNIQUE

Created by David Hager and "unknown"

The problem: to create an inverted range. The following formula does
this, but in a very strange way. The example uses the information 
shown below. 

A1:D1 = {1,2,3,4}
A5:D5 = {5,6,7,5}

=SUM({1,2,3,4}*{5,7,6,5}) = 57

Note that the 2nd array has been inverted. This formula returns an 
answer of 57, which is (1*5)+(2*7)+(3*6)+(4*5). The following formula 
also returns this result, but only when entered in a certain way. If 
you enter this formula in a single cell, it returns an answer of 50.
However, if the same formula is array-entered in two cells, each cell
will return the corrrect answer. The reason for this behavior is not
known.

=SUM(A1:D1*INDEX(A5:D5,5-COLUMN(A5:D5)))

A formula that returns an inverted column range is shown below. The 
range being inverted in this example is iRng. 

=IF(T(OFFSET(iRng,ROWS(iRng)-ROW(OFFSET($A$1,,,ROWS(iRng),)),,,))="",N
(OFFSET(iRng,ROWS(iRng)-ROW(OFFSET($A$1,,,ROWS(iRng),)),,,)),T(OFFSET
(iRng,ROWS(iRng)-ROW(OFFSET($A$1,,,ROWS(iRng),)),,,)))

The key to this formula is the array of inverted cell positions created by

ROWS(iRng)-ROW(OFFSET($A$1,,,ROWS(iRng),)).

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

VBA CODE EXAMPLES 

This procedure prevents the user from using File, Save As.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, 
	Cancel As Boolean)

    If SaveAsUI Then
        Cancel = True
    End If

End Sub


By Jim Rech

This procedure creates a high performance timer.

Declare Function QueryPerformanceFrequency Lib "kernel32" _
(lpFrequency As LARGE_INTEGER) As Long

Declare Function QueryPerformanceCounter Lib "kernel32" _
(lpPerformanceCount As LARGE_INTEGER) As Long

Type LARGE_INTEGER
    lowpart As Long
    highpart As Long
End Type

Sub TestHighResolutionTimer()

    Dim FirstCount As LARGE_INTEGER
    Dim SecondCount As LARGE_INTEGER
    Dim TimerOverhead As Long, Counter As Long
    QueryPerformanceCounter FirstCount
    QueryPerformanceCounter SecondCount
    TimerOverhead = SecondCount.lowpart - FirstCount.lowpart
    QueryPerformanceCounter FirstCount
    ''Procedure to time
    For Counter = 1 To 10000000
    Next
    ''End procedure to time
    QueryPerformanceCounter SecondCount
    MsgBox "Timer counts: " & Format(SecondCount.lowpart -
    FirstCount.lowpart - TimerOverhead, "#,##0")

End Sub

Sub GetHighResolutionTimerFrequency()
  
    Dim Freq As LARGE_INTEGER
    If QueryPerformanceFrequency(Freq) = 0 Then
        MsgBox "Your computer does not support the high performance timer"
    Else
        MsgBox "Your computer's high resolution timer frequency is " &
    Format(Freq.lowpart, "#,##0") & " counts per second"
    End If

End Sub


By Bill Manville

This procedure finds a string across worksheets in a workbook.

Sub gFindIt()

Dim strWhat As String
Dim WS As Worksheet
Dim R As Range

strWhat = txtSearchFor.Text
If strWhat = "" Then Exit Sub

For Each WS In ActiveWorkbook.Worksheets
   Set R = WS.Cells.Find(What:=strWhat, After:=WS.Range("A1"),
   LookIn:=xlFormulas, LookAt :=xlPart, SearchOrder:=xlByRows,
   SearchDirection:=xlNext, MatchCase:= False)
   If Not R Is Nothing Then
    Application.Goto R, Scroll:=True
    Exit For
   End If
Next

End Sub

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

POWER PROGRAMMING TIPS

By Bill Manville

Is there any way to stop the links update box from apperaring when
opening a file?

Answer:

You can open the file by program, specifying UpdateLinks:=0
So, you could create a new very small workbook, whose job is to open the
main workbook, containing just:

Sub Auto_Open()
  Workbooks.Open ThisWorkbook.Path & "\RealOne.XLS", UpdateLinks:=0
  ThisWorkbook.Close False
End Sub


By Bob Umlas

Is there a way to clear a worksheet model of data and leave the formulas
intact?

Sub ResetModel()
   Range("A1").SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
End Sub


By Stephen Bullen

Can you prevent the flickering when a procedure is run in the VBE, similar
to using Application.ScreenUpdating = False ?

Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As 
Long

Sub SomeRoutine()

'Freeze the VBE window - same effect as ScreenUpdating

LockWindowUpdate Application.VBE.MainWindow.HWnd

'Do something



'Unfreeze the VBE window

LockWindowUpdate 0&

End Sub




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

EXCEL 2000 POWER PROGRAMMING TECHNIQUE

By David Hager

The following technique allows you to highlight data in a worksheet
while displaying an UserForm that presents the data in chart form when
the focus returns to the UserForm. This action can be repeated as many
times as desired before closing the UserForm.

Place the event procedures in the module for an UserForm that is named 
chartssUserForm and the MakeNewChart procedure in a module in the same 
workbook. The UserForm needs to have a Spreadsheet object named Spreadsheet1 
(which is not visible) and a ChartSpace object named ChartSpace1 (which is 
visible). The two events in the UserForm module each call the same 
procedure. This is a workaround for the lack of an event that is triggered 
when an UserForm loses or gains focus. A modeless UserForm is created in 
Excel 2000 by setting the ShowModal property to False in the Properties box.

Private Sub ChartSpace1_Click(ByVal ChartEventInfo As OWC.WCChartEventInfo)
    MakeNewChart
End Sub

Private Sub UserForm_Click()
    MakeNewChart
End Sub


Sub MakeNewChart()

    On Error GoTo NoChart

    With chartssUserForm
        .ChartSpace1.Clear
        .ChartSpace1.Charts.Add
        .ChartSpace1.DataSource = .Spreadsheet1
        .Spreadsheet1.Cells.Clear
        Application.ScreenUpdating = False
        Selection.Copy
        Sheets.Add
        Range("a1").PasteSpecial
        Selection.Copy
        .Spreadsheet1.ActiveSheet.Range("a1").Paste
        Application.DisplayAlerts = False
        ActiveWindow.SelectedSheets.Delete
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Set cr = .Spreadsheet1.ActiveSheet.Cells(1, 1).CurrentRegion
        TheMax = Application.WorksheetFunction.Max(Range(cr.Address))
        TheRows = cr.Rows.Count
        TheCols = cr.Columns.Count
    End With

    With chartssUserForm.ChartSpace1.Charts(0)

        For NumSeries = 1 To TheCols - 1
            .SeriesCollection.Add
        Next

        For n = 0 To TheCols - 2

        With chartssUserForm.Spreadsheet1.ActiveSheet

            theseriesnamesrange = .Cells(1, n + 2).Address

            thecatagoriesrange = .Range(.Cells(2, 1), _
            .Cells(TheRows, 1)).Address

            thevaluesrange = .Range(.Cells(2, n + 2), 
            .Cells(TheRows, n + 2)).Address

        End With

        With chartssUserForm.ChartSpace1.Charts(0).SeriesCollection(n)
            .SetData chDimSeriesNames, 0, theseriesnamesrange
            .SetData chDimCategories, 0, thecatagoriesrange
            .SetData chDimValues, 0, thevaluesrange
        End With

        Next

        .HasLegend = True
        .Axes(chAxisPositionLeft).NumberFormat = "General"
        .Axes(chAxisPositionLeft).MajorUnit = 0.1 * TheMax

    End With

    chartssUserForm.Show

    Exit Sub
NoChart:
    MsgBox "Your data range is not valid!", , "Try again"
End Sub


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

DID YOU KNOW?...

You can find a comprehensive list of Excel viruses at:

http://www.datafellows.com/v-descs/

by entering Excel in the Search Virus Description Database box.

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

Issue No.5 OF EEE (PUBLISHED 15May1999)
Next issue scheduled for 01Jun1999.
BY David Hager
dchager@compuserve.com

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

CUMULATIVE INDEX (ISSSUES 1-5):

WORKSHEET FORMULAS:

Issue #1:
-defines a global range name
-sums comma delimited values in a cell
-creates an array of filtered items

Issue #2:
-returns special average for 3 lab results
-uses 13 nested IF arguments
-makes variable link to closed workbook

Issue #3:
-conditional format formula for value in the previous worksheet
-returns a running total across worksheets
-returns a 3D moving average
-makes a 3D array formula

Issue #4:
-returns the number of unique items
-returns the Nth largest unique value
-data validation formula allows only unique entries
-count of unique items based on a criteria
-creates array containing only unique items

Issue #5:
-string that contains only unique characters
-reverses the digits in a number
-reverses the characters in a string
-creates an inverted range


VBA PROCEDURES:

Issue #1:
-indicates whether the path is empty or doesn't exist
-completely removes files and folders from a known directory
-obtains the name of the VBComponent that contains a specified procedure

Issue #2:
-creates a blinking cell effect
-returns values between points in a lookup table
-reads the arguments on the command line

Issue #3:
-custom function for SUMPRODUCT with 3D range argument
-custom function for SUMIF with 3D range argument
-custom function for COUNTIF with 3D range argument

Issue #4:
-counts unique values using advanced filter
-counts unique values using DAO
-counts unique values using pivot table
-counts unique values using collection object

Issue #5:
-prevents the user from using File, Save As
-creates a high performance timer
-finds a string across worksheets in a workbook
-stops links update box from apperaring when opening a file
-clears worksheet of data and leaves formulas intact
-prevents flickering when a procedure is run in the VBE


EXCEL 2000:

Issue #1:
-Office Web Components can be used in UserForms

Issue #2:
-use of the ID property

Issue #3:
-new data handling tools

Issue #4:
-new settings under Tools, Options

Issue #5:
-procedure showing use of modeless UserForm - automatic charting


TIPS AND TECHNIQUES:

Issue #1:

Issue #2:
-create a hyperlink from an object to a VBA procedure

Issue #3:
-Excel functions used in 3D formulas
-inserting rows in 3D named ranges

Issue #4:
-Office Spreadsheet Component has more columns than Excel

Issue #5:
-locate information on Excel viruses