CTE Marketing Cluster Specialist
While most of us love our career
paths, we often hate the administrative tasks that seem to occupy so much of
our time. Often the tasks are repetitive
in nature and we wish we could automate the process. If only we could find programmers to write us
some software specific to our needs. And
can we could find programmers who can maintain the software. What about budgets? “It ain’t gonna Happen.”While most of us do NOT have access to programmers and no desire to learn programming competencies, we can learn to use office application macros. Microsoft Office applications have a macro language call Visual Basic for Applications (VBA). While this is a programming language, the good news is that you can record any of your repetitive processes in these applications and then automate the processes by executing your recording. Better yet, you can attach your recording to a ribbon, menu item, or a button on your office application and execute it any time you want.
Here is a list of competencies you can teach yourself about Microsoft Office Macros.
Part I
1. How to
record and save a VBA Macro
2. How to find
your VBA Macro
3. How to
execute your VBA Macro
Part II
4. How re-record
or edit your Macro
5. How to
attach it to a menu ribbon (menu or button)
6. How to edit
code or create forms (advanced work)
This first step is to understand
when using an Office VBA macro could save you time. Normal day to day tasks and data processing
are ideal candidates for VBA macros.
Suppose every day you need to load in a document template you designed
and then fill out the information required and then save that document. Many times you have selected “Save” or Office
saved it automatically instead of a “Save As” messing up your template. You find yourself cleaning out the changes in
the template at least once a month. Many grading websites generate text files
that can alleviate the old grade book processes. Why reformat the text with
heading, spacing, or formulas each time? A Macro can be built to do all of the
work for you!
Macro Creation Steps
1. Define
Macro Needs and Keystrokes
2. Open
Workbook and Record New Macro
3. Assign
Macro Name and Description
4. Perform
Macro Series
5. Stop Macro
Recording
Creating a Macro is similar to turning on a
video camera that records your every move and will play itself back for you at
any time. Before recording a macro, it is important that you compile all of the
necessary keystrokes for your automated process. Think of all of the processes
that you perform on your imported data to generate the final version. Do you
add columns? Do you format bold headings on titles? Are you using any formulas?
Once you have completed your list of actions, you can start recording the
Macro. The “Record Macro” button can be found under Tools | Macros | Record Macro in versions before 2010. The “Record Macro” button is found under View | Macros | Record Macro in Office 2010 and newer. Once you select the record button, the macro will need to be named. There is also a space for a short description of the tasks the macro is performing.
While recording the macro, complete your standardize tasks that you had listed in the keystroke compilation. Be sure to stay in the excel document you are using. Do not switch to other documents while recording your macro.
When you are complete with the macro, simply press “Stop Recording” from the Macro dropdown list. Your new macro will be listed under the “View Macro” dropdown list. Select your new Macro from the list and press “Run”. This will automatically run the Macro for the data you have selected. The Macro writes a code for the author. The next article in this series will address how to step into the code and address any issues you may have with the Macro.
Remember, VBA macros are not a substitution for heavy data processing such as database applications might need. Macros are useful for many versions of data control and manipulation. Checkout Part II in your journey to project automate your office tasks!
