Function Examples

Convert numbers to text
Display any number as text, 9999 = ninethousand ninehundredandninetynine.
English and Norwegian text, other languages needs some editing.
Source code for Indonesian language (Bahasa Indonesia) is also included, translated by Billy S. C. Ticoalu.
Click here to download this example.
Updated: 2006-05-07 Requires: XL5 File size: 8 kB

Translate worksheet function names to and from English
This workbook can translate worksheet function names from the local language used in Excel to the English function names, e.g. from German to English if you are using a German edition of Excel. It is also possible to translate from English function names to the local function name, e.g. from English to German if you are using a German edition of Excel.
If you are using an English edition of Excel you'll have little use for this workbook.
Click here to download this example.
Updated: 2001-02-14 Requires: XL5 File size: 14 kB

A lookup function with a twist
If you look up numbers/text from lists/databases that contains several duplicate values, you will have use for this function. If the value you are looking for occurs more than once, the function displays a dialogbox that lets you select which value you want the function to return.
The example will only work in Excel97 because of the use of a UserForm, but can easily be converted to Excel5/95.
Click here to download this example.
Updated: 2004-11-26 Requires: XL97 File size: 12 kB

Calculate with time values
This workbook demonstrates how you can calculate with time values (hours, minutes). You may also calculate with negative time values.
Click here to download this example.
Updated: 2000-08-08 Requires: XL5 File size: 7 kB

How to count unique values
This workbook uses a custom function to return the count of equal values in two different ranges. The function counts matches regardless of the position in the range. You will also find a function that counts the matches according to the position in the range.
Click here to download this example.
Updated: 2000-08-08 Requires: XL5 File size: 8 kB

A worksheet function that returns an array with unique values
This workbook contains a custom function that shows how you can return an array with the unique values from a range. You can use this function when you don't want to use the database functionality in Excel.
Click here to download this example.
Updated: 2000-08-08 Requires: XL5 File size: 11 kB

Document last updated 2006-05-07 09:43:13 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-05-09 11:35:50 CET):

Re: My worksheet works slow
I assume your problem is related to running macros.
You can speed up macro execution by:
- declaring all variables with their proper data type (e.g. Dim r As Long)
- turn off ScreenUpdating (e.g. Application.ScreenUpdating = False)
- turn off Calculation (e.g. Application.Calculation = xlCalculationManual)

After your code has finished remember to reset Calculation and ScreenUpdating (usually not necessary in Excel):
- turn on ScreenUpdating (e.g. Application.ScreenUpdating = True)
- turn on Calculation (e.g. Application.Calculation = xlCalculationAutomatic)

Lieu quoc dat from Viet nam wrote (2006-05-09 11:20:12 CET):

My worksheet works slow
How can i make it run faster?

Ole P. from Norway wrote (2006-04-13 12:04:07 CET):

Re: Number To Teks in Indonesian (Bahasa Indonesia)
The code you posted was truncated and incomplete due to limitations of this feedback system.
Send med your code by mail using the address at the bottom of this page and I will make it available for download with credits to you.

Billy S.C Ticoalu from Tolitoli, Indonesia wrote (2006-04-13 03:41:16 CET):

Number To Teks in Indonesian (Bahasa Indonesia)
Master Ole P. Erlandsen
Many thanks for your help, your site realy enlight me and your work realy help me in my daily job .... once again Thank you so much.
I've modified you're code and adapted it into Indonesian ,
hopefully this will help my fellow from Indonesia if they browse to your site

'=====================================================================
'FUNSI TERBILANG (function Number To Teks )
'Created by Ole P. Anderson
'http://www.erlandsendata.no/english/index.php?d=endownloadfunctions
'Cara Penggunaan : "=TERBILANG(CELL;TRUE)" atau "=TERBILANG(CELL;FALSE)
'=====================================================================

[code truncated]

Ole P. from Norway wrote (2006-02-04 10:02:33 CET):

Re: How to change colour of cell after entry in that cell
Use the menu Format, Conditional Formatting... to set the desired colour.

Vish from India wrote (2006-02-04 09:54:19 CET):

How to change colour of cell after entry in that cell
After making entry in the cell I wish to change colour of the cell so that one can came to know that entry was made in that particular cell.

How do I accomplish this?

Ole P. from Norway wrote (2005-03-19 23:54:45 CET):

Re: Convert numbers to text
The available download is now updated with English language.

Ramesh V wrote (2005-03-19 09:50:37 CET):

Convert numbers to text
Can i get this tool in English language - i mean the numbers should be converted in to english. Presently this tool is converting into a different language

Ole P. from Norway wrote (2004-08-19 09:50:19 CET):

Re: How to update the list in particular cell
Take a look at the VLOOKUP worksheet function in the built-in help in Excel, this function (or a similar one) will probably be useful.

Riya from Mumbai wrote (2004-08-19 06:51:22 CET):

How to update the list in particular cell based on the selection of the previous cell value?(Used da
I have created a form in an excel sheet. On select of a particular value from the list the list in the next cell should change accordingly.e.g if I am selecting any name say Riya from the list in the cell I2 the list in the cell J2 should give list that only matches for Riya. And also this change should take place only in Range J. Can u suggest any solution to it?