ISOM 3230 - Practice Questions

Note that you should delcare variables with meaning names, and indent your programme statements to improve readability.

The symbol (*) before a question indicates that the question is deemed too difficult for ISOM 3230. These, however, are foundation problems in learning general programming. If you cannot finish these questions, you may skip it for the final exam and ask a firend who have learnt programming later.

Section 1 - Data Type and Operators

  1. Convert the following algebraic equations into VBA statements. Assume all variables have been probably declared.
    a. s=ut+12gt2s=ut+\frac{1}{2}gt^2
    b. x=1+b+cdex=\frac{1+b+c}{d-e}
    c. y=(A+B)(A2AB+B2)y=(A+B)(A^2-AB+B^2)

  2. Write down the data type and the result for each of the following expressions.

    Expression Data Type Result
    84+3+2
    4+12/5
    5-10 Mod 3
    3+4+6.0
    1+2*3/4
    384 mod 10
    384/10 mod 10
    0 mod 8
  3. Write a VBA programme to produce the following: the user is prompted to enter an integer twice, and then show the following. Below is a smaple output.

18 + 7 = 25
18 - 7 = 11
18 * 7 = 126
18 \ 7 = 2
18 Mod 7 = 4

Fill in the blanks.

Sub Q1_3()
    Dim a As Integer, b As Integer

    a = InputBox("Input Integer a")
    b = InputBox("Input Integer b")

    Debug.Print a & " + " & b & " = " & ______________
    Debug.Print a & " - " & b & " = " & ______________
    Debug.Print a & " * " & b & " = " & ______________
    Debug.Print a & " \ " & b & " = " & ______________
    Debug.Print a & " Mod " & b & " = " & ______________
End Sub
  1. Given that 1 inch equals 2.54 cm and there are 12 inches in a foot, write a VBA program which converts the height of aperson from cm to feet and inches. You may assume that the user inputs only integral values of height in cm. Below is a smaple output in the immediate window.
You are 5 feet 11 inch(es) tall.

You can use an input box to prompt the user to input a value.

Section 2 - Types of Error

  1. Below shows a VBA programe that claculates the sum of integers counting from 1 to 100. That is, 1+2+3+4+5++100=5,0501+2+3+4+5+\dots +100=5,050
Sub Q2_1()
    Dim i As Integer
    Dim sum As Intger

    i = 0
    sum = 0

    Do While i > 100
        sum = sum + i
        i = i + 1
    Loop

    Debug.Print "The sum is " & sum & "."
End Sub

Debug and state the types of errors in the above programme. (Hint: There are two errors.)

  1. What error will occur if the character “A” is inputted into the following VBA programme?
Sub Q2_2()
    Dim mark As Integer

    mark = InputBox("Need Integer")

    Debug.Print mark
End Sub

Section 3 - Selection Statements

  1. What is the expected output of the following programme?
Sub Q3_1()
    Dim x As Integer
    Dim y As Integer
    Dim z As Integer
    x = 3
    y = 2

    z = x - y

    If (x < 0) Or (z > 0) Then
        Debug.Print x & y & z
    Else
        Debug.Print z & y & z
    End If
End Sub
  1. Below is an excrept of a programme. If the values of a, b, and c are 1, 4, and 6, respectively, what is the expected output of the excrept?
    If Not (a > 3) And (b + c > a) Then
        Debug.Print c & b & a
    Else
        Debug.Print a & b & c
    End If
  1. Consider the following VBA programme segment:
    If (x > 10) And (x < 20) Then
        y = 1
    ElseIf x = 10 Then
        y = 2
    ElseIf (x >= 0) And (x < 10) Then
        y = 3
    Else
        y = 4
    End If

Replace the segment with a select ... case statement.

  1. Write a VBA Programme which inputs three real numbers (using double) and displays the greatest number amongst them, assuming that the three integers are not in any order.

Section 4 - Iteration Statements

  1. What is the output of the following VBA programme?
Sub Q4_1()
    Dim i As Integer, j As Integer

    For i = 1 To 3
        For j = 1 To 3
            Debug.Print i & j
        Next j
    Next i
