VBA Public Function to Excel

Question

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

Answer 1

See this related question: http://stackoverflow.com/questions/755812/create-a-custom-worksheet-function-in-excel-vba

In summary:
What you have should work.
Based on the comments to that question, you should place your user-defined function in any module other than ThisWorkbook.

Answer 2

Make sure that your function is in a Module, not in the Worksheet.

Answer 3

If your UDF is in a workbook other than the workbook your calling from, prefix the udf with the workbook name. E.g.

=PERSONAL.XLS!fyi(500,"USD")

Answer 4

The best place for functions such as this is in an Addin... To make an addin:

Make a new workbook

hit alt+F11

create a module, call it MyFunctions or something else meaningfull

drop your funciton in there

Once you have done all this, save your workbook as an ExcelAddin (.xlam) and close it. Go to Excel Options (or Tools/addins) and select your addin (or go to the addins tab and click Go then find it for excel 07)

Now your funciton will always be available in every workbook without having to prefix it