VBA Notes for Professionals - Huihoo

VBA

VBA Notes for Professionals

Notes for Professionals

100+ pages

of professional hints and tricks



Free Programming Books

Disclaimer This is an unocial free book created for educational purposes and is

not aliated with ocial 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 Oce ............................................................................. 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 Oce ............................................................... 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

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download