PDA

View Full Version : Mortgage spreadsheet concept problem



hiflier
02-14-2011, 11:47 PM
I own several mobile home parks. I sometimes buy mobile homes and resell them and carry the mortgage. I've written a mortgage loan program that creates the loan payment sheet and the amortization table. Right now I do this within the original workbook. I then manually copy it to another workbook for the park the mobile home is in. I want to do this automatically as part of the loan creation routine. The problem is that I have some VBA code (which calculates when any payment changes using the Worksheet.change event) which doesn't transfer to the new workbook. For now, I need a flowchart of steps to take to solve the problem (ie, open target workbook, add a worksheet, copy the original to the new worksheet, copy VBA subroutine to the Worksheet.change event on the new worksheet, etc.) Once I learn how this is to flow, I think I can figure out the required code. Right now I'm befuddled. Thanks.

Bob Phillips
02-15-2011, 01:11 AM
Why not have all sheets in the one workbook, and use a Workbook Worksheete event.

IBihy
02-15-2011, 05:59 AM
Hello,

I do not quite agree to xld's suggestion. Of course it is valid to have your data in several workbooks. And, as I understand it, having several workbooks is NOT the problem here. The problem appears to be how to structure the actions the code is supposed to do.
Hiflier, in order to figure out the steps your code is supposed to take, you do not necessarily need specific artifacts like the good ol' flowchart, although it helps. There are other ways, which, in the end, lead to the same result.
Take a few sheets of paper, and a pencil.
On the top of a sheet you write the one sentence abstraction of what the whole coding is supposed to do. As I gather from your entry above, you do have an idea of what the code is supposed to do. Actually, at this stage, do NOT think of coding but simply the business side of what you want to do. Leave enough space between the steps, a good rule is three to four fingers' width, no more than four, five steps on one sheet. In your case, it might look like this:

Mortgage loan payments and amortizations for my mobile home parks 1/2

1. One workbook for each mobile home park.
... three to four fingers' width of space ...
2. Each workbook will hold the loan payments and amortizations for the affected mobile homes in the parks in the same structure.
... three to four fingers' width of space ...
3. The calculations need to be transferred to each workbook.
... three to four fingers' width of space ...
4. The changed workbook needs to be saved.
... three to four fingers' width of space ...
5. Only one mortgage workbook for one park open while calculating.
... three to four fingers' width of space ...

Mortgage loan payments and amortizations for my mobile home parks 2/2

6. Not only the results of the calculations are to be transferred to a specific workbook, but the calculations themselves.
... three to four fingers' width of space ...
and so on.
Into the space between the points you jot down anything that comes into your mind that might support the idea.
For example, for item 1 in my suggestion, write: workbook file name = "Henderson_Park_<date>.xls" (I still use Excel 2003, therefore the *.xls)
Sheet names = Lot_nnnnn Paymnt and Lot_nnnnn Amort, where nnnnn is the identifier of the lot. Thus you would know that you need two sheets for each mobile home you own.
Continue through your above list. In the next step you bring all of the list items in an order and add supporting details. In item 2 there is the hint that all worksheets of a certain type (loan payment, amortization) have the same structure. What could that mean? I would create a template each of the loan payment and amortization sheets in the workbook that does the calculations. Next idea in item 2: Since worksheets are to be created again and again, you'll need a function that creates the worksheets and returns the handle. Think of a name, like Function CreateWorksheet(sheetType as String) As Worksheet where sheetType is e.g. "loan" or "amort" if you like.
After you're through with that, you have an idea of the flow. Re-order your processing steps, taking into account the details. Maybe you'll find that you need more steps, or that steps have common stuff. The steps get names, like "Perform Loan Payment Calculations"
Then, on a new sheet of paper, draw three columns. Title

Input Processing Output

Write down the names of the processing steps in order in the center, leave space between the lines. Left column, input, is what the step needs in order to complete e.g. sale price, down payment, loan amount, etc. Right column, output, what needs to be produced by this piece of processing, e.g. array of monthly payments.
Next is the Pseudo-Code. Take a new sheet of paper, sample title:

Detailed processing of "Perform Loan Payment Calculations"

Pseudo-Code Variables
The column "Pseudo-Code" is two thirds, the column "Variables" is one thrid of the width of the paper.
Under "Pseudo-code" you write in simple English the details of the processing, like
If loan amount is not numeric, issue error message, return to <sheet>, high-light erroneous field.
If interest rate is not numeric , issue error message, return to <sheet>, high-light erroneous field.
in the right column, jot down the variables, loan amount, interest rate, each in its respective line.

