VBA & Macro Problems

Generate a Four-Digit Number with Unique Digits in Excel for Mac

Rana GarciaRana Garcia Nov 14, 2026 868 views

Question details

Generate a random four-digit number in Excel without repeating any digit.

Product
Microsoft Excel for Mac
Device & OS
macOS
Scenario
The user wants a worksheet cell to produce a random four-digit number such as 3842.
Observed behavior
The result should have four digits, start with a nonzero digit, and contain no repeated digits.
Before you start

Open the workbook in Excel for Mac and check that you can access the Visual Basic Editor from the Tools menu. You will need to save the workbook in the macro-enabled .xlsm format to retain a VBA function.

Solution 1Recommended

Create a VBA function for unique four-digit numbers

Use a custom worksheet function when you want Excel to generate a valid number in one cell.

The function generates a number from 1000 through 9999 and checks its digits. If any digit is repeated, it tries again.

1
Open the VBA editor

In Excel for Mac, open the workbook and choose Tools > Macro > Visual Basic Editor.

2
Insert a module

In the Visual Basic Editor, select the workbook in the project pane, then choose Insert > Module.

3
Add the function

Paste this code into the module: Function GenUniqueRand() As Long Static seeded As Boolean Dim n As Long, digits As String Dim i As Long, j As Long Dim unique As Boolean Application.Volatile If Not seeded Then Randomize seeded = True End If Do n = Int(9000 * Rnd) + 1000 digits = CStr(n) unique = True For i = 1 To 3 For j = i + 1 To 4 If Mid(digits, i, 1) = Mid(digits, j, 1) Then unique = False Exit For End If Next j If Not unique Then Exit For Next i Loop Until unique GenUniqueRand = n End Function

4
Use the function

Return to the worksheet, enter =GenUniqueRand() in a cell, and press Return. The result can change when the workbook recalculates.

5
Save the workbook

Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm) to retain the code.

Allow trusted macros: If Excel blocks the function when you reopen the workbook, enable macros only if you trust the file. To keep a generated result unchanged, copy the cell and use Paste Special > Values.
Generate unique digits without VBA

Create a unique four-digit number in WPS Spreadsheet

Use helper cells and formulas in WPS Spreadsheet to select four different digits, then combine them into a number without a macro.

  1. 1. Start a spreadsheet: Open WPS Spreadsheet and create a blank workbook or select an empty area of an existing sheet.
  2. 2. Choose the first digit: Enter =RANDBETWEEN(1,9) in cell A1 so the number cannot start with zero.
  3. 3. Choose the next two digits: Enter =MID(SUBSTITUTE("0123456789",A1,""),RANDBETWEEN(1,9),1) in B1. Then enter =MID(SUBSTITUTE(SUBSTITUTE("0123456789",A1,""),B1,""),RANDBETWEEN(1,8),1) in C1.
  4. 4. Choose the final digit: Enter =MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("0123456789",A1,""),B1,""),C1,""),RANDBETWEEN(1,7),1) in D1.
  5. 5. Combine the digits: Enter =VALUE(A1&B1&C1&D1) in E1. To keep the result when formulas recalculate, copy E1 and use Paste Special > Values.
Generate four-digit numbers without repeated digitsUse formulas without enabling macrosOpen and edit Microsoft Excel .xlsx workbooksUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Can the generated number contain zero?

Yes. Zero can appear in any position except the first, so the result remains a four-digit number.

Why does the number change when Excel recalculates?

The VBA function uses Application.Volatile, so Excel can generate a new result during recalculation. To retain a result, copy the cell and choose Paste Special > Values.

Why does Excel show a name error for the function?

Check that GenUniqueRand is in a standard module in the open workbook and that the cell contains =GenUniqueRand(). If macros are blocked, enable them only for a file you trust.

Can I save the VBA function in an .xlsx file?

No. Choose File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm) to retain the VBA code.