VBA Notes for Professionals (GoalKicker.com) (Z-Library)

📄 File Format: PDF

💾 File Size: 2.2 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

VBA Notes for ProfessionalsVBA Notes for Professionals 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 100+ pages of professional hints and tricks

📄 Page 2

Contents About 1 ................................................................................................................................................................................... Chapter 1: Getting started with VBA 2 ................................................................................................................... Section 1.1: Accessing the Visual Basic Editor in Microsoft Oce 2 ............................................................................. Section 1.2: Debugging 3 .................................................................................................................................................. Section 1.3: First Module and Hello World 4 ................................................................................................................... Chapter 2: Declaring Variables 6 .............................................................................................................................. Section 2.1: Type Hints 6 .................................................................................................................................................. Section 2.2: Variables 7 .................................................................................................................................................... Section 2.3: Constants (Const) 10 ................................................................................................................................... Section 2.4: Declaring Fixed-Length Strings 11 ............................................................................................................. Section 2.5: When to use a Static variable 12 ............................................................................................................... Section 2.6: Implicit And Explicit Declaration 14 ............................................................................................................ 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 .......................................................................................................................................................

📄 Page 3

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 46 ...................................................................................... Section 9.6: Iterating elements of an array 47 .............................................................................................................. Chapter 10: Error Handling 49 .................................................................................................................................... Section 10.1: Avoiding error conditions 49 ...................................................................................................................... Section 10.2: Custom Errors 49 ........................................................................................................................................ Section 10.3: Resume keyword 50 ................................................................................................................................... Section 10.4: On Error statement 52 ............................................................................................................................... Chapter 11: Recursion 55 ................................................................................................................................................ Section 11.1: Factorials 55 ................................................................................................................................................. Section 11.2: Folder Recursion 55 .................................................................................................................................... Chapter 12: Conditional Compilation 57 ................................................................................................................ Section 12.1: Changing code behavior at compile time 57 ........................................................................................... Section 12.2: Using Declare Imports that work on all versions of Oce 58 ............................................................... Chapter 13: Data Types and Limits 60 .................................................................................................................... Section 13.1: Variant 60 ..................................................................................................................................................... Section 13.2: Boolean 61 .................................................................................................................................................. 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 ..............................................

📄 Page 4

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} 85 ...................................................................................... 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 ......................................................................................................................

📄 Page 5

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 ............................................................................................................

📄 Page 6

Section 42.2: Scripting File System Object 164 ............................................................................................................. Section 42.3: Scripting Dictionary object 164 ................................................................................................................ Section 42.4: Internet Explorer Object 165 .................................................................................................................... Chapter 43: VBA Run-Time Errors 168 ................................................................................................................... Section 43.1: Run-time error '6': Overflow 168 ............................................................................................................... Section 43.2: Run-time error '9': Subscript out of range 168 ....................................................................................... Section 43.3: Run-time error '13': Type mismatch 169 .................................................................................................. Section 43.4: Run-time error '91': Object variable or With block variable not set 169 .............................................. Section 43.5: Run-time error '20': Resume without error 170 ...................................................................................... Section 43.6: Run-time error '3': Return without GoSub 171 ........................................................................................ Chapter 44: Copying, returning and passing arrays 173 ............................................................................ Section 44.1: Passing Arrays to Proceedures 173 ......................................................................................................... Section 44.2: Copying Arrays 173 ................................................................................................................................... Section 44.3: Returning Arrays from Functions 175 ..................................................................................................... Chapter 45: Non-Latin Characters 177 .................................................................................................................. Section 45.1: Non-Latin Text in VBA Code 177 .............................................................................................................. Section 45.2: Non-Latin Identifiers and Language Coverage 178 .............................................................................. Chapter 46: API Calls 179 .............................................................................................................................................. Section 46.1: Mac APIs 179 ............................................................................................................................................... Section 46.2: Get total monitors and screen resolution 179 ........................................................................................ Section 46.3: FTP and Regional APIs 180 ....................................................................................................................... Section 46.4: API declaration and usage 183 ................................................................................................................ Section 46.5: Windows API - Dedicated Module (1 of 2) 185 ........................................................................................ Section 46.6: Windows API - Dedicated Module (2 of 2) 189 ....................................................................................... Credits 194 ............................................................................................................................................................................ You may also like 196 ......................................................................................................................................................

📄 Page 7

