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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- csun fall 2019 start date
- csun academic calendar spring 2020
- csun fall 2019 classes
- csun fall 2020 registration
- csun fall 2020 start date
- csun open classes fall 2019
- csun 2020 2021 academic calendar
- csun 2020 fall calendar
- csun academic calendar 2019 2020
- csun academic calendar 2020 21
- csun academic calendar 2020
- csun calendar 2020