Excel VBA Unique Random Colors Generator Function

random colors generator

Generates list of unique random colors for interior fill in Excel.

08.11.2021, Goran Dolenc
Categories: Excel VBA
Tags: colors generator random unique

Requires you to enable Microsoft Scripting Runtime reference in VBA Tools/References, because Sripting.Dictionary is used!

First define public variable for dictionary:

    Option Explicit

    Public dict As Scripting.Dictionary


Next add function:

    Public Function UniqueRandomColor()
        Dim Colour As Integer
        Repeat:
            Randomize
            Do
                Colour = Int(Rnd() * 56) + 1
                ' check if color is in dict
                If dict.Exists(Colour) Then
                    GoTo Repeat
                Else
                    dict.Add Colour, ""
                    Exit Do
                End If
            Loop
            UniqueRandomColor = Colour
    End Function


And finally test it:

    Sub TEST()
        Dim curColor As Variant
        Set dict = New Scripting.Dictionary
        curColor = UniqueRandomColor
    End Sub