VBA Notes for Professionals book

Content list
About
Chapter 1: Getting started with VBA
Section 1.1: Accessing the Visual Basic Editor in Microsoft Oce
Section 1.2: Debugging
Section 1.3: First Module and Hello World
Chapter 2: Comments
Section 2.1: Apostrophe Comments
Section 2.2: REM Comments
Chapter 3: String Literals - Escaping, non-printable characters and line-continuations
Section 3.1: Escaping the " character
Section 3.2: Assigning long string literals
Section 3.3: Using VBA string constants
Chapter 4: VBA Option Keyword
Section 4.1: Option Explicit
Section 4.2: Option Base {0 | 1}
Section 4.3: Option Compare {Binary | Text | Database}
Chapter 5: Declaring Variables
Section 5.1: Type Hints
Section 5.2: Variables
Section 5.3: Constants (Const)
Section 5.4: Declaring Fixed-Length Strings
Section 5.5: When to use a Static variable
Section 5.6: Implicit And Explicit Declaration
Section 5.7: Access Modifiers
Chapter 6: Declaring and assigning strings
Section 6.1: Assignment to and from a byte array
Section 6.2: Declare a string constant
Section 6.3: Declare a variable-width string variable
Section 6.4: Declare and assign a fixed-width string
Section 6.5: Declare and assign a string array
Section 6.6: Assign specific characters within a string using Mid statement
Chapter 7: Concatenating strings
Section 7.1: Concatenate an array of strings using the Join function
Section 7.2: Concatenate strings using the & operator
Chapter 8: Frequently used string manipulation
Section 8.1: String manipulation frequently used examples
Chapter 9: Substrings
Section 9.1: Use Left or Left$ to get the 3 left-most characters in a string
Section 9.2: Use Right or Right$ to get the 3 right-most characters in a string
Section 9.3: Use Mid or Mid$ to get specific characters from within a string
Section 9.4: Use Trim to get a copy of the string without any leading or trailing spaces
Chapter 10: Searching within strings for the presence of substrings
Section 10.1: Use InStr to determine if a string contains a substring
Section 10.2: Use InStrRev to find the position of the last instance of a substring
Section 10.3: Use InStr to find the position of the first instance of a substring
Chapter 11: Assigning strings with repeated characters
Section 11.1: Use the String function to assign a string with n repeated characters
Section 11.2: Use the String and Space functions to assign an n-character string
Chapter 12: Measuring the length of strings
Section 12.1: Use the Len function to determine the number of characters in a string
Section 12.2: Use the LenB function to determine the number of bytes in a string
Section 12.3: Prefer `If Len(myString) = 0 Then` over `If myString = "" Then`
Chapter 13: Converting other types to strings
Section 13.1: Use CStr to convert a numeric type to a string
Section 13.2: Use Format to convert and format a numeric type as a string
Section 13.3: Use StrConv to convert a byte-array of single-byte characters to a string
Section 13.4: Implicitly convert a byte array of multi-byte-characters to a string
Chapter 14: Date Time Manipulation
Section 14.1: Calendar
Section 14.2: Base functions
Section 14.3: Extraction functions
Section 14.4: Calculation functions
Section 14.5: Conversion and Creation
Chapter 15: Data Types and Limits
Section 15.1: Variant
Section 15.2: Boolean
Section 15.3: String
Section 15.4: Byte
Section 15.5: Currency
Section 15.6: Decimal
Section 15.7: Integer
Section 15.8: Long
Section 15.9: Single
Section 15.10: Double
Section 15.11: Date
Section 15.12: LongLong
Section 15.13: LongPtr
Chapter 16: Naming Conventions
Section 16.1: Variable Names
Section 16.2: Procedure Names
Chapter 17: Data Structures
Section 17.1: Linked List
Section 17.2: Binary Tree
Chapter 18: Arrays
Section 18.1: Multidimensional Arrays
Section 18.2: Dynamic Arrays (Array Resizing and Dynamic Handling)
Section 18.3: Jagged Arrays (Arrays of Arrays)
Section 18.4: Declaring an Array in VBA
Section 18.5: Use of Split to create an array from a string
Section 18.6: Iterating elements of an array
Chapter 19: Copying, returning and passing arrays
Section 19.1: Passing Arrays to Proceedures
Section 19.2: Copying Arrays
Section 19.3: Returning Arrays from Functions
Chapter 20: Collections
Section 20.1: Getting the Item Count of a Collection
Section 20.2: Determining if a Key or Item Exists in a Collection
Section 20.3: Adding Items to a Collection
Section 20.4: Removing Items From a Collection
Section 20.5: Retrieving Items From a Collection
Section 20.6: Clearing All Items From a Collection
Chapter 21: Operators
Section 21.1: Concatenation Operators
Section 21.2: Comparison Operators
Section 21.3: Bitwise \ Logical Operators
Section 21.4: Mathematical Operators
Chapter 22: Sorting
Section 22.1: Algorithm Implementation - Quick Sort on a One-Dimensional Array
Section 22.2: Using the Excel Library to Sort a One-Dimensional Array
Chapter 23: Flow control structures
Section 23.1: For loop
Section 23.2: Select Case
Section 23.3: For Each loop
Section 23.4: Do loop
Section 23.5: While loop
Chapter 24: Passing Arguments ByRef or ByVal
Section 24.1: Passing Simple Variables ByRef And ByVal
Section 24.2: ByRef
Section 24.3: ByVal
Chapter 25: Scripting.FileSystemObject
Section 25.1: Retrieve only the path from a file path
Section 25.2: Retrieve just the extension from a file name
Section 25.3: Recursively enumerate folders and files
Section 25.4: Strip file extension from a file name
Section 25.5: Enumerate files in a directory using FileSystemObject
Section 25.6: Creating a FileSystemObject
Section 25.7: Reading a text file using a FileSystemObject
Section 25.8: Creating a text file with FileSystemObject
Section 25.9: Using FSO.BuildPath to build a Full Path from folder path and file name
Section 25.10: Writing to an existing file with FileSystemObject
Chapter 26: Working With Files and Directories Without Using FileSystemObject
Section 26.1: Determining If Folders and Files Exist
Section 26.2: Creating and Deleting File Folders
Chapter 27: Reading 2GB+ files in binary in VBA and File Hashes
Section 27.1: This have to be in a Class module, examples later referred as "Random"
Section 27.2: Code for Calculating File Hash in a Standard module
Section 27.3: Calculating all Files Hash from a root Folder
Chapter 28: Creating a procedure
Section 28.1: Introduction to procedures
Section 28.2: Function With Examples
Chapter 29: Procedure Calls
Section 29.1: This is confusing. Why not just always use parentheses?
Section 29.2: Implicit Call Syntax
Section 29.3: Optional Arguments
Section 29.4: Explicit Call Syntax
Section 29.5: Return Values
Chapter 30: Conditional Compilation
Section 30.1: Changing code behavior at compile time
Section 30.2: Using Declare Imports that work on all versions of Oce
Chapter 31: Object-Oriented VBA
Section 31.1: Abstraction
Section 31.2: Encapsulation
Section 31.3: Polymorphism
Chapter 32: Creating a Custom Class
Section 32.1: Adding a Property to a Class
Section 32.2: Class module scope, instancing and re-use
Section 32.3: Adding Functionality to a Class
Chapter 33: Interfaces
Section 33.1: Multiple Interfaces in One Class - Flyable and Swimable
Section 33.2: Simple Interface - Flyable
Chapter 34: Recursion
Section 34.1: Factorials
Section 34.2: Folder Recursion
Chapter 35: Events
Section 35.1: Sources and Handlers
Section 35.2: Passing data back to the event source
Chapter 36: Scripting.Dictionary object
Section 36.1: Properties and Methods
Chapter 37: Working with ADO
Section 37.1: Making a connection to a data source
Section 37.2: Creating parameterized commands
Section 37.3: Retrieving records with a query
Section 37.4: Executing non-scalar functions
Chapter 38: Attributes
Section 38.1: VB_PredeclaredId
Section 38.2: VB_[Var]UserMemId
Section 38.3: VB_Exposed
Section 38.4: VB_Description
Section 38.5: VB_Name
Section 38.6: VB_GlobalNameSpace
Section 38.7: VB_Createable
Chapter 39: User Forms
Section 39.1: Best Practices
Section 39.2: Handling QueryClose
Chapter 40: CreateObject vs. GetObject
Section 40.1: Demonstrating GetObject and CreateObject
Chapter 41: Non-Latin Characters
Section 41.1: Non-Latin Text in VBA Code
Section 41.2: Non-Latin Identifiers and Language Coverage
Chapter 42: API Calls
Section 42.1: Mac APIs
Section 42.2: Get total monitors and screen resolution
Section 42.3: FTP and Regional APIs
Section 42.4: API declaration and usage
Section 42.5: Windows API - Dedicated Module (1 of 2)
Section 42.6: Windows API - Dedicated Module (2 of 2)
Chapter 43: Automation or Using other applications Libraries
Section 43.1: VBScript Regular Expressions
Section 43.2: Scripting File System Object
Section 43.3: Scripting Dictionary object
Section 43.4: Internet Explorer Object
Chapter 44: Macro security and signing of VBA-projects/-modules
Section 44.1: Create a valid digital self-signed certificate SELFCERT.EXE
Chapter 45: VBA Run-Time Errors
Section 45.1: Run-time error '6': Overflow
Section 45.2: Run-time error '9': Subscript out of range
Section 45.3: Run-time error '13': Type mismatch
Section 45.4: Run-time error '91': Object variable or With block variable not set
Section 45.5: Run-time error '20': Resume without error
Section 45.6: Run-time error '3': Return without GoSub
Chapter 46: Error Handling
Section 46.1: Avoiding error conditions
Section 46.2: Custom Errors
Section 46.3: Resume keyword
Section 46.4: On Error statement
Credits
You may also like