VBA Public User Defined Function in Excel

Question

I have created the function below:

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

When I return to Excel and type =fyi(500,"USD") in a cell, it returns #name.

Please inform me how to solve.

Solution 1

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

Solution 2

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")

Solution 3

See this related question: 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.

Solution 4

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

View more solutions

Comments

[Antoni]

Not sure I'd use this method.. but +1 for obscure UDF knowledge Dick!

[Artjom B.]

fyis is also a function in the code from the question.

[Alexis]

How do you name a module? When I create it it is just named "Module" and when I <right-click> on it the contextual menu does not offer me a "Rename" option.

[Starfish]

@Alexis, when you select the module name there will be a section of the window below that allows you to edit the module properties, including the name. Click and edit the name and hit return and it will be renamed.