INTRODUCTION FOR DISCOVERING THE SECRETS OF MACRO AND VBA IN MICROSOFT EXCEL
Generally, Microsoft Excel is a spreadsheet application used to create lists, perform calculations, and analyse numbers. While the default features of Excel should be enough inmost scenarios, in some cases you will want more complex functionality to perform advanced operations. To make this possible, Excel is accompanied by Microsoft Visual Basic, a programming environment that allows you to use the Visual Basic Application (VBA) language to enhance the usefulness and functionality of a spreadsheet.
VBA allows you to write code that can automatically perform actions on a document and its content. Besides as an end user, now you are a ‘programmer’ to use Excel as a development tool to design an information system.
COURSE OBJECTIVES
This course is designed for working adults for the following objectives:
- To improve current spreadsheet (Microsoft Excel) solution skills.
- To use Visual Basic Application (VBA) language to automate daily tasks.
- To prepare students in designing an user-friendly interface in Excel
- To prepare students to train others.
At the end of the course, students should be able to:
- Understand and apply VBA language commands in Excel
- Use VBA to control the internal calculation or formulas or data flow in the reports.
- Design custom user interfaces in Excel.
- Simplify and speed up the tasks using VBA/Macro
COURSE CONTENT
Getting Started with Excel Macro & VBA
- What is Macro and VBA
- What can you do with Macro and VBA
- Type of Macros
- Recording to Macro
- Testing the Macro
- Examining the Macro
- Modifying the Macro
- Saving Workbooks that contain Macros
- Understanding Macro Security
- Relative or Absolute Recording
How VBA Works with Excel
- What is the Visual Basic Editor?
- Working with the Project Window
- Working with a Code Window
- Referring to Objects
- All about Properties and Methods
VBA Sub and Function Procedures
- Subs versus Functions
- Executing Sub Procedures
- Executing Function Procedures
Programming Concepts
- Using Comments
- Using Variables, Constants and Data Types
- Using Assignment Statements
- Working with Arrays
- Using Labels
- Referring to a Range
Controlling Program Flow and Making Decisions
- The GoTo Statement
- If_Then_Else Satement
- Using Select_Case Statement
- Knocking your code for a Loop
- Using For Each_Next Loops with Collection
Automatic Procedures and Events - Writing an Event-Handler Procedures
- Worksheet-Related Events
- Workbook-Related Events
Communicating with Your Users - Simple Dialog Boxes
- UserForm Basics
- Using UserForm Controls
- UserForm Techniques and Tricks
- Accessing Your Macros through the User Interfaces
Creating Excel Add-Ins
- Why Create Add-Ins
- Working with Add-Ins
- Add-In Basics
Practical Example of Using Macro/ VBA in your Reports
- Extracting data
- Worksheet linking
- Workbook linking
- Automating data manipulation
- Creating Excel Dashboard
- Sharing macro using Add-Ins
