Function creation problem

[excelixis]

Dear all,
I created this macro that works fine.

Sub xxxxxxx()
Keli = Cells(13, 8).Value
Valid1 = Application.WorksheetFunction.VLookup(Day(Keli), Range("ToTalList"), 2, False)
Valid2 = Application.WorksheetFunction.VLookup(Month(Keli), Range("ToTalList"), 3, False)
Valid3 = Application.WorksheetFunction.VLookup(Int(Year(Keli) / 100), Range("ToTalList"), 4, False)
Valid4 = Application.WorksheetFunction.VLookup(Year(Keli) - Int(Year(Keli) / 100) * 100, Range("ToTalList"), 5, False)
MsgBox Valid1 & " " & Valid2 & " " & Valid3 & " " & Valid4
End Sub

When I try to convert it to a usefull function I get a #Name? Error
What do i do wrong? The purpose is to create a date in to words function

Public Function DateInWords(Keli As Range)
Valid1 = Application.WorksheetFunction.VLookup(Day(Keli.Value), ThisWorkbook.Worksheets(1).Range("ToTalList"), 2, False)
Valid2 = Application.WorksheetFunction.VLookup(Month(Keli.Value), ThisWorkbook.Worksheets(1).Range("ToTalList"), 3, False)
Valid3 = Application.WorksheetFunction.VLookup(Int(Year(Keli.Value) / 100), ThisWorkbook.Worksheets(1).Range("ToTalList"), 4, False)
Valid4 = Application.WorksheetFunction.VLookup(Year(Keli.Value) - Int(Year(Keli.Value) / 100) * 100, ThisWorkbook.Worksheets(1).Range("ToTalList"), 5, False)
DateInWords = Valid1 & " " & Valid2 & " " & Valid3 & " " & Valid4
End Function

 

[excelixis]

its not yet an add in, its a worksheet. Yes I m trying the function in the same workbook.

 

[Richard Schollar]

I'm not sure why it isn't working for you. In my test environment it works fine (as it did for you using the Sub instead).

 

[excelixis]

Is it maybe a version problem? I use Excel 2007! Is it possible to attach the tested file?

 

[Richard Schollar]

You can't attach a file directly but you could use a file sharing site like box.net and publish the link in your reply.


EDIT: incidentally, it works in xl2007 for me too

 

[xld]

I want to type a date in a cell and with this function to get the date typed in in words that i have in TotalList range. Like the table below.. in Greek Language.

<TABLE style="WIDTH: 479pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=638><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7387" width=202><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20 width=64> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 104pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=139>ημέρα</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=94>μήνας</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 152pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=202>χρόνος 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 104pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=139>χρόνος 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>μία</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>ιανουαρίου</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>ένα</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>δύο</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>φεβρουαρίου</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>δύο</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>τρείς</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>μαρτίου</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>τρία</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>τέσσερεις</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>απριλίου</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>τέσσερα</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>πέντε</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>μαΐου</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>πέντε</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>έξη</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>ιουνίου</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>έξη</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>εφτά</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>ιουλίου</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>εφτά</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>οκτώ</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>αυγούστου</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>οκτώ</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>εννέα</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>σεπτεμβρίου</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>εννέα</TD></TR></TBODY></TABLE>

Not much use I am afraid, I wanted to see whether you had separate values for the days, months, years, and what your index ids were.

 

[excelixis]

There is my file

 

[Richard Schollar]

Hi

Change your module name to something that doesn't conflict with the name of your function (eg just leave it as Module1). Otherwise you need to prefix the function name with the name of the module:

=DateInWords.DateInWords(H13)

 

[excelixis]

So simple mistake... Thanks a lot my friends..