CFA Society Chicago Events

Event Detail

Wall Street Training: Technical Applications - Excel and More: Visual Basic for Application in Excel (Limited Space)

Date, Time, and Location:
Thursday, September 28, 2017 (add this event to your Outlook Calendar)
9:00 am - 5:00 pm (Two-day course 9/28 and 9/29)
The Standard Club, 320 S. Plymouth Court, Chicago, IL  60604   (Driving Directions)

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

Prerequisite: Excel Fundamentals for the Finance Professional

NOTE: Laptops will not be provided.

Fee*:
Members - $1195
Non-Members - $1395
Student-Members - $495 (Limited seats available)
*Function tickets cannot be used for this event

CFA Institute Qualified Activity: Eligible for 14 credit hours

Attire: Business Casual

Menu: Lunch Buffet (Vegetarian options will be available)

Registration: Register online below

Hosted by: Education Advisory Group

Agenda: September 28th and 29th
8:30 am - 9:00 am - Registration
9:00 am - 5:00 pm - Seminar

Instructor: Hamilton Lin, CFA
Hamilton Lin, CFA, Founder & CEO of Wall St. Training, has a broad background in investment banking and mergers and acquisitions. His responsibilities have included analyzing, structuring and negotiating mergers & acquisitions. Lin custom-builds and develops all of the financial and merger models that he uses which have become corporate and departmental standards. Lin has closed over two dozen transactions and has diverse industry experience ranging from oil & gas to insurance to asset management and related sectors.

About Wall St. Training:
Wall St. Training provides professional financial training solutions to Wall Street through hands-on classroom training and customized corporate training programs for financial analysis which take a hands-on, interactive, practical, non-theoretical approach. Wall Street Training's clients include Bank of America/Merrill Lynch, Credit Suisse, Citigroup, Deutsche Bank, Fidelity, GE, Goldman Sachs, JPMorgan, Morgan Stanley, TPG, and many others. For more information: www.wallst-training.com and www.wstselfstudy.com.

*Special Notes Regarding Fees:
Student rate only available to students approved as a Student-Member of CFA Society Chicago for 2017-2018. Credit card is required to guarantee ALL reservations, no function tickets accepted. Day-of-event registrations accepted on-site only if applicable and space available. There is a $10 surcharge for walk-ins. Visa, MasterCard, American Express, Discover and Diners Club are accepted. Cancellations accepted until 5:00pm, Sept. 21.

Passport Program
Members of any CFA society may attend at the CFA Society Chicago member rate. If you are only a member of CFA Institute, you do not qualify for the society member rate.

CFA Institute CE Qualified Activity
This program qualifies for credit under the guidelines for the CFA Institute Professional Development Program.

Content shared during CFA Society Chicago programs is not, and nothing in it should be construed as, an offer, invitation or recommendation of any specific financial services company or professional, or an offer, invitation or recommendation to sell, or a solicitation of an offer to buy, any securities in any jurisdiction. By registering for and attending this event, you acknowledge that any photographs that may be taken are the property of CFA Society Chicago and give your consent to their use in CFA Society Chicago's online and print business communications and marketing.