I'm trying to implement a function in Visual Basic for Applciations

that uses Pmt and Rate to calculate APRs. So I created a COM object

that does just that. I can instantiate and call it from ASP, but I

can't seem to get it to work as an automation call in SQL. We're

running a query that needs to calculate and select loans based upon

APR, and doing this calculation outside of SQL is proving to be a

serious performance penalty. So I looked up what I needed to do, and

as far as I can tell I'm doing everything properly, but it just won't

work. Here is the object, and some SQL code that calls it. Notice the

only function that works in SQL is the testReturnCode(), however all

these functions work in ASP.

Any help is greatly appreciated!

Bryan

-- BEGIN CODE --

Public Function calcArmAPR(LoanAmount As Double, Interest As Double,

Points As Double, Costs As Double, Term As Integer, Years As Integer,

Index As Double, Margin As Double) As Double

Dim newRate As Double

Dim newAmount As Double

Dim origPayment As Double

Dim newPayment As Double

Dim APR As Double

If Not (IsNumeric(LoanAmount) And IsNumeric(Interest) And IsNumeric

(Points) And IsNumeric(Term) And IsNumeric(Years) And IsNumeric(Index)

And IsNumeric(Margin)) Then

calcArmAPR = 0

Exit Function

End If

newRate = Interest / 1200

newAmount = LoanAmount - Costs - LoanAmount * (Points / 100)

origPayment = Pmt(newRate, Term, -1 * LoanAmount, 0, 0)

newPayment = Pmt((Index + Margin) / 1200, Term, -1 * LoanAmount, 0,

0)

APR = Rate(Term, (((origPayment * Years * 12) + (newPayment * (Term

- (Years * 12)))) / 360), -1 * newAmount, 0, 1, newRate)

calcArmAPR = APR * 1200

End Function

Public Function testReturnCode() As Integer

testReturnCode = 77

End Function

Public Function testByRef(ByRef Val As Integer) As Integer

Val = 1

testByRef = 0

End Function

Public Function testCalculation(ByRef A As Integer, ByRef B As Integer,

ByRef Result As Integer) As Integer

Result = A + B

testCalculation = 0

End Function

And finally the SQL code:

DECLARE

SET NOCOUNT ON

RETURN

1.0, 1000.0, 360, 7, 5.5, 2.0

BEGIN

END

ELSE

BEGIN

END

ELSE

BEGIN

END

ELSE

BEGIN

END

ELSE

BEGIN

END

ELSE

Sent via Deja.com http://www.deja.com/

Before you buy.