Solved: Creating a dynamic user defined function

[helix123]

Sorry if this problem is a bit a long but here goes:

At the moment I have a budget amount (say $30,000) and then have a user defined function which subtracts off each cost per minute (which varies minute to minute) until the budget runs out i.e. it counts the number of iterations/loops that the initial budget lasted for given that it started from a particular minute.

Thus the udf MinutePaidInFullc gives the number of minutes a particular budget amount will last before reaching zero starting from any given minute.

http://www.vbaexpress.com/forum/showthread.php?t=43802

However, as an extension to this problem I have a clock timer macro which shows what minute it is up to.

What I would like to have is the MinutesPaidInFullc user defined function to dynamically update as the minutes progress i.e. the minute on the clock becomes the start minute in the MinutePaidInFullc function.

See the attached workbook for more details. NOTE: make sure the format of cell G6 is set to general.

Any help would be much appreciated.

[Bob Phillips]

Remove the call to the UDF in G15 and change SetTimer to this

Sub SetTimer()
Dim clock As Date
If datStartTime = 0 Then datStartTime = Now
clock = Now - datStartTime
If Second(clock) = 0 Then
Range("G15").Value = MinutePaidInFullc(Range("G2"), Columns(2).Cells(Minute(clock) + 2, 1))
End If
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "RecalcTimer"
End Sub

[helix123]

Cheers! That works good.

If it is not too mcuh would you be able to explain how the code works?

I'm trying to learn vba.

[helix123]

Just one Problem I'm having with the code:

How do I get it to automatically refresh if the budget value is changed while the timer is going?

[Aussiebear]

Then use a Worksheet_Change event to call the sub SetTimer()

[helix123]

Ok so I put this code into ThisWorkbook:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$2" Then
Call SetTimer
End If
End Sub


It is still not updating when I change the budget value (cell G2).

Where am I going wrong?

[Bob Phillips]

The event code should be in the worksheet code, not ThisWorkbook. As the timer code only updates on the change of a minute at present, you need to force it.

Change the event code to

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$2" Then
Call SetTimer(True)
End If
End Sub

and SetTimer to

Sub SetTimer(Optional update As Boolean = False)
Dim clock As Date
If datStartTime = 0 Then datStartTime = Now
clock = Now - datStartTime
If Second(clock) = 0 Or update Then
Range("G15").Value = MinutePaidInFullc(Range("G2"), Columns(2).Cells(Minute(clock) + 2, 1))
End If
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "RecalcTimer"
End Sub

[helix123]

Awesome!

Thanks for your help.