Excel VBA Seed based on the date

Question

I'm working on a programm where I use a seed. This seed should be based on the date, so every single day i get a new value. Using the seed several times a day should not change the value getting back.

This is what my seed looks like:

Randomize

todayValue = Int(50 * Rnd)

Now, how do I base a seed on the date? Is there maybe another way to have a seed based on the date?

Thank you very much!

can you write your code so far? so we can help you better... — Ferdinando, Nov 09 '18 at 18:09

score 0 · Answer 1 · answered Nov 09 '18 at 14:31

See this post on getting seeded random values in VBA.

All you need to do is call the Rnd function with a negative value, prior to running your code. To do that, you can use the Date function. In VBA, Date values are just Longs, representing the number of days since some set date in the past.

Here's an example of how you could do what you want.

Sub rndSeedTest()
    MsgBox Date & " --> " & CLng(Date)
    Rnd -Date
    MsgBox Rnd & _
        vbNewLine & Rnd & _
        vbNewLine & Rnd & _
        vbNewLine & Rnd & _
        vbNewLine & Rnd
End Sub

score 0 · Answer 2 · answered Nov 09 '18 at 14:39

If you want to get same number every time the function runs, then that is not a random number, you have to create something that looks random, for example

Sub test()
    Dim todayValue As Long
    todayValue = Date
    todayValue = todayValue + 1538 ' To make it a little random
    MsgBox todayValue
End Sub

score 0 · Accepted Answer · answered Nov 27 '18 at 12:02

I found a way to do it, i just save the date and the Value of the day in a certain cell on my sheet. this is how my code looks like:

If MainSheet.Cells(1, 1) = Date Then   
    todayValue = MainSheet.Cells(1, 2)
Else    
    Randomize
    todayValue =  Int(50 * Rnd)
    MainSheet.Cells(1,1) = Date    'saves the date in Sheet
    MainSheet.Cells(1,2) = todayValue    'saves todayValue in Sheet
End If

Excel VBA Seed based on the date

3 Answers3