Sign In
Manage PermissionsManage Permissions
|
Export Event

Title

Introduction to Visual Basic for Applications in Excel

Event Description

Introduction to Visual Basic for Applications in Excel

23473038_1643433162403370_676629115026036164_n.jpg

Course Goals & Overview:

Excel is well-known as a very powerful spreadsheet application, but its full potential can arguably only be unlocked using VBA, or the hidden code running behind the scenes. Start with an overview of the basics, such as Excel's object model, data types and variables. Write your own functions and subroutines while learning when to use one or the other, complete with five cumulative case study examples simulating actual use-cases in data validation and automation. While no background in other programming languages is required, it can definitely help during this intensive VBA primer. Those starting from scratch may even end up building the foundation for further coding skills both in Excel (or other Microsoft Office software) and in other applications, as data structures, loops, and other best practices are universal.

Learning Objectives:

  • Learn the fundamentals of VBA in Excel, including the object model, data structures, methods, and properties
  • Begin using the Macro Recorder, before progressing to the VBA Editor and getting familiar with its layout/functions
  • Explore the various elements of the VBA language, such as variables, procedures, VBA functions, and operators
  • Prepare macros with proper error handling and diligent debugging components to accommodate user error
  • Build visually pleasing user forms, following best practices in user interface design to add/manipulate data seamlessly

Course Sections – Part I:

  • Overview of the Excel object model, including the definitions of its various elements
  • Use proper syntax to reference objects, call their methods, and access its properties
  • Forgo the Macro Recorder for the full-featured VBA Editor, becoming acquainted with its various panels/windows
  • Study VBA's wide variety of supported data types (e.g. strings, doubles, Booleans) and how to select the correct one
  • Declare, set, and reset variable, arrays, collections, and more
  • Use Option Explicit to ensure strict control over variable declaration and sloppy execution
  • Annotate code with comments for increased program legibility and to help other contributing developers
  • Write functions and subroutines and learn the differences between the two as well as the best situations for both
  • Utilize Excel's built-in functions in VBA code using the Application keyword to avoid reinventing the wheel
  • Take advantage of the overlaps between VBA functions and Excel functions, especially for text manipulation
  • Understand VBA control statements, such as conditionals and the assortment of loop types
  • Create nested loops to iterate through two-dimensional arrays or ranges of rows/columns
  • Establish variable scope to ensure proper program flow and permission logic

Course Sections – Part II:

  • Identify the various ways a program can yield compile (syntax) errors
  • Handle run-time errors (exceptions) and learn to correct them beforehand based on the accompanying error code
  • Implement error handling techniques with On Error, GoTo, and Resume statements
  • Debug proactively throughout the development process via message boxes and breakpoints
  • Take a more passive approach to debugging during development via the Locals and Immediate views
  • Use Step Into, Step Over, and Step Out to execute specific lines or procedures precisely to narrow down bugs
  • Design message boxes with or without buttons, each complete with their respective subroutines
  • Prompt the user for information using input boxes that accept either inputs (e.g. text, numbers) or a cell range
  • Construct user forms by selecting the appropriate toolbox controls (e.g. TextBox, Label, Frame, buttons)
  • Manage control properties in the VBA Editor to handle both appearance (size/color) and functionality
  • Develop best practices underlying effective forms, utilizing event-specific and control-specific code prudently

Prerequisites:

  • Excel Fundamentals for the Finance Professional

Please Note: Participants will need to bring a laptop with them to the course.

Event Type

Educational

Education Topic

 

Start Time

2/6/2018 8:00 AM

End Time

2/7/2018 4:00 PM

City

Arlington

State/Province

VA

Event Country

 

Event Region

Americas

Location Info

George Mason University - Arlington ​​Campus
Founders Hall
Room 120​
3351 North Fairfax Drive
Arlington, VA 22201

​Click here for map​.

​Metro: Virginia Square (​Orange and Silver lines)

​*Parking Garage is available at Founders Hall 

Speaker

 

CE Credits

 

SER Credit

0.00

Currency

USD

Member Price

899.00

Non-Member Price

999.00

Candidate Price

899.00

Registration

​Click here to register.

If you are having trouble with the online registration, please register here with a manual registration.

All Day Event

 

Recurrence

 

Location

 

Cost

 

Description

 

Begin

2/6/2018 8:00 AM

Attachments

Content Type: MyCFA Calendar
Created at 11/2/2017 11:26 AM by shaykay.t@gmail.com[CASMSTS:username]
Last modified at 1/17/2018 9:21 AM by serena.roche@gmail.com[CASMSTS:username]