XPlus


Download
Installation
Changelog
Support
X-VBA Home

Validators
=CREDIT_CARD_NAME
=FORMAT_CREDIT_CARD
=FORMAT_FORMULA
=FORMAT_FRACTION
=FORMAT_PHONE
=IS_CREDIT_CARD
=IS_EMAIL
=IS_IP_FOUR
=IS_MAC_ADDRESS
=IS_PHONE
=IS_URL

Utilities
=ADDIN_EXISTS
=ADDIN_INSTALLED
=AVERAGESHEET
=COUNTERRORALL
=DISPLAY_TEXT
=EVALUATE_FORMULA
=HIDDEN
=HTML_ESCAPE
=HTML_TABLEIFY
=HTML_UNESCAPE
=ISBADERROR
=ISERRORALL
=JAVASCRIPT
=JSONIFY
=MAXSHEET
=MINSHEET
=REFERENCE_EXISTS
=SPEAK_TEXT
=SUMSHEET
=UUID_FOUR

StringMetrics
=DAMERAU
=DAM_STR
=DAM_STR_OPT
=HAMMING
=LEVENSHTEIN
=LEV_STR
=LEV_STR_OPT
=PARTIAL_LOOKUP

StringManipulation
=CAMEL_CASE
=CAPITALIZE
=COMPANY_CASE
=COUNT_LOWERCASE_CHARACTERS
=COUNT_UPPERCASE_CHARACTERS
=COUNT_WORDS
=DEDENT
=ELITE_CASE
=FORMATTER
=INDENT
=INSPLIT
=INSTRING
=KEBAB_CASE
=LEFT_FIND
=LEFT_SEARCH
=LEFT_SPLIT
=REMOVE_CHARACTERS
=REPEAT
=REVERSE_TEXT
=REVERSE_WORDS
=RIGHT_FIND
=RIGHT_SEARCH
=RIGHT_SPLIT
=SCRAMBLE_CASE
=SHORTEN
=SPLIT_TEXT
=SUBSTITUTE_ALL
=SUBSTR
=SUBSTR_FIND
=SUBSTR_SEARCH
=TRIM_CHAR
=TRIM_LEFT
=TRIM_RIGHT
=ZFILL

Regex
=REGEX_REPLACE
=REGEX_SEARCH
=REGEX_TEST

Range
=ALTERNATE_COLUMNS
=ALTERNATE_ROWS
=AVERAGEHIGH
=AVERAGELOW
=AVERAGEN
=COLUMNIFY
=COUNT_UNIQUE
=COUNT_UNIQUE_COLORS
=FIRST_UNIQUE
=INRANGE
=MAXN
=MINN
=REVERSE_RANGE
=ROWIFY
=SORT_RANGE
=SUMHIGH
=SUMLOW
=SUMN

Random
=RANDBETWEENS
=RANDBOOL
=RANDOM_RANGE
=RANDOM_SAMPLE
=RANDOM_SAMPLE_PERCENT

Properties
=RANGE_COLOR
=RANGE_COMMENT
=RANGE_FONT
=RANGE_HEIGHT
=RANGE_HYPERLINK
=RANGE_NAME
=RANGE_NUMBER_FORMAT
=RANGE_WIDTH
=SHEET_CODE_NAME
=SHEET_NAME
=SHEET_TYPE
=WORKBOOK_AUTHOR
=WORKBOOK_CATEGORY
=WORKBOOK_COMMENTS
=WORKBOOK_COMPANY
=WORKBOOK_CREATION_DATE
=WORKBOOK_HYPERLINK_BASE
=WORKBOOK_KEYWORDS
=WORKBOOK_LAST_AUTHOR
=WORKBOOK_LAST_SAVE_TIME
=WORKBOOK_MANAGER
=WORKBOOK_REVISION_NUMBER
=WORKBOOK_SUBJECT
=WORKBOOK_TITLE

Polyfill
=CONCAT_TEXT
=MAX_IF
=MAX_IFS
=MIN_IF
=MIN_IFS
=TEXT_JOIN

Network
=HTTP
=PARSE_HTML_STRING
=SIMPLE_HTTP

Meta
=CREDITS
=DOCUMENTATION
=VERSION

Math
=INTERPOLATE_NUMBER
=INTERPOLATE_PERCENT

