Excel中的VBA公共用户定义函数 [英] VBA Public User Defined Function in Excel

问题描述

我已经创建了以下功能：

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

当我返回Excel并在单元格中键入 = fyi（500，USD），它返回 #name 。

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

请告诉我如何解决。

推荐答案

功能最好的地方因为这是一个Addin ...
要做一个插件：

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

创建一个新的工作簿

点击Alt + F11

hit alt+F11

创建一个模块，调用MyFunctions或其他有意义的东西

create a module, call it MyFunctions or something else meaningfull

删除你的功能在这里

完成所有操作后，将工作簿保存为ExcelAddin（.xlam）并关闭它。
转到Excel选项（或工具/插件）并选择您的插件（或转到插件选项卡，然后单击开始，然后找到excel 07）

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

这篇关于Excel中的VBA公共用户定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！