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  

Properties Module

This module contains a set of functions for getting properties from Ranges, Worksheets, and Workbooks.


=RANGE_COLOR(range1)

This function gets the color of a cell. The color returned is a number that essentially is one of 16777215 possible color combinations, with every single color being a unique number.

Arguments

Range: range1   --->   is the cell we want to get the color from


Returns

Long: Returns the color of the cell as a number


Examples

  • =RANGE_COLOR(A1) -> 255; Where A1 is colored Red
  • =RANGE_COLOR(A2) -> 65535; Where A2 is colored Yellow
  • =RANGE_COLOR(A3) -> 16777215; Where A3 is colored White
  • =RANGE_COLOR(A4) -> 0; Where A4 is colored Black

=RANGE_COMMENT(range1, [excludeUsername])

This function gets the comment of the selected cell. It also includes an optional parameter that if set to TRUE will remove the Username from the comment.

Arguments

Range: range1   --->   is the cell we want to get the comment from

[(Optional) Boolean: excludeUsername]   --->   if set to TRUE, will remove the Username from the comment


Returns

String: Returns a string of the comment from the cell


Examples

  • =RANGE_COMMENT(A1) -> "Anthony: This is my comment"; Where the cell contains a comment
  • =RANGE_COMMENT(A1, TRUE) -> "This is my comment"

=RANGE_FONT(range1)

This function gets the name of the font of the selected cell.

Arguments

Range: range1   --->   is the cell we want to get the font name from


Returns

String: Returns a string of the number format from the cell


Examples

  • =RANGE_FONT(A1) -> "Calibri"; Where the cell has the font style Calibri
  • =RANGE_FONT(A2) -> "Arial"; Where the cell has the font style Arial

=RANGE_HEIGHT(range1)

This function gets the height of the selected cell

Arguments

Range: range1   --->   is the cell we want to get the height from


Returns

Double: Returns the height of the cell as a Double


Examples

  • =RANGE_HEIGHT(A1) -> 14


=RANGE_NAME(range1)

This function gets the name of the selected cell; Named Ranges can be created using the Name Manager.

Arguments

Range: range1   --->   is the cell we want to get the name from


Returns

String: Returns a string of the name of the cell


Examples

  • =RANGE_NAME(A1) -> "Hello_World"; Where the name of the cell has been named to "Hello_World"

=RANGE_NUMBER_FORMAT(range1)

This function gets the number format of the selected cell.

Arguments

Range: range1   --->   is the cell we want to get the number format from


Returns

String: Returns a string of the number format from the cell


Examples

  • =RANGE_NUMBER_FORMAT(A1) -> "General"; Where the cell has the default number format
  • =RANGE_NUMBER_FORMAT(A2) -> "Accounting"; Where the cell uses the Accounting number format

=RANGE_WIDTH(range1)

This function gets the width of the selected cell

Arguments

Range: range1   --->   is the cell we want to get the width from


Returns

Double: Returns the width of the cell as a Double


Examples

  • =RANGE_WIDTH(A1) -> 20

=SHEET_CODE_NAME([sheetNameOrNumber])

This function returns the code name of the sheet the function resides in, or if a number/name is provided, returns the code name of the sheet that resides at that number/name

Arguments

[(Optional) Variant: sheetNameOrNumber]   --->   is the name or number of the sheet


Returns

String: Returns the code name of the sheet


Examples

  • =SHEET_CODE_NAME() -> "Sheet1"; Where this function resides in Sheet1
  • =SHEET_CODE_NAME("MySheet") -> "Sheet1"
  • =SHEET_CODE_NAME(1) -> "Sheet1"

=SHEET_NAME([sheetNameOrNumber])

This function returns the name of the sheet the function resides in, or if a number/name is provided, returns the name of the sheet that resides at that number/name

Arguments

[(Optional) Variant: sheetNameOrNumber]   --->   is the name or number of the sheet


Returns

String: Returns the name of the sheet


Examples

  • =SHEET_NAME() -> "Sheet1"; Where this function resides in Sheet1
  • =SHEET_NAME("Sheet2") -> "Sheet2"
  • =SHEET_NAME(2) -> "Sheet2"

=SHEET_TYPE([sheetNameOrNumber])

This function returns the type of the sheet the function resides in, or if a number/name is provided, returns the type of the sheet that resides at that number/name

Arguments

[(Optional) Variant: sheetNameOrNumber]   --->   is the name or number of the sheet


Returns

String: Returns the type of the sheet


Examples

  • =SHEET_TYPE() -> "Worksheet"
  • =SHEET_TYPE("MyChart") -> "Chart"
  • =SHEET_TYPE(2) -> "Chart"

=WORKBOOK_AUTHOR([workbookNameOrNumber])

This function returns the author of the workbook that the function resides in, or if a number/name is provided, returns the author of the workbook that resides at that number/name

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the author of the workbook


Note
Workbook author can be set in File->Info->Properties

Examples

  • =WORKBOOK_AUTHOR() -> "John Doe"
  • =WORKBOOK_AUTHOR("Otherbook.xlsx") -> "Jane Doe"