I think you get the idea. I have assumed standard data processing, that is procedural programming. It would have to be done differently if objects were in the game.

Let me know if that's a viable way for you.
Isabella

hiflier
02-15-2011, 06:27 PM
Wow. That is a terrific answer and I'll do exactly what you suggest. One more question: I do indeed have a loan sheet and an amortization sheet in the main program, which I copy to new sheets when the loan is created. I then customize the new sheets for the particulars of the loan. All of it works well as long as I remain in the same workbook (Loan Creator). What I don't know how to code is to open a specific loan workbook (Stardust Loans) and create the loan in the other workbook including transferrring my code in the Worksheet.Change event to the newly created workbook. Can you help with that? Thanks again for educating me on paper and pencil outlining.

IBihy
02-15-2011, 07:06 PM
Hello Hiflier,
I'm glad that you like the answer. If you get stuck, raise a hand. A bit philosophy on Excel programming on the side:
Just because it's "macros only" it doesn't mean you don't have to do some designing. My biggest application (yes I dare call it such) consists of (hold tight) 60 something routines, including the actions for the GUI. --- Now I sound like a smart-ass. You get the idea.

In shorthand, so to speak, creating a "foreign" workbook can be accomplished by
Dim wbThisWorkbook as Workbook
Dim wbLoanWorkbook as Workbook
Set wbThisWorkbook = ThisWorkbook ' the one you start your code in
' That's how it's done if the workbook does not exist
' The new workbook needs to be added to the workbooks collection of the current application object (i.e. the Excel application)
Set wbLoanWorkbook = Workbooks.Add
With wbLoanWorkbook
.Title = "Henderson Park Mobile Homes"
.Subject = "Loans and Amortizations"
.SaveAs Filename:="Henderson_Park.xls" ' give full path, if necessary
End With The situation is a bit different if the "Henderson_Park" workbook already exists.
Dim wbThisWorkbook as Workbook
Dim wbLoanWorkbook as Workbook
Set wbThisWorkbook = ThisWorkbook
Set wbLoanWorkbook = Workbooks.Open("Henderson_Park.xls") ' full path, if needed. This assumes both workbooks are in the same path.
Again, this is very short. I've seen how you copy coding to another workbook, I'll have to research on this one, though.

Have a nice day,
Isabella

