XLib

XLib is very small (~60 KB), open source, free, MIT Licensed, VBA function library that adds around 120 additional functions to VBA, making it much easier to develop VBA application for the Microsoft Office programs.

Some additional characteristics of Xlib include:

• MIT Licensed
• 0 dependencies
• Extremely portable
• Uses late bindings for all functions
  • Late bindings make it easy to install and include within your own projects without having to configure your office programs to use the library. Additionally, when you ship an Office file to another user, they won't need to install or configure anything to use the XLib functions, making it very portable.
• Well tested
  • I have written tests for virtually every single function in XLib, which can be found in the github reposity, so you can quickly see if the functions work on your machine and with your version of the Office programs.
• Works with Excel, Word, PowerPoint, Outlook, Access, and Publisher
  • When needed, XLib checks the Office program that is calling a function and runs different code to ensure that the same API works with many of the different Office programs.
• Many modules are cross platform with Mac
  • Conditional compilation is used so that many of the XLib functions work on Mac as well.

Sample of XLib functions

• Len2 -> Returns length of Strings, Arrays, Dictionaries, Collections, and any other objects that implement the property .Count, including Workbooks, Sheets, Worksheets, Ranges, Documents, Presentations, Slides, and many other Office Objects
• Sort -> Sorts an array in ascedning or descending order
• Large/Small -> Same as the Large() and Small() functions in Excel, but can be used in Word, PowerPoint, etc.
• SubstrFind -> Returns all characters between two substring
• IsInArray -> Returns True if the value is found in an array
• Quarter -> Returns the quarter of the year
• RandBetween -> Same as Excel RandBetween(), but can be used in Word, PowerPoint, etc.
• RegexTest -> Tests if the regex is found in a string
• Jsonify -> Converts arrays into JSON format
• Http -> Performs a web request and returns the response, with options to set headers, send post data, etc.
• ReadFile -> To easily read files
• WriteFile -> To easily write files

Installation

Xlib is written in pure VBA code and uses late bindings, so installation is as simple as importing the Xlib.min.bas module, or alternatively you can simply copy and paste the source code from the Xlib.min.bas module on the github repository.

Table Of Contents

Below are a list of all Modules in XLib and all functions within those modules:

• Array

  • AverageHigh
  • AverageLow
  • CountUnique
  • IsInArray
  • Large
  • Reverse
  • Small
  • Sort
  • SumHigh
  • SumLow

• Color

  • Hex2Hsl
  • Hex2Rgb
  • Hsl2Hex
  • Hsl2Rgb
  • Rgb2Hex
  • Rgb2Hsl
  • Rgb2Hsv

• DateTime

  • DaysOfMonth
  • MonthName2
  • Quarter
  • TimeConverter
  • WeekOfMonth
  • WeekdayName2

• Environment

  • ComputerName
  • OS
  • UserDomain
  • UserName

• File

  • CountFiles
  • CountFilesAndFolders
  • CountFolders
  • CurrentFilePath
  • FileCreationTime
  • FileDrive
  • FileExtension
  • FileFolder
  • FileLastModifiedTime
  • FileName
  • FileSize
  • FileType
  • GetActivePath
  • GetActivePathAccess
  • GetActivePathAndName
  • GetActivePathAndNameAccess
  • GetActivePathAndNameExcel
  • GetActivePathAndNamePowerPoint
  • GetActivePathAndNamePublisher
  • GetActivePathAndNameWord
  • GetActivePathExcel
  • GetActivePathPowerPoint
  • GetActivePathPublisher
  • GetActivePathWord
  • GetFileNameByNumber
  • PathJoin
  • PathSeparator
  • ReadFile
  • WriteFile

• Math

  • Ceil
  • Floor
  • InterpolateNumber
  • InterpolatePercent
  • Max
  • Min
  • ModFloat

• Meta

  • XlibCredits
  • XlibDocumentation
  • XlibVersion

• Network

  • Http
  • ParseHtmlString
  • SimpleHttp

• Random

  • BigRandBetween
  • RandBetween
  • RandBetweens
  • RandBool
  • RandomRange
  • RandomSample

• Regex

  • RegexReplace
  • RegexSearch
  • RegexTest

• StringManipulation

  • CamelCase
  • Capitalize
  • CompanyCase
  • CountLowercaseCharacters
  • CountUppercaseCharacters
  • CountWords
  • DedentText
  • EliteCase
  • Formatter
  • InSplit
  • IndentText
  • KebabCase
  • LeftFind
  • LeftSearch
  • LeftSplit
  • NumberOfUppercaseLetters
  • RemoveCharacters
  • Repeat
  • ReverseText
  • ReverseWords
  • RightFind
  • RightSearch
  • RightSplit
  • ScrambleCase
  • ShortenText
  • SplitText
  • Substr
  • SubstrFind
  • SubstrSearch
  • TextJoin
  • TrimChar
  • TrimLeft
  • TrimRight
  • Zfill

