Things To Do with MS-Excel

Here are some basic things that you should be able to do with MS-Excel to be able to complete the project for SWE 010 758 001 with the estimated effort. Of course, no actual programming experience with Excel is expected. It is clear, though, that the ability to work with MS-Excel and its programming features is key to smooth project completion.

Task 1: Basic MS-Excel Usage

Part A:

1. Create a row in Sheet1 with three columns: a number N, N divided by two, N multiplied by two. Use formulas for the two last columns.
2. Create a table by adding 4 lines below the previously done row, increment N by one for each row
3. Copy table to Sheet2, starting in cell A1
4. Save Sheet2 to ASCII file

Part B:

1. Create a table with 3 columns with headings Date, Time, and Cumulative Time.
2. Add rows to your table, using valid (different) dates for Date and numbers 1-N for Time.
3. Sort data by dates in ascending order and add to Cumulative Time -column a function so that it shows the sum of time before that row/date including the time in the same row.
4. Create a graph where the x-axis is time (dates) and y-axis is time/cumulative time.
5. Add for each date the time as a bar in the graph and cumulative time as a line.
6. Check that you can print the table and graph separately/on separate pages (print preview).

Task 2: Working with International/Regional Settings in MS-Excel

Task 3: Programming MS-Excel with Macros/VBA

Do the same tasks as in Task 1 but by programming them.

1. First try recording the manual steps and study the code
2. Try programming the tasks from scratch
3. Make sure you can control what happens in the file creation process. For example, that you can do at least the following things:

·        set the default folder and file name for the data/sheet to be saved

·        format the data so that you have decimal numbers in file, in fixed columns and with a predefined number of decimals

·        try using GetSaveAsFilename, Open, Format, and Print -methods for these tasks

·        check that changing the regional settings do not disturb you program by testing it with different settings (date, decimal separator, ...)

Links to MS-Excel resources in web

• Excel Tutorials
• VBA Tutor Lessons (you may need to use IE to access these pages)
• MSDN Online Search

Literature on MS-Excel/VBA

There are 4 new books on Excel/VBA in the library and 3 books in the lab 6428. All books are different,  some introduction to Excel, some advanced VBA etc. Try reading those if you have problems with Excel/VBA.

The books in lab 6428 mustn’t be taken away from the lab! There are supposed to stay there so that anybody who needs them will find them close to a computer.