Updating Function
 

I have two custom functions in a workbook. The problem is, they don't
seem to update themselves until I go into the cell and click in the
formula somewhere and then hit Enter.

How do I make them update themselves automatically?

Thanks
J.

Updating Function
 

All cells that the function depends on should be in the argument list of the
function definition and call.
Sometimes, including "Application.Volatile" in the function header is
suggested as a solution.
However, there is no certainty that this will cause the cells to calculate
in the correct sequence and will keep to do so in future versions of Excel,
because this behavior is not part of the specifications of Excel. It also
causes many unnecessary recalculations of the function.
The only correct way is to include all precedent cells in the argument list

--
Kind regards,

Niek Otten

"Egon" wrote in message
oups.com...
I have two custom functions in a workbook. The problem is, they don't
seem to update themselves until I go into the cell and click in the
formula somewhere and then hit Enter.

How do I make them update themselves automatically?

Thanks
J.

Updating Function
 

So if there are cells which are part of the calculation, it will not
update?

J

Updating Function
 

Don't know what you mean by that. But all precedents cells should be in the
argument list. Of course they may be part of a range which is supplied as
just one argument.
If you're still in doubt, give an example of what you're trying to do.

--
Kind regards,

Niek Otten

"Egon" wrote in message
ups.com...
So if there are cells which are part of the calculation, it will not
update?

J

Updating Function
 

What I need it to do is to update itself when I make a change to a
value on the "Project Summary" Sheet. But it is not doing this until I
go in and manually "edit" the formula.

I can't see why, it will do it if I use a Formula to drive things, but
I don't want to use a formula because its too long and complex and the
users who are using it tend to screw it up. This works, but its not
updating itself.

TIA.
J.

Here is the Function in the Cell

=dailycost(A9,'Project Summary'!$G$9)+H9+J9

Here is the Function as defined in the VBE

Function DailyCost(TrackDate As Range, TrackingInfo As Range)


Celladdress = Application.ThisCell.Address
ProperRow = Application.Caller.Row - TrackDate.Row
'Works To here

'Pull the Rate for Fuel from the Cell H7 on the same sheet as the
Function
'Set the location of the information where the values will be pulled
from

'TrackingInfo = Worksheet.Range("project summary").Range("g9")

'Calculate the fuel usage for the day based on the amount
used*rate*conversion
'fuelrate = WorksheetFunction.SumProduct(FuelRateMonthly, FuelUsage,
264.15)

'Set StartDate equal to the cell G9 on the 'Project Summary' Sheet
using a Relative Reference
startdate = TrackingInfo.Offset(ProperRow, 0).Value

'Set EndDate equal to the cell H9 on the 'Project Summary' Sheet using
a Relative Reference
enddate = TrackingInfo.Offset(ProperRow, 1).Value

'Set TrackingRate equal to the cell I9 on the 'Project Summary' Sheet
using a Relative Reference
trackingRate = TrackingInfo.Offset(ProperRow, 2).Value

'Set DailyRate to the FuelRate + TrackingRate
Dailyrate = WorksheetFunction.Sum(fuelrate, trackingRate)

'Set Mobe equal to the cell J9 on the 'Project Summary' Sheet using a
Relative Reference
Mobe = TrackingInfo.Offset(ProperRow, 3).Value

'Set MobeDate equal to the cell K9 on the 'Project Summary' Sheet using
a Relative Reference
ModeDate = TrackingInfo.Offset(ProperRow, 4).Value

'Set DemobeDate equal to the cell M9 on the 'Project Summary' Sheet
using a Relative Reference
DeMobeDate = TrackingInfo.Offset(ProperRow, 6).Value

'Set DeMobe equal to the cell L9 on the 'Project Summary' Sheet using a
Relative Reference
DeMobe = TrackingInfo.Offset(ProperRow, 5).Value