GoalKicker.com – VBA Notes for Professionals 1 About Please feel free to share this PDF with anyone for free, latest version of this book can be downloaded from: https://goalkicker.com/VBABook This VBA Notes for Professionals book is compiled from Stack Overflow Documentation, the content is written by the beautiful people at Stack Overflow. Text content is released under Creative Commons BY-SA, see credits at the end of this book whom contributed to the various chapters. Images may be copyright of their respective owners unless otherwise specified This is an unofficial free book created for educational purposes and is not affiliated with official VBA group(s) or company(s) nor Stack Overflow. All trademarks and registered trademarks are the property of their respective company owners The information presented in this book is not guaranteed to be correct nor accurate, use at your own risk Please send feedback and corrections to web@petercv.com

📄 Page 8

GoalKicker.com – VBA Notes for Professionals 2 Chapter 1: Getting started with VBA Version Office Versions Release Date Notes Release Date Vba6 ? - 2007 [Sometime after][1] 1992-06-30 Vba7 2010 - 2016 [blog.techkit.com][2] 2010-04-15 VBA for Mac 2004, 2011 - 2016 2004-05-11 Section 1.1: Accessing the Visual Basic Editor in Microsoft Oce You can open the VB editor in any of the Microsoft Office applications by pressing Alt + F11 or going to the Developer tab and clicking on the "Visual Basic" button. If you don't see the Developer tab in the Ribbon, check if this is enabled. By default the Developer tab is disabled. To enable the Developer tab go to File -> Options, select Customize Ribbon in the list on the left. In the right "Customize the Ribbon" treeview find the Developer tree item and set the check for the Developer checkbox to checked. Click Ok to close the Options dialog. The Developer tab is now visible in the Ribbon on which you can click on "Visual Basic" to open the Visual Basic Editor. Alternatively you can click on "View Code" to directly view the code pane of the currently active element, e.g. WorkSheet, Chart, Shape.

📄 Page 9

GoalKicker.com – VBA Notes for Professionals 3 You can use VBA to automate almost any action that can be performed interactively (manually) and also provide functionality that is not available in Microsoft Office. VBA can create a document, add text to it, format it, edit it, and save it, all without human intervention. Section 1.2: Debugging Debugging is a very powerful way to have a closer look and fix incorrectly working (or non working) code. Run code step by step First thing you need to do during debugging is to stop the code at specific locations and then run it line by line to see whether that happens what's expected. Breakpoint ( F9 , Debug - Toggle breakpoint): You can add a breakpoint to any executed line (e.g. not to declarations), when execution reaches that point it stops, and gives control to user. You can also add the Stop keyword to a blank line to have the code stop at that location on runtime. This is useful if, for example, before declaration lines to which you can't add a breakpoint with F9 Step into ( F8 , Debug - Step into): executes only one line of code, if that's a call of a user defined sub / function, then that's executed line by line. Step over ( Shift + F8 , Debug - Step over): executes one line of code, doesn't enter user defined subs / functions. Step out ( Ctrl + Shift + F8 , Debug - Step out): Exit current sub / function (run code until its end).

📄 Page 10

GoalKicker.com – VBA Notes for Professionals 4 Run to cursor ( Ctrl + F8 , Debug - Run to cursor): run code until reaching the line with the cursor. You can use Debug.Print to print lines to the Immediate Window at runtime. You may also use Debug.? as a shortcut for Debug.Print Watches window Running code line by line is only the first step, we need to know more details and one tool for that is the watch window (View - Watch window), here you can see values of defined expressions. To add a variable to the watch window, either: Right-click on it then select "Add watch". Right-click in watch window, select "Add watch". Go to Debug - Add watch. When you add a new expression you can choose whether you just want to see it's value, or also break code execution when it's true or when its value changes. Immediate Window The immediate window allows you to execute arbitrary code or print items by preceeding them with either the Print keyword or a single question mark "?" Some examples: ? ActiveSheet.Name - returns name of the active sheet Print ActiveSheet.Name - returns the name of the active sheet ? foo - returns the value of foo* x = 10 sets x to 10* * Getting/Setting values for variables via the Immediate Window can only be done during runtime Debugging best practices Whenever your code doesn't work as expected first thing you should do is to read it again carefully, looking for mistakes. If that doesn't help, then start debugging it; for short procedures it can be efficient to just execute it line by line, for longer ones you probably need to set breakpoints or breaks on watched expressions, the goal here is to find the line not working as expected. Once you have the line which gives the incorrect result, but the reason is not yet clear, try to simplify expressions, or replace variables with constants, that can help understanding whether variables' value are wrong. If you still can't solve it, and ask for help: Include as small part of your code as possible for understanding of your problem If the problem is not related to the value of variables, then replace them by constants. (so, instead of Sheets(a*b*c+d^2).Range(addressOfRange) write Sheets(4).Range("A2")) Describe which line gives the wrong behaviour, and what it is (error, wrong result...) Section 1.3: First Module and Hello World To start coding in the first place, you have to right click your VBA Project in the left list and add a new Module. Your first Hello-World Code could look like this:

