bigal80ak
05-01-2014, 01:58 PM
Ok so I am doing fine till I get to the last part I am completely lost. If you could figure out what he wants or give me an idea what to do I would appreciate it thanks. Might need to send you attachment so you can really understand.
1. You now select and inspect the "Section 4" worksheet and you open the VB editor to inspect the
macro code associated with this worksheet, which includes a VBA module with a "FlagQuotas" sub
procedure. This is a slightly modified version of the example presented in our courseware slides.
Claire needs your help to adjust the code to accommodate some new business rules logic that allows
for totals information. Row 16 includes a "Total MBE" ("management by exception") label and
adjacent cells designed to house a total count for each ticket holder's equal to or exceeded quota value
occurrences. For example, if the Natalie C's entries had 6 of 12 cell values flagged as being >=
quotas, a value of 6 should be assigned to the range of E16.
2. Modify the existing code in FlagQuotas accordingly to increment a counter variable for each ticket
holder, but only when one of their cell values has been flagged as being greater than or equal to a
quota.
Note that comments have been included in the sub procedure to assist you in knowing
where to insert your code and advice on how to handle the processes. Each comment begins with
"PROJECT 3 HINT…". Also, this workflow should be followed:
•
Integrate proper indenting of all statements.
•
Modify the existing comments to include your name and code creation date.
•
Declare 4 new Integer variables, suitably named (i.e. prefix each variable with "int").
•
Next, incorporate the VBA code to increment each of these 4 variables at the right time (i.e. when
the quota has been met or exceeded). This would involve creating a Select Case statement that
determines which ticket holder column the active cell is in, and then update his/her variable.
•
Once the For…Next loop completes, set each of the "Total MBE" cells (D16:G16) with its
matching variable value. Here's an example (note that sample data is shown; your values may
vary) of what Section 4 looks like after the above logic has been implemented (9 for Jack M., 6
for Natalie C., etc…)…
Page 6 of 7
3. Modify the MsgBox statement that ends the sub procedure to include the total count of MBE values.
For example, if Jack M. had 9, Natalie C. had 6, Anna M. had 5, and Bob T. had 5, the message box
would be display…
4. Test/debug your VBA code to ensure correctness. Click the "Evaluate Ticket Quotas" button to
perform your test. This button is already assigned to a macro that will assign new values to the
worksheet and call the FlagQuotas sub procedure.
5. Make sure the worksheets are in the following order (left to right):
•
Section 1
•
Section 2
•
Section 3
•
Section 4
6. Activate the "Section 1" worksheet.
7. Make A1 the active cell and ensure the Normal view is selected.
8. Repeat this process for all worksheets.
9. Activate the "Section 1" worksheet.
10. Re-save the file using the default name.
Page 7 of 7
11. Close all open files and exit Excel.
12. Upload all project file(s) via the class site's "Assignments" (digital dropbox) link. To perform the
upload, click the link. Then, follow the prompts to upload and submit your file(s). Remember to
retain your original files - do not delete them - just in case I need you to make a re-submission. After
the upload occurs, you can confirm your submission by returning to the dropbox link and reviewing
the "Status: Submitted on…" message.
13. Close any open files and exit all applications.
1. You now select and inspect the "Section 4" worksheet and you open the VB editor to inspect the
macro code associated with this worksheet, which includes a VBA module with a "FlagQuotas" sub
procedure. This is a slightly modified version of the example presented in our courseware slides.
Claire needs your help to adjust the code to accommodate some new business rules logic that allows
for totals information. Row 16 includes a "Total MBE" ("management by exception") label and
adjacent cells designed to house a total count for each ticket holder's equal to or exceeded quota value
occurrences. For example, if the Natalie C's entries had 6 of 12 cell values flagged as being >=
quotas, a value of 6 should be assigned to the range of E16.
2. Modify the existing code in FlagQuotas accordingly to increment a counter variable for each ticket
holder, but only when one of their cell values has been flagged as being greater than or equal to a
quota.
Note that comments have been included in the sub procedure to assist you in knowing
where to insert your code and advice on how to handle the processes. Each comment begins with
"PROJECT 3 HINT…". Also, this workflow should be followed:
•
Integrate proper indenting of all statements.
•
Modify the existing comments to include your name and code creation date.
•
Declare 4 new Integer variables, suitably named (i.e. prefix each variable with "int").
•
Next, incorporate the VBA code to increment each of these 4 variables at the right time (i.e. when
the quota has been met or exceeded). This would involve creating a Select Case statement that
determines which ticket holder column the active cell is in, and then update his/her variable.
•
Once the For…Next loop completes, set each of the "Total MBE" cells (D16:G16) with its
matching variable value. Here's an example (note that sample data is shown; your values may
vary) of what Section 4 looks like after the above logic has been implemented (9 for Jack M., 6
for Natalie C., etc…)…
Page 6 of 7
3. Modify the MsgBox statement that ends the sub procedure to include the total count of MBE values.
For example, if Jack M. had 9, Natalie C. had 6, Anna M. had 5, and Bob T. had 5, the message box
would be display…
4. Test/debug your VBA code to ensure correctness. Click the "Evaluate Ticket Quotas" button to
perform your test. This button is already assigned to a macro that will assign new values to the
worksheet and call the FlagQuotas sub procedure.
5. Make sure the worksheets are in the following order (left to right):
•
Section 1
•
Section 2
•
Section 3
•
Section 4
6. Activate the "Section 1" worksheet.
7. Make A1 the active cell and ensure the Normal view is selected.
8. Repeat this process for all worksheets.
9. Activate the "Section 1" worksheet.
10. Re-save the file using the default name.
Page 7 of 7
11. Close all open files and exit Excel.
12. Upload all project file(s) via the class site's "Assignments" (digital dropbox) link. To perform the
upload, click the link. Then, follow the prompts to upload and submit your file(s). Remember to
retain your original files - do not delete them - just in case I need you to make a re-submission. After
the upload occurs, you can confirm your submission by returning to the dropbox link and reviewing
the "Status: Submitted on…" message.
13. Close any open files and exit all applications.