Recursive/Running Function

**goldm** · 05-07-2009, 07:14 AM

Hi,

short question: Is there a way to pass the current value of a cell into a user function, run some calculations, and then change that same cell (without using activecell)?

Longer explanation:
I've got a macro which I'm running to recalculate every few minutes or so as data changes. The macro is calculating the moving average, so it takes the previous moving average, and then takes the new value, and uses those two items plus the time interval to calculate a new moving avg.

Anyway, instead of recalculating the spreadsheet through a macro, I thought a user function would be better.

I started with this:
Function dmvavg(interval As Single, num As Single) As Double
Dim spec As Single
spec = dmvavg
spec = (num * (1 / interval)) + (spec * (1 - 1 / interval))
dmvavg = (num * (1 / interval)) + (spec * (1 - 1 / interval))
End Function

Thanks for any help.

**broro183** · 05-07-2009, 08:30 AM

hi,

Originally Posted by goldm

Hi,
short question: Is there a way to pass the current value of a cell into a user function, run some calculations, and then change that same cell (without using activecell)?
...

If the function is a UDF for use within the spreadsheet, asfaik it is unlikely to allow changes to a cell (other than the one that is calling the function). However, you could wrap your existing function within a Sub which allows for changes to be made to other cells, see the below link for an example which you may be able to adapt:
http://www.mcgimpsey.com/excel/accumulator.html
found via:
http://www.google.co.uk/search?hl=en...+cell%22&meta=

On the odd chance that you can make your Function change other cell values (untested)...
The below code allows for the use of a range (ie the cell in question) to be incorporated into your function (untested). I'm not sure but may be better if this was done "byval" (see Google for discussions abput this)...

Please Login or Register  to view this content.

hth
Rob

**mudraker** · 05-07-2009, 08:34 AM

goldm

Welcome to Exceltip forum

Please take a couple of minutes and read ALL theForum Rules then wrap your VBA code (Rule 3)

Recursive/Running Function

LinkBack

Thread Tools

Rate This Thread

Display

Recursive/Running Function

Re: Recursive/Running Function

Re: Recursive/Running Function

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions