Outline Arrays - CSUN

More Programming with Arrays

More Programming with Arrays

Larry Caretto Mechanical Engineering 209

Computer Programming for Mechanical Engineers

April 18, 2017

April 18, 2017

Outline

? Review arrays

? Using arrays in for loops and expressions ? Arrays to and from worksheet ? Transferring Excel data from rectangular

ranges into type variant VBA variables ? Sending arrays from VBA to worksheets ? Comparing range.value and cells method

for transferring arrays

? Programming assignment six ? Work on programming assignments

2

Semester Calendar

Date

Assignment

Date

Assignment

April 18

Assignment 4 Due

April 20

Quiz 4 on Looping

April Assignment 5 Due April

25

27

May 2

Quiz 5 on Arrays

May 4

May 9

Assignment 6 Due

May 11

Programming Exam

May Final Exam 12:45

18

to 2:45 pm

3

Review For Loops and Arrays

? Use for loops to process all (or several) elements in an array

? Use nested loops for 2D arrays

For k = 1 to 10 For j = 1 to 20 rho(i,j) = M*P(k) / (R*T(j)) Next j

Next k

4

Review Adjustable Size Array

? An initial statement Dim x() As ... lets the compiler know that x is an array

? When it sees x used as an array it will not mark it as an error and halt execution

? When we know the size of the array we use a ReDim statement to set the size

Dim x() As Double, N As Long N = InputBox("Enter Size: ") ReDim x(1 To N)

5

Review The Mysterious Variant

? Type Variant Variables, declared as scalars, can be equated to an array

? They then become arrays

? Used for getting cell data to VBA

6

ME 209 ? Computer Programming for Mechanical Engineers

1

More Programming with Arrays

April 18, 2017

Review Array Exercise

? Write the VBA code to create a current, I, and voltage, V, array from the worksheet data ? Using only Range.Value (Use type variant.) ? Using Cells(,).Value

A

B

C

D

E

F

G

1

I(1) I(2) I(3) I(4) I(5) I(6)

2 V(1) eW(1,h1)ate(a1r,2e) rea(n1,g3)ese(f1o,4r)the(e1,a5)rrea(y1,s6) 3 V(2) eI(2a,1n)deV(2?,2) Rea(2n,3g) ee(f2o,r4)Vei(s2,5A)2e:(A2,56) 4 V(3) e((c3,o1l)ume(3n,21) ,er(o3,w3)se2(3,t4o) 5e)(3I,5is) e(3,6) 5 V(4) eB(41,1:)Ge1(4(,r2o) we(41,3c)oelu(4m,4)nse(24,5t)o e7(4),6)

7

Range.Value gives 2D Array

Sub exerciseA()

Dim I As Variant Dim V As Variant I = Range("B1:G1").Value V = Range("A2:A5").Value

End Sub

Setting a Variant to a single row or column gives a two-dimensional array

8

Cells Gives 1D Array

Sub exerciseB() Dim I(1 to 6) As Double Dim V(1 to 4) As Double Dim k As Integer For k = 1 To 6 I(k) = Cells(1,k+1).Value Next k For k = 1 To 4 V(k) = Cells(k+1,1).Value Next k

End Sub

9

2D Array to VBA Exercise

? Write the VBA code to create a two-dimensional

efficiency array, e, from the worksheet data

? Using only Range.Value B2:G5 Cells(2,2) to ? Using Cells(,).Value Cells(5,7)

A

B

C

D

E

F

G

1

I(1) I(2) I(3) I(4) I(5) I(6)

2 V(1) e(1,1) e(1,2) e(1,3) e(1,4) e(1,5) e(1,6)

3 V(2) e(2,1) e(2,2) e(2,3) e(2,4) e(2,5) e(2,6)

4 V(3) e(3,1) e(3,2) e(3,3) e(3,4) e(3,5) e(3,6)

5 V(4) e(4,1) e(4,2) e(4,3) e(4,4) e(4,5) e(4,6)

10

Range.Value is Simple

Sub exerciseC()

