🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Rnd in Excel — Random Numbers, Randomize, and the Repeating-Sequence Trap

|

VBA Rnd in Excel — Random Numbers, Randomize, and the Repeating-Sequence Trap

TL;DR — Rnd returns a random Single in the range 0 up to (but not including) 1. On its own it is not really random: it replays the same sequence every time the macro runs. Call Randomize once, at the top, to seed the generator from the system clock and get a different sequence each run. For an integer between lo and hi, use Int((hi - lo + 1) * Rnd + lo).

Sub RandomInteger()
    Dim n As Long
    Randomize                          ' seed once from the clock - do this before Rnd
    n = Int((6 - 1 + 1) * Rnd + 1)     ' a whole number from 1 to 6, like a dice roll
    Debug.Print n
End Sub

Rnd, Round and Mod are three math tools that look simple and hide a sharp edge. For Rnd, the edge is that "random" is a promise it does not keep until you ask it to — which is why so many macros pick the same random winner every single time.

What you'll learn

  • The mental model — Rnd is a seeded pseudo-random generator, not true randomness
  • The number-one bug — a "random" result that is identical every run because Randomize is missing
  • The correct formula for a whole number in a range, and the off-by-one people get wrong
  • How to reproduce a sequence on purpose, for tests and demos
  • When to skip Rnd entirely — security, and the worksheet's RandBetween

The mental model: Rnd is seeded, not truly random

A computer cannot conjure true randomness; it runs a formula that produces numbers looking random, starting from a seed. Rnd is one of these pseudo-random generators. Give it the same seed and it produces the same stream of numbers — which is a feature for reproducibility and a trap if you did not know it was happening.

Here is the trap in full. VBA starts every session with the same default seed, so a macro that just calls Rnd deals out the identical sequence on every run:

Sub SameEveryTime()
    Dim i As Long
    For i = 1 To 3
        Debug.Print Rnd        ' 0.7055475, 0.533424, 0.5795186 ...
    Next i                     ' - and the SAME three numbers next run, and the next
End Sub

That is why the "random" prize draw keeps picking the same person, and the "shuffled" list comes out in the same order. Rnd is doing exactly what it was told; nobody told it to start somewhere new.

The number-one fix: Randomize, once, at the top

Randomize reseeds the generator from the system timer, so each run starts from a different point and the sequence actually varies:

Sub DifferentEveryTime()
    Dim i As Long
    Randomize                  ' seed from the clock - the whole macro, not per number
    For i = 1 To 3
        Debug.Print Rnd        ' different three numbers every run now
    Next i
End Sub

Two rules make Randomize behave. Call it once, near the top of the macro — not inside the loop. Seeding repeatedly from a fast clock can hand you the same seed twice in a row and undo the point. And call it before Rnd, because it sets the starting point the following Rnd calls draw from. One Randomize at the top is all a normal macro needs.

A whole number in a range, without the off-by-one

Rnd gives you a fraction; you almost always want an integer between two bounds. The formula that gets it right is worth memorising, because the obvious shortcut is wrong:

n = Int((hi - lo + 1) * Rnd + lo)      ' RIGHT - every value lo..hi is possible
n = Int(Rnd * hi) + 1                  ' subtly wrong when lo isn't 1 / edges off

The + 1 inside the span matters: Rnd never returns 1, so without it the top value hi can never come up. Int((6 - 1 + 1) * Rnd + 1) covers 1 to 6 inclusive — a fair die. Change lo and hi and the same shape covers any inclusive range. Pair it with Int to drop the fraction and you have clean, evenly-spread integers.

Reproduce a sequence on purpose

Because Rnd is seeded, you can turn its determinism into an advantage. For tests, demos, or a bug you need to recreate, seed with a fixed number so the "random" data is identical every run:

Randomize 42                 ' same seed -> same sequence, every single time

Randomize with a numeric argument seeds deterministically, so the Rnd calls that follow reproduce the same stream. There is also Rnd with a negative argument — Rnd(-1) — which restarts a repeatable sequence tied to that number, and Rnd(0), which simply returns the last number again. The headline is symmetric: if a result must differ between runs, Randomize from the clock; if it must be reproducible, Randomize with a constant. Randomness you cannot control is a bug either way.

When not to use Rnd

Rnd is built for sampling, shuffling, and generating test data — not for anything that must be unguessable. It is a predictable pseudo-random generator, so never use it for passwords, tokens, or security codes; a determined person can reproduce its output. For that, use a cryptographic API.

And if you only need a random integer dropped into cells, the worksheet function is often simpler: Application.WorksheetFunction.RandBetween(lo, hi) returns an inclusive integer directly, no Int formula required. One real advantage of Rnd over the sheet's RAND()/RANDBETWEEN, though: those are volatile and recalculate on every edit, so sampled values keep changing. Rnd fires once when the macro runs and the numbers stay put — exactly what you want when a sample must be drawn and then frozen.

How ExcelMaster helps

Rnd fails in ways that look like it is working: the draw runs, numbers appear, and only later does someone notice they are the same numbers as last time — or that the top of the range never shows up, or that a "secure" code is trivially reproducible.

ExcelMaster lets you say what the randomness is for — "pick 20 random rows to audit" or "assign each order a random reviewer" — and it seeds with Randomize so runs actually differ, uses the Int((hi - lo + 1) * Rnd + lo) form so every value is reachable, and steers you to a cryptographic source the moment the word "secure" comes up. You keep the workbook and the code.

Frequently asked questions

Why does my VBA random number stay the same every time?

Because you have not called Randomize. Rnd starts from the same default seed every session, so it replays the identical sequence on each run. Add Randomize once near the top of the macro, before any Rnd, to seed the generator from the system clock and get different numbers each run.

How do I generate a random number between two values in VBA?

Use Int((hi - lo + 1) * Rnd + lo). For example, Int((6 - 1 + 1) * Rnd + 1) gives a whole number from 1 to 6 inclusive. The + 1 inside the span is essential — Rnd never returns 1, so without it the upper bound can never come up. Call Randomize first.

What is the difference between Rnd and Randomize?

Rnd produces the next pseudo-random number in the sequence; Randomize sets the seed that sequence starts from. Rnd on its own always starts from the same seed, so you get the same numbers each run. Randomize (from the clock, or from a fixed number) changes the starting point.

How do I get the same random sequence every run for testing?

Seed with a constant: Randomize 42. The same numeric seed reproduces the same sequence of Rnd values, which is exactly what you want to make a test or a demo repeatable. Use Randomize with no argument (clock seed) when you want the run to differ instead.

Can I use VBA Rnd for passwords or security codes?

No. Rnd is a predictable pseudo-random generator — its output can be reproduced — so it is unsafe for passwords, tokens, or anything that must be unguessable. Use it for sampling, shuffling, and test data; use a cryptographic API for security.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-18.

Related guides: VBA Round · VBA Mod · VBA For Loop · VBA WorksheetFunction · VBA Timer