VBA公共用户定义函数

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.

当我返回Excel并在单元格中键入=fyi(500，“USD”)时，它将返回#name。

Please inform me how to solve.

请告诉我怎么解决。

5 个解决方案

#1

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

这类函数的最佳位置是在一个Addin…让一个外接程序:

Make a new workbook

新建一个工作簿

hit alt+F11

按alt +季

create a module, call it MyFunctions or something else meaningfull

创建一个模块，叫它myfunction或者其他有意义的东西

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)

完成所有这些之后，将工作簿保存为ExcelAddin (.xlam)并关闭它。转到Excel选项(或Tools/addins)，选择您的addin(或转到addins选项卡，单击Go，然后查找Excel 07)

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

现在，您的函数在每个工作簿中都是可用的，而不必加上前缀

#2

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

如果您的UDF位于工作簿之外的工作簿中，那么请在UDF前面加上工作簿名称。如。

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

#3

See this related question: Create a custom worksheet function in Excel VBA

请参见这个相关的问题:在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.

总结:你所拥有的应该是有用的。根据对该问题的评论，您应该将用户定义的函数放在除本工作簿之外的任何模块中。

#4

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

确保您的函数位于模块中，而不是在工作表中。

#5

-2

Check the typo: the function is fyi not fyis.

检查错误:函数是fyi而不是fyis。

See the last line fyis = texts, it should be fyi = texts.

见最后一行fyis = text，应该是fyi = text。

#1