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.

I'll take a free VBA Developer Kit

Tutorial - Loop Through the Alphabet with VBA

Input

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:

  1. 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”
  2. 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.
  3. 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!

Method

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

Output

Under the USER ACTION section, you can add whatever code you want. The next letter in the alphabet is stored in the variable Letter so you can use it in your own project. For example, you can enter

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!:)

If you haven’t already done so, join our VBA Insiders using the form below. After that, share this article with your corner of the world on Twitter and Facebook.