# passing data between UDFs and subroutines

#### mgruber

##### New Member
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.

Michael

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

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

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

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

Hi,

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

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

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

Sheet 2 - Property Page
loancheck_v1.14.xls
ABCDEFGHIJKLMNOPQRS
1ClientCodeNameStreetNumberStreetSuburbStatePostcodePossessionDateDepositContractPriceContractRateContractTermPeriod(w/f/m)P1P2P3P4etcP360
2039572MrSmith14GeorgeLungunaNSW234023/11/01\$-\$49,600.006.75%25m
3039438MrsJones4BlenHillviewNSW215325/01/02\$8,000.00\$52,000.009.50%25f
4040055MrBlack47WuinnCresvilleNSW256704/07/02\$8,000.00\$93,000.0010.15%25w
5039700MrsWhite14PilmarBlaxonNSW233406/04/02\$7,500.00\$74,000.009.75%25w
Properties

Part 2 of 4
Michael Gruber

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

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
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

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

Replies
0
Views
131
Replies
10
Views
1K
Replies
2
Views
181
Replies
1
Views
322
Replies
3
Views
217

1,207,199
Messages
6,077,021
Members
446,251
Latest member
dpf220

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back