Force a spreadsheet to re-calculate each day using VBA

Question

This question is a re-statement of mu previous question "How to force a spreadsheet to recalculate even when no changes are made REPOST".

I'm posting this new question because that question has had little response. This is not to disparage the suggestion that I received for that question. Rather, I had imposed a condition that I didn't want to use VBA. I now believe that there is no way to do it without using VBA.

The spreadsheet in question calculates a "Refill Date" for each row of the spreadsheet, based on the starting inventory balance and date

Here is a link to the spreadsheet
https://www.dropbox.com/scl/fi/0jcr37dum1uut2b4otrzh/Prescription-Inventory-Simpler-ELIMINATE-SOME-COLUMNS.xlsx?dl=0&rlkey=0bokgekbgqw985d707nv3b3bd

My VBA skills are limited. Can someone help?

Thanks for your help.

SweetTasha

Answer 1

I'm posting this new question because that question has had little response.

Well, the question in the other thread itself forces the answer in a direction that is extremely difficult to answer. It is clear that you will only get a few answers per se on that.

The main reason, however, is the time it takes for you to respond to the first reply. It took about 20 minutes for the first reply, but then you responded after 10 days. This is far too long, nobody sees this answer. Look into my profile, the longest reaction time I see is 3 days, after which the thread disappears from my eyes.

And in this thread there is some important information missing which you noted in the other thread... it shouldn't surprise you if you don't get many answers to your questions. .-)

Anyway, let me rephrase your question:

How can we recalculate a workbook automatically after a given time?

The answer to this is simple, use Application.OnTime

If we record a macro and just recalculate the workbook manually we get a macro with only one line:

Sub Macro1()
Calculate
End Sub

Let us rename that macro to "AutoCalculate". If you look at the examples of OnTime it is really simple to call that macro automatically again in e.g. 10 seconds:

Application.OnTime Now + TimeSerial(0, 0, 10), "AutoCalculate"

That works only once, so if we want to execute the same code again (and again and again) after 10 seconds we can put that line inside the macro itself. And let's make a message for the user on the status bar.

Sub AutoCalculate()
Dim When As Date
Calculate
When = Now + TimeSerial(0, 0, 10)
Application.OnTime When, "AutoCalculate"
Application.StatusBar = "Next calculation at " & When
End Sub

Alright, the next step is to start that endless loop if the workbook is opened. There is a special sub called Auto_Open which is execute automatically when the file is opened, so if we rename our macro the work is almost done:

Sub Auto_Open()
Dim When As Date
Calculate
When = Now + TimeSerial(0, 0, 10)
Application.OnTime When, "Auto_Open"
Application.StatusBar = "Next calculation at " & When
End Sub

In the other thread you said "recalculate daily", so the last step is to calculate the recalculation point to midnight when the day changes.

Sub Auto_Open()
Dim When As Date
Calculate
When = Date + 1
Application.OnTime When, "Auto_Open"
Application.StatusBar = "Next calculation at " & When
End Sub

That's it.

I think far more than 50% of all participants in this forum can answer your question, but you have to put it in a way that you can understand from more than 50%. :-) I know it's not that easy, it's just as difficult to type the question into a search engine.

And there's a surprise, I also found the video below, but didn't looked it.

Andreas.

https://www.youtube.com/watch?v=SDCGAIfACZI

Answer 2

Andreas,

My reason for re-posting was to state that I have decided that using VBA is probably the only way to force the re-calculation. Which you have done with your excellent, detailed answer.

Thanks.
SweetTasha

Answer 3

SweetTasha,

Did you watch the video? Does this also work for you without VBA?

Andreas.

Answer 4

Andreas,

I did just now watch it 7:16 PM Eastern Standard Time EST GMT-5:00. I think it explains the solution, but I haven't a chance to try it yet. I will do so later this evening.

Thanks for your help,
SweetTasha

Answer 5

Andreas,

I've been busy working on my Tax return. I need to give that a higher priority.

Thanks.
SweetTasha