passing data between UDFs and subroutines

[mgruber]

Hi,

I want to create a simple UFD that would allow me to copy the contents of one cell to another. I'm learning that UDF's wouldn't allow this, but would it be possible to get the UDF to ask for the two cell locations ie:

FUNCTION COPYCELL(FROM, TO)
CALL SUB COPYCELL()
END FUNCTION

then pass this as variables to be read by a subroutine, ie

SUB COPYCELL()
TO = FROM
END SUB

The idea is to enable me to STORE data in specific cells based on certain conditions.

Look forward to your assistance...
Michael

 

[Legacy 98055]

ORIGINAL POST

Hi,

Ideally I would like a function that looked something like;

Function copycell(source, dest)
dest = source
End Function

so I could enter in my spreadsheet;

=copycell(A1,B2)

to allow me to copy the contents of A1 to B2

Why?, because I have a spreadsheet that creates different totals, depending on what period (date range) is selected.

If A1 = (Period) 1 is selected the total in Cell A2 = X, if A1 = (period) 2 is selected A2=Y

So the logic is;

When A1 = 1 then copy A2 to A3
When A1 = 2 then copy A2 to B3
When A1 = 3 then copy A2 to C3

But I need the cells in row 3 to RETAIN their data unless the number in A1 is changed back to their relevant value because the values in row 3 are read and used as the starting values for the next period.

A simple IF formula, means only 1 cell in row 3 will hold its value ONLY when its condition is true.

I know now that UDF's other return a value to the same cell.

So maybe I need something that says, if A1 value changes paste A2 value?

Any ideas or tips would be muchly appreciated.

Regards
Michael

Hi Michael.
A function can only do one thing. Return a value. Calling another procedure will not help. I think we all have run into this step. When you want "something" to "happen" when "something" "happens", you are most likely going to be using an Event procedure to run your code. How do the values get into A1? Directly entered? DDE? Via calculation(formula in A1)?

Actually, with a second look, it does not appear that you need VBA at all for this. Where are your values initiating?
Tom
This message was edited by TsTom on 2002-08-26 01:02

 

[mgruber]

Tom,

Its a loan spreadsheet which works perfectly, except its 9000 lines long. Each line is a daily balance for a 30yr loan. It pulls in the start balance and interest rate from a client table (say sheet1), applies payments made, fees charged and interest rate changes from sheet2, and calculates this all in sheet3.

I wanted to try and reduce sheet3 to a six monthly period (186 lines) by "storing" the balance at the end of the period in a 4th table.

This 4th table would then be read as the start balance for the next period.

I tried thinking of a SUMIF, but the problem I get is if the period if NOT the one being tested the cell = zero.

A smaller spreadsheet would be ideal, because then I could stored it on my PDA. I could even reduce the period to 3mths (31days x 3 = 93 lines).

Because it generates the totals each time for each property, its a matter of either displaying the whole term and reading the balance I need, or storing checkpoint data somewhere (ie start balances).

For example here's the formula to calculate balance;

=SUM((L7+M8+SUM(D8:H8))-C8)

It simply reads the previous balance (1 cell up), deducts any payments made and adds any fees charged. The interest for that day is then calculated on the previous balance and added to this total.

So for the start of each period I need to track the current balance, and # of days in arrears (and two other figures), but I figure I can concatenate the 4 bits of data into 1 string to store it all in 1 cell. The trick is storing it in a cell.

Hmm, how about a function that;

If cell = x then change this cell, if not x leave cell as previous value.

Something like a CHANGEIF function, but where a FALSE reading just means do nothing, rather than force an alternative result.

Regards
Michael
This message was edited by mgruber on 2002-08-26 01:16
This message was edited by mgruber on 2002-08-26 01:19

 

[Legacy 98055]

Hi Michael
From what I can gather, you should be able to accomplish all of this using native functions. Please download Colo's HTML utility and post an example of your spreadsheet(s). I am probably less able using Excel's functions than you are. However, some other's on this board can make a speadsheet sing. It will be worth your time. Here's the link:

http://www.interq.or.jp/sun/puremis/colo/HtmlMaker.htm

 

[mgruber]

Hi,

Thanks, I worked out the utility, here are the 4 main sheets;

Sheet1 - Input page
Sheet2 - Property Page (also where the start/end values are to be stored)
Sheet3 - Account Page
Sheet4 - Statement page


