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

VBA 7.1 (Office 2019)    |    Expert
  • 14 videos | 1h 57m 23s
  • Includes Assessment
  • Earns a Badge
Rating 4.5 of 77 users Rating 4.5 of 77 users (77)
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.

WHAT YOU WILL LEARN

  • 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

IN THIS COURSE

  • 2m 56s
  • 12m 28s
  • Locked
    3.  Working with Excel Charts from VBA
    9m 15s
  • Locked
    4.  Experiment with Charts from VBA
    10m 28s
  • Locked
    5.  Fetching Stock Data into an Excel Workbook
    8m 28s
    In this video, you'll learn more about Excel's support for stock data and how it can be automated using VBA. You'll follow along onscreen as you learn how to change tracks and work with Excel's Stock Data feature, which pulls in stock prices for specific financial assets. FREE ACCESS
  • Locked
    6.  Automate Stock Fetching Using VBA
    7m 7s
    In this demonstration, you'll learn how to use VBA to automate the fetching of financial data and demonstrate the use of named ranges from within a VBA macro. You'll follow along with the demo to create a button that executes a macro. FREE ACCESS
  • Locked
    7.  Getting Started with MS Access
    9m 52s
    In this demo, you'll learn how to create a sample database in Microsoft Access. This database can be used to house tables of relational data. You'll see how this relational data can be specified in the form of predefined columns and data laid out into rows. You'll also learn how to import data from a CSV file and specify primary keys. FREE ACCESS
  • Locked
    8.  Running SQL Queries in MS Access
    8m 15s
    In this demonstration, you'll learn how to specify the rows in a table as well as a primary key. You'll learn how to import data from a CSV file into this table and recognize how SQL queries work in Microsoft Access. FREE ACCESS
  • Locked
    9.  Querying Data in MS Access
    4m 48s
  • Locked
    10.  Building User Forms in MS Access
    10m
  • Locked
    11.  Inserting Rows into a Table Using Forms and VBA
    10m 53s
  • Locked
    12.  Working with Event Handlers in Excel with VBA
    8m 49s
  • Locked
    13.  Creating and Using Event Handlers in Excel
    11m 23s
  • Locked
    14.  Course Summary
    2m 41s

EARN A DIGITAL BADGE WHEN YOU COMPLETE THIS COURSE

Skillsoft is providing you the opportunity to earn a digital badge upon successful completion on some of our courses, which can be shared on any social network or business platform.

Digital badges are yours to keep, forever.

YOU MIGHT ALSO LIKE

Book Excel VBA In Easy Steps, 4th Edition
Book Microsoft Excel Professional 2021 Guide: A Complete Excel Reference, Loads of Formulas and Functions, Shortcuts, and Numerous Screenshots to Become an Excel Expert
Journey Microsoft Excel
Rating 3.6 of 10 users Rating 3.6 of 10 users (10)

PEOPLE WHO VIEWED THIS ALSO VIEWED THESE

Course Set Your Workspace Up For Success
Rating 4.6 of 175 users Rating 4.6 of 175 users (175)
Course VBA: Building User Interfaces with Forms in VBA & Excel
Rating 4.4 of 78 users Rating 4.4 of 78 users (78)
Course Complete Guide to Excel 365: Pivot, PowerPivot, & Financial Modeling
Rating 4.5 of 353 users Rating 4.5 of 353 users (353)