VBANotesForProfessionals.pdf

(2222 KB) Pobierz
Notes for Professionals
VBA
VBA
Notes for Professionals
of professional hints and tricks
100+ pages
GoalKicker.com
Free Programming Books
Disclaimer
This is an unocial free book created for educational purposes and is
not aliated with ocial VBA group(s) or company(s).
All trademarks and registered trademarks are
the property of their respective owners
Contents
About
................................................................................................................................................................................... 1
Chapter 1: Getting started with VBA
................................................................................................................... 2
Section 1.1: Accessing the Visual Basic Editor in Microsoft Oce
............................................................................. 2
Section 1.2: Debugging
.................................................................................................................................................. 3
Section 1.3: First Module and Hello World
................................................................................................................... 4
Chapter 2: Declaring Variables
.............................................................................................................................. 6
Section 2.1: Type Hints
.................................................................................................................................................. 6
Section 2.2: Variables
.................................................................................................................................................... 7
Section 2.3: Constants (Const)
................................................................................................................................... 10
Section 2.4: Declaring Fixed-Length Strings
............................................................................................................. 11
Section 2.5: When to use a Static variable
............................................................................................................... 11
Section 2.6: Implicit And Explicit Declaration
............................................................................................................ 13
Section 2.7: Access Modifiers
..................................................................................................................................... 14
Chapter 3: Scripting.FileSystemObject
............................................................................................................ 16
Section 3.1: Retrieve only the path from a file path
................................................................................................. 16
Section 3.2: Retrieve just the extension from a file name
....................................................................................... 16
Section 3.3: Recursively enumerate folders and files
.............................................................................................. 16
Section 3.4: Strip file extension from a file name
..................................................................................................... 17
Section 3.5: Enumerate files in a directory using FileSystemObject
...................................................................... 17
Section 3.6: Creating a FileSystemObject
................................................................................................................. 18
Section 3.7: Reading a text file using a FileSystemObject
...................................................................................... 18
Section 3.8: Creating a text file with FileSystemObject
........................................................................................... 19
Section 3.9: Using FSO.BuildPath to build a Full Path from folder path and file name
....................................... 19
Section 3.10: Writing to an existing file with FileSystemObject
............................................................................... 19
Chapter 4: Procedure Calls
................................................................................................................................... 21
Section 4.1: This is confusing. Why not just always use parentheses?
.................................................................. 21
Section 4.2: Implicit Call Syntax
................................................................................................................................. 21
Section 4.3: Optional Arguments
............................................................................................................................... 22
Section 4.4: Explicit Call Syntax
.................................................................................................................................. 22
Section 4.5: Return Values
.......................................................................................................................................... 23
Chapter 5: Naming Conventions
......................................................................................................................... 24
Section 5.1: Variable Names
....................................................................................................................................... 24
Section 5.2: Procedure Names
................................................................................................................................... 27
Chapter 6: Creating a procedure
....................................................................................................................... 29
Section 6.1: Introduction to procedures
..................................................................................................................... 29
Section 6.2: Function With Examples
......................................................................................................................... 29
Chapter 7: Flow control structures
.................................................................................................................... 31
Section 7.1: For loop
.................................................................................................................................................... 31
Section 7.2: Select Case
.............................................................................................................................................. 32
Section 7.3: For Each loop
.......................................................................................................................................... 33
Section 7.4: Do loop
.................................................................................................................................................... 34
Section 7.5: While loop
................................................................................................................................................ 34
Chapter 8: Comments
.............................................................................................................................................. 35
Section 8.1: Apostrophe Comments
........................................................................................................................... 35
Section 8.2: REM Comments
...................................................................................................................................... 35
Chapter 9: Arrays
....................................................................................................................................................... 36
Section 9.1: Multidimensional Arrays
......................................................................................................................... 36
Section 9.2: Dynamic Arrays (Array Resizing and Dynamic Handling)
................................................................ 41
Section 9.3: Jagged Arrays (Arrays of Arrays)
........................................................................................................ 42
Section 9.4: Declaring an Array in VBA
..................................................................................................................... 45
Section 9.5: Use of Split to create an array from a string
...................................................................................... 45
Section 9.6: Iterating elements of an array
.............................................................................................................. 47
Chapter 10: Error Handling
.................................................................................................................................... 49
Section 10.1: Avoiding error conditions
...................................................................................................................... 49
Section 10.2: Custom Errors
........................................................................................................................................ 49
Section 10.3: Resume keyword
................................................................................................................................... 50
Section 10.4: On Error statement
............................................................................................................................... 52
Chapter 11: Recursion
................................................................................................................................................ 55
Section 11.1: Factorials
................................................................................................................................................. 55
Section 11.2: Folder Recursion
.................................................................................................................................... 55
Chapter 12: Conditional Compilation
................................................................................................................ 57
Section 12.1: Changing code behavior at compile time
........................................................................................... 57
Section 12.2: Using Declare Imports that work on all versions of Oce
............................................................... 58
Chapter 13: Data Types and Limits
.................................................................................................................... 60
Section 13.1: Variant
..................................................................................................................................................... 60
Section 13.2: Boolean
.................................................................................................................................................. 60
Section 13.3: String
....................................................................................................................................................... 61
Section 13.4: Byte
......................................................................................................................................................... 62
Section 13.5: Currency
................................................................................................................................................. 63
Section 13.6: Decimal
................................................................................................................................................... 63
Section 13.7: Integer
..................................................................................................................................................... 63
Section 13.8: Long
........................................................................................................................................................ 63
Section 13.9: Single
...................................................................................................................................................... 64
Section 13.10: Double
................................................................................................................................................... 64
Section 13.11: Date
........................................................................................................................................................ 64
Section 13.12: LongLong
.............................................................................................................................................. 65
Section 13.13: LongPtr
.................................................................................................................................................. 65
Chapter 14: String Literals - Escaping, non-printable characters and line-continuations
............................................................................................................................................................................................... 66
Section 14.1: Escaping the " character
....................................................................................................................... 66
Section 14.2: Assigning long string literals
................................................................................................................ 66
Section 14.3: Using VBA string constants
.................................................................................................................. 66
Chapter 15: Declaring and assigning strings
................................................................................................ 68
Section 15.1: Assignment to and from a byte array
................................................................................................. 68
Section 15.2: Declare a string constant
..................................................................................................................... 68
Section 15.3: Declare a variable-width string variable
............................................................................................ 68
Section 15.4: Declare and assign a fixed-width string
............................................................................................. 68
Section 15.5: Declare and assign a string array
....................................................................................................... 68
Section 15.6: Assign specific characters within a string using Mid statement
....................................................... 69
Chapter 16: Converting other types to strings
............................................................................................ 70
Section 16.1: Use CStr to convert a numeric type to a string
.................................................................................. 70
Section 16.2: Use Format to convert and format a numeric type as a string
....................................................... 70
Section 16.3: Use StrConv to convert a byte-array of single-byte characters to a string
................................... 70
Section 16.4: Implicitly convert a byte array of multi-byte-characters to a string
............................................... 70
Chapter 17: Searching within strings for the presence of substrings
.............................................. 71
Section 17.1: Use InStr to determine if a string contains a substring
...................................................................... 71
Section 17.2: Use InStrRev to find the position of the last instance of a substring
............................................... 71
Section 17.3: Use InStr to find the position of the first instance of a substring
..................................................... 71
Chapter 18: Substrings
............................................................................................................................................. 72
Section 18.1: Use Left or Left$ to get the 3 left-most characters in a string
......................................................... 72
Section 18.2: Use Right or Right$ to get the 3 right-most characters in a string
................................................. 72
Section 18.3: Use Mid or Mid$ to get specific characters from within a string
...................................................... 72
Section 18.4: Use Trim to get a copy of the string without any leading or trailing spaces
................................. 72
Chapter 19: Measuring the length of strings
................................................................................................ 73
Section 19.1: Use the Len function to determine the number of characters in a string
....................................... 73
Section 19.2: Use the LenB function to determine the number of bytes in a string
............................................. 73
Section 19.3: Prefer `If Len(myString) = 0 Then` over `If myString = "" Then`
......................................................... 73
Chapter 20: Working with ADO
............................................................................................................................ 74
Section 20.1: Making a connection to a data source
............................................................................................... 74
Section 20.2: Creating parameterized commands
.................................................................................................. 74
Section 20.3: Retrieving records with a query
.......................................................................................................... 75
Section 20.4: Executing non-scalar functions
.......................................................................................................... 77
Chapter 21: Concatenating strings
.................................................................................................................... 78
Section 21.1: Concatenate an array of strings using the Join function
.................................................................. 78
Section 21.2: Concatenate strings using the & operator
......................................................................................... 78
Chapter 22: Assigning strings with repeated characters
....................................................................... 79
Section 22.1: Use the String function to assign a string with n repeated characters
........................................... 79
Section 22.2: Use the String and Space functions to assign an n-character string
............................................. 79
Chapter 23: Scripting.Dictionary object
.......................................................................................................... 80
Section 23.1: Properties and Methods
....................................................................................................................... 80
Chapter 24: VBA Option Keyword
...................................................................................................................... 82
Section 24.1: Option Explicit
........................................................................................................................................ 82
Section 24.2: Option Base {0 | 1}
................................................................................................................................ 83
Section 24.3: Option Compare {Binary | Text | Database}
...................................................................................... 84
Chapter 25: Date Time Manipulation
................................................................................................................ 87
Section 25.1: Calendar
................................................................................................................................................. 87
Section 25.2: Base functions
...................................................................................................................................... 87
Section 25.3: Extraction functions
.............................................................................................................................. 89
Section 25.4: Calculation functions
............................................................................................................................ 90
Section 25.5: Conversion and Creation
..................................................................................................................... 92
Chapter 26: Creating a Custom Class
............................................................................................................... 94
Section 26.1: Adding a Property to a Class
............................................................................................................... 94
Section 26.2: Class module scope, instancing and re-use
...................................................................................... 95
Section 26.3: Adding Functionality to a Class
.......................................................................................................... 95
Chapter 27: Events
..................................................................................................................................................... 97
Section 27.1: Sources and Handlers
........................................................................................................................... 97
Section 27.2: Passing data back to the event source
.............................................................................................. 99
Chapter 28: Attributes
........................................................................................................................................... 101
Section 28.1: VB_PredeclaredId
............................................................................................................................... 101
Section 28.2: VB_[Var]UserMemId
......................................................................................................................... 101
Section 28.3: VB_Exposed
........................................................................................................................................ 102
Section 28.4: VB_Description
................................................................................................................................... 103
Section 28.5: VB_Name
............................................................................................................................................ 103
Section 28.6: VB_GlobalNameSpace
...................................................................................................................... 103
Section 28.7: VB_Createable
................................................................................................................................... 104
Chapter 29: User Forms
......................................................................................................................................... 105
Section 29.1: Best Practices
...................................................................................................................................... 105
Section 29.2: Handling QueryClose
......................................................................................................................... 107
Chapter 30: Object-Oriented VBA
.................................................................................................................... 109
Section 30.1: Abstraction
........................................................................................................................................... 109
Section 30.2: Encapsulation
..................................................................................................................................... 109
Section 30.3: Polymorphism
..................................................................................................................................... 113
Chapter 31: Working With Files and Directories Without Using FileSystemObject
................. 116
Section 31.1: Determining If Folders and Files Exist
................................................................................................ 116
Section 31.2: Creating and Deleting File Folders
.................................................................................................... 117
Chapter 32: Operators
........................................................................................................................................... 118
Section 32.1: Concatenation Operators
................................................................................................................... 118
Section 32.2: Comparison Operators
...................................................................................................................... 118
Section 32.3: Bitwise \ Logical Operators
............................................................................................................... 120
Section 32.4: Mathematical Operators
................................................................................................................... 122
Chapter 33: Collections
.......................................................................................................................................... 123
Section 33.1: Getting the Item Count of a Collection
.............................................................................................. 123
Section 33.2: Determining if a Key or Item Exists in a Collection
......................................................................... 123
Section 33.3: Adding Items to a Collection
............................................................................................................. 124
Section 33.4: Removing Items From a Collection
.................................................................................................. 125
Section 33.5: Retrieving Items From a Collection
.................................................................................................. 126
Section 33.6: Clearing All Items From a Collection
................................................................................................ 127
Chapter 34: Passing Arguments ByRef or ByVal
..................................................................................... 129
Section 34.1: Passing Simple Variables ByRef And ByVal
..................................................................................... 129
Section 34.2: ByRef
................................................................................................................................................... 130
Section 34.3: ByVal
.................................................................................................................................................... 131
Chapter 35: CreateObject vs. GetObject
...................................................................................................... 133
Section 35.1: Demonstrating GetObject and CreateObject
................................................................................... 133
Chapter 36: Macro security and signing of VBA-projects/-modules
............................................. 134
Section 36.1: Create a valid digital self-signed certificate SELFCERT.EXE
........................................................... 134
Chapter 37: Data Structures
............................................................................................................................... 144
Section 37.1: Linked List
............................................................................................................................................. 144
Section 37.2: Binary Tree
.......................................................................................................................................... 145
Chapter 38: Interfaces
........................................................................................................................................... 146
Section 38.1: Multiple Interfaces in One Class - Flyable and Swimable
............................................................... 146
Section 38.2: Simple Interface - Flyable
................................................................................................................. 147
Chapter 39: Reading 2GB+ files in binary in VBA and File Hashes
.................................................. 149
Section 39.1: This have to be in a Class module, examples later referred as "Random"
.................................. 149
Section 39.2: Code for Calculating File Hash in a Standard module
................................................................... 152
Section 39.3: Calculating all Files Hash from a root Folder
.................................................................................. 154
Chapter 40: Sorting
................................................................................................................................................ 158
Section 40.1: Algorithm Implementation - Quick Sort on a One-Dimensional Array
......................................... 158
Section 40.2: Using the Excel Library to Sort a One-Dimensional Array
............................................................ 158
Chapter 41: Frequently used string manipulation
................................................................................... 161
Section 41.1: String manipulation frequently used examples
................................................................................ 161
Chapter 42: Automation or Using other applications Libraries
....................................................... 163
Section 42.1: VBScript Regular Expressions
............................................................................................................ 163

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin