Excel Function (VBA)
Excel Method (VBA)
Excel Object Property (VBA)
Excel Event (VBA)
This page lists all the Excel functions (for worksheet) that have been explained in this website.
You may also want to read:
Differences among Function, Sub, Method, Property
String Function
[table width=”750″ colwidth=”150|600″ ]
Function, Description
CHAR, Convert ASCII value to character
CODE, Convert the first character of a text into ASCII value
COUNTA, Count non-empty cells
FIND, Return position of a substring in a string (case sensitive)
LEFT,
LEN, Return number of characters in a string
MID, Extract a substring from a string
MOD, Get remainder in division
RIGHT,
SEARCH, Return position of a substring in a string (can use Wildcard and case insensitive)
wCapLetter, A custom Function to capitalize first letter in a sentence
wCheckAlphabet, A custom Function to check if a text contains any alphabet
wCheckNumber, A custom Function to check if a text contains any number
wCheckOnlyAlphabet, A custom Function to check if a text contains only alphabet
wCheckOnlyNumber, A custom Function to check if a text contains only number
wCheckSymbol, A custom Function to check if a text contains any symbol
wCheckHKID, A custom Function to check the last digit of HKID
wCountSubStr, A custom Function to count substring in string
wCustomSort, A custom Function to assign numeric value to Text for custom sort
wExtractAlphabet, A custom Function to extract alphabet from text
wExtractNumber, A custom Function to extract number from text
wExtractPercent, A custom Function to extract Percent from text
wExtractSymbol, A custom Function to extract symbol from text
wExtractText, A custom Function to extract substring inside defined text (such as brackets)
wLpad, A custom Function to add leading character
wRandomHKID, A custom Function to generate random HKID (Hong Kong ID card number)
wRandomLetter, A customer Function to generate random letter a to z / A to Z / a to Z
wRandomNumber, A custom Function to generate random number by specifying lowerbound and upperbound
wRemoveBrackets, A custom Function to remove text inside brackets
wRpad, A custom Function to add characters to suffix
wSplit, A custom Function to delimit a text and extract the Nth substring
wSumExtractNumber, A custom Function to Sum number part of text in a Range
wUniqueStr, A custom Function to remove duplicate in a text
[/table]
Numeric Function
[table width=”750″ colwidth=”150|600″ ]
Function, Description
Cell, Get the information of Cell such as address and date/number format
Count, Count number of cells that contain number
Int, Get the integer part of a number
MRound, Round to nearest multiple
Rand, Generate a random decimal number between larger than 0 and smaller than 1
RandBetween, Generate a random number between two integers (both inclusive)
Round, Rounding that uses Round Half Up logic
SumIf, Sum a Range of values that meet a condition
SumIfs, Sum a Range of values that meet multiple conditions
SumProduct, Multiple the sum of two or more arrays and can be used to lookup multiple criteria
SubTotal, Calculate Auto Filter value with aggregate Function
wCountColorCell, A custom Function to count number of colored Cells in a given Range
wExtractNumber, A custom Function to extract number from text
wExtractPercent, A custom Function to extract Percent from text
wGroupDecimal, A custom Function to categorize/ group decimal number
wGroupInt, A custom Function to categorize/ group integer
wIsPrimeNumber, A custom Function to check if a number is prime number
wMRound, A custom Function to simulate spreadsheet MROUND Function
wRandomNumber, A custom Function to generate random number by specifying lowerbound and upperbound
wRound, A custom Function to simulate spreadsheet Round Function
wSumColorCell, A custom Function to sum colored Cells in a given Range
wSumExtractNumber, A custom Function to Sum number part of text in a Range
[/table]
Date Function
[table width=”750″ colwidth=”150|600″ ]
Function, Description
Cell, Get the information of Cell such as address and date/number format
Date, Convert year month and day to date serial
DateDif, Complete year / month / day difference between two days
DateValue, Convert date text to date serial
Days360, Day difference between two days or calculate the year / month difference in decimal places
EDate, Add (or subtract) specified months to a date
Networkdays, Net working days (exclude Sat and Sun) between two days
wNetworkdays, A custom Function that simulates Excel worksheet Networkdays to calculate number of work days (exclude Sat and Sun) between two dates. Suitable for people who cannot install Analysis ToolPak
[/table]
Lookup/Reference Function
[table width=”750″ colwidth=”150|600″ ]
Function, Description
Address, Combine column number and row number to return Cell address in Text format
Cell, Get the information of Cell such as address and date/number format
Indirect, Convert text to Reference
LookUp, Look up a value in a Range and return the corresponding result value in a Range
Rank, Give a number a rank in a range of number.
Offset, Find a Range that has a relative location to another Range
wlookup, A custom Function to Vlookup second matched value or specific occurrence
wrangesort, A custom Function to sort data in ascending order
wFirstColorCell, A custom Function to look for the first colored Cell
wColLastRow, A custom Function to look for last row of specific column
wColLastRowAdd, A custom Function to look for address of last row of specific column
wColLastRowVal, A custom Function to look for value of last row of specific column
wColNm, A custom Function to convert column number to column letter
wColNum, A custom Function to convert column letter to column number
wRowLastColNum, A custom Function to look for last column number of specific row
wRowLastColNm, A custom Function to look for last column name of specific row
wRowLastColAdd, A custom Function to look for address of last column Cell of specific row
wRowLastColVal, A custom Function to look for value of last column Cell of specific row
[/table]
Boolean Function
[table width=”750″ colwidth=”150|600″ ]
Function, Description
IFERROR, Return a desired value if the Cell contains error
wCheckAlphabet, A custom Function to check if a text contains any alphabet
wCheckNumber, A custom Function to check if a text contains any number
wCheckOnlyAlphabet, A custom Function to check if a text contains only alphabet
wCheckOnlyNumber, A custom Function to check if a text contains only number
wCheckSymbol, A custom Function to check if a text contains any symbol
wCheckHKID, A custom Function to check if the Check Digit (last digit) of HKID is correct
wIfWbOpen, A custom Function to check if a workbook is opened
wIsPrimeNumber, A custom Function to check if a number is prime number
wsExist, A custom Function to check if a worksheet exists
[/table]
Workbook/Worksheet Function
[table width=”750″ colwidth=”150|600″ ]
Function, Description
wsExist, A custom Function to check if a worksheet exists
wIfWbOpen, A custom Function to check if a workbook is opened
[/table]
Financial Function
[table width=”750″ colwidth=”150|600″ ]
Function, Description
IRR, Calculate Internal Rate of Return
NPV, Calculate Net Present Value
[/table]
Statistics Function
[table width=”750″ colwidth=”150|600″ ]
Function, Description
Correl, Calculate Coefficient of Correlation
Covariance.P, Calculate Population Covariance
Covariance.S, Calculate Sample Covariance
NORM.DIST, Calculate probability using standard deviation and mean
NORM.S.DIST, Convert Z Score to probability
Percentile, Calculate percentile using (n-1)*p+1
Percentile.inc, Calculate percentile using (n-1)*p+1
Percentile.exc, Calculate percentile using (n+1)*p
Standardize, Calculate Z Score from mean and standard deviation
T.Test, Calculate P value of Independent Sample T Test and Paired-samples T Test
[/table]
Pivot Table Function
[table width=”750″ colwidth=”150|600″ ]
Function, Description
GetPivotData, Get Pivot Table Data.
[/table]
Other Function
[table width=”750″ colwidth=”150|600″ ]
Function, Description
FormulaText, Get Excel Formula of a Cell
Hyperlink, Create Hyperlink to link to other cell to open website or open document
Msgbox, Create a prompt box to alert users
wGetFormula, A custom Function to get Excel Formula of a Cell (similar to FormulaText Function)
wRGB, A custom Function to get RGB color of Range
wLinkStatusDescr, A custom Function to convert xlLinkInfoStatus from numeric value to description
[/table]