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
"C3" skrev i melding ...
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!