How to Generate All Four-Digit Number Permutations with VBA in WPS Spreadsheets
Question details
The user wants a VBA macro in WPS Spreadsheets that reads a four-digit number from cell A1, generates every possible arrangement of its digits, removes duplicate permutations when digits repeat, and writes the unique results to column C.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is working with four-digit numeric values and needs a complete list of unique digit arrangements for analysis, testing, or password-style combination generation.
- Observed behavior
- The desired macro should split the source number into individual digits, generate permutations recursively, deduplicate results via a dictionary, and output unique values to column C.
Open WPS Spreadsheets and confirm the Developer tab is visible; if not, enable it through the ribbon options. Save the workbook as a macro-enabled file type so the VBA project remains available.
Generate Unique Four-Digit Permutations with a Recursive VBA Macro
Use this method when the source digits may repeat and you need only unique permutations written to a worksheet range.
The macro reads the value in cell A1, treats it as text, and builds every possible arrangement with a recursive subroutine. A Scripting.Dictionary removes duplicate results before they are written to column C.
In WPS Spreadsheets, go to Developer > Visual Basic, or press Alt+F11 to open the WPS VBA editor.
In the Project Explorer, right-click VBAProject, select Insert > Module, and keep the blank code window open.
Copy the following macro into the module. It reads cell A1, builds permutations recursively, and uses a Scripting.Dictionary to remove duplicates before writing results to column C: Sub GenerateFourDigitPermutations() Dim inputText As String Dim unique As Object Dim key As Variant Dim i As Long inputText = Trim(CStr(Range("A1").Value)) Set unique = CreateObject("Scripting.Dictionary") If Len(inputText) = 0 Then MsgBox "Enter a four-digit number in A1." Exit Sub End If Permute inputText, "", unique Range("C:C").ClearContents i = 1 For Each key In unique.Keys Cells(i, 3).Value = key i = i + 1 Next key MsgBox "Generated " & unique.Count & " unique permutations." End Sub Sub Permute(chars As String, current As String, unique As Object) Dim i As Long Dim nextChars As String If Len(chars) = 0 Then If Not unique.Exists(current) Then unique.Add current, True Exit Sub End If For i = 1 To Len(chars) nextChars = Left(chars, i - 1) & Mid(chars, i + 1) Permute nextChars, current & Mid(chars, i, 1), unique Next i End Sub
Place the cursor inside GenerateFourDigitPermutations and press F5, or go to Run > Run Sub/UserForm. Return to the worksheet to see the unique permutations starting at C1.
Verify Output and Save the Workbook as Macro-Enabled
Use this after running the macro to check the generated list and preserve the VBA project for future use.
Create Four-Digit Permutation Macros Directly in WPS Spreadsheets
WPS Spreadsheets supports VBA macros, so you can insert the recursive permutation code, run it from the Visual Basic Editor, and write unique four-digit arrangements directly to your worksheet without switching to Excel.
- 1. Install WPS Office: Download WPS Office from the official WPS website, run the installer, and open WPS Spreadsheets.
- 2. Prepare a macro-enabled workbook: Enter the source four-digit number in cell A1, then enable the Developer tab if it is hidden.
- 3. Open the VBA editor and insert a module: Press Alt+F11, right-click VBAProject, choose Insert > Module, and paste the permutation macro code.
- 4. Run the macro: Click inside GenerateFourDigitPermutations and press F5 to generate the unique permutations in column C.
- 5. Save for reuse: Choose File > Save As and save the workbook as Excel Macro-Enabled Workbook (*.xlsm).
Frequently Asked Questions
Does this VBA macro remove duplicate permutations when the four-digit number has repeated digits?
Yes. The macro stores each generated arrangement as a Scripting.Dictionary key, so only unique permutations are written to column C.
How do I open the Visual Basic Editor in WPS Spreadsheets?
Go to Developer > Visual Basic, or press Alt+F11. If Developer is hidden, enable it from the ribbon options.
Where does the macro read the input number and write the results?
The input is read from cell A1, and unique permutations are written to column C starting at C1.
Will this VBA code work in Microsoft Excel?
Yes, the code uses standard Excel VBA syntax and works in both Microsoft Excel and WPS Spreadsheets; enable macros when opening the file.
Can the code handle numbers with leading zeros, such as 0123?
It can when cell A1 is formatted as text, so the leading zero is preserved as part of the input string.
