Microsoft Excel VBA/Macros

14 Hours (Part-time or Full-time available)

For everyone wanting to know how to use a combination of Microsoft Excel and VBA to improve his/her productivity, adding power and flexibility to spreadsheets by creating user-defined functions.

Participants should be fully conversant with Excel and other windows applications but need not have learnt any programming before, although obviously the course is also useful for programmers in other programming languages who wish to learn VBA.

Module 1: Developing Macros

• Create a Macro with the Macro Recorder
• Visual Basic for Applications (VBA)
• Object-Oriented Programming
• Macros
• Macro Options
• The Macro Recorder
• Personal Macro Workbook
• Edit a Macro
• Visual Basic Editor
• Projects
• Modules
• The Modules Folder
• Objects
• Properties
• Methods
• VBA Comments
• Debug a Macro
• The Debugging Process
• Debugging Tools
• Customize the Quick Access Toolbar and Hot-keys
• Work with Macro Security
• Digital Certificates
• Digital Signatures
• Macro Security Settings

Module 2: Formatting Worksheets Using Macros

• Insert Text
• The Selection Object
• The Active Sheet Object
• The Name Property
• The Value Property
• Concatenation
• Format Text
• Sort Data
• The Range Object
• The Select Method
• The Current Region Property
• Duplicate Data
• Data Types
• Variables
• Variable Naming Rules
• Operators
• The Assignment Operator
• Arithmetic Operators
• Comparison Operators
• Logical Operators
• The For Next Loop
• The Do Loop
• The Worksheets Collection Object
• The Count Property
• The Offset Property
• The Copy Method
• The Paste Method
• Generate a Report
• The Columns Object
• The AutoFit Method
• The Address Property
• The Call Command
• The Font Property
• The End Property

Module 3: Creating an Interactive Worksheet

• Determine the Dialog Box Type
• Message Boxes
• Input Boxes
• Capture User Input
• The Input Box Function
• Constants
• The MsgBox Function
• The Code Continuation Character
• The vbCrLf Character
• Decision Structures
• The Select Case Structure
• The If Then Structure

Module 4: Working with Multiple Worksheets

• Insert,Copy, and Delete Worksheets
• The Add Method
• The Copy Method
• The Delete Method
• Rename Worksheets
• Expressions
• The Date Serial Function
• The Format Function
• Modify the Order of Worksheets
• The Move Method
• Print Worksheets
• The Print Preview Method
• The Print Out Method

Module 5: Performing Calculations

• Create User-Defined Functions
• User-Defined Functions
• Types of Functions
• Arguments
• The Else Clause
• Automate SUM Functions
• Declared Range Objects
• The Set Statement
• Range Object Cell Addressing
• The Rows Property
• The Formula Property
• The Columns Property
• Address Property Cell Reference Settings