Download Microsoft Excel VBA User Manual

Transcript
Excel Visual Basic for Applications
Interacting with MS Access.......... 66
Send Keys ................................ 67
User Defined Data Type.................... 67
Enumerations ................................. 68
By Reference, By Value .................... 68
By Name, By Order.......................... 69
Classes .......................................... 70
Creating an Object........................ 70
Using a Class Module .................... 70
Lotus 1-2-3 Translation ................. 73
The Move Object........................ 73
File Operations................................ 76
Opening All files ........................... 76
Writing text files ........................... 76
Using ActiveX Controls ..................... 78
Using the Windows API .................... 79
Case Studies................................... 80
Case Study 1. Using the Personal
Workbook.................................... 80
Case Study 2. Looping through Cells 80
Case Study 3. Processing a Text File 80
Case Study 4. Writing a Loop ......... 81
Case Study 5. Using Control Structures
................................................. 82
Case Study 6. Declaring and Typing
Variables ..................................... 84
Case Study 7. Creating an Add-In
Function ...................................... 84
Case Study 8. Creating a User Form 85
Case Study 9. Handling Workbook files
................................................. 87
Case Study 10. Refreshing Pivot Tables
................................................. 89
Case Study 11. Unmatched Items ... 89
Index ............................................ 94
Simple Method .......................... 41
Using VBA code to construct menus
............................................... 41
Restoring the user’s Toolbars ...... 43
Calling Excel’s built-in Dialogs ........ 44
Review of Excel’s User Interface
features ...................................... 44
User Forms .................................... 45
Designing the User Form ............... 45
Completing the Form’s Events ........ 46
Naming Conventions ..................... 47
User Form Example Code............... 48
List Boxes ................................... 50
Instancing a User Form ................. 50
Using Me ..................................... 51
VBA Memory Arrays......................... 52
Using Arrays to store sets of data ... 52
Dimensioned Arrays...................... 52
The Variant Array ...................... 52
Array Subscripts ........................ 53
Using Cell values in arrays .......... 53
Dynamic Arrays............................ 54
VBA Error Handling.......................... 55
Excel Pivot Tables ........................... 57
Creating a Pivot Table report.......... 57
Data Fields .................................. 58
Excel Charts ................................... 59
Chart Objects............................... 59
Arranging Charts on a Worksheet ... 60
Embedding Chart Data Series......... 61
Application Interaction ..................... 64
Creating Object Model References ... 64
Late Binding.............................. 64
Early Binding............................. 64
Interacting with MS Word ........... 65
Page 3