Unit 8 – Excel Custom Function (UDF)

Access Excel VBA group number with larger than smaller than (age group)

Posted on by Wyman W
This Access Excel tutorial explains how to create custom Function to categorize /group number with larger than smaller than symbol, such as age group. Access Excel categorize / group number with larger than smaller than symbol Sometimes when you have a group of numbers, you may need to categorize / group them into different categories for analysis. For example, you may need to make age group for employees, such as <20 >=20 to <30 >=30 to <40 >=40 In order ...
Read More

Excel convert column letter to column number vise versa

Posted on by Wyman W
This tutorial explains how to convert column number to column letter, and convert column letter to column number in Excel. Excel column letter to column number / column number to column letter conversion Column number refers to the column expressed in integer, while column letter refers to column expressed in alphabet. For example, for Cell D1, column number is 4, while column letter is D. Excel convert column number to column letter To convert column number to column letter, make use o...
Read More

Excel Vlookup second matched value or specific occurrence

Posted on by Wyman W
This tutorial explains how to write a custom VBA Excel Function to vlookup second matched value or specific occurrence. You may also want to read: Case sensitive lookup Excel Lookup Function lookup multiple criteria not in the first column Excel lookup partial text using Vlookup with Wildcard Excel lookup Function lookup multiple criteria Excel Vlookup second matched value or specific occurrence In traditional Vlookup, you are allowed to vertically lookup the first value that ma...
Read More

Excel VBA custom Function last row and last column

Posted on by Wyman W
This tutorial explains how to create a custom VBA Function to find the last row and last column number in Excel worksheet. You may also want to read: Excel find the last row using Worksheet formula Excel VBA custom Function last row and last column In Excel VBA, we always need to loop through each Cell to perform some actions. For Range that you already know, you can easily use For...Each Loop to loop through each Cell for a specified Range as below For Each rng in Range("A1:C10") ...
Read More

Excel Access MRound custom Function round to nearest multiple

Posted on by Wyman W
This tutorial explains how to create a custom Excel Access MRound Function to simulate Excel spreadsheet MRound Function round to nearest multiple. You may also want to read: VBA Excel Access roundup rounddown Function Excel MRound Function Excel Round Function is used to round to nearest digit, while Excel MRound Function is used to round to nearest multiple. The first time I used MRound Function is to round employee bonus to nearest $100, because we want the bonus amount looks better. ...
Read More

Excel VBA Custom Function check if worksheet exists

Posted on by Wyman W
This tutorial explains how to use Excel VBA Custom Function to check if worksheet exists, and add new worksheet if worksheet doesn't exist Excel VBA Custom Function check if worksheet exists Sometimes we want to manipulate worksheet in a workbook but we are not sure if the worksheet exists. For example, we want to add a worksheet, if it already exists, skip the adding worksheet code. VBA code - Custom Function to check if worksheet exists There are many Functions on the internet that do the ...
Read More

Excel VBA Custom Function to Get RGB Color

Posted on by Wyman W
This tutorial explains how to create an Excel VBA custom Function to get RGB Color using Interior.Color Property. Excel VBA Custom Function to Get RGB Color RGB is a VBA Property that can set RGB color. Usually Property comes in a pair, one Method is to Set Property and another is  Get Property. However, RGB does not seem to have the Get Method, therefore you cannot get the RGB color directly. To get the RGB Color, we need to make use Interior.Color Property to get Interior Color code, whic...
Read More

Excel VBA custom Function Find the first colored Cell value

Posted on by Wyman W
This tutorial explains how to create an Excel VBA custom Function to find the first colored Cell value in a Range using ColorIndex Property. You may also want to read Excel VBA Function sum colored Cell/ count colored Cell Find the first colored Cell value using ColorIndex Property This custom Function was originally created for a question raised in Microsoft Community. It seems that many people have been asking similar questions like finding the first non-empty Cell, finding the first ...
Read More

Access Excel custom split Function to delimit text

Posted on by Wyman W
This tutorial shows how to create a custom Access Excel VBA custom Function to delimit text with the help of VBA "Split" Function instead of "Text to Columns". Delimit text in Excel (non-VBA) In Excel worksheet, there is a function called "Text to Columns" (Data > Text to Columns) to separate a text by delimiter. For example, Cell A1 contains "a,b,c" and after split by comma, A1 contains "a", B1 contains "b" and C1 contains "c". An alternative way to do that is using Left Function with...
Read More

Access Excel VBA Check Prime Number using custom Function

Posted on by Wyman W
This tutorial shows a custom Access Excel VBA Function that can check prime number, the Function returns TRUE or FALSE. Access Excel VBA Check Prime Number using custom Function In this tutorial, I will show a custom Access Excel Function that can check prime number, returning TRUE or FALSE. Prime number is an integer that can only be divided by 1 or itself (without remainder). Below are some examples of prime number. 1–20 2 3 5 7 11 13 17 19 23 29 31 37 41 43 47 53 59 ...
Read More