CUMIPMT

[Michael Malinsky]

I am trying to use CUMIPMT in VBA. I've browsed the NG and tried the
ideas found there, but to no avail. Can anyone give me any help on
this or any alternate solutions. I suppose I could use IPMT and a
loop, but I'm sure that could be rather slow.

Any help is appreciated.

Mike.

 

[Jim Cone]

Michael,
This works, but whether it meets your needs?...
'-----------------------
Sub TestFunctionInRange()
Dim dblCumInt As Double
Range("B12").Formula = "=CUMIPMT(1, 100, 1000, 1, 50, 1)"
dblCumInt = Range("B12").Value2
Range("B12").ClearContents
MsgBox dblCumInt
End Sub
'------------------------------------------

Regards,
Jim Cone
San Francisco, USA

 

[Don]

There is no need to print the sheet and clear.

In the VB Editor, go to Tools/References, and be sure there's a check mark
in
front of ATPVBAEN.XLS. Then it will work like this:


Sub testme()
a = CUMIPMT(1, 100, 1000, 1, 50, 1)
End Sub

Don Pistulka

 

[Tom Ogilvy]

without creating the reference, but with the Analysis toolpak - VBA loaded
it would be:

Sub testme()
a = Application.Run("ATPVBAEN.xlA!CUMIPMT", 1, 100, 1000, 1, 50, 1)
MsgBox a
End Sub

 

[Don]

Thanks Tom
That's a new one for me.

Don Pistulka

 

[EvolBob]

How is this Michael?

MsgBox [cumipmt(1,100,1000,1,50,1)]


Regards
Robert McCurdy

 

[Michael Malinsky]

Thanks everyone...I added the ATPVBAEN.XLS reference and tried using
CUMIPMT, but it still didn't work. I tried again this morning and it
worked fine. When you add a reference, do you have to restart Excel or
the VBE to for the reference to take effect?

Thanks again...yet another stumbling block solved by the experts of the
NG!

Mike.

 

[Tom Ogilvy]

No, you don't have to restart excel.