XLib


Download
Installation
X-VBA Home

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
=GetActivePathAndName
=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
=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
=Dec2Hex
=Hex2Dec
=HideText
=HtmlEscape
=HtmlUnescape
=JavaScript
=Jsonify
=Len2
=SpeakText
=UuidFour

Validators
=CreditCardName
=FormatCreditCard
=IsCreditCard
=IsEmail
=IsIPFour
=IsMacAddress
=IsPhone
=IsUrl

Utilities Module

This module contains a set of basic miscellaneous utility functions


=BigDec2Hex(number)

This function is an implementation of Dec2Hex that allows big integers up to 14-byte to be used

Arguments

{Variant}: number   ➔   is the integer that will be converted to a hex string


Returns

{String}: Returns the number rounded down to the nearest integer


Examples

  • =Dec2Hex(255, 8) -> "000000FF"
  • =Dec2Hex(3000000000, 16) -> Error; As Dec2Hex does not support integers this large
  • =BigDec2Hex(3000000000, 16) -> "00000000B2D05E00"

=BigHex(number)

This function is an implementation of the Hex() function that allows for 14-byte integers to be used

Arguments

{Variant}: number   ➔   is the number that will be converted to hex


Returns

{String}: Returns a string of the number converted to hex


Examples

  • =BigHex(255) -> "FF"
  • =Hex(3000000000) -> Error; As hex does not support big integers
  • =BigHex(3000000000) -> "B2D05E00"

=Dec2Hex(number)

This function takes an integer and converts it to a hex string, with the option to specify the number of leading zeros for the hex string returned

Arguments

{Long}: number   ➔   is the integer that will be converted to a hex string


Returns

{String}: Returns the number rounded down to the nearest integer


Examples

  • =Dec2Hex(5) -> "5"
  • =Dec2Hex(5, 2) -> "05"
  • =Dec2Hex(255, 2) -> "FF"
  • =Dec2Hex(255, 8) -> "000000FF"

=Hex2Dec(hexNumber)

This function takes a hex number as a string and converts it to a decimal long

Arguments

{String}: hexNumber   ➔   is the hex number that will be converted to a long


Returns

{Long}: Returns a decimal base number converted from the hex number


Examples

  • =Hex2Dec("FF") -> 255
  • =Hex2Dec("FFFF") -> 65535

=HideText(string1, hiddenFlag, [hideString])

This function takes the value in a cell and visibly hides it if the HideText flag set to TRUE. If TRUE, the value will appear as "********", with the option to set the HideText characters to a different set of text.

Arguments

{String}: string1   ➔   is the string that will be HideText

{Boolean}: hiddenFlag   ➔   if set to TRUE will hide string1

[(Optional) {String}: hideString]   ➔   is an optional string that if set will be used instead of "********"


Returns

{String}: Returns a string to hide string1 if hideFlag is TRUE


Examples

  • =HideText("Hello World", FALSE) -> "Hello World"
  • =HideText("Hello World", TRUE) -> "********"
  • =HideText("Hello World", TRUE, "[Hidden Text]") -> "[Hidden Text]"
  • =HideText("Hello World", UserName()="Anthony") -> "********"

=HtmlEscape(string1)

This function takes a string and escapes the HTML characters in it. For example, the character ">" will be escaped into "%gt;"

Arguments

{String}: string1   ➔   is the string that will have its characters HTML escaped


Returns

{String}: Returns an HTML escaped string


