Print

Print


I used Int as integer only because the originator of this thread had it in
his function and I didn't think about it (or test it). Actually, as it
stands, no parameter need be passed, as in your YOYO2 function.
Re Application.Volatile...From Help:
Marks a user-defined function as volatile. A volatile function must be
recalculated whenever calculation occurs in any cells on the worksheet. A
nonvolatile function is recalculated only when the input variables change.
This method has no effect if it's not inside a user-defined function used to
calculate a worksheet cell.
 
If you don't want it to calculate, you can use something like (untested)
Function xyz()
Application.volatile
if application.caller.parent.parent.name<>thisworkbook.name then exit
function
...
...
end Function

-----Original Message-----
From: Richard Gates [mailto:[log in to unmask]]
Sent: Wednesday, January 26, 2000 4:46 AM
To: [log in to unmask]
Subject: 4 Umlas re Finding Cells' Address within Function


Robert
 
In your YOYO function I couldn't work why u passed (int as integer).  I use
Excel5.
 
'int as integer' caused a compile error so I changed it to 'kkk as integer'
 
For what its's worth (FWIW) all 3 of the following functions work as
planned.
 
Function YOYO(kkk As Integer)
       YOYO = Application.Caller.Address
End Function

Function YOYO2()
       YOYO2 = Application.Caller.Address
End Function

Function YOYO3(Optional kkk)
       YOYO3 = Application.Caller.Address
End Function
 
Question:
 
In general I always include 'Application.Volatile' in all my functions
without really knowing why.  I have written some very complicated
depreciation functions for financial models containing a non homogeneous
time frame (eg months, quarters and years) and found that without the
'Application.Volatile', sometimes the depreciation was not recalculated as
expected.
 
As a regular contributor to the group, would you care to elaborate on the
use of 'Application Volatile'
 
TIA
 
Richard Gates
 
PS.  I have noticed that if I am working in another workbook which is
totally independent from calls to my depreciation model; if
Calculation=xlAutomatic, every time I change a cell in the independent
workbook, the depreciation function recalculates in the dormant (yet open)
financial model.  It can be a real time waster!

 
 
 

----- Original Message ----- 
From: Umlas, Robert  <mailto:[log in to unmask]> 
To: [log in to unmask]
<mailto:[log in to unmask]>  
Sent: Wednesday, January 26, 2000 1:18 AM
Subject: RE: Finding Cells' Address within Function

This will return the address of the calling cell, no matter where it is
called from:
FWIW
Function YOYO(int as integer)
       YOYO = Application.Caller.Address
End Function

-----Original Message-----
From: Richard Gates [ mailto:[log in to unmask]
<mailto:[log in to unmask]> ]
Sent: Tuesday, January 25, 2000 6:31 AM
To: [log in to unmask] <mailto:[log in to unmask]>

Subject: Re: Finding Cells' Address within Function


John
 
What u are asking for seems reasonable but unfortunately a function has only
one purpose and that is to return a value for the function name.
 
Within a function you can call other SUBS and FUNCTIONS but nowhere down the
chain can you change the structure, properties or values of the Application.
You can only return a value.
 
Most of us have tried without success.  If any other members have been able
to do so, would you submit an example.
 
Richard Gates
 
 
----- Original Message ----- 
From: John Putman < [log in to unmask] <mailto:[log in to unmask]> >
To: Excel-Discuss < [log in to unmask]
<mailto:[log in to unmask]> >
Sent: Tuesday, January 25, 2000 11:10 AM
Subject: Finding Cells' Address within Function

> hi group...
> 
> I am trying to do something that I haven't seen before in a function...
> 
> I have a spreadsheet function ("YOYO(int as Integer)") that I want to
> calculate a value for (like a regular function) AND I would also like to
> change the ID property of the same cell depending on the contents of the
> function arguments as well.
> 
> If I were entering the formulas one at a time, it would be no problem at
> all... I could simply include
> 
> ActiveCell.ID = int
> YoYo=int
> 
> and it returns the correct thing for each property.  However, the problem
is
> that when the sheet recalcs or if I were to paste the function to a range
> larger than one cell, the ID property will only set on the active cell,
> which does not change.  So what I'm looking for is a way, within the
> function, to tell which cell I'm actually calculating instead of the
> "activecell".  It seems like since Excel knows which cell it is currently
> calculating, we should be able to pull that info somehow.
> 
> Thanks guys!
> 
> 


*****************************************************************************
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else
is unauthorized. 

If you are not the intended recipient, any disclosure, copying, distribution
or any action taken or omitted to be taken in reliance on it, is prohibited
and may be unlawful. When addressed to our clients any opinions or advice
contained in this email are subject to the terms and conditions expressed in
the governing KPMG client engagement letter.         
*****************************************************************************