vba arrays
Download
Skip this Video
Download Presentation
VBA Arrays

Loading in 2 Seconds...

play fullscreen
1 / 19

VBA Arrays - PowerPoint PPT Presentation


  • 151 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

slide10
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

slide11
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

slide17
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

slide18
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

do you understand
Do you understand
  • What an one-dimensional array is?
  • What intScore(5) means?

CS 105 Spring 2010

ad