Excel 中的 VBA 公共用户定义函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2620068/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Public User Defined Function in Excel
提问by Sugih
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.
请告知如何解决。
回答by TerrorAustralis
The best place for functions such as this is in an Addin... To make an addin:
此类功能的最佳位置是在插件中...制作插件：
Make a new workbook
制作新的工作簿
hit alt+F11
按 alt+F11
create a module, call it MyFunctions or something else meaningfull
创建一个模块，称之为 MyFunctions 或其他有意义的东西
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 选项（或工具/插件）并选择您的插件（或转到插件选项卡，然后单击转到，然后为 excel 07 找到它）
Now your funciton will always be available in every workbook without having to prefix it
现在您的功能将始终在每个工作簿中可用，而无需为其添加前缀
回答by Dick Kusleika
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")
回答by mechanical_meat
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.
总结：
你所拥有的应该工作。
根据对该问题的评论，您应该将您的用户定义函数放在除 ThisWorkbook 之外的任何模块中。
回答by Michael Rodrigues
Make sure that your function is in a Module, not in the Worksheet.
确保您的函数在模块中，而不是在工作表中。
回答by user5591067
Check the typo: the function is fyinot fyis.
检查错字：函数fyi不是fyis。
See the last line fyis = texts, it should be fyi = texts.
看到最后一行fyis = texts，应该是fyi = texts。
