Random String Generation VBA

This blog article shows you how to generate random numbers using VBA. Let’s say the below column A is the real data, and you do not want to share the data, so you want to fake the data.

The code below will generate random string from 30 to 60 characters to replace the data.

Sub RandomString()
     Dim str As String
     Range(“A2”).Select
     While (ActiveCell.Value <> “”)
         str = GenerateRandomCompanyName
         ActiveCell.Value = str
         ActiveCell.Offset(1, 0).Select
         Debug.Print ActiveCell.Address
     Wend
End Sub

Function GenerateRandomCompanyName() As String
     Dim companyName As String
     Dim charSet As String
     Dim i As Integer
     Dim nameLength As Integer
     ‘ Define the character set to use for the company name
     charSet = “ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz “
     ‘ Generate a random length between 30 and 60 characters
     nameLength = Int((60 – 30 + 1) * Rnd + 30)
     ‘ Generate the random company name
     For i = 1 To nameLength
         companyName = companyName & Mid(charSet, Int((Len(charSet) * Rnd) + 1), 1)
     Next i
     ‘ Output the result
     ‘MsgBox “Random Company Name: ” & companyName
     GenerateRandomCompanyName = companyName
End Function

After executing the code, the result is as follows.

Also: Write a VBA program to Generate Test Data using Generative AI

Source Code Download: https://github.com/chanmmn/vba/tree/master/2024/RandomStringGeneration?WT.mc_id=DP-MVP-36769

Reference: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/rnd-function?WT.mc_id=DP-MVP-36769