Microsoft Excel VBA programming course

Description

By the end of the course, you will be confident in using the essential features of Excel Visual Basic to create simple spreadsheet solutions. Delegates must have an excellent working knowledge of Microsoft Excel. This course is for anyone who has never used Excel VBA (or any other Visual Basic package) and who prefers to learn at a regular pace. This course is especially useful if you need to build spreadsheets that go beyond what Excel alone is capable of.

In particular you will be able to:

• Understand what VBA is
• Create macros to automate tasks, as well as understand their limits
• Manipulate the values in your spreadsheet
• Declare variables, and manipulate them
• Write code which looks at a condition and carries out different actions accordingly
• Write functions, frequently used pieces of code
• Understand the Excel object model, and make use of its properties, methods and events
• Trap errors, to avoid users seeing problems and find problems in your code
• Write code which repeats an action a specified number of times

Curriculum

Module 1: Introduction

• Basics of VBA
• Recording and Running Marcos
• Writing vs. Recording Macros

Module 2: Getting to Know the Visual Basic Editor

• VB Editor
• Project Explorer
• Properties Window
• Code Window
• Adding, Removing Modules
• Exporting and Importing Modules
• Naming Modules
• Customising the VB Environment

Module 3: Start Writing Your Code

• Few Time Saver Short keys
• Structure of Sub Procedures
• Calling Sub Procedures
• Structure of Functions
• Calling Functions
• Writing Comments Into the Code

Module 4: Different References to Ranges and Cells

• Selecting Cells (Absolute)
• Selecting Cells (Relative) – Offsetting, Resizing
• Selecting to the End of a Block (Current Region)
• Selecting a Range by its Corners (Xldown,Xlup, XltoRight, XltoLeft)
• Finding Last Row of a Table (3 Methods)

Module 5: Variables, Data Types, Operators

• Variables
• Declaring Multiple Variables
• Object Variables
• Data Types
• Declaring Constants
• Arithmetic Operators
• Using Variables

Module 6: Operations with Cells, Ranges

• Cell Object
• ActiveCell
• Rows and Columns
• Inserting Rows and Columns
• AutoFitting Rows and Columns
• Range Object
• Referring to Named Ranges
• Copying Method
• Copy, Paste Method
• Using PasteSpecial
• Name Property
• Sorting Fields
• Filtering Fields
• Multiple Filtering
• Deleting Filtered Rows
• Removing Duplicate Records
• With Statements
• Sorting Fields
• Filtering Fields
• ‘Deleting Filtered Rows
• ‘Removing Duplicate Records

Module 7: Looping and Decision Structures with IF Statements

• .. Then
• .. Then… Else
• .. Then… ElseIF… Else
• Case Select… End Select
• Do Until Loops
• Do while Loops
• Decision Structures
• .. Next Loops
• For Each …Next Loops

Module 8: Working with Worksheets

• Adding a Worksheet
• Adding a Worksheet Before a Given Sheet
• Adding a Worksheet to the End
• Moving Worksheets
• Deleting Worksheets
• Renaming Worksheets
• Hiding and Un-hiding Worksheets
• Copying Worksheets

Module 9: Working with Workbooks

• Creating a New Workbook
• Saving, Saving As
• Opening a Workbook
• Selecting File From a Folder (FileDialog Objects

Module 10: File dialog pop-up windows

• msoFileDialogFilePicker
• msoFileDialogFolderPicker
• msoFileDialogOpen
• msoFileDialogSaveAs

Module 11: Interaction with Message Boxes & Input Boxes

• Creating a One Line Message Box
• Creating a Formatted Message Box (Multiple Lines)
• Creating a Customised Message Box (Setting Buttons, Format, etc)
• Actions based on the Selected Message Box Button
• Input Boxes
• Giving Pre-Defined Default Values to Input Boxes

Module 12: Pivot Tables

• Creating a Pivot Table
• Adding Row and Column Fields
• Changing Filed Names of Pivot
• Extracting Data from Pivot Tables

Module 13: Preparing for the Advanced Course

• Creating a Basic Sudoku Solver Macro
• Learning How to Understand a Very Complex VBA Code
• Creating Functions
• Creating a Time Stamp of the File to Check it is Ready for Use
• Learning How to Structure Your Excel and Build Up a Macro Control sheet (Radio Buttons, Check Points, Command Buttons)
• Summary and Extra Exercises

For those who would like to expand their knowledge in Excel and who would like to automate reports, tasks in Microsoft Excel.

No requirements

Currently there are no Q&As for this course. Be the first to ask a question.