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

0. Introduction

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

1. Focusing Data Using Filters

03 - Select the active region
04 - Create a filter
05 - Chain criteria using AND
06 - Combine criteria using OR
07 - Find unique items using xlFilter
08 - Restore a range or worksheet to an unfiltered state

2. Managing Files Using VBA

09 - Determine if a workbook exists
10 - Determine if a folder (directory) exists
11 - Detect whether a file is open
12 - Open a workbook
13 - Close a workbook
14 - Save a workbook under a new name
15 - Save a workbook as a CSV file

3. Managing Worksheets Using VBA

16 - Check if a worksheet exists
17 - Create and rename worksheets
18 - Copy a worksheet within the active workbook
19 - Copy a worksheet to a new workbook
20 - Copy a worksheet to an existing workbook
21 - Move a worksheet within the active workbook
22 - Move a worksheet to a new workbook
23 - Move a worksheet to an existing workbook

4. Taking Advantage of Built-In Functions

24 - Use the built-in Open dialog box
25 - Suppress and restore alerts
26 - Calculate data using Excel worksheet functions
27 - Use the current date and time
28 - Remove spaces from before or after a string

5. Managing Charts Using VBA

29 - Create a chart
30 - Move a chart to a chart sheet
31 - Add or remove data series from a chart
32 - Export a chart as an image
33 - Create a line sparkline
34 - Create a column sparkline
35 - Create a win loss sparkline
36 - Delete a sparkline

6. Creating UserForms

37 - Create a UserForm
38 - Add a TextBox to a UserForm
39 - Add a ListBox to a UserForm
40 - Add a ComboBox to a UserForm
41 - Add an option button to a UserForm
42 - Add graphics to a UserForm
43 - Add a SpinButton to a UserForm
44 - Create a multipage or multitab UserForm
45 - Write UserForm data to a worksheet
46 - Run a UserForm

7. Managing PivotTables Using VBA

47 - Record a PivotTable configuration
48 - Assign a PivotTable macro to the Quick Access Toolbar
49 - Advance one step forward in a PivotTable playlist
50 - Move to any PivotTable playlist position

Conclusion

51 - Next steps