File
=COUNT_FILES
=COUNT_FILES_AND_FOLDERS
=COUNT_FOLDERS
=FILE_CREATION_TIME
=FILE_DRIVE
=FILE_EXTENSION
=FILE_FOLDER
=FILE_LAST_MODIFIED_TIME
=FILE_NAME
=FILE_PATH
=FILE_SIZE
=FILE_TYPE
=GET_FILE_NAME
=PATH_JOIN
=READ_FILE
=WRITE_FILE

Environment
=COMPUTER_NAME
=ENVIRONMENT
=OS
=USER_DOMAIN
=USER_NAME

DateTime
=DAYS_OF_MONTH
=MONTH_NAME
=QUARTER
=TIME_CONVERTER
=WEEKDAY_NAME
=WEEK_OF_MONTH

Color
=HEX2HSL
=HEX2RGB
=HSL2HEX
=HSL2RGB
=RGB2HEX
=RGB2HSL
=RGB2HSV

XPlus  

Utilities Module

This module contains a set of basic miscellaneous utility functions


=ADDIN_EXISTS(addinName, [partialNameFlag])

This takes a string name of an Excel Addin, and checks if Excel currently includes the Excel Addin.

Arguments

String: addinName   --->   is the name of a Addin we want to check if it exists

[(Optional) Boolean: partialNameFlag]   --->   if set to TRUE, will perform checks based on partial names instead of requiring the exact name of the Addin


Returns

Boolean: Returns TRUE if the Addin exists, and FALSE if it doesn't


Note
This function is case-insensitive. Also, this function checks if an Addin exists, not if the Addin is currently installed. For example, many versions of Excel include the Solver Addin, but by default this Addin is not active in many cases. ADDIN_EXISTS() will return TRUE for the Solver Addin even if it isn't currently installed. For a function that check if an Addin is currently installed, used the ADDIN_INSTALLED() function.

Examples

  • =ADDIN_EXISTS("SOLVER.XLAM") -> TRUE; Most versions of Excel will have the Solver Addin
  • =ADDIN_EXISTS("solver.xlam") -> TRUE; This function is case-insensitive
  • =ADDIN_EXISTS("NonExistantAddin.xlam") -> FALSE; As this Addin doesn't currently exist
  • =ADDIN_EXISTS("SOLVER") -> FALSE; To use partial matches, use the partialNameFlag
  • =ADDIN_EXISTS("SOLVER", TRUE) -> TRUE; As the partialNameFlag is set and so "SOLVER" will match "SOLVER.XLAM"

=ADDIN_INSTALLED(addinName, [partialNameFlag])

This takes a string name of an Excel Addin, and checks if the Addin is currently installed and active.

Arguments

String: addinName   --->   is the name of a Addin we want to check if it is installed

[(Optional) Boolean: partialNameFlag]   --->   if set to TRUE, will perform checks based on partial names instead of requiring the exact name of the Addin


Returns

Boolean: Returns TRUE if the Addin is installed, and FALSE if it doesn't


Note
This function is case-insensitive

Examples

  • =ADDIN_INSTALLED("SOLVER.XLAM") -> TRUE; Most versions of Excel will have the Solver Addin, and I currently have it installed
  • =ADDIN_INSTALLED("solver.xlam") -> TRUE; This function is case-insensitive
  • =ADDIN_INSTALLED("EUROTOOL.XLAM") -> FALSE; Many versions of Excel will have the Eurotools Addin, and it currently exists, but I currently don't have it installed, so this function returned FALSE
  • =ADDIN_INSTALLED("SOLVER", TRUE) -> TRUE; As the partialNameFlag is set and so "SOLVER" will match "SOLVER.XLAM"

=AVERAGESHEET(partialSheetName, [range1])

This function averages the value of the same cell in multiple sheets based on a partial sheet name.

Arguments

String: partialSheetName   --->   is a string with the partial name of a sheet. For example, if you set this argument to the string "Dat" all sheets with the string "Dat" in their name will be the sheets that are averaged

[(Optional) Range: range1]   --->   is an optional paramter to set the cell that will be averaged. By default, the cell this function resides will be the one that is averaged in the other sheets, but if range1 is set, that is the range that will be averaged.


Returns

Variant: Returns the average of all cells that pass the partial sheet name criteria


