Exercise 7 - Recording and using macros

The following exercises deal with recording macros and using them in combination with different tools.

1. You will use an existing weather sheet saatila7.xls as a base for the exercises. Save it to dive U an open it in Excel. A macro warning will appear as there are some macros in the workbook already. Enable macros as you will need them later on. Some vocabulary:
   • PVM = date
   • Lämpötila = temperature
   • Pilvisyys = overcast
   • Sää = weather
   • Minimi = minimum
   • Maksimi = maximum
   • Anna lämpötila = insert temperature
   • Koko = overclouded
   • Puoli = semiclouded
   • Selkeä = clear
   • Lumisade = snow
   • Räntäsade = sleet
   • Vesisade = rain
   • Lämpötilojen keskiarvo = average temperature
2. There are two sheets, one containing the weather data and the other containing the conditions associated. There are input cells for entering the conditions according to which the contents of the other sheet are formed.
   • Find out how the condition sheet gets its contents. How is it done?
   • Also find out how entering conditions affects calculating temperature means!
   • The whole sheet is protected except for the cells where the user can put in data. These input cells have a separate, unprotected style. Find out how the style is made.
3. Make a macro for inserting, editing or deleting weather data. Follow the plan and instructions below:
   • Plan
     • Unprotect the sheet.
     • Activate one cell in the weather data sheet.
     • Open a data form (Data | Form).
     • Close the form.
     • Reprotect the sheet.
   • Instructions
     • Start recording the macro by selecting Tools | Macro | Record New Macro
     • Name the macro as Edit_weather.
     • Then follow the plan to record.
     • Stop recording by selecting Tools | Macro | Stop Recording or by clicking Stop recording button on the Stop Recording toolbar.
   • Read the code of the macro and try to understand what is in it. To view the code, select Tools | Macro | Macros , activate the macro on the window that opens and click the Edit button. Use the macro plan as a reference when studying the code.
4. Assign the new macro to a button as follows:
   • First you need to unprotect the worksheet as it is not possible to add a button to a protected area.
   • Open the Forms toolbar by selecting View | Tools | Forms .
   • Select Button on the toolbar.
   • Click the left mouse button on the sheet or drag an area of the size of the button on the sheet: An Assign Macro window will open.
   • Select Edit_weather for the macro to be assigned and close the window by clicking OK.
   • Use the right mouse button to change the name of the macro button to a more illuminating one.
   • Add Don't move or size with cells property for the button. The size and location of the button are then fixed even if the sheet under it changes (Excel 2000). The Format Control command on the right mouse button menu is for editing the properties of the button. This property in question is on the Properties tab. If the macro button is activated, click the right mouse button on the "grey" edges of the button.
5. Then create a macro that sorts the weather data in ascending order according to the temperature. Follow the plan below. Name the macro Sort_weather.
   • Plan and instructions
     • Unprotect the sheet.
     • Select the area to be sorted, thus all the colums with weather data.
     • Sort the columns in ascending order accoring to the Lämpötila (temperature) column.
     • Reprotect the sheet.
   • Read the code of the new macro and try to understand it. Use the plan as a reference.
   • Assign the new macro to a button to try if it works.
6. Plan and create a macro that filters weather data that fills the conditions given:
   • Use the Advanced Filter tool.
   • Create a button for the macro on the worksheet.
   • Try your macro!
   • Edit the macro so that the area under filtering is a named range saatila (weather) and the condition range is a named range ehdot (conditions). Make sure you have these named ranges on your workbook.
7. Plan and create a macro that removes the filter created above:
   • For removing filters one selects Data | Filter | Show All.
   • Create a button for the macro on the worksheet.
   • Try your macro!
8. Plan and create a macro for editing, inserting and deleting student data on the Kurssi (course) worksheet.
   • You can create this macro similarly to the Edit_weather macro.
   • Create a button for the macro on the worksheet.
   • Try your macro!
9. Plan and create a macro:
   • This macro is for updating the chart on the Kurssi worksheet after adding students. The chart shows the points of the students and the students are the series which is why adding a student does not automatically update the chart.
   • When recording the macro, update the Source Data area of the chart to fit the new area including the new student.
   • Afret recording the Source Data updating macro, edit it to use two named ranges (Nimet, Tehtavat) (=Names, Problems/Exercises). The student table is divided in two parts, Nimet containing the names of the students and Tehtavat containing their points. Updating the chart makes these definitions necessary. Even if you would add these ranges when recording the macro you would still have to edit the code.
   • The change needed looks like this:

         ActiveChart.SetSourceData Source:=Sheets("Kurssi"). _
         Range("Nimet, Tehtavat"), PlotBy:=xlRows
     

   • Create a button for the macro on the worksheet.
   • Try your macro!
10. Merge the update macro to the student-inserting macro. You can copy the code of the previous macro to the latter. Carefully test the combination macro!
11. There are macros called suojaukset_pois (unprotect) and suojaukset_paalle (protect) in your workbook. Try the macros on every worksheet.

Additional exercises

In the following additional exercises you can use the help of the program or brief VBA basic instructions.

1. Try the taulukko (table) macro of the workbook used above. It transforms an activated Excel table to a HTML table. The macro has a shortcut key combination CTRL-SHIFT-C.
2. Try the two self made functions omasumma and sarja in the workbook. The functions are presented both in the VBA basic instructions and on the lecture notes.
3. Create a function accoring to the plan below:
   • The function is to calculate 2X+4Y+Z.
   • Variables X, Y and Z can be given to the function as cell references.
4. Create a function accoring to the plan below:
   • The function is to combine a column index and a row index to a cell reference.
   • For example, combine the letter C in cell A1 to the number 4 in cell A2 to create a cell reference C4.
   • This function can in practice combine any two distinct character strings.