Excel Macro Training

This course is useful for those MS Excel users who wish to enhance their productivity by automating their repetitive tasks. Excel Macro is a powerful tool that uses VBA development environment and it is easy to learn.

Topics Covered

Introduction to Excel VBA Macros • What is Excel Macro • How to add Developer ribbon to Excel Menu • Creating Macro enabled Workbook (.xlsm) • How to open the VBA Editor • VBA Hierarchy – Macro Library > Macros > Modules > Procedures and Functions > VBA code • Creating your first Macro by Recording • Compiling, Running and Debugging your Macro Programming essentials • Creating Modules (container of Procedures), Procedures (Sub-End Sub) and Functions • Public and Private Procedures • Calling Procedures and Functions in Modules • Passing Arguments to Functions • Variables 1. Declaration of Variables: Implicit and Explicit declarations 2. Declaring multiple variables in one statement • Opening and Closing Workbooks • Assigning values to variables • Accepting input from user (Inputbox function) • Calculating and storing values • Mathematical Operators (+, -, *, /, ^) • Displaying the results using Msgbox function • Msgbox style – vbOkOnly, vbOkCancel, vbAbortRetryIgnore, vbYesNoCancel, vbYesNo, vbRetryCancel • If-Then-ElseIf-Else command • For-Next loop • Do-While loop • Logical Operators – And, Or, Not • String Operator and Adding Strings together with “&” • Adding New line and Tab to strings using vbCrLf and vbTab • Use IsNumeric() to check if the number is Numeric Objects and Collections (group of objects of the same class) in VBA • Range object • Cell object • Worksheet object • Workbook (same as the Excel file) • Methods and properties of objects – Open, Close, Count, Value, Offset • Assigning names to objects through Declarations Adding Controls to your Macro • How to add Controls to Macros • Viewing and Editing Control properties • Common controls – Command Button, List Box, Radio Button, Combo Box

Who should attend

Advanced users of Excel who wish to learn automation through VBA programming

Pre-requisites

1. Knowledge of MS Excel. 2. Excel 2007 should be installed on your computer

What you need to bring

Your computer/Laptop with MS Excel 2007 installed on it

Key Takeaways

• Creating, editing and running Excel VBA Macros • Application of Excel Macros for automating repetitive computational procedures • Creating and using data entry Forms to capture data in a structured format