Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Function
So if there are cells which are part of the calculation, it will not
update? J |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Function
So in other words, I'm better off just creating the IF statement
formula and just locking the cell down.??? J. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilling Function not updating | Excel Worksheet Functions | |||
Value Function not updating | Excel Worksheet Functions | |||
Custom Function not updating | Excel Worksheet Functions | |||
Custom Function not updating | Excel Worksheet Functions | |||
How can I use the NOW function and keep it from auto updating? | Excel Worksheet Functions |