VBA: Leveraging VBA to Work with Charts, Stocks, & MS Access


Overview/Description
Expected Duration
Lesson Objectives
Course Number
Expertise Level



Overview/Description

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. 



Expected Duration (hours)
2.0

Lesson Objectives

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
  • summarize the key concepts covered in this course
  • Course Number:
    it_daexvbdj_03_enus

    Expertise Level
    Expert