请求解释VBA随机数生成代码中UniqueRandDigits函数及后续逻辑

Hey there! Let's break down that UniqueRandDigits function for you step by step—since I know VBA can feel a bit tricky when you're starting out.

Function UniqueRandDigits(x As Long) As String

This line declares a custom function named UniqueRandDigits with two key details:

• It accepts one input parameter: x (a whole number, Long type). In your code, you pass 5 to it, which tells the function to generate a 5-digit string.
• It returns a String (text value) as its output. That's why you can use it directly in Cells(i,3) = "18" & UniqueRandDigits(5) to build a longer text string for your worksheet.

Dim i As Long
Dim n As Integer
Dim s As String

These lines create "storage boxes" for data the function will use as it runs:

• i: A counter variable (typically used in loops to track how many digits we've added so far).
• n: Will temporarily hold each random digit we generate.
• s: Will build up the final string of unique digits as we go.

Even though your code cuts off, based on the function name and how you're using it, here's what the rest of the function almost certainly does (this is the standard approach for generating unique random digits in VBA):

1. Start with an empty string: s = "" — we'll add digits to this as we find unique ones.
2. Loop until we have x unique digits:

   Do While Len(s) < x
       ' Generate a random digit between 0 and 9
       n = Int(Rnd() * 10)
       ' Check if this digit is NOT already in our string
       If InStr(s, CStr(n)) = 0 Then
           ' If it's unique, add it to the string
           s = s & CStr(n)
       End If
   Loop
   

   • Rnd() creates a random number between 0 (inclusive) and 1 (exclusive). Multiplying by 10 gives us a number from 0 to ~9.999, and Int() chops off the decimal to get an integer between 0-9.
   • InStr(s, CStr(n)) checks if the digit (converted to text with CStr()) already exists in our growing string s. If it returns 0, the digit is new and unique.
   • We only add the digit to s if it's unique, so we keep looping until s has exactly x characters.
3. Send the final string back:

   UniqueRandDigits = s
   

   This line passes the completed unique digit string back to wherever the function was called (in your macro, that's to populate column 3 with "18" + the 5-digit unique string).

In your Opgave8 subroutine, you're using UniqueRandDigits(5) to generate a 5-character string of random, non-repeating digits. By prepending "18" to it, you end up with values like 187294 (example) in column 3 for every row where column 12 starts with "262015".

内容的提问来源于stack exchange，提问作者Adem