Function

C3 · Apr 10, 2004

Problem:
In Access I use one function wich returns price in number format to format
in letters - text (Croatian prices). Function is below (and second part of
question).

-----------------------------------------------------------
Public Function Fn1(J)

ReDim D(1 To 8) As Integer

S$ = ""
N$ = LTrim$(Str$(J * 100))
For i = 1 To Len(N$)
U$ = Mid$(N$, i, 1)
D(Len(N$) - i + 1) = Val(U$)
Next i

Select Case D(8)
Case 0:
Case 1: S$ = S$ + "sto"
Case 2: S$ = S$ + "dvjesto"
Case 3: S$ = S$ + "tristo"
Case 4: S$ = S$ + "èetristo"
Case 5: S$ = S$ + "petsto"
Case 6: S$ = S$ + "¹esto"
Case 7: S$ = S$ + "sedamsto"
Case 8: S$ = S$ + "osamsto"
Case 9: S$ = S$ + "devetsto"
End Select

Select Case D(7)
Case 0:
Case 1:
Select Case D(6)
Case 0: S$ = S$ + "deset"
Case 1: S$ = S$ + "jedanaest"
Case 2: S$ = S$ + "dvanaest"
Case 3: S$ = S$ + "trinaest"
Case 4: S$ = S$ + "èetrnaest"
Case 5: S$ = S$ + "petnaest"
Case 6: S$ = S$ + "¹esnaest"
Case 7: S$ = S$ + "sedamnaest"
Case 8: S$ = S$ + "osamnaest"
Case 9: S$ = S$ + "devetnaest"
End Select
D(6) = 0
Case 2: S$ = S$ + "dvadeset"
Case 3: S$ = S$ + "trideset"
Case 4: S$ = S$ + "èetrdeset"
Case 5: S$ = S$ + "pedeset"
Case 6: S$ = S$ + "¹ezdeset"
Case 7: S$ = S$ + "sedamdeset"
Case 8: S$ = S$ + "osamdeset"
Case 9: S$ = S$ + "devedeset"
End Select
If D(7) > 1 And D(6) > 0 Then S$ = S$ + ""

Select Case D(6)
Case 0: If D(8) Or D(7) Or D(6) > 0 Then S$ = S$ + "tisuæa"
Case 1: If (D(7) > 0 Or D(8) > 0) Then S$ = S$ + "jedna tisuæa" Else S$
= S$ + "tisuæu"
Case 2: S$ = S$ + "dvijetisuæe"
Case 3: S$ = S$ + "tritisuæe"
Case 4: S$ = S$ + "èetiritisuæe"
Case 5: S$ = S$ + "pettisuæa"
Case 6: S$ = S$ + "¹esttisuæa"
Case 7: S$ = S$ + "sedamtisuæa"
Case 8: S$ = S$ + "osamtisuæa"
Case 9: S$ = S$ + "devettisuæa"
End Select

Select Case D(5)
Case 0:
Case 1: S$ = S$ + "sto"
Case 2: S$ = S$ + "dvjesto"
Case 3: S$ = S$ + "tristo"
Case 4: S$ = S$ + "èetristo"
Case 5: S$ = S$ + "petsto"
Case 6: S$ = S$ + "¹esto"
Case 7: S$ = S$ + "sedamsto"
Case 8: S$ = S$ + "osamsto"
Case 9: S$ = S$ + "devetsto"
End Select

Select Case D(4)
Case 0:
Case 1:
Select Case D(3)
Case 0: S$ = S$ + "deset"
Case 1: S$ = S$ + "jedanaest"
Case 2: S$ = S$ + "dvanaest"
Case 3: S$ = S$ + "trinaest"
Case 4: S$ = S$ + "èetrnaest"
Case 5: S$ = S$ + "petnaest"
Case 6: S$ = S$ + "¹esnaest"
Case 7: S$ = S$ + "sedamnaest"
Case 8: S$ = S$ + "osamnaest"
Case 9: S$ = S$ + "devetnaest"
End Select
D(3) = 0
Case 2: S$ = S$ + "dvadeset"
Case 3: S$ = S$ + "trideset"
Case 4: S$ = S$ + "èetrdeset"
Case 5: S$ = S$ + "pedeset"
Case 6: S$ = S$ + "¹ezdeset"
Case 7: S$ = S$ + "sedamdeset"
Case 8: S$ = S$ + "osamdeset"
Case 9: S$ = S$ + "devedeset"
End Select
If D(4) > 1 And D(3) > 0 Then S$ = S$ + ""

