How to generate a GUID in Excel with VBA
How can I generate GUIDs in Excel?
Unique IDs: different, like 2 Chainz
There are many variations on the need here: maybe you need each new row in an output file to have a unique ID column, maybe you need to differentiate between orders from multiple tracking systems… the common thread here is needing something that is guaranteed* to be unique.
(*Note: yes, statistics zealots, I am aware that, assuming uniform distribution, there is a 50% chance that in a set of 4,200,000,000,000,000,000 GUIDs, there will be one duplicate… fuck off.)
Fortunately for us Excel and VBA users, generating a GUID with a function is a cinch:
Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.
And hey, astute readers — you might have even noticed that the linked gist uses a GUID!
https://gist.github.com/danwagnerco/39e371ead15c9423b4befe08665b6bd9
“39e371ead15c9423b4befe08665b6bd9” is exactly 32 hexadecimal characters, and if it’s unique enough for Github it will work for us too.
Let’s review the code using the 4-step VBA process as our guide:
Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup
Step 1 – Setup in this function is essentially just our variable declarations and assignments on lines 6-19. We create an Object variable, obj, which will be assigned to the built-in Scriptlet.TypeLib object, and a String, strGUID, which will contain the GUID.
Line 19 is where we assign obj to the Scriptlet.TypeLib object, but because this is a different notation than usual, let’s take a moment to talk about exactly what’s happening here.
Whenever you see:
in VBA, you are observing something called late-binding. Contrast this to early-binding, which looks like this:
If you have read some of my other tutorials, you will often see early-binding in use with the Scripting.Dictionary object:
There are many articles out there that contrast early-binding vs. late binding, and (surprise!) I have a strong opinion about it: use early-binding whenever you can. Period.
(You can write a check to the Dan Wagner Co. for saving you a half-day of reading :troll_smile:)
Unfortunately, in researching and testing the Scriptlet.TypeLib object, I could not figure out how to early-bind that sucker. It constantly throws “object or with block variable not set” errors, and does not even seem to be named the same thing!
Save yourself the struggle here and use late-binding, comfortable in the fact that this is literally the only time, in my years of VBA experience, I have found late-binding to be necessary.
Now that we have put Step 1 – Setup to bed, it’s time to move on.
Step 2 – Exploration is only line 22 here, where we assign strGUID to a manipulation of the .GUID method.
Interestingly, when calling obj.GUID, you wind up with a null-terminated string. This can be quite annoying depending on the application, so we use Left(obj.GUID, Len(objGUID) – 2) to get all characters EXCEPT the last two.
And with that, our Step 2 – Exploration is done!
The variable strGUID contains a full-fledged GUID, with hyphens and curly braces, that looks like this:
{88B6591C-053D-414E-8447-224704047786}
Wahoo! It’s time to move on to the next step.
Step 3 – Execution is where we (potentially) apply a few tweaks to strGUID, depending on how the function was called.
If you look at the function definition itself (which is on line 2), you’ll notice that there are two Optional Parameters:
- IncludeHyphens, a Boolean variable, which is defaulted to True
- IncludeBraces, a Boolean variable, which is defaulted to False
Optional Parameters are, exactly like the name implies, optional. They are not strictly necessary for the function to work!
There are two different ways to use Optional Parameters — one that sets them up as Variant-type and then tests for existence, and one that sets them up as specific-types and includes a default value.
The latter, where the Optional Parameters are configured as specific-types (Boolean in this case) with a default value, is what I recommend whenever possible. Testing for existence is a pain in the ass.
Let’s talk about how each one of these Optional Parameters works as part of our Step 3 – Execution.
The first is IncludeHyphens, which defaults to True. Hyphens are commonly included in GUIDs, but if you would like them removed, you can call this function with IncludeHyphens set to False instead.
On lines 26-28, we handle IncludeHyphens and the resulting strGUID change.
Remember, IncludeHyphens has a default value of True, so if you do not change that, Not True becomes False, skipping line 27.
If, however, IncludeHyphens was set to False, line 27 would be executed. If that is the case, then we replace all of the hyphen characters in strGUID with vbNullString, which is blank. Boom, hyphens removed.
The second Optional Parameter is IncludeBraces, which defaults to False.
A typical GUID does not include those opening and closing curly braces, so the function is set up to remove them by default (meaning that lines 33 and 34 are, by default, executed).
Much like line 27, we update strGUID with the Replace function, first replacing the open curly brace with vbNullString (blank), then replacing the close curly brace vbNullString (blank) as well.
Finally, on line 37, we set the function return to strGUID.
No clean up necessary, so we skip Step 4 – Cleanup — and with that, we’re done!
You can see how this function works inside the VBA editor by calling it a few times and writing the output to the immediate window like so:
Or, you can follow along with me in this 8-minute guide to the function, how to use it in the wild, and how adjusting the Optional Parameters can give you different results:
Are you creating GUIDs and writing them with ease? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.