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

Network Module

This module contains a set of functions for performing networking tasks such as performing HTTP requests and parsing HTML.


=Http(url, [httpMethod], [headers], [postData], [asyncFlag], [statusErrorHandlerFlag], [parseArguments])

This function performs an HTTP request to the web and returns the response as a string. It provides many options to change the http method, provide data for a POST request, change the headers, handle errors for non-successful requests, and parse out text from a request using a light parsing language.

Arguments

{String}: url   ➔   is a string of the URL of the website you want to fetch data from

[(Optional) {String = "GET"}: httpMethod]   ➔   is a string with the http method, with the default being a GET request. For POST requests, use "POST", for PUT use "PUT", and for DELETE use "DELETE"

[(Optional) {Variant}: headers]   ➔   is either an array or a Scripting Dictionary of headers that will be used in the request. For an array, the 1st, 3rd, 5th... will be used as the key and the 2nd, 4th, 6th... will be used as the values. For a Scripting Dictionary, the dictionary keys will be used as header keys, and the values as values. Finally, in the case when no headers are set, the User-Agent will be set to "XPlus" as a courtesy to the web server.

[(Optional) {Variant = ""}: postData]   ➔   is a string that will contain data for a POST request

[(Optional) {Boolean}: asyncFlag]   ➔   is a Boolean value that if set to TRUE will make the request asynchronous. By default requests will be synchronous, which will lock Excel while fetching but will also prevent errors when performing calculations based on fetched data.

[(Optional) {Boolean}: statusErrorHandlerFlag]   ➔   is a Boolean value that if set to TRUE will result in a User-Defined Error String being returned for all non 200 requests that tells the user the status code that occured. This flag is useful in cases where requests need to be successful and if not errors should be thrown.

[(Optional) {Variant}: parseArguments]   ➔   is an array of arguments that perform string parsing on the response. It uses a light scripting language that includes commands similar to the Excel Built-in LEFT(), RIGHT(), and MID() that allow you to parse the request before it gets returned. See the Note on the scripting language, and the Warning on why this argument should be used.


Returns

{String}: Returns the parsed HTTP response as a string


Note
The parseArguments parameter uses a light scripting language to perform string manipulations on the HTTP response text that allows you to parse out the relevant information to you. The language contains 5 commands that can be used for parsing. Please check out the examples as well below for a better understanding of how to use the parsing language:

{"ID", "idOfAnElement"} -> HTML inside of the element with the specified ID
{"TAG", "div", 2} -> HTML inside of the second div tag found
{"LEFT", 100} -> The 100 leftmost characters
{"LEFT", "Hello World"} -> All characters left of the first "Hello World" found in the HTML
{"RIGHT", 100} -> The 100 rightmost characters
{"RIGHT", "Hello World"} -> All characters right of the last "Hello World" found in the HTML
{"MID", 100} -> All character to the right of the 100th character in the string
{"MID", "Hello World"} -> All characters right of the first "Hello World" found in the HTML
Warning!
Excel has a limit on the number of characters that can be placed within a cell. This limit is a max of 32767 characters. If the request returns any more than this, a #VALUE! error will be returned. Most webpages surpass this number of characters, which makes the Excel Built-in function WEBSERVICE() not very useful. However, internally VBA can handle around 2,000,000,000 characters, which more characters that found on virtually every single webpage. As a result, parsing arguments should be used with this function so that you can parse out the relevant information for a request without this function failing. See the Note on the syntax of the light parsing language.

Examples

  • =Http("https://httpbin.org/uuid") -> "{"uuid: "41416bcf-ef11-4256-9490-63853d14e4e8"}"
  • =Http("https://httpbin.org/user-agent", "GET", {"User-Agent","MicrosoftExcel"}) -> "{"user-agent": "MicrosoftExcel"}"
  • =Http("https://httpbin.org/status/404",,,,,TRUE) -> "#RequestFailedStatusCode404!"; Since the status error handler flag is set and since this URL returns a 404 status code. Also note that this formula is easier to construct using the Excel Formula Builder
  • =Http("https://en.wikipedia.org/wiki/Visual_Basic_for_Applications",,{"User-Agent","MicrosoftExcel"},,,,{"ID","mw-content-text","LEFT",3000}) -> Returning a string with the leftmost 3000 characters found within the element with the ID "mw-content-text" (we are trying to get the release date of VBA from the VBA wikipedia page, but we need to do more parsing first)
  • =Http("https://en.wikipedia.org/wiki/Visual_Basic_for_Applications",,{"User-Agent","MicrosoftExcel"},,,,{"ID","mw-content-text","LEFT",3000,"MID","appeared"}) -> Returns the prior string, but now with all characters right of the first occurance of the word "appeared" in the HTML (getting closer to parsing the VBA creation date)
  • =Http("https://en.wikipedia.org/wiki/Visual_Basic_for_Applications",,{"User-Agent","MicrosoftExcel"},,,,{"ID","mw-content-text","LEFT",3000,"MID","appeared","MID",""}) -> From the prior result, now returning everything after the first occurance of the "" in the prior string
  • =Http("https://en.wikipedia.org/wiki/Visual_Basic_for_Applications",,{"User-Agent","MicrosoftExcel"},,,,{"ID","mw-content-text","LEFT",3000,"MID","appeared","MID","","LEFT"," "1993"; Finally this is all the parsing needed to be able to return the date 1993 that we were looking for

=ParseHtmlString(htmlString, parseArguments)

This function parses an HTML string using the same parsing language that the HTTP() function uses. See the HTTP() function for more information on how to use this function.

Arguments

{String}: htmlString   ➔   is a string of the HTML

{Variant}: parseArguments   ➔   is an array of arguments that perform string parsing on the response. It uses a light scripting language that includes commands similar to the Excel Built-in LEFT(), RIGHT(), and MID() that allow you to parse the request before it gets returned. See the Note on the HTTP() function, and the Warning on the HTTP() function on why this argument should be used.


Returns

{Variant}: Returns the parsed HTTP response as a string


Examples

  • =ParseHtmlString("HTML String from the webpage: https://en.wikipedia.org/wiki/Visual_Basic_for_Applications","ID","mw-content-text","LEFT",3000,"MID","appeared","MID","","LEFT"," "1993"

=SimpleHttp(url, parseArguments)

This function performs an HTTP request to the web and returns the response as a string, similar to the HTTP() function, except that only requires one parameter, the URL, and then takes an infinite number of strings after it as the parsing arguments instead of requiring an Array to use. Essentially, this function is a little cleaner to set up when performing very basic GET requests.

Arguments

{String}: url   ➔   is a string of the URL of the website you want to fetch data from

{Variant}: parseArguments()   ➔   is an array of arguments that perform string parsing on the response. It uses a light scripting language that includes commands similar to the Excel Built-in LEFT(), RIGHT(), and MID() that allow you to parse the request before it gets returned. See the Note on the HTTP() function, and the Warning on the HTTP() function on why this argument should be used.


Returns

{Variant}: Returns the parsed HTTP response as a string


Examples

  • =SimpleHttp("https://en.wikipedia.org/wiki/Visual_Basic_for_Applications","ID","mw-content-text","LEFT",3000,"MID","appeared","MID","","LEFT"," "1993"; See the examples in the HTTP() function, as this example has the same result as the example in the HTTP() function. You can see that this function is cleaner and easier to set up than the corresponding HTTP() function.