Vba arrays
Download
1 / 19

VBA Arrays - PowerPoint PPT Presentation


  • 150 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' VBA Arrays' - michel


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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
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


An array is a list of elements
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


An array is
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


  • We can assign a value to an element in the array
    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


    Intscore 1 to 50 location in memory
    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


    Why use arrays
    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


    Displaying data stored in an array
    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


    Notice an exciting useful feature
    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


    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


    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


    Show array using cells notation
    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


    Reverse array using cells notation
    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


    Best golf scores
    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


    Two dimensional array
    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)


    Create a two dimensional array storing the numbers in a table at their corresponding positions
    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


    Assigning values to the table table at

    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


    Displaying values of the array table at

    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


    Do you understand
    Do you understand table at

    • What an one-dimensional array is?

    • What intScore(5) means?

    CS 105 Spring 2010


    ad