Algorithms and Visual Basic programming

1
Algorithms and Visual Basic programming

• DGD 9

2

• Q1. Mike Raymond operates Uncle Mikes Used Car
  Lot. Mike has developed a formula to help him
  estimate the current value of any of his
  inventory of used cars. The formula is
• V C1 x C2 x C3 x P
• where
• V is the present value of the car
• P is the original selling price (i.e., when the
  car was new)
• where A is the age of the car in years
• C2 is a factor that reflects the cars mileage
• C2 1.2 if mileage is low
• C2 1.0 if mileage is average
• C2 0.7 if mileage is high
• C3 is a factor that reflects the cars overall
  condition
• C3 1.1 if the condition is excellent
• C3 1.0 if the condition is good
• C3 0.8 if the condition is poor

3
(No Transcript)
4

• Sub P_Value()
• Dim Price As Currency, Mileage As String
• Dim Age As Single, Condition As ____ String _____
• Dim C1 As Single, C2 As Single, C3 As Single
• Dim row As Integer
• Worksheets(Mike).Activate
• row ____________________ (will be discussed in
  the next slides)
• Price Cells(row, 2)
• Age Cells(row, 3)
• Mileage Cells(row, 4)
• Condition Cells(row, 5)
• C1 __(2 / (Age 2)) 0.5________
• If Mileage ___ low" _______ Then
• C2 1.2
• ElseIf Mileage ____ "average" ___________ Then
• C2 1

5

• If Mileage ___ low" _______ Then
• C2 1.2
• ElseIf Mileage ____ "average" ___________ Then
• C2 1
• Else _____ C3 0.8________
• End If
• If Condition ___ "excellent"____ Then
• C3 1.1
• ElseIf Condition ____ "good"______ Then
• C3 1
• Else _____ C3 0.8 _______
• End If
• Cells(row, 7) ____C1 C2 C3 Price_________
• End Sub

6
1a) To fill predetermined row when the sub
P_Value() is activated (for example row 5)

• Option Explicit
• Sub P_Value()
• Dim Price As Currency, Mileage As String
• Dim Age As Single, Condition As String
• Dim C1 As Single, C2 As Single, C3 As Single
• Dim row As Integer
• Worksheets("Mike").Activate
• row 5
• Price Cells(row, 2)
• Age Cells(row, 3)
• Mileage Cells(row, 4)
• Condition Cells(row, 5)

• C1 (2 / (Age 2)) 0.5
• If Mileage "low" Then
• C2 1.2
• ElseIf Mileage "average" Then
• C2 1
• Else
• C2 0.7
• End If
• If Condition "excellent" Then
• C3 1.1
• ElseIf Condition "good" Then
• C3 1
• Else
• C3 0.8
• End If
• Cells(row, 7) Round((C1 C2 C3 Price), 2)

7
1b) To fill the specific number of rows when the
sub P_Value() is activated

• Option Explicit
• Sub P_Value()
• Dim Price As Currency, Mileage As String
• Dim Age As Single, Condition As String
• Dim C1 As Single, C2 As Single, C3 As Single
• Dim row As Integer, I as integer
• Worksheets("Mike").Activate
• For I 5 to 12
• row I
• Price Cells(row, 2)
• Age Cells(row, 3)
• Mileage Cells(row, 4)
• Condition Cells(row, 5)

• C1 (2 / (Age 2)) 0.5
• If Mileage "low" Then
• C2 1.2
• ElseIf Mileage "average" Then
• C2 1
• Else
• C2 0.7
• End If
• If Condition "excellent" Then
• C3 1.1
• ElseIf Condition "good" Then
• C3 1
• Else
• C3 0.8
• End If
• Cells(row, 7) Round((C1 C2 C3 Price), 2)

8
1c) To fill all the rows until the first empty
cell when the sub P_Value() is activated

• Option Explicit
• Sub P_Value()
• Dim Price As Currency, Mileage As String
• Dim Age As Single, Condition As String
• Dim C1 As Single, C2 As Single, C3 As Single
• Dim row As Integer, I as integer
• Worksheets("Mike").Activate
• Row 5
• Do Until(IsEmpty(Cells(Row,1)))
• Price Cells(row, 2)
• Age Cells(row, 3)
• Mileage Cells(row, 4)
• Condition Cells(row, 5)

• C1 (2 / (Age 2)) 0.5
• If Mileage "low" Then
• C2 1.2
• ElseIf Mileage "average" Then
• C2 1
• Else
• C2 0.7
• End If
• If Condition "excellent" Then
• C3 1.1
• ElseIf Condition "good" Then
• C3 1
• Else
• C3 0.8
• End If
• Cells(row, 7) Round((C1 C2 C3 Price), 2)

9
1d) To fill a row correspond to the active row
when the sub P_Value() is activated

• Option Explicit
• Sub P_Value()
• Dim Price As Currency, Mileage As String
• Dim Age As Single, Condition As String
• Dim C1 As Single, C2 As Single, C3 As Single
• Dim row As Integer
• Worksheets("Mike").Activate
• row ActiveCell.row
• Price Cells(row, 2)
• Age Cells(row, 3)
• Mileage Cells(row, 4)
• Condition Cells(row, 5)

• C1 (2 / (Age 2)) 0.5
• If Mileage "low" Then
• C2 1.2
• ElseIf Mileage "average" Then
• C2 1
• Else
• C2 0.7
• End If
• If Condition "excellent" Then
• C3 1.1
• ElseIf Condition "good" Then
• C3 1
• Else
• C3 0.8
• End If
• Cells(row, 7) Round((C1 C2 C3 Price), 2)

10
(No Transcript)
11

• Q2. When and where do you use each of the
  following?
• Dim x As Integer
• To define variables within the body of the
  function or procedure.
• x As Integer
• To define the formal parameters in the function
  or procedure
• ByRef x As Integer
• We use byRef when we define the formal parameters
  in the function or procedure in order to change
  the value of the actual parameters.
• (IF THE ACTUAL PARAMETER IS AN EXPERISSION, THEN
  THERE IS NO EFFECT FOR BYREF)
• ByVal x As Integer
• We use byVal when we define the formal parameters
  in the function or procedure in order not to
  change the value of the actual parameters.

12
Q3

• Sub Main()
• Dim a As Integer, c As Integer
• Dim Result As Single, b As Single
• Const d 1.5
• a 1
• b 2
• c 3
• Result Apples(a, b d, c)
• MsgBox ("a " a " b " b " c " c "
  Result " Result)
• Call Oranges(b d, Result, a)
• MsgBox ("a " a " b " b " c " c "
  Result " Result)
• End Sub
• Function Apples(x As Integer, y As Single, ByValz
  As Integer) As Single
• x 2 x
• y 2 y
• z 2 z
• Apples x y z

13

• Q. What is the significance of being able to pass
  parameters between procedures?
• Divide tasks into smaller units
• Need only definition/Givens/Results not method
• Names can change