Excel VBA: Managing Files and Data

Learn how to automate complex tasks and get more meaningful insights from data in Excel using Visual Basic for Applications (VBA) code.

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