 Special Offer Advanced Excel VBA prorgrams in finance & statistics. - open source code - Visual Basic Collection Learn to write Excel macros and get over 1200 macros. Multiple Regression The Multiple Regression Forecasting template provides a solid basis for identifying value drivers and forecasting data.
 Excel VBA Basic Tutorial 3 This page contains the 3rd lesson on the Excel VBA Basic Tutorial series.  It covers topics in creating and managing array and understanding the VBA decision and loop structures.  Beginners in VBA programming are encouraged to go through the prior lessons in this series if they had not already done so.  This document contains information about the following topics. Microsoft Support site or the Excel VBA Help section on your computer contains comprehensive examples on most the issues covered on this page.  For more information, please refer to them. Creating and Managing Array    Microsoft Support   Declaring an Array With Dim Statement An array is a set of sequentially indexed elements having the same intrinsic data type. Each element of an array has a unique identifying index number. Changes made to one element of an array don't affect the other elements.  Before signing values to an array, the array needs to be created.  You can declare the array by using the Dim statement. For example, to declare a one-dimensional array with 5 elements, type the following:             Dim Arr(4) The element’s index of the array starts from 0 unless Option Base 1 is specified in the public area (area outside of the sub procedure).  If Option Base 1 is specified, the index will start from 1. The following example assigns values to the array and displays all values in a message box :          Option Base 1             Sub assignArray( )                   Dim Arr(5)                   Arr(1) = “Jan”                   Arr(2) = “Feb”                        Arr(3) = “Mar”                        Arr(4) = “Apr”                   Arr(5) = “May”                   Msgbox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5)             End Sub * The number inside the array, i.e. Arr(1), is the index.  One (1) is the index of the first element in the   array. Resize an Array With Redim Statement The ReDim statement is used to size or resize a dynamic array that has already been formally declared. For example, if you have already declared an array with an element value of 5 and decided to change the number of the element to 6, you can do the following to resize the array:             Redim Arr(6) We incorporate it into our last example:          Option Base 1          Sub assignArray( )                   'Dim Arr(5)                   Redim Arr(6)                   Arr(1) = “Jan”                   Arr(2) = “Feb”                   Arr(3) = “Mar”                   Arr(4) = “Apr”                   Arr(5) = “May”                   Arr(6) = “Jun”                   Msgbox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5)             End Sub Note that the Dim Arr(5) statement is commoned out, because leaving this original statement in the sub will causing a compile error. Manage Dynamic Array A word of caution in using the Redim Statement to resize an array - resize the array can erase the elements in it.  In the following example, all the values assigned prior to resize the array are erased.  Only the value assigned to the array after resize remains.          Option Base 1             Sub assignArray( )                                    Redim Arr(5)                   Arr(1) = “Jan”                   Arr(2) = “Feb”                   Arr(3) = “Mar”                   Arr(4) = “Apr”                   Arr(5) = “May”                   Redim Arr(6)                   Arr(6) = “Jun”                   Msgbox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5) & "-" & Arr(6)             End Sub By replace the Redim Arr(6) with Redim Preserve Arr(6), all values will remain.  For example:           Option Base 1           Sub assignArray( )                   Redim Arr(5)                   Arr(1) = “Jan”                   Arr(2) = “Feb”                   Arr(3) = “Mar”                   Arr(4) = “Apr”                   Arr(5) = “May”                   Redim Preserve Arr(6)                   Arr(6) = “Jun”                   Msgbox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5) & "-" & Arr(6)             End Sub Create Multi-Dimensional Array An array can also store multiple dimensional data.  To simplify our tutorial, example on a two-dimensional array is used.  Assume you have data of a local store's yearly sale in the following table and you want to store the data in a two-dimensional array:                              Year 2003     Year 2004            CD Sale            1,000           1,500          DVD Sale           1,200            2,000 First we create the array as follow:                 Dim Arr(2,2) Then we assign the values into the array.  We treat the first dimension as the year and the second dimension as the product sale:             arr(1,1) = 1000             arr(1,2) = 1200             arr(2,1) = 1500             arr(2,2) = 2000 We now display the values of the array with a message box:           Msgbox "Sale of CD in 2003 is " & arr(1,1) & vbCrLf  & "Sale of CD in 2004 is " _                                 & arr(2,1)  & vbCrLf  & "Sale of DVD in 2003 is " & arr(1,2) & vbCrLf  _                         & "Sale of DVD in 2004 is " & arr(2,2) The complete precedure is as followed:                      Option Base 1             Sub multDimArray( )                              Dim Arr(2,2)                   arr(1,1) = 1000                   arr(1,2) = 1200                   arr(2,1) = 1500                   arr(2,2) = 2000                        Msgbox "Sale of CD in 2003 is " & arr(1,1) & vbCrLf  & "Sale of CD in 2004 is " _                                    & arr(2,1) & vbCrLf  & "Sale of DVD in 2003 is " & arr(1,2) & vbCrLf  _                                    & "Sale of DVD in 2004 is " & arr(2,2)             End Sub * vbCrLf stands for VB Carriage Return Line Feed.  It puts a return and a new line as shown in the   message box above.  The underscore "_" on the back of the first line of the message box means    "continue to the next line" Return to Top of Page Find The Size of  an Array The largest available subscript for the indicated dimension of an array can be obtained by using the Ubound function.  In our one-dimensional array example, Ubound(arr) is 5.   In our two-dimensional array example above, there are two upper bound figures - both are 2.  UBound returns the following values for an array with these dimensions*:          Dim A(1 To 100, 0 To 3, -3 To 4)          Statement             Return Value          UBound(A, 1)                         100                            UBound(A, 2)                            3          UBound(A, 3)                            4 * Example taken from Excel VBA Help section. The UBound function is used with the LBound function to determine the size of an array. Use the LBound function to find the lower limit of an array dimension.          Statement             Return Value          LBound(A, 1)                            1                            LBound(A, 2)                            0          LBound(A, 3)                           -3 To get the size of an array, use the following formula:         UBound(Arr) - LBound(Arr) + 1 For example:         Ubound(A,1) - LBound(A,1) + 1         = 100 - 1 + 1         = 100         Ubound(A,2) - LBound(A,2) + 1         = 3 - 0 + 1         = 4         Ubound(A,3) - LBound(A,3) + 1         = 4 - (-3) + 1         = 8 For more information on arrays check Microsoft Support Return to Top of Page Decision Structures - IF and Select Case IF ... Then Statement The IF ... Then is a single condition and run a single statement or a block of statement. Example, the following statement set variable Status to "Adult" if the statement is true:         If Age >= 18 Then Status = "Adult" You can also use multiple-line block in the If statement as followed:         If Ago >= 18 Then             Status = "Adult"             Vote = "Yes"         End If Note that in the multiple-line block case, End If statement is needed, where the single-line case does not. Return to Top of Page IF ... Then ... Else The If ... Then ... Else statement is used to define two blocks of conditions - true and false. Example:          If Age >=22 Then         Drink = "Yes"     Else         Drink = "No"     End If Again, note that End If statement is needed in this case as well since there is more than one block of statements. Return to Top of Page IF ... Then ... ElseIf The IF ... Then ... ElseIf is used to test additional conditions without using new If ... Then statements. For Example:    If Age >= 18 and Age < 22 Then         Msgbox "You can vote"     ElseIf Age >=22 and Age < 62 Then         Msgbox "You can drink and vote"     ElseIf Age >=62 Then         Msgbox "You are eligible to apply for Social Security Benefit"     Else         Msgbox "You cannot drink or vote"     End If Note that the last condition under Else is, implicitly, Age < 18. Return to Top of Page Select Case Select Case statement is an alternative to the ElseIf statement.  This method is more efficient and readable in coding the the If ... Then ... ElseIf statment.  Example:     Select Case Grade         Case Is >= 90             LetterGrade = "A"         Case Is >= 80             LetterGrade = "B"         Case Is >= 70             LetterGrade = "C"         Case Is >= 60             LetterGrade = "D"         Case Else             LetterGrade = "Sorry"     End Select Return to Top of Page Loop Structures For ... Next Use For ... Next loop if the number of loops is already defined and known.  A For ... Next loop uses a counter variable that increases or decreases in value during each iteration of the loop.  This loop structure is being used the most for our examples on this site.  Here is an example of the For ... Next loop:     For i = 1 to 10         Cells(i, 1) = i     Next i In this example, i is the counter variable from 1 to 10.  The looping process will send value to the first column of the active sheet and print i (which is 1 to 10) to row 1 to 10 of that column. Note that the counter variable, by default, increases by an increment of 1. Return to Top of Page For ... Next Loop With Step You can use the Step Keyword to sepcify a different increment for the counter variable.  For example:     For i = 1 to 10 Step 2         Cells(i, 1) = i     Next i This looping process will print values with an increment of 2 on row 1, 3, 5, 7 and 9 on column one. You can also have decrement in the loop by assign a negative value afte the Step keyword.  For example:     For i = 10 to 1 Step -2         Cells(i, 1) = i     Next i This looping process will print values with an increment of -2 starts from 10 on row  10, 8, 6, 4 and 2 on column one. Return to Top of Page Do While ... Loop You can use the Do While ... Loop to test a condition at the start of the loop.  It will run the loop as long as the condition is ture and stops when the condition becomes false.  For Example:    i = 1     Do While i =< 10         Cells(i, 1) = i         i = i + 1     Loop This looping process yields the same result as in the For ... Next structures example. One thing to be caution is that sometimes the loop might be a infinite loop.  And it happens when the condition never beomes false.  In such case, you can stop the loop by press [ESC] or [CTRL] + [BREAK]. Return to Top of Page Do Until ... Loop You can test the condition at the beginning of the loop and then run the loop until the test condition becomes true. Example:     i = 1     Do Until i = 11         Cells(i, 1) = i         i = i + 1        Loop This looping process yields the same result as in the For ... Next structures example. Return to Top of Page Do ... Loop While When you want to make sure that the loop will run at least once, you can put the test at the end of loop.  The loop will stop when the condition becomes false.   (compare this loop structure to the Do ... While Loop.) For Example:        i = 1     Do         Cells(i, 1) = i         i  = i + 1     Loop While i < 11   This looping process yields the same result as in the For ... Next structures example.        Return to Top of Page Do ... Loop Until This loop structure, like the Do ... Loop While, makes sure that the loop will run at least once, you can put the test at the end of loop.  The loop will stop when the condition becomes true.   (compare this loop structure to the Do ... Until Loop.) For Example:        i = 1     Do         Cells(i, 1) = i         i  = i + 1     Loop Until i = 11   This looping process yields the same result as in the For ... Next structures example.    Return to Top of Page 