Northeastern University VBA in MS Excel Project

Description

3 attachmentsSlide 1 of 3attachment_1attachment_1attachment_2attachment_2attachment_3attachment_3.slider-slide > img { width: 100%; display: block; }
.slider-slide > img:focus { margin: auto; }

Unformatted Attachment Preview

Youngbok Ryu
Excel VBA
VBA setting in MS Excel
?
?
?
File-Options-Trust Center-Trust Center Settings-Macro Settings
File-Options-Trust Center- Trust Center Settings-Protected View
File-Options-Customize Ribbon-Developer
Implementing VBA
?
?
Developer-Visual Basic or Alt+F11
Insert-Module
o Example 1: Sub… End Sub
Sub Test1()
MsgBox “Hello VBA”
End Sub
o Example 2: If… Then… Else… End If
Sub Test2()
If MsgBox(“Have you used VBA before?”, vbYesNo) = vbYes Then
MsgBox “You are good at VBA”
Else
MsgBox “You are a novice”
End If
End Sub
o Example 3: Dim… As Long
Sub Test3()
Dim lngT As Long
lngT = 10 + 20
MsgBox “10 + 20 = ” & lngT
End Sub
Youngbok Ryu
o Example 4: For… Next
Sub Test4()
Dim i As Long
For i = 1 To 10
Cells(i, 1) = i
Next
End Sub
o Example 5: Clear entire sheet
Sub Test5()
Cells.Clear
End Sub
?
Macro Buttons for Examples 4 and 5
Linear Programming in VBA
Refer to “Excel_Example Week6.xlsm” file. Note that the extension is *.xlsm, which is different
from ordinary Excel file extension (*.xlsx).
?
Refer to “LP_2Inputs_VBA” sheet
Sub Button1_Click()
Dim i As Integer
For i = 1 To 18
Range(“B22”) = i
SolverSolve UserFinish:=True
Range(“F” & i + 1) = 1 / Range(“E22”)
Next i
End Sub
Note: “SolverSolve” establishes a reference to the Solver add-in; “UserFinish:=True” returns the
Solver results without displaying the Solver Results dialog box.
Youngbok Ryu
?
Refer to “LP_Airports_WD” sheet
Sub Button2_Click()
Dim i As Integer
For i = 1 To 8
Range(“C11”) = i
SolverSolve UserFinish:=True
Range(“K” & i + 1) = Range(“j14”)
Range(“J2:J9”).Copy
Range(“L” & i + 1).Select
Selection.PasteSpecial Transpose:=True
Next i
End Sub
Agency
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
N
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Input 1
12,247.45
53,888.77
34,292.86
7,348.47
19,595.92
58,787.75
14,696.94
39,191.84
19,595.92
17,146.43
12,247.69
61,237.24
9,797.96
68,585.71
44,090.82
8,573.21
12,222.95
36,742.35
Input 2
24,494.90
20,820.66
34,292.86
46,540.31
31,843.37
19,595.92
36,742.35
41,641.33
22,045.41
41,641.33
24,495.14
24,494.90
31,843.37
29,393.88
26,944.39
44,090.82
24,568.38
20,973.76
Agency
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
N
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Input 1
12,247.45
53,888.77
34,292.86
7,348.47
19,595.92
58,787.75
14,696.94
39,191.84
19,595.92
17,146.43
12,247.69
61,237.24
9,797.96
68,585.71
44,090.82
8,573.21
12,222.95
36,742.35
Input 2
24,494.90
20,820.66
34,292.86
46,540.31
31,843.37
19,595.92
36,742.35
41,641.33
22,045.41
41,641.33
24,495.14
24,494.90
31,843.37
29,393.88
26,944.39
44,090.82
24,568.38
20,973.76
Constraints
1.09
1.04
1.57
2.02
1.42
1.00
1.62
1.89
1.00
1.84
1.09
1.22
1.39
1.45
1.28
1.92
1.09
1.00
Index
18
w1
2.68584E-06
w2
4.29735E-05
Objective
1.00
Relative Score
1.0000
0.9620
0.6387
1.0000
0.7447
1.0000
0.7576
0.5278
1.0000
0.6579
1.0000
0.8216
1.0000
0.6909
0.7835
0.9487
1.0000
1.0000
Linear programming
Relative Score
N
1
2
3
4
5
6
7
8
Airport
A
B
C
D
E
F
G
H
Area
1,200
503
500
1,041
1,002
478
481
1,346
Apron
304,182
213,729
41,003
112,464
30,000
63,000
47,210
503,274
Terminal
45,600
38,778
11,800
21,050
8,000
23,000
9,300
76,370
Runway
353,610
348,120
269,955
395,730
192,330
389,115
268,995
421,305
Flights
30,707
46,875
15,608
39,871
4,887
41,088
19,010
129,153
Passengers
4,030,859
4,783,120
1,039,967
1,744,524
427,974
2,165,572
971,313
11,709,741
Cargo
74,184
19,050
1,587
4,919
1,574
5,414
3,826
39,556
N
1
2
3
4
5
6
7
8
Airport
A
B
C
D
E
F
G
H
Area
1,200
503
500
1,041
1,002
478
481
1,346
Index
8
Left
Slacks
Right
1,346
0
1346
Apron
304,182
213,729
41,003
112,464
30,000
63,000
47,210
503,274
503,274
0
503274
Terminal
45,600
38,778
11,800
21,050
8,000
23,000
9,300
76,370
76,370
0
76370
Runway
353,610
348,120
269,955
395,730
192,330
389,115
268,995
421,305
421,305
0
421305
Flights
30,707
46,875
15,608
39,871
4,887
41,088
19,010
129,153
129,153
0
129153
Passengers
4,030,859
4,783,120
1,039,967
1,744,524
427,974
2,165,572
971,313
11,709,741
11,709,741
0
11709741
Cargo
74,184
19,050
1,587
4,919
1,574
5,414
3,826
39,556
Lambdas Objective
0
0
0
0
0
0
0
0
0
0
0 6.28E-13
0
0
1
0
1
39,556 Objective
0
0
39556
A
B
1
0
0
0
0
0
0
0
C
0
1
0
0
0
0
0
0
Optimize values of slacks
D
0
0
1
0
0
0
0
0
E
0
0
0
1
0
0
0
0
F
0
0
0
0
1
0
0
0
0
0
0
0
0
1
0
0
G
H
0
0
0
0
0
0
1
0
0
0
0
0
0
0
0
1
Solve the following linear programming question for the data below and describe the results.
To that end, create a macro button using VBA.
Note: This is a two-output case. Show the objective values and relative scores of all agencies.
Agency
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
N
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Output 1
12,247.45
53,888.77
34,292.86
7,348.47
19,595.92
58,787.75
14,696.94
39,191.84
19,595.92
17,146.43
12,247.69
61,237.24
9,797.96
68,585.71
44,090.82
8,573.21
12,222.95
36,742.35
Output 2
24,494.90
20,820.66
34,292.86
46,540.31
31,843.37
19,595.92
36,742.35
41,641.33
22,045.41
41,641.33
24,495.14
24,494.90
31,843.37
29,393.88
26,944.39
44,090.82
24,568.38
20,973.76

Purchase answer to see full
attachment

Tags:
excel

IT

vba

User generated content is uploaded by users for the purposes of learning and should be used following FENTYESSAYS.COM ESSAY’s honor code & terms of service.