README.md

VBA-Common-Library

A library of common Excel VBA functions that I have found useful over the years.

How to Use

1. Create a module in excel called common.
2. Copy the functions you need into the common module.
3. Call functions by using common.functionName.

Base64 Encode

Encodes string to Base64.

Input: String
Output: String encoded to base64

Binary To String

Decodes binary to string. 2003 Antonin Foller, http://www.motobit.com

Input: Variant as bianary data (ex. VT_UI1 | VT_ARRAY)

Output: String

Convert Range To Delimited Lists

Converts range or named range to delimited lists.

Input:

1. String Worksheet Name (ex. "Sheet 1")
2. String Range Name (ex. "A1:B5" or "clientNames")
3. String Delimiter (ex. ";" or ", ")

Output: String Delimited List (ex. "Hello, World,")

Count Non-Blank Array Items

Count the number of items in an array that contain a value.

Input: Variant Array (ex. [1,2, ,4])
Output: Integer (ex. 3)

Damerau-Levenshtein Distance (String Metric)

This function takes two strings of any length and calculates the Damerau-Levenshtein Distance between them. Damerau-Levenshtein Distance differs from Levenshtein Distance in that it includes an additional operation, called Transpositions, which occurs when two adjacent characters are swapped. Thus, Damerau-Levenshtein Distance calculates the number of Insertions, Deletions, Substitutions, and Transpositons needed to convert string1 into string2. As a result, this function is good when it is likely that spelling errors have occured between two string where the error is simply a transposition of 2 adjacent characters.

Author: Anthony Mancini, https://github.com/x-vba/xlib

Modified By: Justin Icenhour, 2021

Required Types:

1. common.CaseSensitivity

Required References:

1. Microsoft Scripting Library

Input:

1. String String1 (ex. "foo")
2. String String2 (ex. "bar")
3. CaseSensitive Case Sensitivity (ex. CaseSensitive.Sensitive)

Output: Integer Distance (ex. "5")

Enable Events (Sub-Routine)

Enable or disable events and screen updating on the application level.

Input: Boolean Enable (ex. True or False)

Output: None

Find Query In Column

Finds queried value in a specified column and returns the row number where the query is found.

Required Functions:

1. common.getColumnLetter

Input:

1. String Search Worksheet Name (ex. "Sheet 1")
2. String Search Term (ex. "foo")
3. String Search Column (ex. "A:A")

Output: Integer Row Number

Find Query In Row

Finds queried value in a specified row and returns the column number where the query is found.

Required Functions:

1. common.getColumnLetter

Input:

1. String Search Worksheet Name (ex. "Sheet 1")
2. String Search Term (ex. "foo")
3. String Search Row (ex. "1:1")

Output: Integer Column Number

Fuzzy Find

Configurable fuzzy find algorithm for string matching.

Required Types:

1. common.CaseSensitivity

Required References:

1. Microsoft Scripting Library

Required Functions:

1. common.originalMetric
2. common.damerau
3. common.hamming
4. common.levenshtein
5. common.sorensenDice
6. common.ngrams
7. common.tversky
8. common.uniqueArrayElements
9. common.jaccard
10. common.jaroWinkler
11. common.simpleMatching
12. common.min
13. common.max

Input:

1. String Query (ex. "foo")
2. Range Search Range (ex. Range("A1:B5"))
3. Worksheet Search Sheet Name (ex. ThisWorkbook.Sheets("Sheet 1"))
4. Optional CaseSensitivity Case Sensitive (ex. CaseSensitivity.Sensitive)
5. Optional Variant Weights (ex. Array(1, .2, 3, 4, 5, .06, 7, 8, .009))
6. Optional Boolean Tversky Symmetry (ex. True)
7. Optional Variant Tversky Weights (ex. Array(1, 2))

Output: String Closest Matched Value

Generate Range of Available Printers (Sub-Routine)

Finds specified column header on specified sheet and enters a list of printers available on the network into the column below the header.

Required Functions:

1. common.findQueryInRow
2. common.getColumnLetter

Input:

1. String Destination Sheet (ex. "Sheet 1")
2. String Destination Column Header (ex. "Printer List")

Output: None

Get Column Letter

Returns the column letter for specified column number.

Input: Long Column Number
Output: String Column Letter

Hamming Distance (String Metric)

This function takes two strings of the same length and calculates the Hamming Distance between them. Hamming Distance measures how close two strings are by checking how many Substitutions are needed to turn one string into the other. Lower numbers mean the strings are closer than high numbers.

Author: Anthony Mancini, https://github.com/x-vba/xlib

Modified By: Justin Icenhour, 2021

Required Types:

1. common.CaseSensitivity

Required References:

1. Microsoft Scripting Library

Input:

1. String String1 (ex. "foo")
2. String String2 (ex. "bar")
3. CaseSensitive Case Sensitivity (ex. CaseSensitive.Sensitive)