Examples

  • =AVERAGESHEET("- Data") -> 6.67; Where this function resides in cell C2 and the workbook contains the sheets "Jan - Data", "Feb - Data", "Mar - Data", "HelloWorld", "SumSheet", cell C2 in sheets "Jan - Data" (which contains value 5), "Feb - Data" (which contains value 7), "Mar - Data" (which contains value 8) will be averaged
  • =AVERAGESHEET("- Data", A1) -> 2; Same as the above example except cell A1 will be used instead of C2 and where A1 contains 1, 2, and 3 for values in the other sheets

=COUNTERRORALL(rangeArray)

This function takes a range or multiple ranges, and returns a count of all Errors and User-Defined Error Strings within those ranges. User-Defined Error Strings are strings that start with character "#" and end with either the character "!" or "?". This is similar to the format of errors in Excel, such as "#DIV/0!", "#VALUE!", "#NAME?", "#REF!", etc. User-Defined Error Strings are used all throughout XPlus, so this is a useful function for checking errors in XPlus functions. Additionally, users can create their own User-Defined Error Strings in Excel and use this function to check for those errors.

Arguments

Variant: rangeArray()   --->   is the range or multiple ranges whose errors will be counted


Returns

Variant: Returns the number of errors counted


Examples

  • =COUNTERRORALL(A1:A6) -> 4; Where A1="Hello World", A2="#DIV/0!", A3="#ErrorMessage!", A4="#ErrorMessage?", A5="#NAME?", A6="12345678"

=DISPLAY_TEXT(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) a textbox will display all the text in the cell, making it easier to read and manage large strings of text in a single cell.

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


Examples

  • =DISPLAY_TEXT("hello", "world") -> "hello world" and displays the text in a textbox
  • =DISPLAY_TEXT(A1:A2) -> "hello world" and displays the text in a textbox, where A1="hello" and A2="world"
  • =DISPLAY_TEXT(B1:B2, "Three") -> "One Two Three" and displays the text in a textbox, where B1="One" and B2="Two"

=EVALUATE_FORMULA(formulaText, rangeArray)

This function takes a formula as a string with placeholders in it, and executes and returns the value of that formula with the values from the placeholder used as inputs. Placeholders are in the form of {1}, {2}, {3}, etc., with the first placeholder starting at 1

Arguments

String: formulaText   --->   is the formula with placeholders as text

Variant: rangeArray()   --->   is any number of ranges to use as inputs and that will be replaced with the placeholders


Returns

Variant: Returns the executed value from the formula


Note
This function only support ranges in the rangeArray, as the values in the placeholders are replaced with the addresses of the ranges used as inputs
Warning!
Any evaluation function in any programming language can result in security vulnerabilites if misused. Particularly, when the inputs for this function are user inputs from other sources, it is possible for malicious inputs and functions to be executed. As a result, use this function with care, and please research more examples of eval function security vulnerabilites and best practices.

Examples

  • =EVALUATE_FORMULA("=SUM({1})+AVERAGE({2})", A1:A3, A4:A6) -> 80; Where A1:A3=[10, 20, 30] and A4:A6=[15, 20, 25]
  • =EVALUATE_FORMULA("=SUM({1})/COUNT({1})", A1:A3, A4:A6) -> 20; Where A1:A3=[10, 20, 30]; Notice I've used the {1} placeholder twice here

=HIDDEN(string1, hiddenFlag, [hideString])

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

Arguments

String: string1   --->   is the string that will be hidden

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

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

