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.
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.
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.
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.
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.
CFA Institute does not endorse, promote, review or warrant the accuracy of the products or services offered by the CFA Society Chicago or verify or endorse the pass rates claimed by the CFA Society Chicago. CFA, Chartered Financial Analyst, and the CFA Institute Logo, are just a few of the trademarks owned by CFA Institute.
CFA Society Chicago | 33 N. LaSalle Street, Suite 910 | Chicago, IL 60602 | firstname.lastname@example.org