Recalculate Random numbers weekly ONLY

[raggd]

I am currently building a database of rooms that each have a four digit passcode that needs to change on a weekly basis. I think i can calculate random numbers for each 'room' but i am not sure how to make it calculate only once every week.

I have been trying to modify the below link code but as this only generates a random number for each new cell I am unsure how to give a new random number on every weekly calculation... a code to clear all cells in column then recalculate all possibly?
http://www.mrexcel.com/forum/excel-questions/396985-create-new-products-numbers-excel.html

 

[Chihiro]

You should be able to accomplish this by calling on Today() and Weekday().
So if you want it to run on every Monday, it will be something like.

IF Weekday(Today()) = vbMonday Then

Insert your code after.

However, to fully automate, you can just code VBA as Workbook.Open Event and use batch file and Windows Task Scheduler to schedule update (of course, you will have to store generated number in another field so that it won't over write when you open it manually).

 

[raggd]

Thank you Chihiro.

I ended up using a UDF to get a random number:

Public Function Rand_once(ByVal r As Range)
    Rand_once = Rnd
End Function

then inserting into:
=ROUND(rand_once(A2)*10000,0)
with formatting "0000"
This doesn't give unique numbers but close enough for what I need, e.g. what's the password.
Then whenever i need to recalculate I have a copy and paste the same VBA code to recalculate. I will now try to combine with your weekday function to get a weekly calculation.

Question: as

IF Weekday(Today()) = vbMonday Then

is true for 24 hours, then won't it recalculate many times over on monday or will it only do it once?

Many thanks for your help so far,

 

[Chihiro]

It will recalculate, whenever you call upon the code on certain day of the week.
I'd recommend to stop the code when value is already present in output cell.