Efisiensi tugas berulang di Excel dengan Visual Basic for Application (VBA). Mulai dari pengenalan dasar variable, constant dan operator, perulangan, form control, fungsi buatan, menyimpan dan menjalankan customized excel sebagai aplikasi
CONTENT
1. Unleashing the power of excel with VBA
1.1 barries to entry
1.2 knowing your tools : the developer
1.3 understanding which file types allow macros
1.4 macros security
1.5 Overview of recording, storing and running a macro
1.6 running a macro
1.7 understanding the VB editor
1.8 understanding shortcomings of the macro recorder
2. This sounds like BASIC, so why doesn’t it look familiar?
2.1 understanding the parts of VBA “speech”
2.2 VBA is not really hard
2.3 Examining recorded macro code: using the VB editor and help
2.4 Using debugging tools to figure out recorded code
2.5 Object browser: the ultimate reference
2.6 seven tips for cleaning up recorded code
3. Referring to ranges
3.1 the range object
3.2 Referencing ranges in other range
3.3 Referencing a range relative to another range
3.4 Using the cells property to select a range
3.5 using the offset property to refer to a range
3.6 using the resize property to change the size of a range
3.7 Using the Columns and Rows properties to specify a range
3.8 Using the Union method to join multiple ranges
3.9 Using the Intersect method to create a new range from
overlapping ranges
3.10 Using the IsEmpty function to check whether a cell is empty
3.11 Using the CurrentRegion property to select a data range
3.12 Case Study: Using the SpecialCells method to select specific cells
3.13 Using the Areas collection to return a noncontiguous range
3.14 Referencing tables
4. Looping and flow control
4.1 For…Next loops
4.2 Do loops
4.3 The VBA loop: for each
4.4 Flow control: using if…. then…. else and select case
5. R1c1-style formulas
5.1 toggling to R1C1-style references
5.2 witnessing the miracles of excel formulas
5.3 understanding the R1C1 reference style
5.4 using r1c1 formulas with array formulas
6 Creating and manipulating names in VBA
6.1 Global versus local names
6.2 adding names
6.3 deleting names
6.4 adding comments
6.5 types of names
6.6 hidding names
6.7 checking for the existence of a name
7. Event programming
7.1 levels of events
7.2 using events
7.3 workbook events
7.4 worksheet events
7.5 chart events
7.6 application-level events
8. Arrays
8.1 declaring an array
8.2 Declaring a multidimensional array
8.3 Filling an array
8.4 Retrieving data from an array
8.5 Using arrays to speed up code
8.6 Using dynamic arrays
8.7 Passing an array
9.Creating classes and collections
9.1 Inserting a class module
9.2 Trapping application and embedded chart events
9.3 Creating a custom object
9.4 Using a custom object
9.5 Using collections
9.6 Using dictionaries
9.7 Using user-defined types to create custom properties
10. Useforms: An introduction
10.1 input boxes
10.2 message boxes
10.3 creating a userform
10.4 calling and hiding a useform
10.5 Programming usefroms
10.6 Programming controls
10.7 Using basic form controls
10.8 verifying field entry
10.9 Illegal window closing
10.10 Getting a file name
11.Data mining with Advanced Filter
11.1 Replacing a loop with AutoFilter
11.2 Advanced Filter—easier in VBA than in Excel
11.3 Using Advanced Filter to extract a unique list of values
11.4 Using Advanced Filter with criteria ranges
11.5 Using filter in place in Advanced Filter
11.6 The real workhorse: xlFilterCopy with all records rather
than unique records only
12. Using VBA to create pivot tables
12.1 Understanding how pivot tables evolved over various Excel
versions
12.2 While building a pivot table in Excel VBA
12.3 Using advanced pivot table features
12.4 Filtering a data set
12.5 Using the Data Model in Excel 2019
12.6 Using other pivot table features
13.Excel power
13.1 File operations
13.2 Combining and separating workbooks
13.3 working with cell comments
13.4 Tracking user changes
13.5 Techniques for VBA pros
14. Sample user-defined functions
14.1 Creating user-defined functions
14.2 Sharing UDFs
14.3 Useful custom Excel functions
15. Creating charts
15.1 Using .AddChart2 to create a chart
15.2 Understanding chart styles
15.3 Formatting a chart
15.4 Creating a combo chart
15.5 Creating waterfall charts
15.6 Exporting a chart as a graphic
16. Data visualizations and conditional formatting
16.1 VBA methods and properties for data visualization
16.2 Adding data bars to a range
16.3 Adding color scales to a range
16.4 Adding icon sets to a range
16.5 Using visualization tricks
16.6 Using other conditional formatting methods
17.Dashboarding with sparklines in Excel 2019
17.1 Creating sparklines
17.2 Scaling sparklines
17.3 Formatting sparklines
17.4 Creating a dashboard
18. Reading from and writing to the web
18.1 Getting data from the web
18.2 Using Application.OnTime to periodically analyze data
18.3 Publishing data to a web page
19. Text file processing
19.1 Importing from text files
19.2 Writing Text Files
20. Automating Word
20.1 Using early binding to reference a Word object
20.2 Using late binding to reference a Word object
20.3 Using the New keyword to reference the Word application
20.4 Using the CreateObject function to create a new instance of
an object
20.5 Using the GetObject function to reference an existing instance
of Word
20.6 Using constant values
20.7 Understanding Word’s objects
20.8 Controlling form fields in Word
21. Using Access as a back end to enhance multiuser access to data
21.1 ADO versus DAO
21.2 The tools of ADO
21.3 Adding a record to a database
21.4 Retrieving records from a database
21.5 Updating an existing record
21.6 Deleting records via ADO
21.7 Summarizing records via ADO
21.8 Other utilities via ADO
21.9 SQL Server examples
22. Advanced userform techniques
22.1 Using the UserForm toolbar in the design of controls on
userforms
22.2 More userform controls
22.3 Controls and collections
22.4 Modeless userforms
22.5 Using hyperlinks in userforms
22.6 Adding controls at runtime
22.7 Adding help to a userform
22.8 Creating transparent forms
23. The Windows Application Programming
Interface (API)
23.1 Understanding an API declaration
23.2 Using an API declaration
23.3 Making 32-bit- and 64-bit-compatible API declarations
23.4 API function examples
24. Handling errors
24.1 What happens when an error occurs?
24.2 Basic error handling with the On Error GoTo syntax
24.3 Generic error handlers
24.4 Training your clients
24.5 Errors that won’t show up in debug mode
24.6 Errors while developing versus errors months later
24.7 The ills of protecting code
24.8 More problems with passwords
24.9 Errors caused by different versions
25. Customizing the ribbon to run macros
25.1 Where to add code: The customui folder and file
25.2 Creating a tab and a group
25.3 Adding a control to a ribbon
25.4 Accessing the file structure
25.5 Understanding the RELS file
25.6 Renaming an Excel file and opening a workbook
25.7 Using images on buttons
25.8 Troubleshooting error messages
25.9 Other ways to run a macro
26. Creating add-ins
26.1 Characteristics of standard add-ins
26.2 Converting an Excel workbook to an add-in
26.3 Having a client install an add-in
26.4 Standard add-ins are not secure
26.5 Closing add-ins
26.6 Removing add-ins
26.7 Using a hidden workbook as an alternative to an add-in
27. An introduction to creating Office add-ins
27.1 Creating your first Office add-in—Hello World
27.2 Adding interactivity to an Office add-in
27.3 A basic introduction to HTML
27.4 Using XML to define an Office add-in
27.5 Using JavaScript to add interactivity to an Office add-in
28. What’s new in Excel 2019 and what’s changed
28.1 Office 365 subscription versus Excel 2019 perpetual
28.2 If it has changed in the front end, it has changed in VBA
28.3 Learning the new objects and methods
28.4 Compatibility mode
