VBA

Notes for ProfessionalsVBA

Notes for Professionals

100+ pages

of professional hints and tricks

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

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

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 Modiﬁers

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

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

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

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

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

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

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

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 ﬁxed-width string

Section 6.5: Declare and assign a string array

Section 6.6: Assign speciﬁc 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 speciﬁc 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 ﬁnd the position of the last instance of a substring

Section 10.3: Use InStr to ﬁnd the position of the ﬁrst 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 ﬁle path

Section 25.2: Retrieve just the extension from a ﬁle name

Section 25.3: Recursively enumerate folders and ﬁles

Section 25.4: Strip ﬁle extension from a ﬁle name

Section 25.5: Enumerate ﬁles in a directory using FileSystemObject

Section 25.6: Creating a FileSystemObject

Section 25.7: Reading a text ﬁle using a FileSystemObject

Section 25.8: Creating a text ﬁle with FileSystemObject

Section 25.9: Using FSO.BuildPath to build a Full Path from folder path and ﬁle name

Section 25.10: Writing to an existing ﬁle 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+ ﬁles in binary in VBA and File Hashes

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

100

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

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

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

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

100

103

105

Chapter 28: Creating a procedure

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

109

Section 28.1: Introduction to procedures

Section 28.2: Function With Examples

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

109

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

109

Chapter 29: Procedure Calls

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

111

Section 29.1: This is confusing. Why not just always use parentheses?

Section 29.2: Implicit Call Syntax

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

111

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

111

112

113

117

124

Section 29.3: Optional Arguments

Section 29.4: Explicit Call Syntax

Section 29.5: Return Values

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

112

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

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

Chapter 30: Conditional Compilation

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

114

Section 30.1: Changing code behavior at compile time

Section 30.2: Using Declare Imports that work on all versions of Oce

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

114

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

115

Chapter 31: Object-Oriented VBA

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

117

Section 31.1: Abstraction

Section 31.2: Encapsulation

Section 31.3: Polymorphism

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

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

121

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

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

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

125

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

125

Chapter 33: Interfaces

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

127

Section 33.1: Multiple Interfaces in One Class - Flyable and Swimable

Section 33.2: Simple Interface - Flyable

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

128

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

127

Chapter 34: Recursion

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

130

Section 34.1: Factorials

Section 34.2: Folder Recursion

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

130

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

130

Chapter 35: Events

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

132

Section 35.1: Sources and Handlers

Section 35.2: Passing data back to the event source

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

132

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

134

Chapter 36: Scripting.Dictionary object

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

136

Section 36.1: Properties and Methods

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

136

Chapter 37: Working with ADO

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

138

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

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

138

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

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

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

Chapter 38: Attributes

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

142

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

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

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

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

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

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

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

144

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

145

138

139

141

142

143

144

Chapter 39: User Forms

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

146

Section 39.1: Best Practices

Section 39.2: Handling QueryClose

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

146

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

148

Chapter 40: CreateObject vs. GetObject

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

150

Section 40.1: Demonstrating GetObject and CreateObject

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

150

Chapter 41: Non-Latin Characters

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

151

Section 41.1: Non-Latin Text in VBA Code

Section 41.2: Non-Latin Identiﬁers and Language Coverage

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

151

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

152

Chapter 42: API Calls

Section 42.1: Mac APIs

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

153

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

153

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)

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

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

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

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

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

163

Chapter 43: Automation or Using other applications Libraries

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

168

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

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

168

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

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

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

Chapter 44: Macro security and signing of VBA-projects/-modules

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

173

Section 44.1: Create a valid digital self-signed certiﬁcate SELFCERT.EXE

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

173

Chapter 45: VBA Run-Time Errors

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

183

Section 45.1: Run-time error '6': Overﬂow

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

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

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

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

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

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

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

184

Chapter 46: Error Handling

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

188

Section 46.1: Avoiding error conditions

Section 46.2: Custom Errors

Section 46.3: Resume keyword

Section 46.4: On Error statement

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

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

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

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

188

Credits

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

194