MIS 462 Summer2022 Summative Assignment

600 points

VBA Subs and Functions, Chapters 3 to 7

Do all of these problems in a single Excel Workbook.
Solve each problem on a separate Worksheet.
Your Excel File must be setup like you did in Formative01 with appropriate Worksheet tab names.

(200) When done, upload your file, named Summative08.xlsm, (this is to be a Macro-Enabled workbook) to the D2L Assignment Folder 'Summative08'.

Before beginning this homework, you are strongly advised to first read Chapters 3 to 15.

Chapter 3 Programming Fundamentals

(50) 1. Briefly describe the difference in these three VBA statements:
Dim X as Integer = 300
Const X as Integer = 300
Dim X(300) as Integer

(50) 2. Briefly describe the purpose of the VBA "UCase" function. Hint/note: this is not to be confused with the Excel 'Upper' function!

(50) 3. Below write the construct used to perform an action on all objects in a collection.

(50) 4. Briefly describe the difference between a 'Do-While' and a 'For-Next' loop.
In other words, how do you decide to use one or the other?

Chapter 4 Sub Procedures

Review this chapter, pay special attention to the development of the worksheet sorting application.

(100) 5. Develop a sub procedure "Sub WorksheetCount".
This sub procedure must:

• count the number of worksheets in the current workbook and displays a message box stating this fact.
• uses a keyboard shortcut of Ctrl+Shift+C
• if there is not an active worksheet perform error handling
  
  Copy and paste your code below.

Chapter 5 Function Procedures

User Defined Functions

(50) 6. Create a user defined function that returns the cosine of a random number.
Paste your function in the space provided below.

Chapter 7 Programming Examples and Techniques

Working with Ranges

(50) 7. (see page 242) Modify the code for the 'Select By Value.xlsm' worksheet to select cell values that are greater than 100.
Make sure to add the macro and worksheet to your Summative07.xlsm file.

Paste your modified code below. Do not provide the entire code module, just the modified line(s).