INF 110 Challenges Chapter 5 Excel  VBA Programming

Challenges Chapter # 5          Excel 2007 User Instructions

                                     Excel 2010 & 2013 User Instructions

Challenge 5 Page 211 Excel VBA Programming Textbook

Do step 5 to produce a workbook named challengesChapter5ExcelLastName.xlsm.

General Guidelines: Set up a variable for sheet number, row number, and column number, use 3 For-Next loops, one to step through each of the 3 sheets, then use two nested loops to go through the 5 rows and 5 columns of each sheet (cells A1:E5) to place the identifier string into each cell. If you have completed challenge 5 from the chapter 4, you can copy the code from part 1 and revise it to get the required code.

Flowchart: Be sure to create a flowchart for challenge 5 on page 211 by using the PowerPoint shapes symbols. You could revise the flowchart from challenge 5 in Excel 4 Challenges to create the necessary flowchart for challenge 5. Use the same flowchart from challenge 5 Excel chapter 4 (with 3 loops) to create the flowchart for challenge 5 Excel 5 just change some processing boxes in the previous flowchart. Save the flowchart slides in a PowerPoint file named FlowchartsChallengesChapter5ExcelLastName.pptx. Send me this file as an email attachment at least 3 days before the assignment is due so that I can check it for you. Submit this file in Blackboard together with your Excel workbook files.

Hints and corrections:

  1. In step 5 on page 211, open a workbook with the default 3 worksheets (Sheet1, Sheet2, and Sheet3); put the code into the SelectionChange event of the 1st worksheet ("Sheet1") since no control is mentioned in the directions in the book. Use the Autofit method in your coding for each column containing data, to best-fit the string data in cells A1:E5 of each worksheet. Use the With / End With structure to change the following Font properties of cells A1: E5 as listed below. See p174 in book as guideline. Put this code between the last two Next statements in your program.

    .Bold = True
    .Name = "Times New Roman"
    .Size = 12
    .Color = vbRed

    Use the following code as a guideline to display the sheet name and the cell position. Use 3 For-Next loops to set-up the sheet index (1-3), and the row and column indexes (1-5). Use the code from chapter 4 on 3 dimensional arrays as a guideline for the 3 For-Next loops, but remember the indexes for sheet number, row number, and column number are one-based not zero-based. These For-Next loops must surround the code listed below with the loop for the sheet number as the outer-most loop. All three sheets should be formatted using the code listed so the code needs to be placed at the bottom of the outer-loop between the two last Next statements.

    Sheets(intSheetNumber).Cells(intRowNumber, Chr(intColNumber + 64)).Value = _
    Sheets(intSheetNumber).Name & ":$" & Chr(intColNumber + 64) & "$" _
    & Str(intRowNumber)
     

Sample Spreadsheet:

Students should submit the electronic versions of the three files using the Assignment link, send the  PowerPoint file with the flowcharts for the challenges named (FlowchartsChallengesChapter5ExcelLastName.pptx), send the challenges workbook named  (challengesChapter5ExcelLastName.xlsm), together with the (In-ChapterExcel5LastName.xlsm), each spreadsheet in the In-Chapter Excel workbook should use the following names (AddWorkbooksp167, ApplicationObjectp164, SelectingWorksheetsp173-174, RangeObjectp174, WithEndWithp178, TotalSelectionRandomNumbers, SoundWindowsMediaPlayer, SoundWindowsAPIp208-209), reflecting the page in the text from which the work comes.
Substitute your last name for LastName listed in file names above

Note: Steps to submit your work: Submit the three files by clicking on the Assignments link in the left pane then click Exer5 link in the right pane, Choose the challengesChapter5ExcelLastName.xlsm  workbook, add another file named FlowchartsChallengesChapter5ExcelLastName.pptx, then click Submit.
Note: both the Flowcharts and Challenges workbook are both submitted under the Exer5 link.

Next click on the InChap5 link. Choose the In-ChapterExcel5LastName.xlsm workbook, then click Submit.

Please remember to put comments in the top of the code for the challenges see slide 10 in Excel1 PowerPoint slide for example.