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

COMMENTS

Welcome to the 17th issue of the Excel Experts E-letter (or EEE), by 
David Hager. EEE is now a monthly publication. Feel free to distribute
copies of EEE to your friends and colleagues and to contribute your Excel 
gems to EEE so that others can benefit from your work. 

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 
of each month. There will be periods when EEE is not published due to time
and travel constraints.

http://www.j-walk.com/ss/excel/eee/index.htm
     
**********************************
Top Excel Sites

Go to this site for a great index of Excel information.

http://www.mathtools.net/Excel/index.html

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

WEB INFORMATION ON:

ExecuteExcel4Macro method

Access to closed workbooks:
http://eva.dc.lsoft.com/scripts/wa.exe?A2=ind9908D&L=excel-l&P=R8921&m=28403
http://eva.dc.lsoft.com/scripts/wa.exe?A2=ind9812C&L=excel-l&P=R8589&m=28403
http://x43.deja.com/=dnc/getdoc.xp?AN=370019007&CONTEXT=936234727.222691395&hitnum=103
http://x30.deja.com/=dnc/getdoc.xp?AN=394587442&CONTEXT=936236165.1461321770&hitnum=69

Excel charts:
http://peach.ease.lsoft.com/scripts/wa.exe?A2=ind9810B&L=excel-g&P=R5489
http://x30.deja.com/=dnc/getdoc.xp?AN=497422618&CONTEXT=936236165.1461321770&hitnum=37
http://x43.deja.com/=dnc/getdoc.xp?AN=362117773&CONTEXT=936234727.222691395&hitnum=121
http://x43.deja.com/=dnc/getdoc.xp?AN=247283361&CONTEXT=936234727.222691395&hitnum=187

Printing:
http://x43.deja.com/=dnc/getdoc.xp?AN=240180079&CONTEXT=936234727.222691395&hitnum=191
http://x43.deja.com/=dnc/getdoc.xp?AN=296331018&CONTEXT=936234727.222691395&hitnum=160
http://support.microsoft.com/support/kb/articles/Q139/4/05.asp

Formula.Find:
http://www.mailbase.ac.uk/lists/excel-vb-discuss/1999-03/0001.html

Custom Views:
http://eva.dc.lsoft.com/scripts/wa.exe?A2=ind9606&L=excel-l&P=R8426
http://eva.dc.lsoft.com/scripts/wa.exe?A2=ind9606&L=excel-l&P=R8508

Delete Rows:
http://x30.deja.com/=dnc/getdoc.xp?AN=484149794&CONTEXT=936236165.1461321770&hitnum=97

Set.Update.Status
http://support.microsoft.com/support/kb/articles/Q108/3/84.asp

Close:
http://x43.deja.com/=dnc/getdoc.xp?AN=209988463&CONTEXT=936234727.222691395&hitnum=202

ShowBar:
http://x43.deja.com/=dnc/getdoc.xp?AN=364967596&CONTEXT=936234727.222691395&hitnum=120


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

WORKSHEET FORMULA TIP

by Harlan Grove

Question from Microsoft Excel worksheet formula newsgroup:

> Is there a formula that I could use to count the number
> of rows that contain data based on my criteria..
...
> Using this data as an example
>
> A    B   C    D    E    F
> 1    3    5    8    12    13
> 3    2    6    5    7    9
> 5    7    4    8    12    3
>
>I would like to identify how many times 3 and 5 appear together
>and I might even want to identify how many times 3, 5 and 12
>appear together.
>From the above example data you can see that the numbers
>3, 5 and 12 wont always be in the same column, using
>DCOUNT restricts me to identifying the column heading in my
>field criteria.
...
>My criteria my look like this
>
>Column headings---->    Find 1  Find 2  Find 3  Find 4  Find 5  Find 6
>and I would enter---->           3          5         12
>the formula / function ->  whatever it is that would do what I require
>would return a count on the number of rows that contain 3,5 and 12
>any order
...

Answer:

With your sample data range named MyData and the 'criteria' entry cells 
(all 6) range above named MyCrit, try this array formula.

=COUNT(IF(MMULT(COUNTIF(MyCrit,MyData),
TRANSPOSE(COLUMN(MyData)^0))=COUNT(MyCrit),1))

Note: this assumes no duplicate 'criteria' entries. 

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

POWER FORMULA/FUNCTION TECHNIQUES

by David Hager

I wrote this array formula to combine the functionality of the XIRR and MIRR
functions. This formula returns the internal rate of return for a schedule 
of cash flows that is not necessarily periodic while considering both the 
cost of the investment and the interest received on reinvestment of cash. 
The fields used in the formula are defined below.

