Excel VBA: Managing Files and Data (2020)

Course details

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.

Concepts

Introduction

Automate processes using Visual Basic for application code
What you should know

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

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

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

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

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

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

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