Issue No. 17 (March 1, 2000)
**********************************
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
**********************************
Excel Expert Newsletter Archives
Here you'll find the archives of David Hager's Excel Expert's E-Letter, produced in 1999-2001. This information is old and unorganized, but it's here because it still contains lots of useful information. The newsletters contains quite a few links. Needless to say, most are no longer valid.
It's interesting to note that some of the key problems back then are still key problems today.