📄 Page 11

GoalKicker.com – VBA Notes for Professionals 5 Sub HelloWorld() MsgBox "Hello, World!" End Sub To test it, hit the Play-Button in your Toolbar or simply hit the F5 key. Congratulations! You've built your first own VBA Module.

📄 Page 12

GoalKicker.com – VBA Notes for Professionals 6 Chapter 2: Declaring Variables Section 2.1: Type Hints Type Hints are heavily discouraged. They exist and are documented here for historical and backward-compatibility reasons. You should use the As [DataType] syntax instead. Public Sub ExampleDeclaration() Dim someInteger% '% Equivalent to "As Integer" Dim someLong& '& Equivalent to "As Long" Dim someDecimal@ '@ Equivalent to "As Currency" Dim someSingle! '! Equivalent to "As Single" Dim someDouble# '# Equivalent to "As Double" Dim someString$ '$ Equivalent to "As String" Dim someLongLong^ '^ Equivalent to "As LongLong" in 64-bit VBA hosts End Sub Type hints significantly decrease code readability and encourage a legacy Hungarian Notation which also hinders readability: Dim strFile$ Dim iFile% Instead, declare variables closer to their usage and name things for what they're used, not after their type: Dim path As String Dim handle As Integer Type hints can also be used on literals, to enforce a specific type. By default, a numeric literal smaller than 32,768 will be interpreted as an Integer literal, but with a type hint you can control that: Dim foo 'implicit Variant foo = 42& ' foo is now a Long foo = 42# ' foo is now a Double Debug.Print TypeName(42!) ' prints "Single" Type hints are usually not needed on literals, because they would be assigned to a variable declared with an explicit type, or implicitly converted to the appropriate type when passed as parameters. Implicit conversions can be avoided using one of the explicit type conversion functions: 'Calls procedure DoSomething and passes a literal 42 as a Long using a type hint DoSomething 42& 'Calls procedure DoSomething and passes a literal 42 explicitly converted to a Long DoSomething CLng(42) String-returning built-in functions The majority of the built-in functions that handle strings come in two versions: A loosely typed version that returns a Variant, and a strongly typed version (ending with $) that returns a String. Unless you are assigning the return value to a Variant, you should prefer the version that returns a String - otherwise there is an implicit conversion of the return value.

📄 Page 13

