Quick Jump
Introduction | Example | Tutorial | Applications | Comments

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

wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


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


Comments

Copy and paste the example macro into your own project and let me know how you’re using it by leaving a comment below.

I hope you’ll subscribe to my email list. Once you’ve subscribed, you’ll get access to a free copy of my Excel Add-In with tons of useful features and you’ll get monthly tutorials where I show you how to do more neat stuff with VBA:)

Share this article with your corner of the world on Google+, Twitter and Facebook!


wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.