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  

File Module

This module contains a set of functions for gathering info on files. It includes functions for gathering file info on the current workbook, as well as functions for reading and writing to files, and functions for manipulating file path strings.


=COUNT_FILES([filePath])

This function returns the number of files at the specified folder path. If no path is given, the current workbook path will be used.

Arguments

[(Optional) String: filePath]   --->   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

Integer: Returns the number of files in the folder


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
This function includes the count for hidden files as well. For example, when a workbook is open, a hidden file for the workbook is created, so if you run this function in the same folder as the workbook and notice the file count is one higher than expected, it is likely due to the hidden file.

Examples

  • =COUNT_FILES() -> 6
  • =COUNT_FILES("C:\hello") -> 10

=COUNT_FILES_AND_FOLDERS([filePath])

This function returns the number of files and folders at the specified folder path. If no path is given, the current workbook path will be used.

Arguments

[(Optional) String: filePath]   --->   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

Integer: Returns the number of files and folders in the folder


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
This function includes the count for hidden files and folders as well

Examples

  • =COUNT_FILES_AND_FOLDERS() -> 8
  • =COUNT_FILES_AND_FOLDERS("C:\hello") -> 30

=COUNT_FOLDERS([filePath])

This function returns the number of folders at the specified folder path. If no path is given, the current workbook path will be used.

Arguments

[(Optional) String: filePath]   --->   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

Integer: Returns the number of folders in the folder


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
This function includes the count for hidden folders as well. Hidden folders are often prefixed with a . character at the beginning

Examples

  • =COUNT_FOLDERS() -> 2
  • =COUNT_FOLDERS("C:\hello") -> 20

=FILE_CREATION_TIME([filePath])

This function returns the file creation time of the file specified in the file path argument. If no file path is specified, the current Excel workbook is used. Also, if a full path isn't used, a path relative to the folder the workbook resides in will be used.

Arguments

[(Optional) String: filePath]   --->   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

String: Returns the file creation time of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FILE_CREATION_TIME() -> "1/1/2020 1:23:45 PM"
  • =FILE_CREATION_TIME("C:\hello\world.txt") -> "1/1/2020 5:55:55 PM"
  • =FILE_CREATION_TIME("vba.txt") -> "12/25/2000 1:00:00 PM"; Where "vba.txt" resides in the same folder as the workbook this function resides in

=FILE_DRIVE([filePath])

This function returns the drive of the file specified in the file path argument. If no file path is specified, the current Excel workbook is used. Also, if a full path isn't used, a path relative to the folder the workbook resides in will be used.

Arguments

[(Optional) String: filePath]   --->   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

String: Returns the file drive of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FILE_DRIVE() -> "A:"; Where the current workbook resides on the A: drive
  • =FILE_DRIVE("C:\hello\world.txt") -> "C:"
  • =FILE_DRIVE("vba.txt") -> "B:"; Where "vba.txt" resides in the same folder as the workbook this function resides in, and where the workbook resides in the B: drive

=FILE_EXTENSION([filePath])

This function returns the extension of the file specified in the file path argument. If no file path is specified, the current Excel workbook is used. Also, if a full path isn't used, a path relative to the folder the workbook resides in will be used.

Arguments

[(Optional) String: filePath]   --->   is a string path of the file on the system, such as "C:\hello\world.txt"


Returns

String: Returns the extension of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FILE_EXTENSION() = "xlsx"
  • =FILE_EXTENSION("C:\hello\world.txt") -> "txt"
  • =FILE_EXTENSION("vba.txt") -> "txt"; Where "vba.txt" resides in the same folder as the workbook this function resides in

=FILE_FOLDER([filePath])

This function returns the path of the folder of the file specified in the file path argument. If no file path is specified, the current Excel workbook is used. Also, if a full path isn't used, a path relative to the folder the workbook resides in will be used.

Arguments

[(Optional) String: filePath]   --->   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

String: Returns the path of the folder where the file resides in as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FILE_FOLDER() -> "C:\my_excel_files"
  • =FILE_FOLDER("C:\hello\world.txt") -> "C:\hello"
  • =FILE_FOLDER("vba.txt") -> "C:\my_excel_files"; Where "vba.txt" resides in the same folder as the workbook this function resides in

=FILE_LAST_MODIFIED_TIME([filePath])

This function returns the file last modified time of the file specified in the file path argument. If no file path is specified, the current Excel workbook is used. Also, if a full path isn't used, a path relative to the folder the workbook resides in will be used.

Arguments

[(Optional) String: filePath]   --->   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

String: Returns the file last modified time of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FILE_LAST_MODIFIED_TIME() -> "1/1/2020 2:23:45 PM"
  • =FILE_LAST_MODIFIED_TIME("C:\hello\world.txt") -> "1/1/2020 7:55:55 PM"
  • =FILE_LAST_MODIFIED_TIME("vba.txt") -> "12/25/2000 3:00:00 PM"; Where "vba.txt" resides in the same folder as the workbook this function resides in

=FILE_NAME([filePath])

This function returns the name of the file specified in the file path argument. If no file path is specified, the current Excel workbook is used. Also, if a full path isn't used, a path relative to the folder the workbook resides in will be used.

Arguments

[(Optional) String: filePath]   --->   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

String: Returns the name of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FILE_NAME() -> "MyWorkbook.xlsm"
  • =FILE_NAME("C:\hello\world.txt") -> "world.txt"
  • =FILE_NAME("vba.txt") -> "vba.txt"; Where "vba.txt" resides in the same folder as the workbook this function resides in

=FILE_PATH([filePath])