GoalKicker.com – VBA Notes for Professionals 7 Debug.Print Left(foo, 2) 'Left returns a Variant Debug.Print Left$(foo, 2) 'Left$ returns a String These functions are: VBA.Conversion.Error -> VBA.Conversion.Error$ VBA.Conversion.Hex -> VBA.Conversion.Hex$ VBA.Conversion.Oct -> VBA.Conversion.Oct$ VBA.Conversion.Str -> VBA.Conversion.Str$ VBA.FileSystem.CurDir -> VBA.FileSystem.CurDir$ VBA.[_HiddenModule].Input -> VBA.[_HiddenModule].Input$ VBA.[_HiddenModule].InputB -> VBA.[_HiddenModule].InputB$ VBA.Interaction.Command -> VBA.Interaction.Command$ VBA.Interaction.Environ -> VBA.Interaction.Environ$ VBA.Strings.Chr -> VBA.Strings.Chr$ VBA.Strings.ChrB -> VBA.Strings.ChrB$ VBA.Strings.ChrW -> VBA.Strings.ChrW$ VBA.Strings.Format -> VBA.Strings.Format$ VBA.Strings.LCase -> VBA.Strings.LCase$ VBA.Strings.Left -> VBA.Strings.Left$ VBA.Strings.LeftB -> VBA.Strings.LeftB$ VBA.Strings.LTtrim -> VBA.Strings.LTrim$ VBA.Strings.Mid -> VBA.Strings.Mid$ VBA.Strings.MidB -> VBA.Strings.MidB$ VBA.Strings.Right -> VBA.Strings.Right$ VBA.Strings.RightB -> VBA.Strings.RightB$ VBA.Strings.RTrim -> VBA.Strings.RTrim$ VBA.Strings.Space -> VBA.Strings.Space$ VBA.Strings.Str -> VBA.Strings.Str$ VBA.Strings.String -> VBA.Strings.String$ VBA.Strings.Trim -> VBA.Strings.Trim$ VBA.Strings.UCase -> VBA.Strings.UCase$ Note that these are function aliases, not quite type hints. The Left function corresponds to the hidden B_Var_Left function, while the Left$ version corresponds to the hidden B_Str_Left function. In very early versions of VBA the $ sign isn't an allowed character and the function name had to be enclosed in square brackets. In Word Basic, there were many, many more functions that returned strings that ended in $. Section 2.2: Variables Scope A variable can be declared (in increasing visibility level): At procedure level, using the Dim keyword in any procedure; a local variable. At module level, using the Private keyword in any type of module; a private field. At instance level, using the Friend keyword in any type of class module; a friend field. At instance level, using the Public keyword in any type of class module; a public field. Globally, using the Public keyword in a standard module; a global variable. Variables should always be declared with the smallest possible scope: prefer passing parameters to procedures, rather than declaring global variables.

📄 Page 14

GoalKicker.com – VBA Notes for Professionals 8 See Access Modifiers for more information. Local variables Use the Dim keyword to declare a local variable: Dim identifierName [As Type][, identifierName [As Type], ...] The [As Type] part of the declaration syntax is optional. When specified, it sets the variable's data type, which determines how much memory will be allocated to that variable. This declares a String variable: Dim identifierName As String When a type is not specified, the type is implicitly Variant: Dim identifierName 'As Variant is implicit The VBA syntax also supports declaring multiple variables in a single statement: Dim someString As String, someVariant, someValue As Long Notice that the [As Type] has to be specified for each variable (other than 'Variant' ones). This is a relatively common trap: Dim integer1, integer2, integer3 As Integer 'Only integer3 is an Integer. 'The rest are Variant. Static variables Local variables can also be Static. In VBA the Static keyword is used to make a variable "remember" the value it had, last time a procedure was called: Private Sub DoSomething() Static values As Collection If values Is Nothing Then Set values = New Collection values.Add "foo" values.Add "bar" End If DoSomethingElse values End Sub Here the values collection is declared as a Static local; because it's an object variable, it is initialized to Nothing. The condition that follows the declaration verifies if the object reference was Set before - if it's the first time the procedure runs, the collection gets initialized. DoSomethingElse might be adding or removing items, and they'll still be in the collection next time DoSomething is called. Alternative VBA's Static keyword can easily be misunderstood - especially by seasoned programmers that usually work in other languages. In many languages, static is used to make a class member (field, property, method, ...) belong to the type rather than to the instance. Code in static context cannot reference code in instance context. The VBA Static keyword means something wildly different.

📄 Page 15

GoalKicker.com – VBA Notes for Professionals 9 Often, a Static local could just as well be implemented as a Private, module-level variable (field) - however this challenges the principle by which a variable should be declared with the smallest possible scope; trust your instincts, use whichever you prefer - both will work... but using Static without understanding what it does could lead to interesting bugs. Dim vs. Private The Dim keyword is legal at procedure and module levels; its usage at module level is equivalent to using the Private keyword: Option Explicit Dim privateField1 As Long 'same as Private privateField2 as Long Private privateField2 As Long 'same as Dim privateField2 as Long The Private keyword is only legal at module level; this invites reserving Dim for local variables and declaring module variables with Private, especially with the contrasting Public keyword that would have to be used anyway to declare a public member. Alternatively use Dim everywhere - what matters is consistency: "Private fields" DO use Private to declare a module-level variable. DO use Dim to declare a local variable. DO NOT use Dim to declare a module-level variable. "Dim everywhere" DO use Dim to declare anything private/local. DO NOT use Private to declare a module-level variable. AVOID declaring Public fields.* *In general, one should avoid declaring Public or Global fields anyway. Fields A variable declared at module level, in the declarations section at the top of the module body, is a field. A Public field declared in a standard module is a global variable: Public PublicField As Long A variable with a global scope can be accessed from anywhere, including other VBA projects that would reference the project it's declared in. To make a variable global/public, but only visible from within the project, use the Friend modifier: Friend FriendField As Long This is especially useful in add-ins, where the intent is that other VBA projects reference the add-in project and can consume the public API. Friend FriendField As Long 'public within the project, aka for "friend" code Public PublicField As Long 'public within and beyond the project Friend fields are not available in standard modules.