End Sub
  1. What is the output of the following VBA programme?
Sub Q4_2()
    Dim n As Integer, s As Integer

    s = 50
    n = 1

    Do
        n = n * 2
    Loop Until (n > s)

    Debug.Print n
End Sub
  1. The following programme segment outputs 8 ‘*’ in the immediate window.
Sub Q4_3()
    Dim i As Integer

    Do
        i = i + 1
        Debug.Print "*"
    Loop Until <condition>
End Sub

Write a Boolean expression to replace <condition>.

  1. By using only one for loop, write a VBA Programme which sums up all the integers between two input numbers inclusely, and display the result in a message box. For example, if the user inputted 5 and 10, the result should be 5+6++10=455+6+\dots+10=45.
    Note that you cannot assume that the first input is smaller than the second input; you need to write lines to adjust accordingly.

Section 5 - Subroutines and Functions

  1. Consider the following VBA programme. If Q5_1_main is run, what is the output?
Option Explicit
Dim num As Integer

Sub Q5_1_main()
    num = 10
    Q5_1_side num
    Debug.Print num
End Sub


Sub Q5_1_side(ByRef x As Integer)
    x = x + 1
    Debug.Print x
End Sub
  1. Consider the following VBA programme. If Q5_2_main is run, what is the output?
Option Explicit
Dim n As Integer

Sub Q5_2_main()
    n = 3
    Q5_2_cal n
    Debug.Print n
End Sub

Sub Q5_2_cal(ByRef x As Integer)
    Dim y As Integer

    y = x
    x = y * 2 + 1
    x = x + y
End Sub
  1. Refer to question 2. Rewrite Q5_2_cal as a function, and make subsequent changes in Q5_2_main.

  2. (*) Consider the following VBA programme. If Q5_4_main is run, what is the output?

Sub Q5_4_main()
    Debug.Print F(6)
End Sub

Function F(ByVal n As Integer) As Integer
    If n = 1 Then
        F = 1
    Else
        F = F(n - 1) * 2
    End If
End Function
  1. (*) A non-negative interger is a prime number if it has only two factors: one and the number itself.
    (a) Write a function checkPrime. The function accepts an integer paramater n which is greater than one and returns a Boolean result. If n is a prime number, the function returns TRUE; otherwise, it returns FALSE.

    (b) Write a VBA programme which uses the function checkPrime to print all the prime number between 2 and 100.

Section 6 - Array

  1. What is the output of the following VBA programme?
Sub Q6_1()
    Dim Num(1 To 10) As Integer

    Num(1) = 7
    Num(2) = 6
    Num(3) = 4
    Num(4) = 3
    Num(5) = 5
    Num(6) = 2
    Num(7) = 14
    Num(8) = 11
    Num(9) = 9
    Num(10) = 1

    Debug.Print Num(Num(7) \ Num(5))

End Sub
  1. What is the output of the following VBA programme?
Sub Q6_2()
    Dim Num(1 To 10) As Integer, i As Integer

    Num(1) = 2
    Num(2) = 3
    For i = 3 To 10
        Num(i) = Num(i - 1) + Num(i - 2)
    Next

    Debug.Print Num(6)
End Sub
  1. What is the output of the following VBA programme?
Option Explicit
Option Base 0

Sub Q6_3()
    Dim num(5) As Integer, i As Integer, max As Integer

    For i = LBound(num) To UBound(num)
        num(i) = (i - 1) ^ 2
    Next

    max = num(LBound(num))

    For i = LBound(num) + 1 To UBound(num)
        If num(i) > max Then
            max = num(i)
        End If
        Debug.Print max
    Next
End Sub
  1. A programme allows users to input the English scores of 20 studetns and display the number of students in each of the following score ranges: 0 - 59, 60 - 69, 70 - 79, 80 - 89, 90 - 100. The following is a sample output.
90-100: 9
80-89: 9
70-79: 1
60-69: 1
 0-59: 0

Complete the following programme by filling in the blanks.

