Issue No. 10 (August 1, 1999)

[Item URL]

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

COMMENTS

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

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

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

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

TOP EXCEL WEB SITES

See www.beyondtechnology.com for some great Excel tips and a free Excel
newsletter.

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

WORKSHEET FORMULA TIP

By George Simms

This array formula returns the sum of cells in the 3D range bounded by the
by the sheets named in cells B2 and B3.

=SUM(N(INDIRECT(ROW(INDIRECT(B2&":"&B3))&"!A1")))


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

POWER FORMULA TECHNIQUE

By Alab Beban

Here is an example of how to solve a set of simultaneous equations using 
Excel.

Start with equations that are linearly independent so that there is, 
in fact, a solution; e.g.,

17 = 5x + 3y + 2z
13 = 2x + 4y +  z
22 = 3x + 2y + 5z

Put the coefficients of the unknowns in, e.g., A1:C3 (i.e., 5,3,2 in
A1:C1, 2,4,1 in A2:C2, etc.);

Put the constants (17, 13, 22) in, e.g., D1:D3;

Highlight, e.g., E1:E3 and array enter (i.e., enter with
Ctrl+Shift+Enter instead of just Enter)

=MMULT(MINVERSE(A1:C3,D1:D3)

and the solution vector (1,2,3) will appear in E1:E3; 
i.e., x=1, y=2, z=3

For a set of equations that does not have a solution, the #VALUE error
will appear in E1:E3.

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

VBA CODE EXAMPLES 

By Andrew Baker

Use this procedure to disable the Excel close button.

Send in Me.Caption into either of the following routines. Make sure you do
this on the initialise event for 'DisableActiveDialogMenuControls'

'-----------------------------Declarations to Remove Dialog Controls

Private Const MF_BYPOSITION As Long = &H400     
''' Deletes the menus byposition (this is our default)

Private Const MF_BYCOMMAND As Long = &H0        
''' Deletes the menu by Command ID. This is rarely used and is shown here 
for information purposes only.

Private Const mlNUM_SYS_MENU_ITEMS As Long = 9  
''' This is the number of items on the system menu

Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long,
ByVal bRevert As Long) As Long

Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal
nPosition As Long, ByVal wFlags As Long) As Long

Private Declare Function FindWindowA Lib "user32" (ByVal lpClassName As
String, ByVal lpWindowName As String) As Long


' Comments:   Deletes the system control menu of the specified window.
'
' Arguments:  DialogCaption The caption of the window whose control
'                           menu you want to delete. If not specified,
'                           Application.Caption is assumed.
'
Public Sub DisableActiveDialogMenuControls(DialogCaption As String)
    Dim lHandle As Long, lCount As Long
    On Error Resume Next
    DialogCaption = DialogCaption & vbNullChar
    lHandle = FindWindowA(vbNullString, DialogCaption)

    ' Only continue if the passed window handle isn't zero.

    If lHandle <> 0 Then

        ' There are 9 items on the application control menu.
        ' Loop through and disable each one.

        For lCount = 1 To mlNUM_SYS_MENU_ITEMS

            ' The nPosition of the DeleteMenu function will always be 0,
            ' because as we delete each menu item, the next one moves up
            ' into the first position (index of 0).

            DeleteMenu GetSystemMenu(lHandle, False), 0, MF_BYPOSITION
        Next lCount

    End If

End Sub

' Comments:   Restores the system control menu of the specified window.
'
' Arguments:  szCaption   (Optional) The caption of the window whose control
'                         menu you want to delete. If not specified,
'                         Application.Caption is assumed.
'

Public Sub EnableActiveDialogMenuControls(DialogCaption As String)

    Dim lHandle As Long
    On Error Resume Next
    DialogCaption = DialogCaption & vbNullChar
    lHandle = FindWindowA(vbNullString, DialogCaption)

    ' Passing True to the bRevert argument of the GetSystemMenu API restores
    ' the control menu of the specified window.

    GetSystemMenu lHandle, True

End Sub


By Robert Rosenburg

This routine clears only numbers from a selection (leaving the formulas & 
any cells containing text alone).

Sub ClearNumbersOnly()

    Dim iCalc As Integer
    Dim rngCell As Range

    On Error GoTo Error

    If LCase(TypeName(Selection)) = "range" Then

        iCalc = Application.Calculation
        Application.Calculation = xlCalculationManual

        For Each rngCell In Selection
            If Not rngCell.HasFormula Then
                If Application.IsNumber(rngCell) Then rngCell.ClearContents
            End If
        Next rngCell

        Application.Calculation = iCalc

    End If

Error:
	Msgbox "Error in: ClearNumbersOnly"
End Sub

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

POWER PROGRAMMING TECHNIQUE

By David Hager

The goal is to create a protected worksheet where filtering and formatting
can be done without unprotecting the worksheet. That can be accomplished 
by placing the following event procedure in the corresponding sheet module.

Public bFlag As Boolean

Private Sub Worksheet_Calculate()
    If bFlag Then Exit Sub
    On Error Resume Next
    With Application
    	.EnableEvents = False
    	.Undo
    	.EnableEvents = True
    End With
End Sub

Now, this procedure will prevent changes to cell contents as long as the
Calculate event is triggered. This can be done by placing the following 
formula in cell A1.

=COUNTA(A2:A65536,B:IV)

If you try to drag and drop data to an area that already contains data,
an Excel message prompt appears, but either way it is answered does not 
affect the protection of the data. If the formula in A1 is moved, circular
reference messages appear, but again, the formula is not affected. These
prompts and messages occur before any worksheet-based event, so there does
not seem to be a way to prevent their appearance.

This technique work especially well for sheets containing a list used as
a flat database. The filtering of the list does not trigger the Calculate
event. 

You might want to have the option to update a worksheet protected in this 
way. This can be done by using the following procedure.

Sub ChangeTheSheet()
    bFlag = True
    ' some code here to change the worksheet
    bFlag = False
End Sub


By David Hager

There is a little-known effect for the display of charted data that can add 
considerable polish to a chart presentation. This applies to data ordered
by either rows or columns. When records are arranged by rows, they can be 
hidden through filtering by using Data, Filter, AutoFilter. Columns of
charted data can be hidden by using the Format, Column, Hide command. In 
either case, it turns out that the data that has been filtered or hidden
no longer appears on the chart. This effect is quite useful for the 
viewing of data with a single chart, since what appears on the chart is
controlled by the visible data on the worksheet. For example, you can have 
a chart with many data series and view them one at a time. Other descriptive 
fields or rows can be added to the data table that enhance to ability to 
filter the data in different ways. Due to the options available for the 
manipulation of data in the data filtering process, this effect works best 
when the data is ordered in rows, assuming that the data set is not larger 
than the number of columns.

One drawback to using this technique for the display on information in a 
meeting is that changes to the source data would have to be done by toggling
between the chart and worksheet holding the data. Fortunately, there is an
easy way to overcome this problem. The desired filter settings can be stored
in custom views. Then, a listbox with those views can be added to the chart
sheet. Since an ActiveX listbox cannot be used on a chart sheet, you will have 
to use the native Excel listbox that is available from the Forms toolbar. You
can add the desired custom view names programatically or by linking it to a
worksheet range. The following procedure will add all of the custom views in
a workbook to the listbox. It contains a workaround for a problem in Excel that
prevents a normal looping process for the Custom Views collection. 

See:
http://support.microsoft.com/support/kb/articles/q164/0/21.asp
for more details (this article may not currently be available at the
Microsoft site).


Function CreateArrayAndAddToListBox()

    Dim TheArrayCount As Integer
    Dim ListArray()
    
    With ActiveWorkbook

	.CustomViews.Add "Temp"
        
        TheArrayCount = .CustomViews.Count - 1
        ReDim Preserve ListArray(TheArrayCount)
        
        For n = 1 To TheArrayCount
            .CustomViews(n).Show
            ListArray(n) = .CustomViews(n).Name & _
	    "  (" & ActiveSheet.Name & ")"
        Next
        
        For i = 1 To TheArrayCount
        
            For j = i + 1 To TheArrayCount
            
                If ListArray(i) > ListArray(j) Then
                    tVar = ListArray(i)
                    ListArray(i) = ListArray(j)
                    ListArray(j) = tVar
                End If
                
            Next
            
        Next
        
	.Sheets("TheChart").ListBoxes("lbShow").List = ListArray

       .CustomViews("Temp").Delete

    End With
    
End Function

Be aware that there are some problems in running code that shows a chart as a
view. I experienced several system crashes, so try to avoid this scenario. 
It might be preferable to use the worksheet list link, since you can include 
only the custom views you want for a given chart quite easily this way. Then, 
right-click on the listbox and assign the macro shown below to it.


Sub ChangeChartView()

    Application.ScreenUpdating = False
    ThisChart = ActiveSheet.Name

    With ActiveChart.ListBoxes("lbShow")
    	ActiveWorkbook.CustomViews(.List(.ListIndex)).Show
    End With

    Sheets(ThisChart).Activate
    Application.ScreenUpdating = True

End Sub

Then, by clicking on an item in the listbox, the custom view corresponding to 
the name of the item clicked will be shown. That will cause the filtering and/or
the hiding of columns to be applied to the source data for the chart. That, in
turn, will cause complete data points or complete data series to not appear on
the chart. If you are using a legend on your chart, it will change to reflect 
only the data series currently appearing on the chart. 

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

EXCEL 2000 TIP

Are you interested in the role XML plays in Excel 2000 file conversion and
web data transmission? See:

http://msdn.microsoft.com/isapi/msdnlib.idc?theURL=/library/officedev/
offhtml9/shared/ofelexml.htm

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

DO YOU KNOW?...

the steps for making an Office 2000 COM add-in? Here is Stephen Bullen's
guide through that process.

Using MOD 2000:

1. Open FP (one instance)

2. switch to the VBE

3. Add a new addin project

Using VB6:

1. Start VB6, electing to create a new COM Addin

2. Do nothing

3. Do nothing

Both:

4. Add a normal module, containing:

Public oFP As FrontPage.Application
Public oEvents As New CEvents

5. Add a class module called CEvents, containing:

Public WithEvents oBtn As CommandBarButton

Private Sub oBtn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault 
As Boolean)
MsgBox "Clicked in " & oFP.ActiveWebWindow.Caption
End Sub

6. Add code to the Designer's code module:

Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal 
ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As 
Object, custom() As Variant)

Dim oBar As CommandBar, oBtn As CommandBarButton

Set oFP = Application
Set oBar = oFP.CommandBars("Menu Bar")
RemoveMenu
Set oBtn = oBar.Controls.Add(msoControlButton)

With oBtn
    .Caption = "Test"
    .Tag = "FPT"
    .Style = msoButtonCaption
End With

Set oEvents.oBtn = oBtn

End Sub

Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As 
AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
RemoveMenu
End Sub

Private Sub RemoveMenu()
Dim oCtls As CommandBarControls, oCtl As CommandBarControl

Set oCtls = oFP.CommandBars.FindControls(Tag:="FPT")

If Not oCtls Is Nothing Then
    For Each oCtl In oCtls
        oCtl.Delete
    Next
End If

End Sub


7. Compile it into a DLL

8. Close the project / Addin

9. Swtich to / open FrontPage 2000

10. Click on Tools, Addins to start the addin

11. Click on the "Test" menu item added to the menu bar - see the message

12. Start another instance of FrontPage

13. Click on the "Test" menu item added to the menu bar - see the message

14. Use File/Open to open a new FP web

15. Click on the "Test" menu item added to the menu bar - see the message


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

Issue No.10 OF EEE (PUBLISHED 31Jul1999)
Next issue scheduled for 15Aug1999.
BY David Hager
dchager@compuserve.com

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

CUMULATIVE INDEX (ISSSUES 6-10):

WORKSHEET FORMULAS:

Issue #6:
-HYPERLINK formula for finding information from newsgroups

Issue #7:
-Formulas to tranform a string into a sorted string

Issue #8:
-Numerous formulas used in conditional formatting

Issue #9:
-Counts the number of cells discontiguous range based on a criteria
-Formula that allows reference to be used in defined name formula

Issue #10:
-Returns a 3D sum from sheet A to sheet B
-Formula for solving a set of simultaneous equations 



VBA PROCEDURES:

Issue #6:
-Creates a list of all number formats in use in the active workbook
-Procedure for removing tabs and carriage returns in worksheet cells
-Displays pop-up messages when the mouse cursor is rested over embedded 
charts

Issue #7:
-UDF to tranform a string into a sorted string
-Data encryption/decryption method for strings
-Procedure for manipulating custom number formats

Issue #8:
-UDFs used in conditional formatting

Issue #9:
-Finds cells on a worksheet containing data displayed as #####
-Prints out all cell comments from a workbook
-Procedure to look up Windows 95 serial number
-Turns off the 'Break on Unhandled Errors in Class Module'option in the VBE
-Returns information from a closed workbook with VBA

Issue #10:
-Procedure to disable the Excel close button
-Routine to clear only numbers from a selection
-Method for protecting a worksheet which can be filtered  
-Making changes to a chart by filtering/hiding data



EXCEL 2000:

Issue #6:
-Cannot create interactive web pages with just Excel 2000

Issue #7:
-Problems associated with copy/paste
-Create array formula in Spreadsheet Component

Issue #8:

Issue #9:
-Problem with workbook containing a hyperlink saved as a Web page

Issue #10:
-Steps for creating a COM add-in


TIPS AND TECHNIQUES:

Issue #6:

Issue #7:
-Spreadsheet Component calculates dates differently than Excel

Issue #8:
-Combining worksheet controls with conditional formatting

Issue #9:
-Registers functions into user-defined catagories and provides descriptions 
for their arguments
-Method for providing additional security for passwords

Issue #10:
-URL for comprehensive Excel/XML information at Microsoft web site