📄 Page 16

GoalKicker.com – VBA Notes for Professionals 10 Instance Fields A variable declared at module level, in the declarations section at the top of the body of a class module (including ThisWorkbook, ThisDocument, Worksheet, UserForm and class modules), is an instance field: it only exists as long as there's an instance of the class around. '> Class1 Option Explicit Public PublicField As Long '> Module1 Option Explicit Public Sub DoSomething() 'Class1.PublicField means nothing here With New Class1 .PublicField = 42 End With 'Class1.PublicField means nothing here End Sub Encapsulating fields Instance data is often kept Private, and dubbed encapsulated. A private field can be exposed using a Property procedure. To expose a private variable publicly without giving write access to the caller, a class module (or a standard module) implements a Property Get member: Option Explicit Private encapsulated As Long Public Property Get SomeValue() As Long SomeValue = encapsulated End Property Public Sub DoSomething() encapsulated = 42 End Sub The class itself can modify the encapsulated value, but the calling code can only access the Public members (and Friend members, if the caller is in the same project). To allow the caller to modify: An encapsulated value, a module exposes a Property Let member. An encapsulated object reference, a module exposes a Property Set member. Section 2.3: Constants (Const) If you have a value that never changes in your application, you can define a named constant and use it in place of a literal value. You can use Const only at module or procedure level. This means the declaration context for a variable must be a class, structure, module, procedure, or block, and cannot be a source file, namespace, or interface. Public Const GLOBAL_CONSTANT As String = "Project Version #1.000.000.001" Private Const MODULE_CONSTANT As String = "Something relevant to this Module" Public Sub ExampleDeclaration()

📄 Page 17

GoalKicker.com – VBA Notes for Professionals 11 Const SOME_CONSTANT As String = "Hello World" Const PI As Double = 3.141592653 End Sub Whilst it can be considered good practice to specify Constant types, it isn't strictly required. Not specifying the type will still result in the correct type: Public Const GLOBAL_CONSTANT = "Project Version #1.000.000.001" 'Still a string Public Sub ExampleDeclaration() Const SOME_CONSTANT = "Hello World" 'Still a string Const DERIVED_CONSTANT = SOME_CONSTANT 'DERIVED_CONSTANT is also a string Const VAR_CONSTANT As Variant = SOME_CONSTANT 'VAR_CONSTANT is Variant/String Const PI = 3.141592653 'Still a double Const DERIVED_PI = PI 'DERIVED_PI is also a double Const VAR_PI As Variant = PI 'VAR_PI is Variant/Double End Sub Note that this is specific to Constants and in contrast to variables where not specifying the type results in a Variant type. While it is possible to explicitly declare a constant as a String, it is not possible to declare a constant as a string using fixed-width string syntax 'This is a valid 5 character string constant Const FOO As String = "ABCDE" 'This is not valid syntax for a 5 character string constant Const FOO As String * 5 = "ABCDE" Section 2.4: Declaring Fixed-Length Strings In VBA, Strings can be declared with a specific length; they are automatically padded or truncated to maintain that length as declared. Public Sub TwoTypesOfStrings() Dim FixedLengthString As String * 5 ' declares a string of 5 characters Dim NormalString As String Debug.Print FixedLengthString ' Prints " " Debug.Print NormalString ' Prints "" FixedLengthString = "123" ' FixedLengthString now equals "123 " NormalString = "456" ' NormalString now equals "456" FixedLengthString = "123456" ' FixedLengthString now equals "12345" NormalString = "456789" ' NormalString now equals "456789" End Sub

📄 Page 18

