User Defined Functions

This repository brings together a set of user-defined functions (UDFs) developed for VBA and Power Query (M), with the goal of extending the native capabilities of Excel and Power BI. Here you will find both functions created by me and useful functions developed by others — always properly organized, documented, and referenced.

🎯 Objective

• To centralize, organize, and facilitate access to a catalog of reusable functions
• To accelerate the development of solutions in Excel, Power Query, and Power BI.
• To maintain a versioned and easily searchable repository.
• To reuse functions tested and validated in different contexts.

Table of Contents

M Code (Power Query)

Function	Description	Documentation
Binary.Unzip	Extracts file from a compressed ZIP file	📄
DateTime.ToUnixTime	Converts a datetime format to Unix Time Stamp	📄
Decision.EntropyWeights	Calculates the weights of a decision multicriteria using the entropy weighting method	📄
Decision.TOPSIS	Applies the TOPSIS multicriteria method to a table in order to rank alternatives	📄
List.Correlation	Calculates the correlation coefficient between two lists of numeric values	📄
List.Intercept	Calculates the intercept B of the linear regression line Y = AX + B between two numerical lists X and Y	📄
List.Outliers	Returns a numerical list of outliers existing in a list	📄
List.PopulationStdDev	Calculates the population standard deviation of a numerical list	📄
List.Primes	Returns a list of prime numbers less than or equal to a given number n	📄
List.Rank	Returns a list of ranks for a given list of values	📄
List.Slope	Calculates the slope A of the linear regression Y = AX + B between two numerical lists X and Y	📄
List.Variance	Calculates the population variance of a numerical list	📄
List.WeightedAverage	Calculates the weighted average of a list of values given a corresponding list of weights	📄
Number.FromRoman	Converts a Roman numeral to a number	📄
Number.IsInteger	Checks if a number is integer or not	📄
Number.IsPrime	Checks if a number is prime or not	📄
Number.ToRoman	Converts a number to Roman numeral, if it's possible	📄
Statistical.NormDist	Calculates the Normal distribution for a given input x	📄
Statistical.NormInv	Returns the inverse of the CDF of the normal distribution	📄
Table.AddColumnFromList	Adds a new column to a table using values from a provided list	📄
Table.CorrelationMatrix	Calculates the correlation matrix for a given table	📄
Table.NormalizeColumnNames	Cleans and standardizes column names in a table	📄
Table.NormalizeTextColumns	Cleans, standardizes and formats column names in a table	📄
Table.NormalizeTextColumns	Cleans, standardizes and formats text columns in a table	📄
Table.RemoveBlankColumns	Removes columns from a table that contain only blank values	📄
Table.TransposeCorrectly	Transposes a table without losing the original column names	📄
Text.CountChar	Counts the occurrences of a specific character in a given text string	📄
Text.ExtractNumbers	Returns all numeric values from a given text	📄
Text.HtmlToPlainText	Converts HTML content to plain text	📄
Text.RegexExtract	Extracts a pattern from a text by using a regular expression	📄
Text.RegexReplace	Replaces a pattern in a text that match a given regular expression	📄
Text.RegexSplit	Splits a text into a list of strings based on a regular expression pattern	📄
Text.RegexTest	Tests whether a text matches a regular expression pattern	📄
Text.RemoveAccents	Removes any accent from characters in a text	📄
Text.RemoveDoubleSpaces	Replaces any sequence of multiple spaces in a text to sigle spaces	📄
Text.RemoveLetters	Removes all alphabetic characters from a text	📄
Text.RemoveNumerals	Removes all digits from a text	📄
Text.RemovePunctuations	Removes all punctuations from a text	📄
Text.RemoveStopwords	Removes common Portuguese stopwords from a text	📄
Text.RemoveWeirdChars	Removes special and non-printable characters from a text	📄

VBA (Visual Basic Application)

Function	Description	Documentation
AreArraysEquals	Checks if two arrays are equal	📄
AutoFillFormulas	Automatically fills formulas across a range using a reference cell's formula	📄
CleanString	Cleans a string by removing or replacing special and control characters with spaces	📄
DisableRefreshAll	Disables the "Refresh All" option for all OLEDB connections in a workbook	📄
EnableRefreshAll	Enables the "Refresh All" option for all OLEDB connections in a workbook	📄
FileExists	Checks if a file exists at the specified file path.	📄
FileNameIsValid	Checks if a given name can be used as a valid file name	📄
GetAllFileNames	Retrieves an array of all file names from a folder and its subfolders	📄
GetLetters	Extracts ASCII letters from a string, in lowercase/td>	📄
GetMonthNumberFromName	Converts a month name to its corresponding numeric value	📄
GetStringBetween	Extracts a string between two specified delimiters	📄
GetStringWithSubstringInArray	Searches through an array of strings and returns the first string that contains a specified substring	📄
GetTableColumnNames	Returns the column names of an Excel table	📄
IsAllTrue	Checks if all elements in a boolean array are True	📄
IsInArray	Checks whether a value exists in an array	📄
ListObjectExists	Checks whether a ListObject (Excel table) exists in a workbook	📄
PreviousMonthNumber	Returns the month's number that precedes the month of a given date	📄
RangeHasAnyFormula	Checks if a range contains any cell with formulas	📄
RangeHasConstantValues	Checks if a range contains any constant (non-formula) cell	📄
RangeIsHidden	Checks if a range has no visible cell	📄
RangeToHtml	Converts a range into an HTML string	📄
SendEmail	Sends an HTML email using CDO with NTLM authentication	📄
SetQueryFormula	Sets a Power Query formula for a query in the current workbook	📄
StringContains	Checks if a string contains a substring	📄
StringEndsWith	Checks if a string ends with a specified substring	📄
StringStartsWith	Checks if a string starts with a specified substring	📄
SubstringIsInArray	Searches a array for any string element that contains a specified substring and returns True on the first match	📄
Summation	Computes the numeric summation of a mathematical expression over an integer index range	📄
TableHasQuery	Checks if a ListObject (Excel table) is associated to a query	📄
WorksheetHasListObject	Checks if a worksheet contains a ListObject (Excel table)	📄

🤝 Contributions

Contributions are welcome! If you have an interesting feature, improvement, or fix for any function or documentation, feel free to open a pull request or an issue.

🗂️ Other UDF Repositories

Here is a list of very useful repos of user-defined functions:

• M by Imke Feldmann
• M Custom Functions by Štěpán Rešl
• m-custom-functions by Tirnovar
• M-tools by Kim Burgess
• PowerBi-code by ibarrau
• PowerQueryFunctions by OscarValerock
• PowerQueryLib by NinMonkey