Function to return the address of a lookup value

If you look at many of Excel lookup functions you will find out that they either return a value or a position of an item. There is no function that returns the address where a lookup value is found.

Below is a code for a user defined function that takes two arguments: a lookup value and a range, and returns the address where the lookup value is found within the range. If the lookup value is not found, it will return the text “#N/A”.

You can download the function as a text from the Download Page.

If you need more help on how to create a user defined function click here.

Function LookupAddress(LookupVal As String, TheRange As Range)
Dim Cell As Range, Found As Boolean
For Each Cell In TheRange.Cells
If Cell.Value = LookupVal Then
LookupAddress = Cell.Address
Found = True
Exit For
End If
Next Cell
If Not Found Then LookupAddress = “#N/A”
End Function

Applies to Excel 2003

How to make your macros and functions available to all workbooks

When you create a macro or a user defined function in a workbook, by default that macro will only be visible for that specific workbook.
If you have a code that you use repeatedly and you want it to be available for all workbooks then you have to create a global workbook called Personal.xls and save it in Excel startup folder.

How to create the file:

1. Open a new workbook.
2. Save As Personal.xls in folder:
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
3. Copy your macros from their original workbooks to personal.xls.
4. Click Window –> Hide to hide the file.

The path may differ slightly depending on which version of office you are using.

How to use the file:

1. Whenever you start Excel personal.xls will be opened automatically, and all your macros will be available for use in any workbook.

2. Keep personal.xls hidden unless you want to add macros to it.

3. When you want to add new macros Click Window –> Unhide and select “personal.xls”.

4. To add a new macro click Tools –> Macro –> Record new macro. You will get the following dialogue box.

savemacro

5. Under “Store macro in:” select “Personla.xls”.

6. When you are done hide the file again.

If you are interested in more details please see the following Microsoft office link:

Deploy your Excel macros from a central file

Applies to: Excel 2003

Function to return the day of the week in text (Name of the day)

Excel has a built in function which returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default, or 1 (Monday) to 7 (Sunday) if you use a return type of 2. But there is no direct function to return the name of the day itself i.e. Monday, Tuesday…etc.The following code is an attempt to handle this case.


Function WeekdayText(dDate, iRtype)
‘ get the weekday no. from excel bilt-in Weekday function
iwday = WorksheetFunction.Weekday(dDate)
If iRtype = 2 Then
‘ Return the full name of the day
WeekdayText = WorksheetFunction.Text(iwday, “ddddd”)
Else
‘ Return the short name of the day
WeekdayText = WorksheetFunction.Text(iwday, “ddd”)
End If
End Function

The function accepts two arguments: Date and return type. If the return type is 1, the function will return an abbreviated name of the day i.e. Mon, Tue …. etc. If the return type is 2, the function will return the full name of the day i.e. Monday, Tuesday …. etc.

Use this code to create a user defined function and then you can use it like any other built-in function.

If you are not familiar with visual basic then you can achieve the same result by encapsulating the Weekday function within the Text function, in a cell, as follows:

=TEXT(WEEKDAY(date),”ddd”)

Related Links:

How to create a user-defined function

Applies to: Excel 2003

Function to decide whether a year is a leap year or not

The following Visual Basic code is for a function to determine whether a certain year is leap year or not. It will receive the year as an argument and return TRUE for a leap year and FALSE for others.Copy this code and paste it in a new visual basic module.


Function IsLeap(iYear)
If (iYear Mod 400) = 0 Then
IsLeap = True
ElseIf (iYear Mod 100) = 0 Then IsLeap = False
ElseIf (iYear Mod 4) = 0 Then IsLeap = True
Else: IsLeap = False
End If
End Function

Related Links:

Blog Post: How to create a user-defined function
External: Wikipedia article on Leap_year

Applies to: Excel 2003

How to create a user-defined function

If you know Visual Basic programming then you can create your own user-defined functions in Excel. Follow the steps below:

1. On the Tools menu select Macro.

2. On the Macro submenu select Visual Basic Editor.

User defined1

3. You will be switched to Visual Basic Editor Menu.

User defined 2

4. On the Insert menu (Visual Basic Editor) select Module.

5. This will open the code window.

User defined 3

6. Type the Visual Basic code for your function.

7. On the File menu click Close and return to Microsoft Excel.

8. Your new function should now be ready for use like any other Excel function.

Applies to: Excel 2003