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.
VBA: Leveraging VBA to Work with Charts, Stocks, & MS Access
discover the key concepts covered in this course
use VBA to create a pivot table and work with that pivot table both through VBA and directly via Excel
use VBA to create a simple chart on a new sheet and manipulate the data that it contains
change the chart type, format the chart to use 2-D as well as 3-D chart types from VBA, resize it, and save it as an image
work with Excel's Stock Data feature, which pulls in stock prices for specific financial assets
use VBA to automate the fetching of financial data and demonstrate the use of named ranges from within a VBA macro
illustrate the use of MS-Access, a lightweight relational database, and create a sample database (.acc) file and a table
specify the rows in a table as well as a primary key, import data from a CSV file into this table, and recognize how SQL queries work in MS Access
run SQL queries, such as SELECT-FROM-WHERE queries, to analyze data in MS Access
combine MS Access and VBA, add user forms to an Access database, configure various user controls, such as buttons and input boxes, and configure a calculated column based on an expression value
use SQL INSERT statements to add rows to the Access database based on user input to a form
demonstrate the use of event handlers in Excel, create a VBA subroutine to be executed when the user attempts to save a workbook, and add similar handlers for events, such as opening or closing a workbook
create an event handler for activating a worksheet, insert a new worksheet, and change the contents of a cell