English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 3h 46m | 492 MB
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.
Table of Contents
Introduction
1 Automate processes using Visual Basic for application code
2 What you should know
Focusing Data Using Filters
3 Select the active region
4 Create a filter
5 Chain criteria using AND
6 Combine criteria using OR
7 Find unique items using xlFilter
8 Restore a range or worksheet to an unfiltered state
Managing Files Using VBA
9 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
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
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
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
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
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
Resolve the captcha to access the links!