'This Section Works
EndDateNull = IIf(enddate = "", Dailyrate, 0)
TrackEnd = IIf(TrackDate <= enddate, Dailyrate, EndDateNull)
TrackToday = IIf(TrackDate = startdate, TrackEnd, 0)
StartTracking = IIf(Now = TrackDate, TrackToday, 0)
DailyCost = StartTracking

Updating Function
 

You have lots of cells addressed outside of the argument list. This just
doesn't work.
It will work if you try to debug the function, but in a workbook
recalculation the function will not even be called because Excel thinks none
of the inputs have changed.

I repeat my advice, based on many, many years of experience with many
versions of Excel and many other suggestions for workarounds which all
didn't work in all circumstances:

*** the only reliable way is to include all inputs in the argument list***


--
Kind regards,

Niek Otten

"Egon" wrote in message
oups.com...
What I need it to do is to update itself when I make a change to a
value on the "Project Summary" Sheet. But it is not doing this until I
go in and manually "edit" the formula.

I can't see why, it will do it if I use a Formula to drive things, but
I don't want to use a formula because its too long and complex and the
users who are using it tend to screw it up. This works, but its not
updating itself.

TIA.
J.

Here is the Function in the Cell

=dailycost(A9,'Project Summary'!$G$9)+H9+J9

Here is the Function as defined in the VBE

Function DailyCost(TrackDate As Range, TrackingInfo As Range)


Celladdress = Application.ThisCell.Address
ProperRow = Application.Caller.Row - TrackDate.Row
'Works To here

'Pull the Rate for Fuel from the Cell H7 on the same sheet as the
Function
'Set the location of the information where the values will be pulled
from

'TrackingInfo = Worksheet.Range("project summary").Range("g9")

'Calculate the fuel usage for the day based on the amount
used*rate*conversion
'fuelrate = WorksheetFunction.SumProduct(FuelRateMonthly, FuelUsage,
264.15)

'Set StartDate equal to the cell G9 on the 'Project Summary' Sheet
using a Relative Reference
startdate = TrackingInfo.Offset(ProperRow, 0).Value

'Set EndDate equal to the cell H9 on the 'Project Summary' Sheet using
a Relative Reference
enddate = TrackingInfo.Offset(ProperRow, 1).Value

'Set TrackingRate equal to the cell I9 on the 'Project Summary' Sheet
using a Relative Reference
trackingRate = TrackingInfo.Offset(ProperRow, 2).Value

'Set DailyRate to the FuelRate + TrackingRate
Dailyrate = WorksheetFunction.Sum(fuelrate, trackingRate)

'Set Mobe equal to the cell J9 on the 'Project Summary' Sheet using a
Relative Reference
Mobe = TrackingInfo.Offset(ProperRow, 3).Value

'Set MobeDate equal to the cell K9 on the 'Project Summary' Sheet using
a Relative Reference
ModeDate = TrackingInfo.Offset(ProperRow, 4).Value

'Set DemobeDate equal to the cell M9 on the 'Project Summary' Sheet
using a Relative Reference
DeMobeDate = TrackingInfo.Offset(ProperRow, 6).Value

'Set DeMobe equal to the cell L9 on the 'Project Summary' Sheet using a
Relative Reference
DeMobe = TrackingInfo.Offset(ProperRow, 5).Value

'This Section Works
EndDateNull = IIf(enddate = "", Dailyrate, 0)
TrackEnd = IIf(TrackDate <= enddate, Dailyrate, EndDateNull)
TrackToday = IIf(TrackDate = startdate, TrackEnd, 0)
StartTracking = IIf(Now = TrackDate, TrackToday, 0)
DailyCost = StartTracking

Updating Function
 

So in other words, I'm better off just creating the IF statement
formula and just locking the cell down.???

J.

Updating Function
 

Not necessarily. Just include the arguments in the argument list.

--
Kind regards,

Niek Otten

"Egon" wrote in message
oups.com...
So in other words, I'm better off just creating the IF statement
formula and just locking the cell down.???

J.