Dim e As Variant e = Range("B2:G5").Value

End Sub

Here e is declared as a scalar variant, but becomes an array when set to a worksheet range

11

Cells Better for Known Numbers

Sub exerciseD() Dim e(1 To 4, _ 1 To 6) As Double Dim k As Long Dim m As Long For k = 1 To 4 For m = 1 To 6 e(k, m) = Cells(k + 1, _ m + 1).Value Next m Next k

End Sub

12

ME 209 ? Computer Programming for Mechanical Engineers

2

More Programming with Arrays

April 18, 2017

Review Arrays to Procedures

? When passing arrays to procedures it is not necessary to Dim the array

? Instead, the argument to handle an array is written with empty parentheses

Function mean(x() as Double) As Double Dim k as Long mean = 0 For k = LBound(x) To UBound(x) mean = mean + x(k) Next k mean = mean /(UBound(x) -LBound(x) + 1)

End Function

13

Review Use of Mean Function

? Mean function shown on previous slide must be called from other VBA code

? Cannot be called from worksheet ? Example of VBA code that calls mean Dim x(1 To 10) As Double For k = 1 To 10

x(k) = rnd() `Random number Next k msgBox "Mean = " & mean(x)

14

Information Transfer Time

? Using a single array (code below) to transfer a large amount of information to the worksheet takes 0.904 s/item compared with 46.0 s/item using cells approach (next slide)

For i = 1 To nRows For j = 1 To nCols x(i, j) = Sin(i * j / nRows) Next j

Next i wks.Range("A1:J100000") = x

15

Information Transfer Time

? Using a single array to transfer a large amount of information to the worksheet takes 0.904 s/item (previous slide) compared with 46.0 s/item using cells approach below

For i = 1 To nRows For j = 1 To nCols wks.Cells(i, j) = Sin(i * j _ / nRows) Next j

Next i

16

Programming Assignment Six

? See online assignment for details ? Fit straight line to

experimental data

? Workbook pa6Start.xlsm has data and shell for function

? Equation of fitted line:

? Equations to use for assignment:

y

1 N

N i 1

yi

x

1 N

N i 1

xi

N y^i y 2

R2 1

i 1

N

yi2 N

2

y

i 1

N

xi yi N (x)( y)

b

i 1 N

xi2 N (x)2

i1

a y bx

17

Programming Assignment Six II

? Download workbook pa6Start.xlsm with VBA shell for array function

? Create function that reads worksheet data into type Variant arrays x and y

? Copy approach used in lectures

? Do calculations for a, b, and R2 using this array data

? Use worksheet shell statements to create your own array function

18

ME 209 ? Computer Programming for Mechanical Engineers

3

More Programming with Arrays

April 18, 2017

Assignment Six Code Shell

Function _ ( ) as Variant

Dim output(1 To 3, 1 To 2) As Variant

output(1, 1) = "Slope = " output(2, 1) = "Intercept = " output(3, 1) = "R-squared = " output(1, 2) = output(2, 2) = output(3, 2) = = output End Function

19

Program Design

? All equations shown below have sums

of data (or calculations from data) over

same set of (xi, yi), i = 1, N

? All calculations can be done in a single

For

loop

except

for

R2

numerator

N y^i

y 2

i 1

y

1 N

N i 1

yi

x

1 N

N

xi

i 1

N y^i y 2

N

xi yi N (x)( y)

b

i 1 N

xi2 N (x)2

i 1

a y bx

R2 1

i 1

N

yi2 N

2

y

i 1

20

Array Functions

? An Excel array function is one which produces values in two or more cells

? Example is Linest regression function ? Select multiple cells (here D1:E5) for

output, enter formula, and press Ctrl+Shift+Enter

21

Result of Linest Array Function

Output results from array formula

? See Help for Linest function to get meaning of all results for statistical line fit of this function

Input range from previous slide

Fitted line for data is

0.74 2.08

With R2 = 0.991557

22

ME 209 ? Computer Programming for Mechanical Engineers

4

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download