IBihy
02-15-2011, 07:25 PM
You can do it like that:
Public Sub test2()
Workbooks.Add
With Application.VBE.ActiveVBProject.VBComponents("Sheet1").CodeModule
.InsertLines 3, "Private Sub Worksheet_Activate()"
.InsertLines 4, " ActiveSheet.ScrollArea = " & Chr(34) & "A1:F20" & Chr(34)
.InsertLines 5, "End Sub"
End With
End Sub
This is not elegant, but shows that it may be done. I found it on a German Excel forum, www.herber.de/ (http://www.herber.de/) (all in German).

hiflier
02-15-2011, 08:01 PM
Thanks. That's exactly what I need. The code to open an existing workbook worked, but the one to open a new book and save it failed during the .saveas method. I am in Excel 2007, which requires the syntax, object.SaveAs (newfilename As String). But when I do that,
.SaveAs ("Henderson Park.xls"), I get an error message, "Microsoft Office Excel cannot access the file 'C:\D0422700'. It gives several reasons this may be true, including file not found, file in use, and the name is the same as an open workbook. But where does Excel get that name? Every time I step through the subroutine it gives the same error but the file name is different. Always numbers and letters, but different every time. Any ideas?

IBihy
02-15-2011, 08:21 PM
Hi,
wild guess here: The letters and numbers may be the object identifier. What's the file type for Excel2007, *xlsx? Exchange *.xls with the appropriate ending for Excel 2007.

Isabella

IBihy
02-15-2011, 08:25 PM
or, add the complete correct file path, like
C:\Documents And Settings\Administrator\MyMobileHomes\Henderso_Park.xlsx

hiflier
02-15-2011, 08:47 PM
That's it. I needed xlsx and the full path. Thanks. By the way, do you ever sleep? I show 4:45 am for your last post. And I lived 4 years in Zweibrucken.

IBihy
02-16-2011, 07:35 AM
I'm a nocturnal animal.

hiflier
02-16-2011, 11:13 PM
One more problem. Maybe it should be a new thread. I cannot get the VBA code to transfer to the new workbook. I want to write code to the Worksheet.Change event in the new book. I tried the following routine:

Workbooks(MyNewBook).Activate
Sheets(LoanName).Select
With ThisWorkbook.VBProject.VBComponents._(ActiveSheet.CodeName).CodeModule
N = .CountOfLines
.InsertLines N + 1, "Private Sub Worksheet_Change(ByVal Target As Range)"
.InsertLines N + 2, "etc, etc

It fails on the "With ThisWorkBook...
I get Error 9, Subscript out of Range. Can you help? Is there a better way? Thanks.

Bob Phillips
02-17-2011, 01:06 AM
If you held all the worksheets in one book, and use workbook events as I suggested, you wouldn't have this problem :)

IBihy
02-17-2011, 08:16 AM
Hello Hiflier,

as I said in my previous entry, this is one way to do it, so it doesn't work for you. Tough, but not the end of the world. Gimme a bit of time to figure it out for you.

Isabella

IBihy
02-17-2011, 08:18 AM
Addendum: BTW, you do want to go to the other workbook, so "ThisWorkbook" is not the correct reference. Have you tried the reference to the loan and amortization workbook?

IBihy
02-17-2011, 08:58 AM
Nooooo, the entry above is garbage. pls ignore.

IBihy
02-17-2011, 09:17 AM
Are you sure the Sheet(LoanName) exists already??
Gathering thought:

Copy code in WS_Change event in WB1 to WS_Change event in WB2

1. Let Excel know that VBProject is being manipulated.
Hope that exists in Excel 2007, I have Excel 2003:
- Add a reference to "Microsoft Visual Basic for Applications Extensibility ..." both WBs
- Tell Excel to trust VBProject manipulation.
2. Copy code from WB1.
- Upon creating a new loan, before the new WB actually exists, grab the code in WS_Change event of WB1
- Use VBProject objects
3. Update WS_Change event in WB2
- Navigate to the correct sheet
- Drop the code in WS_Change event of WB2
4. Save WB2.
- Invoke the workbook save method on WB2.

Is that about it? Some code will follow.

Isabella

hiflier
02-17-2011, 09:26 AM
Thanks, Isabella.

For xld, you miss the point. I already have all the worksheets in one workbook, and it works fine. But the best solution for what I am doing is to have separate workbooks. If that is not possible, I'll accept that. If it is possible, I want to learn how to do it, if for no other reason that it's intellectually stimulating. For me, that's what this forum is all about. You've helped me in the past, and I appreciate it. Thanks again.

Bob Phillips
02-17-2011, 09:31 AM
You say it is the best solution, i am arguing that multiple workbooks is usually a bad (often a very bad solution). When I want to create new workbooks of a standard form, I create templates, and add from the templates, and of course a template can always contain a base set of code.

hiflier
02-17-2011, 09:37 AM
Isabella, I don't understand point 1, and why in point 2 do you say "before the new wb actually exists"? Point 2 and following could be:
- create new WB, rename it
- create the loan WS
- copy WS_change from WB1 to WS_change in the WB2 loan WS
3. create the new loan
- current code does this
4. save the new WB
Thanks.
Dave

hiflier
02-17-2011, 09:40 AM
You say it is the best solution, i am arguing that multiple workbooks is usually a bad (often a very bad solution). When I want to create new workbooks of a standard form, I create templates, and add from the templates, and of course a template can always contain a base set of code.

OK, this is a new issue for me. Why is it better to have one workbook than multiple workbooks? Is it a technical issue?

IBihy
02-21-2011, 07:40 AM
Hello Dave,

according to my understanding and for Excel2003: If you want to add code (subs or functions) to an existing set of code by automation, you manipulate VBProject. Excel's macro security default settings usually do not allow this, unless you tell Excel to trust this. That's the reason.

With the expression "...before the new WB actually exists..." I was thinking in my mind set of doing this. It's not a killer condition, though.
Of course you can still grab the WS_Change event code in WB1, when the the new WB2 and its sheets already exist.

Again, anything I say is tried with Excel2003, it's the only one I have.:bawl

Regards,
Isabella

IBihy
02-24-2011, 10:43 AM
Hello Hiflier,

to my knowledge, there is no technical issue to having multiple WBs. It just makes coding a mite more complex.

If xld says, that in his mind, multiple WBs contradicts best solution, again, I do not agree.

"Best solution" is to be used in the client's sense, in the case here, it's you.
You decided to have multiple WBs, so be it.
Of course, sometimes clients (outside, the guys in the real world who pay greenbacks for work done) are not always aware of how a "best solution" may look like. A bit of coaching, discussing the pros and cons, and a solid estimate of the efforts in the array of possible solutions, will usually help the decision.

Regards,
Isabella