Crreating an Object

A

Art Montz

"Out of stack space" error.... can't figure why. Trying to
create a simple object.

Sub TestLoanObject()
Dim rg As Range
Dim objLoan As Loan


Set rg = ThisWorkbook.Worksheets("Loans").Range("A2")
Set objLoan = New Loan

Do Until IsEmpty(rg)
With objLoan

-- it hangs here when trying to assign a value to the Term
property --

.Term = rg.Offset(0, 1).Value
.InterestRate = rg.Offset(0, 2).Value
.PrincipalAmount = rg.Offset(0, 3).Value
rg.Offset(0, 4).Value = .Payment
End With
Set rg = rg.Offset(1, 0)
Loop

Set objLoan = Nothing
Set rg = Nothing
End Sub
 
S

Stan Scott

Art,

I assume that "Loan" is a class object -- but that's not necessary. Nor is
it necessary to create a range object. BTW, IsEmpty(rg) is only true when
the variable has not been initialized, or is explicitly set to empty. This
code of yours loops through all 65536 rows. I think you intend it to stop
when the CELL in column A is empty -- this isn't the same thing at all.

Others can give you more details, but there's no need to create objects and
whatnot. Adjust this code as needed:

Sub ComputePayments()
dim t
t = 1
With ThisWorkbook.Worksheets("Loans").Range("B1") 'starting with
col B, for the term
Do Until Len(.Offset(t, 0)) = 0
.Offset(t, 3) = Application.Pmt(.Offset(t, 1), .Offset(t, 0),
..Offset(t, 2))
t = t + 1
Loop
End With
End Sub

Stan Scott
New York City
 
J

John Green

Hi Art,

You might want to take Stan Scott's advice and simplify your code. However,
if you want to use your own object, you will need to check that your class
module code is OK.

The following very simple class module code works OK for me with your code
and might help you check your own code.

Class Module Loan
----------------------------------------------------------------------------
----------
Option Explicit

Public Term As Double
Public InterestRate As Double
Public PrincipalAmount As Double

Public Function Payment()
Payment = Application.Pmt(InterestRate / 12, Term *
12, -PrincipalAmount)
End Function
----------------------------------------------------------------------------
 
A

Art Montz

Thanks Stan. You are correct, it is/was a class object
with "Term", "InterestRate", etc... as class properties.

It was supposed to be simple exercise on class
development. I understand the concept but uanble to get
this to work. The Loan Class property "Term" should get
its assigned value from:

.Term = rg.Offset(0, 1)

While debugging, the spreadsheet value of the "rg" range
is displayed when mouse pointer is placed over the
statement.. also the default value of "term"(w/mouse
pointer). As I [F8]my way through this, I see this goes
into an infinite loop within the "Term" property. It
doesn't matter which property I use, they all go into
infinte loops until stack message. The property statements
are very very basic...

Public Property Get Term() As Variant
Term = mvarTerm
End Property

Public Property Let Term(ByVal vNewValue As Variant)
Term = vNewValue
End Property


Code taken from a book "Excel 2003" by Steven M. Hansen. I
have been playing around with this for 2+ hours... I don't
get it.......... but thanks for your timely response.

-Art
Dallas, TX
 
K

keepITcool

for simple stuff keep some variable in your class public..
else you must define the property lets and the gets :)

Option Explicit
'class loan
Public term As Integer
Public interestrate As Double
Public principalamount As Double

Public Function Payment() As Double
on error goto oops
Payment = Application.WorksheetFunction.Pmt(Me.interestrate, Me.term,
Me.principalamount)
exit function
oops:
Payment=cverr(xlerrvalue)

End Function



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Art Montz said:
Thanks Stan. You are correct, it is/was a class object
with "Term", "InterestRate", etc... as class properties.

It was supposed to be simple exercise on class
development. I understand the concept but uanble to get
this to work. The Loan Class property "Term" should get
its assigned value from:

.Term = rg.Offset(0, 1)

While debugging, the spreadsheet value of the "rg" range
is displayed when mouse pointer is placed over the
statement.. also the default value of "term"(w/mouse
pointer). As I [F8]my way through this, I see this goes
into an infinite loop within the "Term" property. It
doesn't matter which property I use, they all go into
infinte loops until stack message. The property statements
are very very basic...

Public Property Get Term() As Variant
Term = mvarTerm
End Property

Public Property Let Term(ByVal vNewValue As Variant)
Term = vNewValue
End Property


Code taken from a book "Excel 2003" by Steven M. Hansen. I
have been playing around with this for 2+ hours... I don't
get it.......... but thanks for your timely response.

-Art
Dallas, TX
-----Original Message-----
Art,

I assume that "Loan" is a class object -- but that's not necessary.
Nor is it necessary to create a range object. BTW, IsEmpty(rg) is
only true when the variable has not been initialized, or is explicitly
set to empty. This code of yours loops through all 65536 rows. I
think you intend it to stop when the CELL in column A is empty -- this
isn't the same thing at all.

Others can give you more details, but there's no need to create
objects and whatnot. Adjust this code as needed:

Sub ComputePayments()
dim t
t = 1
With ThisWorkbook.Worksheets("Loans").Range ("B1") 'starting
with
col B, for the term
Do Until Len(.Offset(t, 0)) = 0
.Offset(t, 3) = Application.Pmt(.Offset(t, 1), .Offset(t,
0), ..Offset(t, 2)) t = t + 1
Loop
End With
End Sub

Stan Scott
New York City




.
Click to expand...
Click to expand...
 
D

Dick Kusleika

Art

Public Property Let Term(ByVal vNewValue As Variant)
Term = vNewValue
End Property
Click to expand...

This should be

mvarTerm = vNewValue

That's why you're getting an infinite loop.
 
Top