Sub Q6_4()
    Const n = 20

    Dim sRange(1 To 5) As Integer
    Dim sName(1 To 5) As String
    Dim score(1 To n) As Integer
    Dim i As Integer, k As Integer

    For i = 1 To _____________
        sRange(i) = 0
    Next

    sName(1) = "90-100"
    sName(2) = "80-89"
    sName(3) = "70-79"
    sName(4) = "60-69"
    sName(5) = " 0-59"

    For i = 1 To _____________
        score(____) = InputBox("Enter Score")
        Select Case score(i)
            Case Is >= 90
                k = 1
            Case Is >= 80
                k = 2
            Case Is >= _____________
                k = 3
            Case Is >= 60
                k = 4
            Case Else
                k = 5
        End Select
        sRange(k) = __________________________
    Next

    For i = 1 To 5
        Debug.Print _______________________________________
    Next
End Sub
  1. Write a subroutine that takes an integer array. The subrouting will update the array by multiplying each element by 2.

  2. (*) A Fibonacci seqence is given with the first two terms as 1, and the other terms as the sum of the previous two terms: F1=1F_1 = 1, F2=1F_2 = 1, F3=F1+F2=2F_3 = F_1 + F_2 = 2.
    (a) Write a function nthFibonacci. The function accepts an integer parameter n which is greater than 0 and returns the n-th Fibonacci number FnF_n.
    (b) Using nthFibonacci, write a function sumOfFibonacci. The function accepts an integer parameter n which is greater than 0 and returns the sum of the Fibonacci sequence up to the n-th fibonacci number.
    (c) Optimize the nthFibonacci function by doing the following: store the n-th fibonnaci number into a global array so that the programme needs not to re-calculate any fibonnaci number.

Option Explicit
Option Base 1
Global fibonacciNumber() As Long

Sub Q6_6()
    ReDim fibonacciNumber(1)
    Debug.Print sumOfFibonacci(40)
End Sub

Function nthFibonacci(ByVal n As Integer) As Long
    If UBound(fibonacciNumber) >= n Then
        nthFibonacci = fibonacciNumber(n)
        Exit Function
    End If
    ReDim Preserve fibonacciNumber(1 To n)
    If n = 1 Or n = 2 Then
        nthFibonacci = 1
        fibonacciNumber(n) = 1
    Else
        nthFibonacci = nthFibonacci(n - 1) + nthFibonacci(n - 2)
        fibonacciNumber(n) = nthFibonacci
    End If
End Function

Function sumOfFibonacci(ByVal n As Integer) As Long
    Dim i As Integer

    sumOfFibonacci = 0

    For i = 1 To n
        sumOfFibonacci = sumOfFibonacci + nthFibonacci(i)
    Next
End Function

Section 7 - Objects and Classes (Collections) in VBA

  1. Consider an active workbook. Write a VBA programme with a single for loop to output names of all worksheets of that workbook.
  2. Refer to question 1. Now you have opened a number of workbooks. Write a VBA programme to output names of all worksheets of all workbooks.
  3. Write down the expresson used to close the first workbook.
  4. You downloaded the data for a particular stock. Row 1 contains the title of each column, including “Opening”, “High”, “Low”, and “Close”. However, you do not know their order.
    a) Write a programme segment to find out the cell with the word “Close” in it. You should save the range into an object variable called closingTitle. Also, there is a chance that your downloaded file is corrupted, and the word “Close” is not in row 1. Write a statement to checkt if the target has been found.
    b) With closingTitle, store the closing price of the stock into a dynamic array data. Note that you do not know how many records are there.
    c) Refer to part b. Store the closing price of the stock into a static array dataStatic instead.
    d) Bold and underline the title in row 1.
    e) In row 1, find a column with the label “stockSplit”. Clear everything in that column.
  5. Write down the equivalent address of the following expression.
    a) Range("A1").offset(3,7)
    b) Cells(5,6).offset(2,-5)
    c) Range("C5:D9").cells(3)
    d) Range("B2").Offset(2,3).Cells(5,4)