VBA Public Function to Excel
- by Sugih
Dear sir ,
I have create below function
Option Explicit
Public Function fyi(x As Double, f As String) As String
Application.Volatile
Dim data As Double
Dim post(5)
post(1) = "Ribu "
post(2) = "Juta "
post(3) = "Milyar "
post(4) = "Trilyun "
post(5) = "Ribu Trilyun "
Dim part As String
Dim text As String
Dim cond As Boolean
Dim i As Integer
If (x < 0) Then
fyi = " "
Exit Function
End If
If (x = 0) Then
fyi = "Nol"
Exit Function
End If
If (x < 2000) Then
cond = True
End If
text = " "
If (x >= 1E+15) Then
fyi = "Nilai Terlalu Besar"
Exit Function
End If
For i = 4 To 1 Step -1
data = Int(x / (10 ^ (3 * i)))
If (data 0) Then
part = fyis(data, cond)
text = text & part & post(i)
End If
x = x - data * (10 ^ (3 * i))
Next
text = text & fyis(x, False)
fyi = text & f
End Function
Function fyis(ByVal y As Double, ByVal conds As Boolean) As String
Dim datas As Double
Dim posts(2)
posts(1) = "Puluh"
posts(2) = "Ratus"
Dim parts As String
Dim texts As String
'Dim conds As Boolean
Dim j As Integer
Dim value(9)
value(1) = "Se"
value(2) = "Dua "
value(3) = "Tiga "
value(4) = "Empat "
value(5) = "Lima "
value(6) = "Enam "
value(7) = "Tujuh "
value(8) = "Delapan "
value(9) = "Sembilan "
texts = " "
For j = 2 To 1 Step -1
datas = Int(y / 10 ^ j)
If (datas 0) Then
parts = value(datas)
If (j = 1 And datas = 1) Then
y = y - datas * 10 ^ j
If (y = 1) Then
posts(j) = "belas"
Else
value(y) = "Se"
End If
texts = texts & value(y) & posts(j)
fyis = texts
Exit Function
Else
texts = texts & parts & posts(j)
End If
End If
y = y - datas * 10 ^ j
Next
If (conds = False) Then
value(1) = "Satu "
End If
texts = texts & value(y)
fyis = texts
End Function
but when I return to Excel
and type
'=fyi(500,"USD") it return to #name?
please do me favor to inform me how to solve
Rgds,
Sugih