• StringMetrics

  • Damerau
  • Hamming
  • Levenshtein

• Utilities

  • BigDec2Hex
  • BigHex
  • CallTextToSpeech
  • Dec2Hex
  • Hex2Dec
  • HideText
  • HtmlEscape
  • HtmlUnescape
  • JavaScript
  • Jsonify
  • Len2
  • SpeakText
  • UuidFour

• Validators

  • CreditCardName
  • FormatCreditCard
  • IsCreditCard
  • IsEmail
  • IsIPFour
  • IsMacAddress
  • IsPhone
  • IsUrl

Documentation

Array Module

This module contains a set of functions for manipulating and working with arrays.

========================================

AverageHigh

This function returns the average of the top values of the number specified in the second argument. For example, if the second argument is 3, only the top 3 values will be averaged

Arguments

• array1 {Variant}: is the range that will be averaged
• numberAveraged {Integer}: is the number of the top values that will be averaged

Returns

• {Variant}: Returns the average of the top numbers specified

Examples

• =AverageHigh({1,2,3,4}, 2) -> 3.5; as 3 and 4 will be averaged
• =AverageHigh({1,2,3,4}, 3) -> 3; as 2, 3, and 4 will be averaged

========================================

AverageLow

This function returns the average of the bottom values of the number specified in the second argument. For example, if the second argument is 3, only the bottom 3 values will be averaged

Arguments

• array1 {Variant}: is the range that will be averaged
• numberAveraged {Integer}: is the number of the bottom values that will be averaged

Returns

• {Variant}: Returns the average of the bottom numbers specified

Examples

• =AverageLow({1,2,3,4}, 2) -> 1.5; as 1 and 2 will be averaged
• =AverageLow({1,2,3,4}, 3) -> 2; as 1, 2, and 3 will be averaged

========================================

CountUnique

This function counts the number of unique occurances of values within a range or multiple ranges

Arguments

• array1 {Variant}: is the group of cells we are counting the unique values of

Returns

• {Variant}: Returns the number of unique values

Examples

• =CountUnique(1, 2, 2, 3) -> 3;
• =CountUnique("a", "a", "a") -> 1;
• =CountUnique(arr) -> 3; Where arr = [1, 2, 4, 4, 1]

========================================

IsInArray

This function checks if a value is in an array

Arguments

• value1 {Variant}: is the value that will be checked if its in the array
• array1 {Variant}: is the array

Returns

• {Boolean}: Returns boolean True if the value is in the array, and false otherwise

Examples

• =IsInArray("hello", {"one", 2, "hello"}) -> True
• =IsInArray("hello", {1, "two", "three"}) -> False

========================================

Large

This function returns the nth highest number an in array, similar to Excel's LARGE function.

Arguments

• array1 {Variant}: is the array that the number will be pulled from
• nthNumber {Integer}: is the number of the top value that will be chosen. For example, a nthNumber of 1 results in the 1st highest value being chosen, when a number of 2 results in the 2nd, etc.

Returns

• {Variant}: Returns the nth highest number

Examples

• =Large({1,2,3,4}, 1) -> 4
• =Large({1,2,3,4}, 2) -> 3

========================================

Reverse

This function takes an array and reverses all its elements

Arguments

• array1 {Variant}: is the array that will be reversed

Returns

• {Variant}: Returns the a reversed array

Examples

• =Reverse({1,2,3}) -> {3,2,1}

========================================

Small

This function returns the nth lowest number an in array, similar to Excel's SMALL function.

Arguments

• array1 {Variant}: is the array that the number will be pulled from
• nthNumber {Integer}: is the number of the bottom value that will be chosen. For example, a nthNumber of 1 results in the 1st smallest value being chosen, when a number of 2 results in the 2nd, etc.

Returns

• {Variant}: Returns the nth smallest number

Examples

• =Small({1,2,3,4}, 1) -> 1
• =Small({1,2,3,4}, 2) -> 2

========================================

Sort

This function is an implementation of Bubble Sort, allowing the user to sort an array, optionally allowing the user to specify the array to be sorted in descending order

Arguments

• sortableArray {Variant}: is the array that will be sorted
• (Optional) [descendingFlag {Boolean}]: changes the sort to descending

Returns

• {Variant}: Returns the a sorted array

Examples

• =Sort({1,3,2}) -> {1,2,3}
• =Sort({1,3,2}, True) -> {3,2,1}

========================================

SumHigh

This function returns the sum of the top values of the number specified in the second argument. For example, if the second argument is 3, only the top 3 values will be summed

Arguments

• array1 {Variant}: is the range that will be summed
• numberSummed {Integer}: is the number of the top values that will be summed

Returns

• {Variant}: Returns the sum of the top numbers specified

Examples

• =SumHigh({1,2,3,4}, 2) -> 7; as 3 and 4 will be summed
• =SumHigh({1,2,3,4}, 3) -> 9; as 2, 3, and 4 will be summed

========================================

SumLow

This function returns the sum of the bottom values of the number specified in the second argume