=HTML_ESCAPE(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

  • =HTML_ESCAPE("

    Hello World

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

=HTML_TABLEIFY(rangeTable)

This function takes a range in a table format and generates an HTML table from it. It uses the first row in the range chosen as the headers, and all other data as row data.

Arguments

Range: rangeTable   --->   is a range that will be formatted as an HTML table string.


Returns

String: Returns an HTML table string with data from the range populated in it


Examples

  • =HTML_TABLEIFY(A1:C5) -> ...

=HTML_UNESCAPE(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

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

    Hello World

    "

=ISBADERROR(rangeArray)

This function is similar to Excel's Built-in ISERROR() except that it only returns TRUE for #NULL!, #NAME?, #REF!, #DIV/0!, and #NUM! Errors

Arguments

Variant: rangeArray()   --->   is a range or multiple ranges that may contain errors


Returns

Variant: Returns TRUE if there is one of the listed bad errors in the range, or else FALSE


Warning!
Excel generates a lot of errors when using common formulas, and some of these are errors the user intends to create, where as some errors are likely to be unintended errors. For example, users typically do not intend to generate a #DIV/0 error or a #REF! error on purpose. This function attempts to only consider the latter errors (errors that may likely be unintentional). As a result, this formula can be interpretted as attempting to signal for errors that are likely unintentional and maybe should be explicitly handled by the users. However, a FALSE value from this formula DOES NOT mean that there are no errors in the spreadsheet or the ranges that this formula is operating on are free from error.

Examples

  • =ISBADERROR(#NAME?) -> TRUE; #NAME? is unlikely to be generated by the users, as it occurs when the user attempts to use a function that doesn't exist
  • =ISBADERROR(#NUM!) -> TRUE; #NUM! is often generated in Math functions where invalid inputs are used
  • =ISBADERROR(#DIV/0!) -> TRUE; #DIV/0! is not typically generated by users intentionally
  • =ISBADERROR(#REF!) -> TRUE; #REF! is often generated when deleting rows that a function points to for an input, and is typically unlikely to be generated by users (except sometimes in the case of using the INDIRECT() function)
  • =ISBADERROR(#NULL!) -> TRUE; #NULL! is often generated when using incorrect range references in formulas
  • =ISBADERROR(#N/A) -> FALSE; #N/A may sometimes be intentionally generated by the users
  • =ISBADERROR(#VALUE!) -> FALSE; #VALUE! may sometimes be intentionally generated by the users
  • =ISBADERROR(A1:A3) -> TRUE; Where A1=#NAME?, A2=#N/A, A3=#VALUE!
  • =ISBADERROR(A1, A2, A3) -> TRUE; Where A1=#NAME?, A2=#N/A, A3=#VALUE!

=ISERRORALL(range1)

This function is an extension of Excel's =ISERROR(). It returns TRUE for all of Excel's built in errors, similar to =ISERROR() but also returns TRUE for User-Defined Error Strings. User-Defined Error Strings are strings that start with character "#" and end with either the character "!" or "?". This is similar to the format of errors in Excel, such as "#DIV/0!", "#VALUE!", "#NAME?", "#REF!", etc. User-Defined Error Strings are used all throughout XPlus, so this is a useful function for checking errors in XPlus functions. Additionally, users can create their own User-Defined Error Strings in Excel and use this function to check for those errors.

Arguments

Range: range1   --->   is the range that will be checked for an error


Returns

Boolean: Returns TRUE if the range contains an Excel error or a User-Defined Error String


Examples

  • =ISERRORALL("Not an Error") -> FALSE
  • =ISERRORALL(1/0) -> TRUE
  • =ISERRORALL("#UserDefinedErrorString!") -> TRUE
  • =ISERRORALL("#UserDefinedErrorString?") -> TRUE
  • =ISERRORALL("UserDefinedErrorString") -> FALSE; The format for the User-Defined Error String is incorrect since it is missing the character "#" at the beginning, and either "!" or "?" at the end

=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, A1:A6) -> "["Hello","World",1,2,3,4.5]"

=MAXSHEET(partialSheetName, [range1])

This function gets the max value of the same cell in multiple sheets based on a partial sheet name.

Arguments

String: partialSheetName   --->   is a string with the partial name of a sheet. For example, if you set this argument to the string "Dat" all sheets with the string "Dat" in their name will be the sheets that the max value is picked from

[(Optional) Range: range1]   --->   is an optional paramter to set the cell that will be maxed. By default, the cell this function resides will be the one that is maxed in the other sheets, but if range1 is set, that is the range that will be maxed.


Returns

Variant: Returns the max of all cells that pass the partial sheet name criteria


Examples

  • =MAXSHEET("- Data") -> 8; Where this function resides in cell C2 and the workbook contains the sheets "Jan - Data", "Feb - Data", "Mar - Data", "HelloWorld", "SumSheet", cell C2 in sheets "Jan - Data" (which contains value 5), "Feb - Data" (which contains value 7), "Mar - Data" (which contains value 8) will be maxed
  • =MAXSHEET("- Data", A1) -> 3; Same as the above example except cell A1 will be used instead of C2 and where A1 contains 1, 2, and 3 for values in the other sheets

=MINSHEET(partialSheetName, [range1])

This function gets the min value of the same cell in multiple sheets based on a partial sheet name.

Arguments

String: partialSheetName   --->   is a string with the partial name of a sheet. For example, if you set this argument to the string "Dat" all sheets with the string "Dat" in their name will be the sheets that the min value is picked from

[(Optional) Range: range1]   --->   is an optional paramter to set the cell that will be mined. By default, the cell this function resides will be the one that is mined in the other sheets, but if range1 is set, that is the range that will be mined.


Returns

Variant: Returns the min of all cells that pass the partial sheet name criteria


Examples

  • =MINSHEET("- Data") -> 5; Where this function resides in cell C2 and the workbook contains the sheets "Jan - Data", "Feb - Data", "Mar - Data", "HelloWorld", "SumSheet", cell C2 in sheets "Jan - Data" (which contains value 5), "Feb - Data" (which contains value 7), "Mar - Data" (which contains value 8) will be mined
  • =MINSHEET("- Data", A1) -> 1; Same as the above example except cell A1 will be used instead of C2 and where A1 contains 1, 2, and 3 for values in the other sheets

=REFERENCE_EXISTS(referenceName, [partialNameFlag])

This takes a string name of a VBA reference library, and checks if the current workbook currently includes the VBA library as a reference.

Arguments

String: referenceName   --->   is the name of a reference we want to check if it exists

[(Optional) Boolean: partialNameFlag]   --->   if set to TRUE, will perform checks based on partial names instead of requiring the exact name of the reference


Returns

Boolean: Returns TRUE if the reference exists, and FALSE if it doesn't


Note
This function is case-insensitive

Examples

  • =REFERENCE_EXISTS("Excel") -> TRUE; The "Excel" Library is typically included as a reference
  • =REFERENCE_EXISTS("VBA") -> TRUE; The "VBA" Library is typically included as a reference
  • =REFERENCE_EXISTS("vba") -> TRUE; The "VBA" Library is typically included as a reference, and this function works on case-insensitive checks
  • =REFERENCE_EXISTS("MSHTML") -> FALSE; The "MSHTML" Library is typically not included as a reference, but can be included by the user
  • =REFERENCE_EXISTS("VB") -> FALSE; There is typically no library named "VB"
  • =REFERENCE_EXISTS("VB", TRUE) -> TRUE; Since the partialNameFlag is set to TRUE and since a reference to the "VBA" library exists, this will match the "VBA" library

=SPEAK_TEXT(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


Examples

  • =SPEAK_TEXT("Hello", "World") -> "Wello World" and the text will be spoken through the speaker
  • =SPEAK_TEXT(A1:A2) -> "Hello World" and the text will be spoken through the speaker, where A1="Hello" and A2="World"
  • =SPEAK_TEXT(B1:B2, "Three") -> "One Two Three" and the text will be spoken through the speaker, where B1="One" and B2="Two"

=SUMSHEET(partialSheetName, [range1])

This function sums up the value of the same cell in multiple sheets based on a partial sheet name.

Arguments

String: partialSheetName   --->   is a string with the partial name of a sheet. For example, if you set this argument to the string "Dat" all sheets with the string "Dat" in their name will be the sheets that are summed up

[(Optional) Range: range1]   --->   is an optional paramter to set the cell that will be summed. By default, the cell this function resides will be the one that is summed up in the other sheets, but if range1 is set, that is the range that will be summed up.


Returns

Variant: Returns the sum of all cells that pass the partial sheet name criteria


Examples

  • =SUMSHEET("- Data") -> 20; Where this function resides in cell C2 and the workbook contains the sheets "Jan - Data", "Feb - Data", "Mar - Data", "HelloWorld", "SumSheet", cell C2 in sheets "Jan - Data" (which contains value 5), "Feb - Data" (which contains value 7), "Mar - Data" (which contains value 8) will be summed up
  • =SUMSHEET("- Data", A1) -> 6; Same as the above example except cell A1 will be used instead of C2 and where A1 contains 1, 2, and 3 for values in the other sheets

=UUID_FOUR()

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

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