=WORKBOOK_CATEGORY([workbookNameOrNumber])

This function returns the category of the workbook that the function resides in, or if a number/name is provided, returns the category of the workbook that resides at that number/name

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the category of the workbook


Note
Workbook category can be set in File->Info->Properties

Examples

  • =WORKBOOK_CATEGORY() -> "Category1"
  • =WORKBOOK_CATEGORY("Otherbook.xlsx") -> "Category2"

=WORKBOOK_COMMENTS([workbookNameOrNumber])

This function returns the comments of the workbook that the function resides in, or if a number/name is provided, returns the comments of the workbook that resides at that number/name

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the comments of the workbook


Note
Workbook comments can be set in File->Info->Properties

Examples

  • =WORKBOOK_COMMENTS() -> "This is my workbook"
  • =WORKBOOK_COMMENTS("Otherbook.xlsx") -> "This is my other workbook"

=WORKBOOK_COMPANY([workbookNameOrNumber])

This function returns the company of the workbook that the function resides in, or if a number/name is provided, returns the company of the workbook that resides at that number/name

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the company of the workbook


Note
Workbook company can be set in File->Info->Properties

Examples

  • =WORKBOOK_COMPANY() -> "Hello Company"
  • =WORKBOOK_COMPANY("Otherbook.xlsx") -> "World Company"

=WORKBOOK_CREATION_DATE([workbookNameOrNumber])

This function returns the creation date of the workbook that the function resides in, or if a number/name is provided, returns the creation date of the workbook that resides at that number/name

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the creation date of the workbook


Examples

  • =WORKBOOK_CREATION_DATE() -> "1/1/2020 10:00:00 PM"
  • =WORKBOOK_CREATION_DATE("Otherbook.xlsx") -> "1/5/2020 8:00:00 PM"


=WORKBOOK_KEYWORDS([workbookNameOrNumber])

This function returns the keywords of the workbook that the function resides in, or if a number/name is provided, returns the keywords of the workbook that resides at that number/name

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the keywords of the workbook


Note
Workbook keywords can be set in File->Info->Properties

Examples

  • =WORKBOOK_KEYWORDS() -> "accounting, jan, hello"
  • =WORKBOOK_KEYWORDS("Otherbook.xlsx") -> "finance, feb, world"

=WORKBOOK_LAST_AUTHOR([workbookNameOrNumber])

This function returns the last author of the workbook that the function resides in, or if a number/name is provided, returns the comments of the workbook that resides at that number/name. Last author is the person who last saved the workbook.

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the last author of the workbook


Examples

  • =WORKBOOK_LAST_AUTHOR() -> "John Doe"
  • =WORKBOOK_LAST_AUTHOR("Otherbook.xlsx") -> "Jane Doe"

=WORKBOOK_LAST_SAVE_TIME([workbookNameOrNumber])

This function returns the last save time of the workbook that the function resides in, or if a number/name is provided, returns the last save time of the workbook that resides at that number/name

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the last save time of the workbook


Examples

  • =WORKBOOK_LAST_SAVE_TIME() -> "1/3/2020 10:00:00 PM"
  • =WORKBOOK_LAST_SAVE_TIME("Otherbook.xlsx") -> "1/10/2020 8:00:00 PM"

=WORKBOOK_MANAGER([workbookNameOrNumber])

This function returns the manager of the workbook that the function resides in, or if a number/name is provided, returns the manager of the workbook that resides at that number/name

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the manager of the workbook


Note
Workbook manager can be set in File->Info->Properties

Examples

  • =WORKBOOK_MANAGER() -> "Manager John"
  • =WORKBOOK_MANAGER("Otherbook.xlsx") -> "Manager Jane"

=WORKBOOK_REVISION_NUMBER([workbookNameOrNumber])

This function returns the revision number of the workbook that the function resides in, or if a number/name is provided, returns the revision number of the workbook that resides at that number/name

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the revision number of the workbook


Examples

  • =WORKBOOK_REVISION_NUMBER() -> 1
  • =WORKBOOK_REVISION_NUMBER("Otherbook.xlsx") -> 2

=WORKBOOK_SUBJECT([workbookNameOrNumber])

This function returns the subject of the workbook that the function resides in, or if a number/name is provided, returns the subject of the workbook that resides at that number/name

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the subject of the workbook


Note
Workbook subject can be set in File->Info->Properties

Examples

  • =WORKBOOK_SUBJECT() -> "MySubject"
  • =WORKBOOK_SUBJECT("Otherbook.xlsx") -> "MyOtherSubject"

=WORKBOOK_TITLE([workbookNameOrNumber])

This function returns the title of the workbook that the function resides in, or if a number/name is provided, returns the title of the workbook that resides at that number/name

Arguments

[(Optional) Variant: workbookNameOrNumber]   --->   is the name or number of the workbook


Returns

String: Returns the title of the workbook


Note
Workbook title can be set in File->Info->Properties

Examples

  • =WORKBOOK_TITLE() -> "MyWorkbook"
  • =WORKBOOK_TITLE("Otherbook.xlsx") -> "MyOtherWorksheet"