1 / 19

VBA Arrays

VBA Arrays. What is an array? How can you identify an array by looking at the code? What is an element of an array and where is it stored?. CS 105 Spring 2010. What can we do to avoid this?. Dim strName0 As String Dim strName1 As String Dim strName2 As String Dim strName3 As String

gertrude-lehmann
Download Presentation

VBA Arrays

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. VBA Arrays What is an array? How can you identify an array by looking at the code? What is an element of an array and where is it stored? CS 105 Spring 2010

  2. What can we do to avoid this? Dim strName0 As String Dim strName1 As String Dim strName2 As String Dim strName3 As String Dim strName4 As String Dim strName5 As String Dim strName6 As String Dim strName7 As String Dim strName8 As String Dim strName9 As String CS 105 Spring 2010

  3. An array is a list of elements We can create an array! strName is the array name Dim strName(0 To 9) As String We are declaring 10 variables using shorthand. Their names are strName (0) strName (1) and so on Note we have to use the parentheses CS 105 Spring 2010

  4. An array is • an organized list of variables that can contain values such as student names • individual variables in the array are called elements and are designated by a number also called an index or subscript • Example: Dim strEmployee(1 To 50) As String Dim strStudent(0 To 25) As String Dim curBalance(10) As Currency (default is zero so it is 0 to 10) Dim intScore (1 to 860) As Integer

  5. We can assign a value to an element in the array strName(0) = “Janet Baker” strName(1) = “George Lee” strName(2) = “Sue Li” strName(3) = “Sam Hoosier” strName(4) = “William Macy” … strName(2) holds the string “Sue Li” strName(2) is an element in the array CS 105 Spring 2010

  6. intScore (1 to 50) location in memory Memory address intScore(1) intScore(2) intScore(3) intScore(4) intScore(5) intScore(6) intScore(7) intScore(8) intScore(9) intScore(...) …… intScore 50 100002 100004 100006

  7. Why use arrays? • Loops work well with arrays • Say we want to load a list of all our employees from a database • We can do that by writing a loop that copies each employee name into a separate element of the array CS 105 Spring 2010

  8. Displaying data stored in an array strEmployee(1) strEmployee(2) strEmployee(3) strEmployee(4) strEmployee(5) lblShow.Caption = lblShow.Caption & _ strEmployee(intCounter) & vbCrLf CS 105 Spring 2010

  9. Notice an exciting/useful feature Dim strEmployee(1 To 20) As String (code) lblShow.Caption = lblShow.Caption & _ strEmployee(intCounter) & vbCrLf The index can be a variable, like intCounter, but it has to be in the range of the array, that is between 1 and 20 in this case CS 105 Spring 2010

  10. Fill Array—Using Cells Notation Dim strEmployee(1 To 20) As String Dim intCounter As Integer intCounter = 1 lblShow.Caption = "" Do While intCounter <= 20 strEmployee(intCounter) = Range("A" & _ intCounter).Value lblShow.Caption = lblShow.Caption & _ strEmployee(intCounter) & vbCrLf intCounter = intCounter + 1 Loop CS 105 Spring 2010

  11. Fill Array—Using Cells Notation Dim mintScores(0 To 200) As Integer Private Sub cmdMakeArray_Click() Dim intRow As Integer intRow = 1 Do While Cells(intRow, 1).Value <> "" mintScores(intRow) = Cells(intRow, 1).Value intRow = intRow + 1 Loop End Sub CS 105 Spring 2010

  12. Show Array—Using Cells Notation Dim mintScores(0 To 200) As Integer Private Sub cmdShowArray_Click() Dim intRow As Integer intRow = 1 Do While Cells(intRow,1).Value <> "" Cells(intRow,7).Value = mintScores(intRow) intRow = intRow + 1 Loop End CS 105 Spring 2010

  13. Reverse Array—Using Cells Notation Dim mintScores(0 to 200) As Integer Private Sub cmdReverse_Click() Dim intRow As Integer Dim intArray As Integer intArray = 15 intRow = 1 Do While intArray > 0 Cells (intRow, 8).Value = mintScores(intArray) intRow = intRow + 1 intArray = intArray - 1 Loop End Sub CS 105 Spring 2010

  14. Best Golf Scores Do While Cells(intRow,1).Value <> “” intScores(intRow) = Cells(intRow,1).Value If intScores(intRow) < 69 Then intRunningTotal = intRunningTotal + 1 Cells(5,7).Value = intRunningTotal End If intRow = intRow + 1 Loop CS 105 Spring 2010

  15. Two-dimensional Array What if you want to store numbers in a table, for example? The two-dimensional array is like a spreadsheet, with a “row” and “column” denoting each spot. So, each member in an array is noted by intArray(intRow, intColumn)

  16. Create a two-dimensional array, storing the numbers in a table attheir corresponding positions Dim intTable(1 to 2, 1 to 3) as Integer CS 105 Spring 2010

  17. Assigning values to the table Dim mintTable(1 to 2, 1 to 3) as Integer For intRow = 1 to 2 For intColumn = 1 to 3 mintTable(intRow,intColumn)= _ Cells(intRow,intColumn).Value Next intColumn Next intRow

  18. Displaying values of the array Dim mintTable(1 to 2, 1 to 3) as Integer For intRow = 1 to 2 For intColumn = 1 to 3 Cells(intRow,intColumn).Value = _ mintTable(intRow,intColumn) Next intColumn Next intRow CS 105 Spring 2010

  19. Do you understand • What an one-dimensional array is? • What intScore(5) means? CS 105 Spring 2010

More Related