Select Case D(3)
Case 0: If Int(J) > 0 Then S$ = S$ + " kuna"
Case 1: S$ = S$ + "jedna kuna"
Case 2: S$ = S$ + "dvije kune"
Case 3: S$ = S$ + "tri kune"
Case 4: S$ = S$ + "èetiri kune"
Case 5: S$ = S$ + "pet kuna"
Case 6: S$ = S$ + "¹est kuna"
Case 7: S$ = S$ + "sedam kuna"
Case 8: S$ = S$ + "osam kuna"
Case 9: S$ = S$ + "devet kuna"
End Select

If (D(1) > 0 Or D(2) > 0) And Int(J) > 0 Then S$ = S$ + " i "
Select Case D(2)
Case 0:
Case 1:
Select Case D(1)
Case 0: S$ = S$ + "deset"
Case 1: S$ = S$ + "jedanaest"
Case 2: S$ = S$ + "dvanaest"
Case 3: S$ = S$ + "trinaest"
Case 4: S$ = S$ + "èetrnaest"
Case 5: S$ = S$ + "petnaest"
Case 6: S$ = S$ + "¹esnaest"
Case 7: S$ = S$ + "sedamnaest"
Case 8: S$ = S$ + "osamnaest"
Case 9: S$ = S$ + "devetnaest"
End Select
D(1) = 0
Case 2: S$ = S$ + "dvadeset"
Case 3: S$ = S$ + "trideset"
Case 4: S$ = S$ + "èetrdeset"
Case 5: S$ = S$ + "pedeset"
Case 6: S$ = S$ + "¹ezdeset"
Case 7: S$ = S$ + "sedamdeset"
Case 8: S$ = S$ + "osamdeset"
Case 9: S$ = S$ + "devedeset"
End Select
If D(2) > 1 And D(1) > 0 Then S$ = S$ + ""

Select Case D(1)
Case 0: If D(2) > 0 Then S$ = S$ + " lipa"
Case 1: S$ = S$ + "jedna lipa"
Case 2: S$ = S$ + "dvije lipe"
Case 3: S$ = S$ + "tri lipe"
Case 4: S$ = S$ + "èetiri lipe"
Case 5: S$ = S$ + "pet lipa"
Case 6: S$ = S$ + "¹est lipa"
Case 7: S$ = S$ + "sedam lipa"
Case 8: S$ = S$ + "osam lipa"
Case 9: S$ = S$ + "devet lipa"
End Select

Fn1 = S$

End Function
-----------------------------------------------

How do I use this function, in way that some cell (J223) who have some
expression (etc. =J27*1,22) and for result some number - and that number I
want in another cell in text (converted with function above)?

Thanks!

Rob van Gelder · Apr 10, 2004

Try: =Fn1(J223)

or

=Fn1(J27*1,22)

Harald Staff · Apr 10, 2004

Hi

Open the VB editor (Alt F11 or similar). Insert a module (menu Insert >
Module). Paste the function in.

Then a couple of modifications:

1 Declare your variables like this:

Dim S$, N$, U$
Dim i As Integer
Dim D(1 To 8) As Integer

2 Rename it (because FN1 is a cell address in Excel) and include
declarations:

Public Function Fnc1(J As Double) As String
' code, to
Fnc1 = S$

3 Now, in any Excel cell, a simple formula like:
=Fnc1(A1)

HTH. Best wishes Harald

Bob Phillips · Apr 10, 2004

HI,

You can use it like this

=FN1(J27*1,22)

but when I tested it I got ab error, due to the function name. If I changed
it to say NumToWords, it worked fine.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Harald Staff · Apr 10, 2004

Rob van Gelder said:
Try: =Fn1(J223)

You try -it says #REF.
FN1 is a cell address.
;-)
Best wishes Harald

Rob van Gelder · Apr 10, 2004

Yes, I spotted your post afterward, tried it, and promptly slapped my
forehead.

Function

C3

Rob van Gelder

Harald Staff

Bob Phillips

Harald Staff

Rob van Gelder