Output: Integer Distance (ex. "5")

HTTP Request

Sends http POST or GET request and returns the response.

Input:

1. String URL (ex. "https://api.insightly.com/v3.1/Contacts/")
2. Boolean Post (ex. True or False) - Optional

Output: String HTTP Response

Jaccard Similarity Coefficient (String Metric)

Calculate the Jaccard Similarity Coefficient.

Inspired By: DigitecGalaxus, C#, https://github.com/DigitecGalaxus/Jaccard

Required Types:

1. common.CaseSensitivity

Input:

1. String String1 (ex. "foo")
2. String String2 (ex. "bar")
3. CaseSensitive Case Sensitivity (ex. CaseSensitive.Sensitive)

Output: Double Coefficient (ex. ".1234")

Jaro-Winkler Distance (String Metric)

Calculate the Jaro-Winkler distance.

Inspired By: jordanthomas, Javascript, https://github.com/jordanthomas/jaro-winkler

Required Types:

1. common.CaseSensitivity

Input:

1. String String1 (ex. "foo")
2. String String2 (ex. "bar")
3. CaseSensitive Case Sensitivity (ex. CaseSensitive.Sensitive)

Output: Double Distance (ex. ".1234")

JSON Converter (Class)

Tools for using JSON with VBA.

VBA-JSON v2.3.1 - (c) Tim Hall - https://github.com/VBA-tools/VBA-JSON

JSONLib - Copyright (c) 2013, Ryo Yokoyama http://code.google.com/p/vba-json/

VBA-UTC v1.0.6 - (c) Tim Hall - https://github.com/VBA-tools/VBA-UtcConverter

Convert to ISO

Convert local date to ISO 8601 string.

Input: Date UTC Local Date
Output: String ISO Date

Convert to JSON

Convert dictionary, collection, or array to JSON.

Input:

1. Variant Dictionary, Collection, or Array to be converted
2. Variant Whitespace - "Pretty" print json with given number of spaces per indentation (Integer) or given string
3. Long Current indentation (Default: 0)

Output: String JSON

Convert to UTC

Convert local date to UTC date.

Input: Date Local Date
Output: Date UTC Date

Parse ISO

Parse ISO 8601 date string to local date.

Input: String ISO date string
Output: Date Local Date

Parse JSON

Convert JSON to dictionary or collection.

Input: String JSON
Output: Object Dictionary or Collection

Parse UTC

Parse UTC date to local date.

Input: Date UTC Date
Output: Date Local Date

Levenshtein Distance (String Metric)

This function takes two strings of any length and calculates the Levenshtein Distance between them. Levenshtein Distance measures how close two strings are by checking how many Insertions, Deletions, or Substitutions are needed to turn one string into the other. Lower numbers mean the strings are closer than high numbers. Unlike Hamming Distance, Levenshtein Distance works for strings of any length and includes 2 more operations. However, calculation time will be slower than Hamming Distance for same length strings, so if you know the two strings are the same length, its preferred to use Hamming Distance.

Author: Anthony Mancini, https://github.com/x-vba/xlib

Modified By: Justin Icenhour, 2021

Required Types:

1. common.CaseSensitivity

Required References:

1. Microsoft Scripting Library

Input:

1. String String1 (ex. "foo")
2. String String2 (ex. "bar")
3. CaseSensitive Case Sensitivity (ex. CaseSensitive.Sensitive)

Output: Integer Distance (ex. "5")

Lock All Sheets (Sub-Routine)

Locks or unlocks all sheets, unless a sheet is provided then only that sheet will be locked or unlocked.

Input:

1. Boolean Locked (ex. True or False)
2. Worksheet Single Sheet

Output: None

Maximum Value In An Array (max)

This function takes multiple numbers or multiple arrays of numbers and returns the max number. This function also accounts for numbers that are formatted as strings by converting them into numbers.

Author: Anthony Mancini, https://github.com/x-vba/xlib

Input: Variant Numbers (ex. Array(1, 3, 5, 5, 9, 9.5))

Output: Double Max Value (ex. 9.5)

Minimum Value In An Array (min)

This function takes multiple numbers or multiple arrays of numbers and returns the min number. This function also accounts for numbers that are formatted as strings by converting them into numbers.

Author: Anthony Mancini, https://github.com/x-vba/xlib

Input: Variant Numbers (ex. Array(.5, 1, 3, 5, 5, 9, 9.5))

Output: Double Min Value (ex. .5)

nGrams

