Transcript WHY A PIVOT TABLE?

EXCEL 2007 MACROS
 TOM
FARRAR OF CNEXTWAVE INC.
 GOAL : LEARNING ABOUT EXCEL MACROS.
 PRESENTATION INCLUDES DISCUSSION AND
DEMONSTRATION.
 QUESTIONS DURING OR AFTER THE
PRESENTATION.
PRIMARY NAVIGATION:
 THE
RIBBON
 THE QUICK ACCESS TOOLBAR (QAT)
 KEYBOARD SHORTCUTS
 RECORDED
OR WRITTEN VBA CODE
 RECORDED WORKS LIKE A TAPE RECORDER
 WRITTEN VBA IS SIMILAR TO PROGRAMMING
CODE
 USED TO AUTOMATE REPETITIVE TASKS
 TURN MULTIPLE MOUSE “CLICKS” INTO ONE
•RECORDING MACROS
•EDITING MACROS
•ASSIGNING MACROS TO TOOLBAR
•PROTECTING THE MACRO
•ADVANCED MACROS
RECORDING A MACRO
•PLAN MACRO
•RECORD MACRO (DEVELOPER RIBBON)
•NAME MACRO
•SHORTCUT KEY (OPTIONAL)
•WHERE STORED
•DESCRIPTION (OPTIONAL)
•ENTER STEPS
•STOP RECORDING
PERSONAL WORKBOOK
“THIS” WORKBOOK
OTHER WORKBOOK
NameOne
Macro
NameTwo Macro
NameThree Macro
TodaysDate Macro
EDITING A MACRO
•THE VBA EDITOR
•READING A MACRO
•EDITING A MACRO
•PROJECT EXPLORER
•MODULES
•PROPERTIES WINDOW
•CODE WINDOW
•SUB/MACRO NAME
•COMMENTS
•MACRO CODE
•OBJECT-ORIENTED CODE
•OBJECTS, PROPERTIES, METHODS, EVENTS, &
COLLECTIONS
•EXAMPLES OF OBJECT: CELL, WORKSHEET,
RANGE
•EXAMPLES OF PROPERTIES: NAME, COLOR,
HEIGHT
•EXAMPLES OF METHODS: SELECT, COPY, OFFSET
•FOR INSTANCE, RANGE(“B3”).SELECT
OR, WORKSHEETS(1).NAME=“INVOICE”
•Object
A "thing" Worksheet
•Method Something a "thing" can do Add a "thing"
•Property A characteristic of a "thing" Name
•EDIT RECORDED MACROS
•RECORD/EDIT “PASTE VALUES”
•RECORD/COMBINE MACROS
ASSIGN MACRO TO
TOOLBAR
PROTECTING THE
MACRO
ADVANCED MACROS
•SUBROUTINE MACRO
•CUSTOMIZED FUNCTION
•INTERACTIVE MACRO
•IF/THEN/ELSE ROUTINES
•CALENDAR MACRO
•A MACRO OPERATING INSIDE
ANOTHER MACRO
•CAN BREAK A COMPLEX MACRO
INTO SMALLER PARTS
•OR PROVIDE USERS A CHOICE, LIKE
CHOICE “A” OR CHOICE “B”
•LET’S TRY ONE….
•USED TO EXECUTE COMPLICATED
CALCULATIONS
•PROTECT CALCULATION DETAILS
•AVAILABLE WITH OTHER STANDARD
EXCEL FUNCTIONS
•LET’S TRY ONE….
•BREAK IN MACRO REQUIRING USER
ACTION BEFORE CONTINUING
•USED FOR INPUT, MESSAGE, OR
STATUS-BAR BOXES REQUIRING
ATTENTION
•LET’S TRY ONE…..
•SIMILAR TO PREVIOUS SUBROUTINE
EXAMPLE
•CREATE THE “IF” FIRST
•THEN ADD THE “ELSE” CODE
•THEN ADD THE “ELSEIF” CODE
•LET’S TRY ONE…..
•INTERNET DOWNLOAD EXAMPLE
•EMAIL EXAMPLE
CALENDAR DATES
TO REPORT
 MACROS
AUTOMATE TASKS
 MACROS ARE RECORDED OR WRITTEN
 VBA IS THE BACKGROUND “CODE” FOR
MACROS
 RECORDED MACROS CAN BE EDITED BY YOU
 WITH PRACTICE, YOU CAN GET PRETTY
GOOD AT MACROS
TIMESAVING
KEYSTROKES
CTRL/X - CUT
CTRL/Y - COPY
CTRL/V - PASTE
CTRL/Z - UNDO
CTRL/Y - REDO
CTRL/F - FIND
CTRL/G - GO TO
CTRL/L & CTRL/T – CREATE LIST