Hello Excellers, welcome back to another blog post in my #Excel #MacroMonday 2021 series. Today let’s write our own Excel macro function that will generate a random string of characters. This random string generator can be helpful to create unique passwords of any given length automatically at any time.
Table of contents
- Preparing To Write The Macro.
- Starting The Macro.
- Function Procedures.
- Declaring Variables.
- Message Box Warning.
- Specify The Array Values And Characters.
- Selecting Characters Individually To Generate The Random String.
- The Function Result. Generate A Random String!.
- Ending The Function.
- What Is An Excel Add-In?.
- Install the Add-In To Generate A Random String.
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.
Starting The Macro.
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.
We now need to declare three variables for this Function. By declaring these variables, Excel creates memory containers for each of these values.
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.
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.
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.
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.
Ending The Function.
Excel generates the random string. At this point, the code and also the Function ends.
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 ued 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, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.