=POWER((SUM(IF(values>0,values*(POWER(1+rRate,(MAX(dates)-dates)/daybase))
,0)))/(SUM(IF(values<0,values/(POWER(1+iRate,(MAX(dates)-dates)/daybase))
,0)))*-1,1/((MAX(dates)-MIN(dates))/daybase))-1

where:

values is the row or column range of cashflows
dates is the row or column range of corresponding dates
iRate is the interest rate you pay on the money used in the cash flows
rRate is the interest rate you receive on the cash flows as you reinvest them
daybase is days-in-year basis to use (usually 360 or 365).


The following UDF provides the same functionality as the array formula.


Function XMIRR(TheValues As Range, TheDates As Range, iRate, rRate, daybase)

    Dim rCount As Integer
    Dim cCount As Integer
    Dim rCounter As Integer
    Dim cCounter As Integer
    Dim TheVal As Double
    Dim TheDate As Double
    Dim MaxDate As Double
    Dim MinDate As Double
    Dim PosSum As Double
    Dim NegSum As Double
    On Error GoTo eFunction

    rCount = TheValues.Rows.Count
    cCount = TheValues.Columns.Count

    PosSum = 0
    NegSum = 0

    MinDate = TheDates.Offset(0, 0).Resize(1, 1).Value

    If rCount > cCount Then
        MaxDate = TheDates.Offset(rCount - 1, 0).Resize(1, 1).Value

        For rCounter = 0 To rCount - 1
            TheVal = TheValues.Offset(rCounter, 0).Resize(1, 1).Value
            TheDate = TheDates.Offset(rCounter, 0).Resize(1, 1).Value
            If TheVal < 0 Then
                NegSum = NegSum + TheVal / ((1 + iRate) ^ ((TheDate - _
		MinDate) / daybase))
            Else
                PosSum = PosSum + TheVal * ((1 + rRate) ^ ((MaxDate - _
		TheDate) / daybase))
            End If
        Next

    Else

        MaxDate = TheDates.Offset(0, cCount - 1).Resize(1, 1).Value

        For cCounter = 0 To cCount - 1
            TheVal = TheValues.Offset(0, cCounter).Resize(1, 1).Value
            TheDate = TheDates.Offset(0, cCounter).Resize(1, 1).Value
            If TheVal < 0 Then
                NegSum = NegSum + TheVal / ((1 + iRate) ^ ((TheDate - _
		MinDate) / daybase))
            Else
                PosSum = PosSum + TheVal * ((1 + rRate) ^ ((MaxDate - _
		TheDate) / daybase))
            End If
        Next

    End If

    XMIRR = ((PosSum / NegSum * -1) ^ (1 / ((MaxDate - MinDate) / _
            daybase))) - 1
    Exit Function
eFunction:
    XMIRR = CVErr(2015)
End Function



by Laurent Longre

This VB function returns the same result as Excel's WEEKNUM function.
Function WKNUM(D As Date) As Long
  D = Int(D)
  WKNUM = DateSerial(Year(D + (8 - WeekDay(D)) Mod 7 - 3), 1, 1)
  WKNUM = ((D - WKNUM - 3 + (WeekDay(WKNUM) + 1) Mod 7)) \ 7 + 1
End Function 


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


VBA CODE EXAMPLES 

by Jim Rech

This procedure changes the font size in all cell comments on a worksheet.

Sub ChgAllComments()
    Dim Cell As Range
    For Each Cell In Cells.SpecialCells(xlCellTypeComments)
        With Cell.Comment.Shape.TextFrame.Characters.Font
            .Size = 9
        End With
    Next
End Sub


by Stephen Bullen

This procedure open shows Excel's DataForm in New Record mode.

Sub ShowDataFormWithNewRecord()

   'Send a keystroke 

   SendKeys "+{TAB 6} "

   'This is the same as ActiveSheet.ShowDataForm,
   'but without the International Issues

   CommandBars.FindControl(Id:=860).Execute

End Sub


**********************************
EXCEL DEVELOPER'S TIPS

Pull in correct values from Internet:

When importing stock data into a sheet using a web query, fractional stock 
prices less than 1 may be interpreted by Excel as dates. However, selecting 
Tools, Options, Transition and then clicking "Transition formula entry" 
coerces Excel into accepting the desired value.


Use class modules from another project:

By John Green

You can create an instance of a class in another project by creating a 
function in the referenced project containing the class module. In the 
project containing the class module, include something like the following 
code, in a standard module:

Function GetClass() As Class1
  Set GetClass = New Class1
End Function

In the project that wants to access the class, use something like the 
following code:

Dim cls As Object

Sub Test()
  Set cls = GetClass()
End Sub

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

Issue No.17 OF EEE (PUBLISHED 01Mar2000)
Next issue scheduled for 01Apr2000.
BY David Hager
dchager@compuserve.com

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