如何在VBA中按指定概率生成目标字符串？

Hey there! I’ve got you covered with a straightforward VBA solution that will generate those destination strings exactly as you need. Here’s how it works:

We’ll use VBA’s Rnd() function to generate a random number between 0 and 1, then map that number to your desired probability ranges:

• 0 to 0.6 → Oxford (60% chance)
• 0.6 to 0.9 → Liverpool (30% chance)
• 0.9 to 1 → Cardiff (10% chance)

Full VBA Code #

Sub GenerateDestinations()
    Dim i As Integer
    Dim randomNum As Double
    Dim destination As String
    
    ' Seed the random number generator to get unique sequences each run
    Randomize
    
    ' Generate 6 outputs matching your sample (adjust the number as needed)
    For i = 1 To 6
        randomNum = Rnd()
        
        Select Case randomNum
            Case Is <= 0.6
                destination = "Oxford"
            Case Is <= 0.9
                destination = "Liverpool"
            Case Else
                destination = "Cardiff"
        End Select
        
        ' Print formatted string to the Immediate Window (Ctrl+G to view)
        Debug.Print "From To London " & destination
    Next i
End Sub

' Optional reusable function to get a single destination
Function GetDestination() As String
    Dim randomNum As Double
    randomNum = Rnd()
    
    Select Case randomNum
        Case Is <= 0.6
            GetDestination = "Oxford"
        Case Is <= 0.9
            GetDestination = "Liverpool"
        Case Else
            GetDestination = "Cardiff"
    End Select
End Function

How to Use This: #

1. Open the VBA Editor in Excel (press Alt + F11)
2. Insert a new module (Right-click your workbook in the Project Explorer → Insert → Module)
3. Paste the code above
4. Run the GenerateDestinations sub (press F5 while in the sub)
5. Check the Immediate Window (press Ctrl + G) to see your output strings

Quick Notes: #

• The Randomize statement ensures you get different random sequences every time you run the code (skip it only if you want the same sequence repeatedly).
• The GetDestination function is perfect if you want to reuse this logic elsewhere in your VBA project, or even turn it into a worksheet function.
• To generate more or fewer outputs, just adjust the number in the For i = 1 To 6 line.

Tweaking probabilities later is a breeze—just update the values in the Select Case statements!

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