Examples

  • =HtmlEscape("

    Hello World

    ") -> "<p>Hello World</p>"

=HtmlUnescape(string1)

This function takes a string and unescapes the HTML characters in it. For example, the character "%gt;" will be escaped into ">"

Arguments

{String}: string1   ➔   is the string that will have its characters HTML unescaped


Returns

{String}: Returns an HTML unescaped string


Examples

  • =HtmlUnescape("<p>Hello World</p>") -> "

    Hello World

    "

=JavaScript(jsFuncCode, jsFuncName, [argument1])

This function executes JavaScript code using Microsoft's JScript scripting language. It takes 3 arguments, the JavaScript code that will be executed, the name of the JavaScript function that will be executed, and up to 16 optional arguments to be used in the JavaScript function that is called. One thing to note is that ES5 syntax should be used in the JavaScript code, as ES6 features are unlikely to be supported.

Arguments

{String}: jsFuncCode   ➔   is a string of the JavaScript source code that will be executed

{String}: jsFuncName   ➔   is the name of the JavaScript function that will be called

[(Optional) {Variant}: argument1]   ➔   - argument16 are optional arguments used in the JScript function call


Returns

{Variant}: Returns the result of the JavaScript function that is called


Examples

  • =JavaScript("function helloFunc(){return 'Hello World!'}", "helloFunc") -> "Hello World!"
  • =JavaScript("function addTwo(a, b){return a + b}","addTwo",12,24) -> 36

=Jsonify(indentLevel, stringArray)

This function takes an array of strings and numbers and returns the array as a JSON string. This function takes into account formatting for numbers, and supports specifying the indentation level.

Arguments

{Byte}: indentLevel   ➔   is an optional number that specifying the indentation level. Leaving this argument out will result in no indentation

{Variant}: stringArray()   ➔   is an array of strings and number in the following format: {"Hello", "World"}


Returns

{Variant}: Returns a JSON valid string of all elements in the array


Examples

  • =Jsonify(0, "Hello", "World", "1", "2", 3, 4.5) -> "["Hello","World",1,2,3,4.5]"
  • =Jsonify(0, {"Hello", "World", "1", "2", 3, 4.5}, 10) -> "["Hello","World",1,2,3,4.5]"

=Len2(val)

This function is an extension on the Len() function by returning the length of strings, arrays, numbers, and many other objects in Excel, Word, PowerPoint, and Access, including Objects such as Dictionaries. Internally, any Object that implements a .Count property will have a length returned by this function. Also, any number used within this function will be converted to a string and then its length returned.

Arguments

{Variant}: val   ➔   is the value you want the length from


Returns

{Integer}: Returns an integer of the length of the value specified


Examples

  • =Len2("Hello") -> 5; As the string is 5 characters long
  • =Len2(arr) -> 3; Where arr is an array with {1, 2, 3} in it, and the array has 3 values in it
  • =Len2("100") -> 3; As the string is 3 characters long
  • =Len2(100) -> 3; As the integer is 3 characters long when converted to a string
  • =Len2(Range("A1:A3")) -> 3; As the Excel Range has 3
  • =Len2(col) -> 5; Where col is a Collection with 5 items in it
  • =Len2(dict) -> 2; Where dict is a Dictionary with 2 key/value pairs in it
  • =Len2(Application.Documents) -> 3; Where we currently have 3 documents open
  • =Len2(Application.ActivePresentation.Slides) -> 10; Where the active PowerPoint Presentation has 10 slides

=SpeakText(textArray)

This function takes the range of the cell that this function resides, and then an array of text, and when this function is recalculated manually by the user (for example when pressing the F2 key while on the cell) this function will use Microsoft's text-to-speech to speak out the text through the speakers or microphone.

Arguments

{Variant}: textArray()   ➔   is an array of ranges, strings, or number that will be displayed


Returns

{Variant}: Returns all the strings in the text array combined as well as displays all the text in the text array


Note
Note that text-to-speech is only available on Microsoft Excel. This function will still return the combined string from the text array, but will only result in speech through the speakers in Microsoft Excel

Examples

  • =SpeakText("Hello", "World") -> "Hello World" and the text will be spoken through the speaker

=UuidFour()

This function generates a unique ID based on the UUID V4 specification. This function is useful for generating unique IDs of a fixed character length.

Arguments

None


Returns

{String}: Returns a string unique ID based on UUID V4. The format of the string will always be in the form of "xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx" where each x is a hex digit, and y is either 8, 9, A, or B.


Examples

  • =UuidFour() -> "3B4BDC26-E76E-4D6C-9E05-7AE7D2D68304"
  • =UuidFour() -> "D5761256-8385-4FDA-AD56-6AEF0AD6B9A5"
  • =UuidFour() -> "CDCAE2F5-B52F-4C90-A38A-42BD58BCED4B"