Training: Visual Basic for Applications (VBA)

• Excel's VBA can be a powerful tool useful for a multitude of

• purposes if you know how to leverage its capabilities, debug
• issues, and mitigate for specific limitations. In this introductory
• course, you'll begin by using subroutines in VBA to perform
• operations. You'll then define functions and reference and edit
• cell ranges and Excel sheets with VBA. After that, you'll invoke
• subroutines with relative cell references, record macros in Excel,
• and debug macros in VBA. You'll insert columns and sheets from VBA
• and format cells based on a condition in VBA both manually and
• using a FormatConditions object. Finally, you'll illustrate how
• clearing formatting using a FormatConditions object will only clear
• formatting created using a FormatConditions object, not by using
• if-else conditionals.

• One of the many capabilities of Excel's VBA is building complex

• user interfaces within Excel. Part of this is the creation of
• complex forms for gathering input data. In this course, you'll
• learn how to create user forms using VBA in Excel and, in doing so,
• achieve user interfaces with complex controls like combo boxes and
• spinners. You'll also learn how to send emails from VBA while being
• mindful of security risks and the configuration required if the
• email provider is Gmail. You'll learn how to validate form input
• data, insert it into an Excel spreadsheet, and illustrate how such
• user input can be accepted using complex controls, such as radio
• buttons and checkboxes, and buttons that, when clicked, trigger the
• invocation of VBA subroutines.

Leveraging VBA with Excel has many useful capabilities, including the creation and management of pivot tables and charts, and the automation of tasks in MS Access, the lightweight relational database.

In this course, you'll learn how to create and edit pivot tables and different chart types, such as bar charts and line charts, using VBA in Excel. You'll then save these Excel charts as images, again using VBA. Next, you'll use Excel's stock datatype to access financial data.

Moving on, you'll learn how to use VBA to automate tasks in MS Access, accept user input, validate it, and insert it into an MS Access database. You'll also learn how to run simple SQL queries against that database. Finally, you'll set up event handlers in Excel using VBA and illustrate their purpose.