SHEET1

Edited: (JPG) HTML Table was too long
This message was edited by Juan Pablo G. on 2002-08-26 21:29

 

[mgruber]

Hi,

Thanks, I worked out the utility, here are the 4 main sheets;


SHEET 1 - Input Page

loancheck_v1.14.xls
	B	C	D	E
2	Investor'sDetails:
3	Name:	CompanyName
4	StreetNumber:	POBoxABC
5	StreetName:
6	Suburb:	Suburbia
7	State:	NSW
8	Postcode:	2001
9	Phone:	(xx)xxxx-xxxx
10	Fax:	(xx)xxxx-xxxx
11	Mobile:	xxxxxxxx-xxxx
12	Email:	mymail@email.com.au
13
14	SystemVariables
15	DefaultPeriod(Days)	10
16	ForeclosePeriod(Days)	20
17	Eviction	5
18	StatementPeriod(Max:6mths)	6
19
20	UserInput
21	EnterClientCode:	039572
22	EnterStatementNumber:	1
23	StatementPeriodStartDate	23/11/01
24	StatementPeriodEndDate	22/05/02
25
26	ClientStatus
27	Today'sStatus	26/08/2002	5daysinForeclosureby$675.16
28	Enterdatetocheckstatus:	24/02/02	3daysinArrearsby$346.80
Input

Part 1 of 4
Michael Gruber
This message was edited by mgruber on 2002-08-26 20:36

 

[mgruber]

Sheet 2 - Property Page

loancheck_v1.14.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	ClientCode	Name	StreetNumber	Street	Suburb	State	Postcode	PossessionDate	Deposit	ContractPrice	ContractRate	ContractTerm	Period(w/f/m)	P1	P2	P3	P4	etc	P360
2	039572	MrSmith	14	George	Lunguna	NSW	2340	23/11/01	$-	$49,600.00	6.75%	25	m
3	039438	MrsJones	4	Blen	Hillview	NSW	2153	25/01/02	$8,000.00	$52,000.00	9.50%	25	f
4	040055	MrBlack	47	Wuinn	Cresville	NSW	2567	04/07/02	$8,000.00	$93,000.00	10.15%	25	w
5	039700	MrsWhite	14	Pilmar	Blaxon	NSW	2334	06/04/02	$7,500.00	$74,000.00	9.75%	25	w
Properties

Part 2 of 4
Michael Gruber

 

[mgruber]

Sheet 4 - Statement Page (Last Sheet)

This page calculates everything, the idea is to store the balance for the period define on page 1 and to store each period 1-360 (12mthx30yrs), on the same row as the client on page 2. This is where I had the idea to do a copy/paste of the balance.

The idea is that the top of page 4 reads this balance data stored on page 2 to generate the figures for that period.
This message was edited by Juan Pablo G. on 2002-08-26 21:34

 

[mgruber]

Tom,

I guess the last page was too big. But if you imagine a standard amortization table, then that's pretty much it.

Anyway, the input page can be seen and the destination cells for the period's balance's can be seen and that's the important part.

Ok, thinking about Damon Ostrander's tip (below), the target address would be C22 on Sheet1. But each case would be a vlookup (or some other reference function), where it would look for the client code (C22 on Sheet1) in Sheet2 (the account page) to find the correct row, then paste the balance from sheet4 (let's same L5) into the correct Period column (P1-P360).

If the above worked, then it would be a matter of creating the right matching code for each case.

With a minimum period of 1mth, and 12mths a year for 30yrs, then can be only a total of 360 cases (periods)

Damon Ostrander's Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = [a1].Address Then
Select Case Target.Value
Case 1: [a3] = [a2]
Case 2: [b3] = [a2]
Case 3: [c3] = [a2]
End Select
End If
Application.EnableEvents = True
End Sub


Michael Gruber
This message was edited by mgruber on 2002-08-26 23:04

 

[Legacy 98055]

Hi Mgruber.
I think I'm in over my head.
Seeing that you have gone at such lengths to try and explain your problem, and there have been no other replies, I'll try and help you out if I can. Mail the file(s) if you wish and maybe then I'll be able to grasp what it is you are trying to accomplish. Do you have any sort of instant messenger? I have not used it but I'm thinking about firing it up. I will likely be unable to give it any quality time until Thursday.

TsTom@Hotmail.com

Tom