Solved: Spreadsheet not updating when I open it daily?

[jay20aiii]

Hi, I'm having a go at making a spreadsheet in excel to automatically work out how much has been paid in bills each month based on the current date.

Firstly I run this VBA Macro on a bunch of cells to calculate what date should be displayed for each of my payments:
[vba]
Function NextPayment(day As Integer) As Date
If day > DatePart("d", Now) Then
NextPayment = DateValue(day & "/" & Month(Now) & "/" & Year(Now))
Else
If DatePart("m", Now) = 12 Then
NextPayment = DateValue(day & "/01/" & Year(Now) + 1)
Else
NextPayment = DateValue(day & "/" & Month(Now) + 1 & "/" & Year(Now))
End If
End If
End Function
[/vba]
I just want it to either put the payment date into the relevant cell, or if the payment would have been paid (that day of the month has been exceeded) then add a month to the date to prepare it for Next months calculation.

Then I use those dates in this VBA Macro to try and calculate how much has yet to be paid based on the current date:
[vba]
Function SumBills(Dates As Range, Values As Range) As Double
For x = 1 To Dates.Rows.Count
If DatePart("d", Now) > 17 Then
SumBills = SumBills + Values(x, Values.Columns.Count).Value
Else
If DatePart("m", Dates(x, Dates.Columns.Count).Value) = DatePart("m", Now) Then
SumBills = SumBills + Values(x, Values.Columns.Count).Value
End If
End If
Next
End Function
[/vba]
The Dates argument is a range of cells that contain the dates that the payments will be made.
The Values argument is a range of adjacent cells that contain the value of that particular bill.

If the current date is beyond the 17th of the Month then it just adds up all the values because they will all be due at the start of the next month.
Otherwise I am just checking the Month value (since I already formatted that in the previous function).
If the Month Value matches the Month value of todays date then I add up the values of those cells to get the total amount due to be paid.

Trouble is, it worked when I made the spreadsheet. But if I open the spreadsheet from day to day, it never recalulates the SumBills function. Or maybe it does, but there is something wrong with the logic? "Caluclate Now" and "Calculate Sheet" makes no difference.

How do you get a spreadsheet to automatically calculate itself when you open it, and factor in the current date? Have I missed something? Or can you just not do it? Or maybe theres another way that works better?

Any help appreciated

[jay20aiii]

My first function wasn't performing properly. I fixed it now.

[Kenneth Hobs]

When you say that your ran it, I am not sure what that means.

If you just put it as a UDF, it will not update unless a calculate event occurs. You can force that in the Open event for the workbook, Sheet1.Calculate for example.

You might also look into using Application.Volatile.

[jay20aiii]

By running the Functions I mean that the functions are called from a cell in my worksheet.

E.g. One of my Bill Date Cells contains "=NextPayment(3)" which gives it a value of "03/05/2012" (using the function from my first post) based on todays date of "05/04/2012".

I just assumed that when I open the worksheet each day, these cells will be updated automatically?

Thanks for your suggestions. I will have a read up on them.