Determine the grams of a given length for a string. (ex. nGrams("Hello World", 2) = ("He", "el", "ll", "lo", "o ", " W", "Wo", "or", "rl", "ld")

Input:

1. String text (ex. "foo")

Output: Variant nGram (ex. Array("He", "el", "ll", "lo", "o ", " W", "Wo", "or", "rl", "ld"))

One Digit Number to Text

Converts any single-digit number to text.

Input: String Digit (ex. "5")
Output: String Text (ex. "Five")

Original Metric (String Metric)

String metric.

Required Types:

1. common.CaseSensitivity

Input:

1. String String1 (ex. "foo")
2. String String2 (ex. "bar")
3. CaseSensitive Case Sensitivity (ex. CaseSensitive.Sensitive)

Output: Double Metric (ex. ".5")

Remove Duplicates

Removes duplicate values from specified range.

Input:

1. Worksheet Origin Worksheet (ex. ThisWorkbook.Sheets("Sheet 1"))
2. Range Origin Range (ex. ThisWorkbook.Sheets("Sheet 1").Range("A1:B5"))

Output: Scripting.Dictionary of which the keys are the values from the range with the duplicates removed

Remove Leading String

Removes the specified leading string if it appears at the beginning of the whole string. You can determine the output to be text or an excel formula to achieve the same result.

Input:

1. String Leading string to be removed (ex. "foo ")
2. String Whole string (ex. "foo bar")
3. Boolean Return Excel formula or text (ex. True or False)

Output:

1. String Text (ex. "bar")
2. StringFormula (ex. "IF(LEFT(" & whole & ",LEN("& lead &"))=""" & lead & """,RIGHT(" & whole & ",LEN(" & whole & ")-LEN(" & lead & ")), " & whole & ")")

Save Object, Chart, or Shape as Image to Desktop (Sub-Routine)

Takes any shape, chart, or other object and exports it to you desktop as a PNG file.

Input:

1. String Name of worksheet where the object resides
2. String Name of object
3. String File name of exported image

Output: None

Simple Matching Metric (String Metric)

Calculate the simple matching metric.

Required Types:

1. common.CaseSensitivity

Input:

1. String String1 (ex. "foo")
2. String String2 (ex. "bar")
3. CaseSensitive Case Sensitivity (ex. CaseSensitive.Sensitive)

Output: Double Metric (ex. ".1234")

Sorensen-Dice Distance (String Metric)

Get the edit-distance according to Dice between two values.

Inspired By: words, Javascript, https://github.com/words/dice-coefficient

Required Types:

1. common.CaseSensitivity

Required References:

1. Microsoft Scripting Library

Required Functions:

1. common.ngrams

Input:

1. String String1 (ex. "foo")
2. String String2 (ex. "bar")
3. CaseSensitive Case Sensitivity (ex. CaseSensitive.Sensitive)

Output: Integer Distance (ex. "5")

Spell Number as Currency

Takes a number and spells it out in words eg. 1 to "One".

Required Functions:

1. common.oneDigitNumberToText
2. common.twoDigitNumberToText
3. common.threeDigitNumberToText

Input:

1. Variant Number to Spell (ex. 123.01)
2. String Name of Currency (ex. "Dollars")

Output: String Text (ex. "One Hundred Twenty Three Dollars And One Cent")

String To Binary

Encodes string to binary. 2003 Antonin Foller, http://www.motobit.com

Input: String Text (ex. "Hello World")
Output: Binary (ex. "01001000 01100101 01101100 01101100 01101111 00100000 01010111 01101111 01110010 01101100 01100100")

Three Digit Number to Text

Converts any three-digit number to text.

Required Functions:

1. common.oneDigitNumberToText
2. common.twoDigitNumberToText

Input: String Three digit number (ex. "123")
Output: String Number as text (ex. "One Hundred Twenty Three")

To Camel Case

Converts string to camel case.

Required Functions:

1. common.toPascalCase

Input: String Text (ex. "Hello World")
Output: String Text (ex. "helloWorld")

To Pascal Case

Converts string to pascal case.

Input: String Text (ex. "hElLo WoRlD")
Output: String Text (ex. "HelloWorld")

Two Digit Number to Text

Converts any two-digit number to text.

Required Functions:

1. common.oneDigitNumberToText

Input: String Two digit number (ex. "42")
Output: String Text (ex. "Forty Two")

Tversky Index (String Metric)

Computes the Tversky index between two sequences. For alpha = beta = 0.5, the index is equal to Dice's coefficient. For alpha = beta = 1, the index is equal to the Tanimoto coefficient.

Inspired By: compute-io, Javascript, https://github.com/compute-io/tversky-index

Required Types:

1. common.CaseSensitivity

Required Functions:

1. common.uniqueArrayElements

Input:

1. String String1 (ex. "foo")
2. String String2 (ex. "bar")
3. CaseSensitive Case Sensitivity (ex. CaseSensitive.Sensitive)
4. Optional Boolean Symmetry (ex. True)
5. Optional Double String1 Weight (ex. .5)
6. Optional Double String2 Weight (ex. .5)

Output: Double Index (ex. ".1234")

Unique Array Elements

Computes the unique elements of an array.

Required References:

1. Microsoft Scripting Library

Input: Variant Array (ex. Array(1,1,2,3))

Output: Variant Array (ex. Array(1,2,3))