landscape test 2
DESCRIPTION
landscape test 2TRANSCRIPT
CO4001Data Analysis
Modelling 4Automation & catch up
What is a macro? O Small program that runs inside the Excel
application (VBA)O Allows you to:
O Record procedures that take many stepsO Subsequently run them with a keystroke or the
click of a buttonO Alter the procedure by changing the code (if
you want!)
Macro Security O Macro Security
O Enable macros O Disable macrosO Do not Open
To display Developer tab on Ribbon – from File menu
To Set Security Level
Record a Macro Example 1. Plan what you wish to record2. Do it manually to check you know what to do3. For example – using the Countries sheet we
want to sort the countries into EU and non-EU then by Population:1. Highlight required cells A4 to F492. Sort by EU descending3. Sort by Population ascending
4. Check it works (First country in list Luxembourg?)
5. Now sort on Country ascending ready to record
Record a Macro Developer>Record Macro
Run Macro CountrySort
OR CTRL + p
Developer > Macros >Run
Delete a Macro Developer > Macros > Delete
Attaching a Macro to a Button(Form Control)
Buton 1
Assign a macro to a ButtonSelect the relevant Macro > OK
Give the Button a LabelRight-click then Edit Text
Editing Macros Developer>Macros>Edit
VB Macro Code
Hide Sheet TabsFile > Options >Advanced, Scroll to Display options for worksheet, Deselect show sheet tabs, Click ok
Workshop
O Exercise 4.1 CountrySort macroO Exercise 4.2 ViewReport macroO Exercise 4.3 Menu macroO Exercise 4.4 & 4.5 LookupsO Discussion Board contribution