This function returns the path of the file specified in the file path argument. If no file path is specified, the current Excel workbook is used. Also, if a full path isn't used, a path relative to the folder the workbook resides in will be used.

Arguments

[(Optional) String: filePath]   --->   is a string path of the file on the system, such as "C:\hello\world.txt"


Returns

String: Returns the path of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FILE_PATH() -> "C:\my_excel_files\MyWorkbook.xlsx"
  • =FILE_PATH("C:\hello\world.txt") -> "C:\hello\world.txt"
  • =FILE_PATH("vba.txt") -> "C:\hello\world.txt"; Where "vba.txt" resides in the same folder as the workbook this function resides in

=FILE_SIZE([filePath], [byteSize])

This function returns the file size of the file specified in the file path argument, with the option to set if the file size is returned in Bytes, Kilobytes, Megabytes, or Gigabytes. If no file path is specified, the current Excel workbook is used. Also, if a full path isn't used, a path relative to the folder the workbook resides in will be used.

Arguments

[(Optional) String: filePath]   --->   is a string path of the file on the system, such as "C:\hello\world.txt"

[(Optional) String: byteSize]   --->   is a string of value "KB", "MB", or "GB"


Returns

Double: Returns the size of the file as a Double


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FILE_SIZE() -> 1024
  • =FILE_SIZE(,"KB") -> 1
  • =FILE_SIZE("vba.txt", "KB") -> 0.25; Where "vba.txt" resides in the same folder as the workbook this function resides in

=FILE_TYPE([filePath])

This function returns the file type of the file specified in the file path argument. If no file path is specified, the current Excel workbook is used.

Arguments

[(Optional) String: filePath]   --->   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

String: Returns the file type of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • FILE_TYPE() -> "Microsoft Excel Macro-Enabled Worksheet"
  • FILE_TYPE("C:\hello\world.txt") -> "Text Document"
  • FILE_TYPE("vba.txt") -> "Text Document"; Where "vba.txt" resides in the same folder as the workbook this function resides in

=GET_FILE_NAME([filePath], [fileNumber])

This function returns the name of a file in a folder given the number of the file in the list of all files

Arguments

[(Optional) String: filePath]   --->   is a string path to the file on the system, such as "C:\hello\world.txt"

[(Optional) Integer = -1: fileNumber]   --->   is the number of the file in the folder. For example, if there are 3 files in a folder, this should be a number between 1 and 3


Returns

String: Returns the name of the specified file


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
This function includes hidden files as well. For example, when a workbook is open, a hidden file for the workbook is created, so if you run this function in the same folder as the workbook and notice the file count is one higher than expected, it is likely due to the hidden file.

Examples

  • =GET_FILE_NAME(,1) -> "hello.txt"
  • =GET_FILE_NAME(,1) -> "world.txt"
  • =GET_FILE_NAME("C:\hello", 1) -> "one.txt"
  • =GET_FILE_NAME("C:\hello", 1) -> "two.txt"
  • =GET_FILE_NAME("C:\hello", 1) -> "three.txt"

=PATH_JOIN(pathArray)

This function combines multiple strings or a range of values into a file path by placing the separator "\" between the arguments

Arguments

Variant: pathArray()   --->   is an array of ranges and strings that will be combined


Returns

Variant: Returns a string with the combined file path


Examples

  • =PATH_JOIN(A1:A3) -> "C:\hello\world.txt"
  • =PATH_JOIN("C:", "hello", "world.txt") -> "C:\hello\world.txt"

=READ_FILE(filePath, [lineNumber])

This function reads the file specified in the file path argument and returns it's contents. Optionally, a line number can be specified so that only a single line is read. If a full path isn't used, a path relative to the folder the workbook resides in will be used.

Arguments

String: filePath   --->   is a string path of the file on the system, such as "C:\hello\world.txt"

[(Optional) Integer: lineNumber]   --->   is the number of the line that will be read, and if left blank all the file contents will be read. Note that the first line starts at line number 1.


Returns

String: Returns the contents of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
This function may run very slowly when running it on large files. Also, for files that are not in text format (such as compressed zip files) this file contents returned will not be in a usable format.

Examples

  • =READ_FILE("C:\hello\world.txt") -> "Hello" World
  • =READ_FILE("vba.txt") -> "This is my VBA text file"; Where "vba.txt" resides in the same folder as the workbook this function resides in
  • =READ_FILE("multline.txt", 1) -> "This is line 1";
  • =READ_FILE("multline.txt", 2) -> "This is line 2";

=WRITE_FILE(filePath, fileText, [appendModeFlag])

This function creates and writes to the file specified in the file path argument. If no file path is specified, the current Excel workbook is used. Also, if a full path isn't used, a path relative to the folder the workbook resides in will be used.

Arguments

String: filePath   --->   is a string path of the file on the system, such as "C:\hello\world.txt"

String: fileText   --->   is the text that will be written to the file

[(Optional) Boolean: appendModeFlag]   --->   is a Boolean value that if set to TRUE will append to the existing file instead of creating a new file and writing over the contents.


Returns

String: Returns a message stating the file written to successfully


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
Be careful when writing files, as misuse of this function can results in files being overwritten accidently as well as creating large numbers of files accidently.

Examples

  • =WRITE_FILE("C:\MyWorkbookFolder\hello.txt", "Hello World") -> "Successfully wrote to: C:\MyWorkbookFolder\hello.txt"
  • =WRITE_FILE("hello.txt", "Hello World") -> "Successfully wrote to: C:\MyWorkbookFolder\hello.txt"; Where the Workbook resides in "C:\MyWorkbookFolder\"