Hello Excellers, welcome back to another blog post in my #Excel #MacroMonday series. Today let’s write our own Excel macro function that will generate a random string of characters. This random string generator or password generator can be helpful to create unique passwords of any given length automatically at any time. If you’re looking for a way to create unique passwords that are still easy to remember, we’ve got the perfect solution.

Preparing To Write The Macro.

First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.

You then choose to either create a module to store your code either in your Personal Macro Workbook or in your current workbook or create a UDF or User Defined Function as we are writing our Function or Formula.

What’s the difference?. If you save the macro in your Personal Macro workbook, it will be available for use in any of my Excel workbooks. If you store it in the current workbook, then use is restricted to that workbook. As we write a UDF or user-defined function, we can make it available as just another regular function or formula in Excel. We do this by saving it as an Exel Add-In.

I want to be able to use this macro at any time to generate a password. So I will save this as an Excel Add-In.

More About Your Personal Macro Workbook (PMW).

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Starting The Macro.

Function Procedures.

So, in the way that every Sub procedure begins with the words Sub, Functions always start with the word Function and end with the phrase End Function. So, we begin to write our code by typing Function and the name we want to call your function. In this example, I want to call my function PasswordString.

Function PasswordString (Length As Integer)
End Function

Declaring Variables.

We now need to declare three variables for this Function. By declaring these variables, Excel creates memory containers for each of these values.

Dim Strings As Variant, x As Long, str As String

Message Box Warning.

We need the user to enter a value into the length argument of the function to be greater than zero. If the value is not greater than zero, a message box pops up to warn the user to enter the correct value to enter, a value greater than zero characters.

If Length <1 Then
    MsgBox "Length variable must be greater than 0"
    Exit Function
  End If

Specify The Array Values And Characters.

This next piece of code specifies the array of characters and values that Excel can use to generate the string for the password randomly. We declared at the beginning of the function that this value is a Variant. A variant is a particular data type that can contain any kind of data except fixed-length. Excel can therefore generate the random password string made of numbers and characters, precisely what we want in a password.

Strings = Array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", _
  "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", _
  "y", "z", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "!", "@", _
  "#", "$", "%", "^", "&", "*", "A", "B", "C", "D", "E", "F", "G", "H", _
  "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", _
  "W", "X", "Y", "Z")

Selecting Characters Individually To Generate The Random String.

First, Excel sets the length of the generated password by using x=1 to length, which is the length of the password specified in the functions only argument.

For x = 1 To Length
    Randomize
    str = str & Strings(Int((UBound(Strings) - LBound(Strings) + 1) * Rnd + LBound(Strings)))
  Next x

The Function Result. Generate A Random String!.

The result of the randomized string is then returned and displayed as a typical result in the Excel cell.

PasswordString = str

Ending The Function.

Excel generates the random string. At this point, the code and also the Function ends.

End Function

At last, it is now time to test the function. Great, it looks good. That is how to generate a random string in Excel. A great way to generate Excel passwords.

Finally, it is now time to make this Function available in any of my Excel workbooks. We do this by creating an Excel Add-In.

What Is An Excel Add-In?.

An Excel add-in is simply a File with an extension of .xlam or .xll. Files are loaded up when Excel starts up and contain VBA code (.xlam extensions) that add extra functionality. They usually save you time and or help you avoid errors. While there are Add-Ins that are already available, and there are many Add-Ins that are a lot of third-party Add-Ins – a lot of them are free to download. Just take a look around. So, follow the steps below to convert our UDF to re-use.

• Click on the File Tab
• Select Info (Excel 2016)
• Next, click on the Title and Comments – These are the pieces of information that a user sees and uses when they are loading the Add-In.

• Fill in these fields with something appropriate and informative to the user
• FInally, click Save As and select the file extension .xlam
• The name you choose here will be the name of the Add-In
• The file path automatically is changed.You can keep this location or change it

Install the Add-In To Generate A Random String.

• Open a new workbook
• Developer Tab | Add-Ins | Excel Add-Ins
• Select your Add-In and hit Ok in the dialog box. In this example it is Generate A Rndom String.
• The Add-In will now be activated.  Your personal random string generator.

That’s it.  Your very own random string generator. A simple piece of  VBA can turn into something really useful.  Easy as that.  Would you find this useful?  Likewise, please share in the comments below and let me know if you have used the random string generator.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 50 Excel Tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

How To Excel At Excel – Macro Mondays Blog Posts.

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.

• Create User Defined Function (UDF) And Excel Add-In To Count Cells By Their Colour – Macro Monday
• Excel Tip – Learn How To Generate Random Dates With RANDBETWEEN and The DATE Functions – Formula Friday
• Formula Friday- Excel Tip- Generate Random Numbers With Excel.
• Excel VBA Macro – Write Random Numerical Values To A Range Of Cells
• Formula Friday – Randomly generate words in Excel with INDEX and RANDBETWEEN.

Let me know in the comments below if you would like the free Excel Password Generator.