GoalKicker.com – VBA Notes for Professionals 12 Section 2.5: When to use a Static variable A Static variable declared locally is not destructed and does not lose its value when the Sub procedure is exited. Subsequent calls to the procedure do not require re-initialization or assignment although you may want to 'zero' any remembered value(s). These are particularly useful when late binding an object in a 'helper' sub that is called repeatedly. Snippet 1: Reuse a Scripting.Dictionary object across many worksheets Option Explicit Sub main() Dim w As Long For w = 1 To Worksheets.Count processDictionary ws:=Worksheets(w) Next w End Sub Sub processDictionary(ws As Worksheet) Dim i As Long, rng As Range Static dict As Object If dict Is Nothing Then 'initialize and set the dictionary object Set dict = CreateObject("Scripting.Dictionary") dict.CompareMode = vbTextCompare Else 'remove all pre-existing dictionary entries ' this may or may not be desired if a single dictionary of entries ' from all worksheets is preferred dict.RemoveAll End If With ws 'work with a fresh dictionary object for each worksheet ' without constructing/destructing a new object each time ' or do not clear the dictionary upon subsequent uses and ' build a dictionary containing entries from all worksheets End With End Sub Snippet 2: Create a worksheet UDF that late binds the VBScript.RegExp object Option Explicit Function numbersOnly(str As String, _ Optional delim As String = ", ") Dim n As Long, nums() As Variant Static rgx As Object, cmat As Object 'with rgx as static, it only has to be created once 'this is beneficial when filling a long column with this UDF If rgx Is Nothing Then Set rgx = CreateObject("VBScript.RegExp") Else Set cmat = Nothing

📄 Page 19

GoalKicker.com – VBA Notes for Professionals 13 End If With rgx .Global = True .MultiLine = True .Pattern = "[0-9]{1,999}" If .Test(str) Then Set cmat = .Execute(str) 'resize the nums array to accept the matches ReDim nums(cmat.Count - 1) 'populate the nums array with the matches For n = LBound(nums) To UBound(nums) nums(n) = cmat.Item(n) Next n 'convert the nums array to a delimited string numbersOnly = Join(nums, delim) Else numbersOnly = vbNullString End If End With End Function Example of UDF with Static object filled through a half-million rows *Elapsed times to fill 500K rows with UDF: - with Dim rgx As Object: 148.74 seconds - with Static rgx As Object: 26.07 seconds * These should be considered for relative comparison only. Your own results will vary according to the complexity and scope of the operations performed. Remember that a UDF is not calculated once in the lifetime of a workbook. Even a non-volatile UDF will recalculate whenever the values within the range(s) it references are subject to change. Each subsequent recalculation event only increases the benefits of a statically declared variable. A Static variable is available for the lifetime of the module, not the procedure or function in which it was declared and assigned. Static variables can only be declared locally. Static variable hold many of the same properties of a private module level variable but with a more restricted scope. Related reference: Static (Visual Basic)

📄 Page 20

GoalKicker.com – VBA Notes for Professionals 14 Section 2.6: Implicit And Explicit Declaration If a code module does not contain Option Explicit at the top of the module, then the compiler will automatically (that is, "implicitly") create variables for you when you use them. They will default to variable type Variant. Public Sub ExampleDeclaration() someVariable = 10 ' someOtherVariable = "Hello World" 'Both of these variables are of the Variant type. End Sub In the above code, if Option Explicit is specified, the code will interrupt because it is missing the required Dim statements for someVariable and someOtherVariable. Option Explicit Public Sub ExampleDeclaration() Dim someVariable As Long someVariable = 10 Dim someOtherVariable As String someOtherVariable = "Hello World" End Sub It is considered best practice to use Option Explicit in code modules, to ensure that you declare all variables. See VBA Best Practices how to set this option by default. Section 2.7: Access Modifiers The Dim statement should be reserved for local variables. At module-level, prefer explicit access modifiers: Private for private fields, which can only be accessed within the module they're declared in. Public for public fields and global variables, which can be accessed by any calling code. Friend for variables public within the project, but inaccessible to other referencing VBA projects (relevant for add-ins) Global can also be used for Public fields in standard modules, but is illegal in class modules and is obsolete anyway - prefer the Public modifier instead. This modifier isn't legal for procedures either. Access modifiers are applicable to variables and procedures alike. Private ModuleVariable As String Public GlobalVariable As String Private Sub ModuleProcedure() ModuleVariable = "This can only be done from within the same Module" End Sub Public Sub GlobalProcedure() GlobalVariable = "This can be done from any Module within this Project"