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  

Random Module

This module contains a set of functions for generating and sampling random data.


=RANDBETWEENS(startOrEndNumberArray)

This function is similar to RANDBETWEEN, except that it allows multiple ranges from which to pick a random number. One of the ranges from which to generate a random number between is chosen at an equal probably.

Arguments

Variant: startOrEndNumberArray()   --->   


Returns

Variant: Returns either TRUE or FALSE based on the random value choosen


Note
This function always requires an even number of inputs. Essentially, when using multiple numbers, the 1st and 2nd will make up a range from which to pull a random number between, the 3rd and 4th will make a different range, and so on. If an even number is used, this function will return a User-Defined Error. See the ISERRORALL() function for how to handle these numbers.

Examples

  • =RANDBETWEENS(1, 10, 5000, 5010) -> 6
  • =RANDBETWEENS(1, 10, 5000, 5010) -> 5002
  • =RANDBETWEENS(1, 10, 5000, 5010) -> 8
  • =RANDBETWEENS(1, 10, 5000, 5010) -> 3
  • =RANDBETWEENS(1, 10, 5000, 5010) -> 5010
  • =RANDBETWEENS(1, 10, 5000, 5010) -> 2
  • =RANDBETWEENS(5, 10, 15, 20, 25, 30, 35, 40) -> 32

=RANDBOOL()

This function generates a random Boolean (TRUE or FALSE) value

Arguments

None


Returns

Boolean: Returns either TRUE or FALSE based on the random value choosen


Examples

  • =RANDBOOL() -> TRUE
  • =RANDBOOL() -> FALSE
  • =RANDBOOL() -> TRUE
  • =RANDBOOL() -> TRUE
  • =RANDBOOL() -> FALSE
  • =RANDBOOL() -> FALSE

=RANDOM_RANGE(startNumber, stopNumber, stepNumber)

This function takes 3 numbers, a start number, a stop number, and a step number, and returns a random number between the start number and stop number that is an interval of the step number.

Arguments

Integer: startNumber   --->   is the beginning value of the range

Integer: stopNumber   --->   is the end value of the range

Integer: stepNumber   --->   is the step of the range


Returns

Integer: Returns a random number between the start and stop that is a multiple of the step


Examples

  • =RANDOM_RANGE(50, 100, 10) -> 60
  • =RANDOM_RANGE(50, 100, 10) -> 50
  • =RANDOM_RANGE(50, 100, 10) -> 90
  • =RANDOM_RANGE(0, 10, 2) -> 8
  • =RANDOM_RANGE(0, 10, 2) -> 0
  • =RANDOM_RANGE(0, 10, 2) -> 4
  • =RANDOM_RANGE(0, 10, 2) -> 10

=RANDOM_SAMPLE(rangeArray)

This function takes an array of cells and returns a random value from the cells chosen

Arguments

Range: rangeArray   --->   a single cell or multiple cells where the sample will be pulled from


Returns

Variant: Returns a random cell value from the array of cells chosen


Examples

  • =RANDOM_SAMPLE(A1:A5) -> "Hello"; where "Hello" is the value in cell A3, and where A3 was the chosen random cell
  • =RANDOM_SAMPLE(A1:A5) -> "World"; where "World" is the value in cell A2, and where A2 was the chosen random cell

=RANDOM_SAMPLE_PERCENT(valueRange, percentRange)

This function takes 2 ranges, one with values that will be sampled, and the other with the percentage chance that the value will be sampled, and returns a value from the sample.

Arguments

Range: valueRange   --->   is the range containing values of which one will be sampled

Range: percentRange   --->   is the range that contains the percent chances of the values in the valueRange


Returns

Variant: Returns a random value from the valueRange


Warning!
Internally this function sums up all the number in the percentRange to calculate percentage chances of a sample. For example, if the percentRange contains the values 10 and 90, the first value will have a 10/(10+90) = 10% chance of being chosen. Similarly, if the values in the percentRange contains the values 5 and 45, the first value will have a 5/(5+45) = 10% chance of being chosen. This means you have to be careful when choosing percentages in the percentRange, as if you choose 0.1 and 0.8 for the percentRange, the percentage chance the first value is chosen is NOT 10%, but rather 0.1/(0.1+0.8) = 11.1%. Thus, you should be careful to only interpret a 0.1 in the percentRange as a 10% chance only if the values in the percentRange actually sum up to 1.0.

Examples

  • =RANDOM_SAMPLE_PERCENT(A1:A2, B1:B2) -> "Hello"; Assuming the valueRange contains ["Hello", "World"], and percentRange contains [.9, .1]
  • =RANDOM_SAMPLE_PERCENT(A1:A2, B1:B2) -> "Hello"; Assuming the valueRange contains ["Hello", "World"], and percentRange contains [.9, .1]
  • =RANDOM_SAMPLE_PERCENT(A1:A2, B1:B2) -> "Hello"; Assuming the valueRange contains ["Hello", "World"], and percentRange contains [.9, .1]
  • =RANDOM_SAMPLE_PERCENT(A1:A2, B1:B2) -> "Hello"; Assuming the valueRange contains ["Hello", "World"], and percentRange contains [.9, .1]
  • =RANDOM_SAMPLE_PERCENT(A1:A2, B1:B2) -> "Hello"; Assuming the valueRange contains ["Hello", "World"], and percentRange contains [.9, .1]
  • =RANDOM_SAMPLE_PERCENT(A1:A2, B1:B2) -> "Hello"; Assuming the valueRange contains ["Hello", "World"], and percentRange contains [.9, .1]
  • =RANDOM_SAMPLE_PERCENT(A1:A2, B1:B2) -> "Hello"; Assuming the valueRange contains ["Hello", "World"], and percentRange contains [.9, .1]
  • =RANDOM_SAMPLE_PERCENT(A1:A2, B1:B2) -> "World"; Assuming the valueRange contains ["Hello", "World"], and percentRange contains [.9, .1]; Notice how "World" shows up less since there is only a 10% chance it is chosen.