BulletProofing your Excel Spreadsheets Tech Day 2013 Presentation
Bullet-Proofing your Excel Spreadsheets Tech Day 2013 Presentation Margaret Gill mjgill@noctrl. edu Chris Kardaras cjkardaras@noctrl. edu Marco V. Martinez mvmartinez@noctrl. edu
OUTLINE • • Overview Data Protection Why Use Macros? Enabling Macros, and a Warning Recording a Macro Capturing a Formula in a Macro Resources Tech Day 11/2013 2
PROTECTING DATA FROM ERRORS • Data errors can occur in many ways: – manual entry – data from external sources – data transmission errors • We can make better decisions if we can detect and prevent invalid data • In Excel, this is called Data Validation Tech Day 12/2013 3
WHERE IS IT FOUND? Select the cells/rows/columns that you want to validate, then Tech Day 12/2013 4
HOW TO CONFIGURE Tech Day 12/2013 5
WHAT IS THE PURPOSE OF MACROS? • Manipulate data • Apply style and formatting Tech Day 12/2013 6
HOW WOULD I EVER USE MACROS? • Manipulating research data Tech Day 11/2013 7
HOW WOULD I EVER USE MACROS? • Classroom gradebook Tech Day 12/2013 8
HOW WOULD I EVER USE MACROS? IF YOU ARE REPETITIVELY PERFORMING THE SAME ACTION IN EXCEL, THEN IT IS WISE TO USE A MACRO Tech Day 12/2013 9
ENABLING MACRO CREATION • To execute or work with macros, you must enable the Developer tab: , then Tech Day 12/2013 10
ENABLING MACRO EXECUTION • Most Excel installations have macro execution disabled by default • Be careful if you enable macro execution – you are allowing code to run on your computer Tech Day 12/2013 11
HOW DO I MAKE A MACRO? • Open sample excel file Tech Day 11/2013 12
HOW DO I MAKE A MACRO? • Click on the “Developer” tab or Tech Day 12/2013 13
HOW DO I MAKE A MACRO? • Do you need your macro to be dependent upon coordinates, or relative? – Dependent upon coordinates – only works in coordinates recorded – Relative – dependent upon where cursor is Dependent on coordinates Tech Day 12/2013 Relative Reference 14
HOW DO I MAKE A MACRO? • If want relative reference click “Relative Reference” • Click “Record” Tech Day 12/2013 15
HOW DO I MAKE A MACRO? Name your Macro Click “OK” Create shortcut for macro Tech Day 12/2013 16
HOW DO I MAKE A MACRO? RECORD YOUR MACRO! Tech Day 12/2013 17
HOW DO I MAKE A MACRO? • Click “Stop” when finished Tech Day 12/2013 18
HOW DO I RUN MY MACRO? • When open Excel click either – “Macros” – Macros command “Run” • (eg. Option + Command + A) Tech Day 12/2013 19
HOW WOULD I EVER USE MACROS? IF YOU ARE REPETITIVELY PERFORMING THE SAME ACTION IN EXCEL, THEN IT IS WISE TO USE A MACRO FORMULAS!!!! Tech Day 11/2013 20
HOW TO CREATE A NEW FORMULA? Tech Day 11/2013 21
HOW TO CREATE A NEW FORMULA? Tech Day 11/2013 22
HOW TO CREATE A NEW FORMULA? Tech Day 11/2013 23
HOW TO CREATE A NEW FORMULA? Tech Day 11/2013 24
HOW TO CREATE A NEW FORMULA? Tech Day 11/2013 25
HOW TO CREATE A NEW FORMULA? DEMONSTRATION Tech Day 11/2013 26
HOW TO CREATE A NEW FORMULA? Function Area(Length As Double, Width As Double) Area = Length * Width End Function Tech Day 11/2013 27
RESOURCES • Tips on Data Validation – http: //office. microsoft. com/en-us/excel-help/applydata-validation-to-cells-HP 010072600. aspx • Microsoft Excel Recording Macros – http: //office. microsoft. com/en-us/excelhelp/overview-RZ 102337714. aspx? CTT=1§ion=1 • Excel for Dummies – http: //www. dummies. com/how-to/computerssoftware/ms-office/Excel-2013/Macros. Programming. html Tech Day 12/2013 28
RESOURCES • Writing Your First VBA Function in Excel – http: //www. fontstuff. com/vbatut 01. htm • Writing Your Own Functions In VBA – http: //www. cpearson. com/excel/writingfunctions invba. aspx Tech Day 11/2013 29
- Slides: 29