Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

LinkedIn Learning

Excel VBA: Managing Files and Data

via LinkedIn Learning

Overview

Prepare for a new career with $100 off Coursera Plus
Gear up for jobs in high-demand fields: data analytics, digital marketing, and more.
Creating filters
Chaining and combining filter criteria
Determining whether workbooks and worksheets exist, with VBA
Opening, closing, and saving workbooks
Using VBA to calculate data via Excel's built-in functions
Creating charts with sparklines
Adding fields to UserForms
Manipulating PivotTables with VBA

Automate complex tasks and get more meaningful insights from data in Excel using Visual Basic for Applications (VBA) code. This course demonstrates how to work more efficiently in this powerful and popular spreadsheet program. Curt Frye shows how to focus in on important data, with filters; manage workbooks and worksheets; access built-in functions; create charts; build UserForm interfaces for data entry; and manage PivotTables using Excel VBA.

Syllabus

Introduction
  • Automate processes using Visual Basic for application code
  • What you should know
1. Focusing Data Using Filters
  • Select the active region
  • Create a filter
  • Chain criteria using AND
  • Combine criteria using OR
  • Find unique items using xlFilter
  • Restore a range or worksheet to an unfiltered state
2. Managing Files Using VBA
  • Determine if a workbook exists
  • Determine if a folder (directory) exists
  • Detect whether a file is open
  • Open a workbook
  • Close a workbook
  • Save a workbook under a new name
  • Save a workbook as a CSV file
3. Managing Worksheets Using VBA
  • Check if a worksheet exists
  • Create and rename worksheets
  • Copy a worksheet within the active workbook
  • Copy a worksheet to a new workbook
  • Copy a worksheet to an existing workbook
  • Move a worksheet within the active workbook
  • Move a worksheet to a new workbook
  • Move a worksheet to an existing workbook
4. Taking Advantage of Built-In Functions
  • Use the built-in Open dialog box
  • Suppress and restore alerts
  • Calculate data using Excel worksheet functions
  • Use the current date and time
  • Remove spaces from before or after a string
5. Managing Charts Using VBA
  • Create a chart
  • Move a chart to a chart sheet
  • Add or remove data series from a chart
  • Export a chart as an image
  • Create a line sparkline
  • Create a column sparkline
  • Create a win/loss sparkline
  • Delete a sparkline
6. Creating UserForms
  • Create a UserForm
  • Add a TextBox to a UserForm
  • Add a ListBox to a UserForm
  • Add a ComboBox to a UserForm
  • Add an option button to a UserForm
  • Add graphics to a UserForm
  • Add a SpinButton to a UserForm
  • Create a multipage or multitab UserForm
  • Write UserForm data to a worksheet
  • Run a UserForm
7. Managing PivotTables Using VBA
  • Record a PivotTable configuration
  • Assign a PivotTable macro to the Quick Access Toolbar
  • Advance one step forward in a PivotTable playlist
  • Move to any PivotTable playlist position
Conclusion
  • Next steps

Taught by

Curt Frye

Reviews

Start your review of Excel VBA: Managing Files and Data

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

Someone learning on their laptop while sitting on the floor.