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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- a beginners guide to arrays and do loops
- vba notes for professionals huihoo
- self introductions basic information
- identifying unique values in an array or range vba
- d r l i e w v o o n k i o n g excel vba tutor
- vba in excel 2
- working with array functions and dlls in excel vba
- vba cheat sheets automate excel
- using a vb activex dll from visual lisp or vba
- implementation of microarray data analysis
Related searches
- marketing notes for students
- strategic management notes for mba
- great job notes for students
- medical student notes for billing
- doctor notes for work
- school notes for students
- thank you notes for service provided
- encouraging notes for students
- thank you notes for professional services
- vba codes for excel
- common vba codes for excel
- nslds for professionals log in