Introduction to Programming with Excel and VBA

1. Introduction to Programming with Excel and VBA Course Overview

2. Course Goals • Learn general problem-solving skills and methods, and apply them to a process for developing computer and spreadsheet applications • Be able to create and evaluate logical expressions, and use them to direct the behavior of spreadsheets and programs • Understand the concepts of sequential, selective, modular, and repetitive task processing, and be able to apply them to build applications in Excel and Visual Basic for Applications (VBA)

3. Enhance Your Skill Sets • Problem solving skills: learn how to organize yourself to develop a process to accomplish a goal • Excel skills: learn how to use some of the advanced features of Excel to avoid repetitive tasks and build awesome spreadsheets • Logic skills: useful in Excel, in working with databases, in programming, and in plenty of other situations • Programming skills: learn the fundamentals of programming; if nothing else, be able to communicate with programmers and understand their issues

4. We Will NOT Learn • Everything about Excel. There is way, way too much. But you will learn enough that you can figure out most of the other things on your own if you want to. • Everything about programming. Programming and the things you can do with it is a whole major in itself. But you will learn enough to go on to CS 162 and see if you want to become an expert programmer.

5. Types of Learning • When we talk about “learning”, there are different things we could mean • A man named Benjamin Bloom was in charge of a group in the 1950’s that came up with a classification of types of learning, called “Bloom’s taxonomy” • There are six major categories, each dependent on the previous one • There are some extra details on the following slides for those who are interested

6. Bloom’s Taxonomy (1) • Knowledge: Recall data or information. • Examples: Recite a policy. Quote prices from memory to a customer. Knows the safety rules. • Key Words: defines, describes, identifies, knows, labels, lists, matches, names, outlines, recalls, recognizes, reproduces, selects, states. • Comprehension: Understand the meaning, translation, interpolation, and interpretation of instructions and problems. State a problem in one's own words. • Examples: Rewrites the principles of test writing. Explain in one’s own words the steps for performing a complex task. Translates an equation into a computer spreadsheet. • Key Words: comprehends, converts, defends, distinguishes, estimates, explains, extends, generalizes, gives examples, infers, interprets, paraphrases, predicts, rewrites, summarizes, translates.

7. Bloom’s taxonomy (2) • Application: Use a concept in a new situation or unprompted use of an abstraction. Applies what was learned in the classroom into novel situations in the work place. • Examples: Use a manual to calculate an employeeís vacation time. Apply laws of statistics to evaluate the reliability of a written test. • Key Words: applies, changes, computes, constructs, demonstrates, discovers, manipulates, modifies, operates, predicts, prepares, produces, relates, shows, solves, uses. • Analysis: Separates material or concepts into component parts so that its organizational structure may be understood. Distinguishes between facts and inferences.  • Examples: Troubleshoot a piece of equipment by using logical deduction. Recognize logical fallacies in reasoning. Gathers information from a department and selects the required tasks for training. • Key Words: analyzes, breaks down, compares, contrasts, diagrams, deconstructs, differentiates, discriminates, distinguishes, identifies, illustrates, infers, outlines, relates, selects, separates.

8. Bloom’s taxonomy (3) • Synthesis: Builds a structure or pattern from diverse elements. Put parts together to form a whole, with emphasis on creating a new meaning or structure. • Examples: Write a company operations or process manual. Design a machine to perform a specific task. Integrates training from several sources to solve a problem. Revises and process to improve the outcome. • Key Words: categorizes, combines, compiles, composes, creates, devises, designs, explains, generates, modifies, organizes, plans, rearranges, reconstructs, relates, reorganizes, revises, rewrites, summarizes, tells, writes. • Evaluation: Make judgments about the value of ideas or materials. • Examples: Select the most effective solution. Hire the most qualified candidate. Explain and justify a new budget. • Key Words: appraises, compares, concludes, contrasts, criticizes, critiques, defends, describes, discriminates, evaluates, explains, interprets, justifies, relates, summarizes, supports.

9. Bloom’s Taxonomy Categories • Knowledge • Comprehension • Application • Analysis • Synthesis • Evaluation • To be able to actually use knowledge, you have to get to at least the Application stage.

10. Example: Grammar • Knowledge: I am able to recite some rules of grammar, but when I actually write something, it might still be ungrammatical • Comprehension: I can explain the rules of grammar in my own words • Application: I can use the rules of grammar to write grammatically

11. How do we get to the Application level of learning?

12. Learning to Solve Problems is Like Learning to Ride a Bicycle • You can read all you want about how to ride a bike • You can watch someone demonstrate how to do it • But you have to actually get on the bike to learn how to ride it! • Likewise, to learn to design processes, you have to actually try it

13. Theory and Application • The course is divided into modules • In each module, you will study some theory • There will typically be a set of exercises to help you understand and absorb the theory • Then, you will have a project to work on where you apply the theory • The information is progressive and cumulative: don’t try to skip ahead • It takes time to absorb and internalize the ideas, so if your are taking this course for credit, keep up with the schedule

14. Excel Versions • You’ll need a copy of Excel 2010 (for Windows) or Excel 2011 (for Mac). You can get by with Excel 2007 for Windows. • We’ll make an effort to have everything compatible with both platforms. Where there are differences, we’ll point them out. Some materials will be presented for one platform only, where the differences are trivial or we have already covered them.

15. Topics We’ll Cover • General problem solving; intro to Excel • Naming and scope • Logic and conditionals • Procedures and functions • Repetition and loops • Files • Arrays • Algorithms • Some selected Excel topics