用户定义的函数在工作表中返回#REF，在VBA中工作 [英] User defined function returns #REF in worksheet, works in VBA

问题描述

我正在写一个简单的UDF，告诉我货币代码是否为G10。当我在VBA中的一个子进行测试时，它工作正常，但是当我尝试在电子表格中使用它时，我得到#REF。

I'm trying to write a simple UDF that tells me whether a currency code is G10 or not. It works fine when I test it in a sub in VBA but when I try to use it in a spreadsheet I get #REF.

Function IsG10(Cur As Range) As Boolean

  Dim G10s As Variant
  Dim Rslt As Boolean
  Dim Cross As Variant

 Cross = Cur.Value
 Rslt = False

 G10s = Array("USD", "GBP", "EUR", "CHF", "NOK", "SEK", "AUD", "NZD", "CAD", "JPY")

If Not (Application.WorksheetFunction.IsText(Cross)) Or Len(Cross) > 3 Then
    IsG10 = CVErr(xlErrNA)
Else
    For Each i In G10s
        If Cross = i Then Rslt = True
    Next i
End If

IsG10 = Rslt

End Function

这是我使用的子测试：

Sub Test()

 Dim TC As Range
 Dim ans As Variant
 Set TC = Range("Y53")

 ans = IsG10(TC)

End Sub

我已经尝试了很多东西来使其工作，删除错误处理，返回一个字符串而不是一个布尔没有什么工作。我看不到任何明显的UDF错误。

I've tried quite a few things to make it work, removed the error handling, returning a string instead of a bool... Nothing works. I can't see any obvious UDF mistakes.

谢谢！

推荐答案

p>你需要重命名你的功能。 ISG10 是一个有效的单元格地址，所以当您将公式放在单元格中，如 = ISG10（A1）时，Excel不能正确解析它。当我将它更改为函数Foobar（Cur As Range）As Boolean 。

You need to rename your function. ISG10 is a valid cell address, so when you put a formula in a cell like =ISG10(A1), Excel can't parse it correctly. It works just fine when I change it to Function Foobar(Cur As Range) As Boolean.

这篇关于用户定义的函数在工作表中返回#REF，在VBA中工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！