Introduction | Example | Tutorial | Applications
Introduction - Loop Through the Alphabet with VBA
Loop through the alphabet with the Excel VBA Asc and VBA Chr functions. I made this macro for you so all you have to do is copy and paste it into your own project to loop through the alphabet yourself.
Example - Loop Through the Alphabet with VBA
Sub loopABC() Dim FirstLetter As String * 1 Dim CapitalLetters As Boolean Dim NumberOfLetters As Integer Dim ichr, i, icount As Integer Dim Letter As String * 1 '------------------------ 'USER INPUT FirstLetter = "A" 'the letter you want to start with CapitalLetters = True 'set to True if you want capital letters (A B C). False if you want lowercase (a b c) NumberOfLetters = 26 'number of letters you want to loop through 'END OF INPUT '------------------------ If CapitalLetters = True Then FirstLetter = UCase(FirstLetter) If CapitalLetters = False Then FirstLetter = LCase(FirstLetter) ichr = Asc(FirstLetter) For i = 1 To NumberOfLetters If CapitalLetters = True Then If ichr > 90 Then ichr = 64 + ichr - 90 Letter = Chr(ichr) Else If ichr > 122 Then ichr = 96 + ichr - 122 Letter = Chr(ichr) End If ichr = ichr + 1 '--------------------------------------------- 'USER ACTION ' Your letter is stored in the variable Letter. ' Do what you want with it here. ' For example: Range("A" & i) = Letter '--------------------------------------------- Next i End Sub
Make powerful macros with our free VBA Developer Kit It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
Tutorial - Loop Through the Alphabet with VBA
Once you copy and paste this macro into your VBA Editor, there are only 3 variables you’ll need to change. These variables are listed under the USER INPUT section near the top of the macro. The three variables are:
- FirstLetter - This is the first letter you want your loop to display. The string length is set to 1, so it will only store one character. Examples of valid input include letters like “A” or “t”
- CapitalLetters - This variable tells the macro whether or not you want capital letters. Setting it to True will cause the procedure to display letters like
A B C. Setting it to False will display letters like a b c.
- NumberOfLetters - This is how many letters you want to loop through. It can be any positive number. If you enter a number larger than the number of letters left in the alphabet, the macro will start back over at A, like
X Y Z A B C. Set this variable to something like 100 and see for yourself!
The loopABC macro loops through the alphabet by using a comination of the Chr and the Asc VBA functions. Here’s a brief description of what these functions do:
- VBA Chr - This returns the character for a given ASCII code. For example, Chr(65) returns the letter “A”
- VBA Asc - This is the opposite of Chr. It Returns the ASCII code of a character, like a letter. For example Asc(“A”) yields a value of 65
Under the USER ACTION section, you can add whatever code you want. The next letter in the alphabet is stored in the variable
Range("A" & i) = Letter
in the USER INPUT section to print the letters to Column A.
Application Ideas - Loop Through the Alphabet with VBA
I use a variation of this macro to save each page of my Word Documents as separate PDFs with letters in the file names instead of numbers. This is great for things like saving cover pages for Appendices.
I’m sure you have your own uses for a macro like this - otherwise you wouldn’t be reading this tutorial!:)