Macros in Microsoft Excel and Word

Macros in Microsoft Excel and Word Demo training by Ferenc Nagy

What are the Macros? • Recorded • repetitive user actions • multistep tasks on documents • tedious mcjobs • must be exactly reproduced

Why We Use Them? • Save our mind to make important decisions • Help the work of our less experienced colleagues • Standardize the look and feel of our reports • Be sure that all necessary steps will be done

Planning Recording Starting Cleaning Debugging Saving Loading Exec Button Actions  Keystrokes Tools  Macros  New  Start  VB Editor  VBE  Debug  VBE  File  Export  VBE  File  Import View  Toolbars  … Step of Macro Writing and Usage

Recording Macros • Tools  Macros  New • Properties • name • hotkey • Place • current document • global • description • Press keys, enter data, click with mouse • Suspend, resume, finish

Methods for Starting Macros • Tools  Macros  Start • Press hotkey on property screen • Press button on toolbar

Cleaning of Visual Basic Text • Tools  Macros  VB Editor • What is recorded? • Not mouse clicks but property changes of VB objects. • Discard: • accidental detours • properties not intended to change Why?

Debugging Macros •  VBE  Debug • Stepwise execution • Inspect values of variables • Topic of advanced courses

Saving and Loading Macros • Tools  Macros  VB EditorFile  Export • Save macro as .bas or .cls files for future usage • Tools  Macros  VB EditorFile  Import • Load macro as .bas or .cls file to use in another documents

Definition of an Exec Button • Look for a bitmap or plan a new one • Create a new toolbar for your macros • Place the bitmap and the help text there

Examples • Plain data files  Excel worksheets • Fixedcolumn width checked by validation • Colums delimited by commas and parentheses • Format and print Word documents • Insert headers, footers, page numbers • Change font size and page orientation • Search and replace • given words or punctuation marks • ugly accented characters like ôôõÔÕûÛ ôôõÔÕûÛ to nice HungarianőŐűŰ characters

Record a Word macro Replacing all „is” with „is not” Paint all „.”-s to red. Define header: „In the name of Allah the compassionate and merciful.” Record an Excel Macro Opening a text file Create columns at commas , Delete all parentheses () Insert a heading in the first row Practice

Thank you for the attention. • It’s your turn to learn and use what you have learnt.

Macros in Microsoft Excel and Word