1 / 29

Bullet-Proofing your Excel Spreadsheets

Bullet-Proofing your Excel Spreadsheets. Tech Day 2013 Presentation. Outline. Overview Data Protection Why Use Macros? Enabling Macros, and a Warning Recording a Macro Capturing a Formula in a Macro Resources. Protecting data from errors. Data errors can occur in many ways:

derex
Download Presentation

Bullet-Proofing your Excel Spreadsheets

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Bullet-Proofing your Excel Spreadsheets Tech Day 2013 Presentation

  2. Outline • Overview • Data Protection • Why Use Macros? • Enabling Macros, and a Warning • Recording a Macro • Capturing a Formula in a Macro • Resources

  3. 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

  4. Where is it found? Select the cells/rows/columns that you want to validate, then

  5. How to Configure

  6. What is the purpose of macros? • Manipulate data • Apply style and formatting

  7. How would i ever use macros? • Manipulating research data

  8. How would i ever use macros? • Classroom gradebook

  9. How would i ever use macros? If you are repetitively performing the same action in excel, then it is wise to use a macro

  10. Enabling macro creation • To execute or work with macros, you must enable the Developer tab: , then

  11. 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

  12. How do i make a macro? • Open sample excel file

  13. How do i make a macro? • Click on the “Developer” tab or

  14. 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 Relative Reference

  15. How do i make a macro? • If want relative reference click “Relative Reference” • Click “Record”

  16. How do i make a macro? Name your Macro Click “OK” Create shortcut for macro

  17. How do i make a macro? Record your Macro!

  18. How do i make a macro? • Click “Stop” when finished

  19. How do i run my macro? • When open Excel click either • “Macros” • Macros command • (eg. Option + Command + A) “Run”

  20. 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!!!!

  21. How to create a new formula?

  22. How to create a new formula?

  23. How to create a new formula?

  24. How to create a new formula?

  25. How to create a new formula?

  26. How to create a new formula? Demonstration

  27. How to create a new formula? Function Area(Length As Double, Width As Double) Area = Length * Width End Function

  28. Resources • Tips on Data Validation • http://office.microsoft.com/en-us/excel-help/apply-data-validation-to-cells-HP010072600.aspx • Microsoft Excel Recording Macros • http://office.microsoft.com/en-us/excel-help/overview-RZ102337714.aspx?CTT=1&section=1 • Excel for Dummies • http://www.dummies.com/how-to/computers-software/ms-office/Excel/Excel-2013/Macros-Programming.html

  29. Resources • Writing Your First VBA Function in Excel • http://www.fontstuff.com/vba/vbatut01.htm • Writing Your Own Functions In VBA • http://www.cpearson.com/excel/writingfunctionsinvba.aspx

More Related