UDF在VBA中工作，返回#REF！在工作簿中 [英] UDF works in VBA, returns #REF! in workbook

问题描述

我编写了一个函数，用于计算工作簿中特定工作表2014年的销售额，然后在特定单元格中打印2014年的总销售额.我可以在VBA中调用该函数，没问题.但是，如果尝试在工作簿中使用该功能，则会出现参考错误.我知道#REF！意味着公式引用的单元格无效，我是否需要向要调用该函数的单元格中返回某种值?例如，如果我在单元格F25中键入sum2014("A1151")，是否需要返回某种布尔值才能使函数正常工作?

I wrote a function that calculates the sales for 2014 for the specific worksheets inside the workbook, then prints the total sales for 2014 in a specific cell. I can call the function inside of VBA, no issue. But if try to use the function in the workbook, I get a reference error. I know #REF! means the formula refers to a cell that isn't valid, do I need to be returning some kind of value into the cell that I am trying to call the function into? For example, if I type sum2014("A1151") into cell F25, do I need to be returning some kind of boolean value for the function to work properly?

Function sum2014(someaccount As String) As Currency
Dim i As Integer
Dim total As Currency
Dim ws As Worksheet
total = 0
Set ws = ActiveWorkbook.Worksheets(someaccount)
     For i = 1 To 50
          If ws.Range("A3").Offset(i, 0) >= DateValue("January 1, 2014") And ws.Range("A3").Offset(i, 0) <= DateValue("December 1, 2014") Then
          total = total + ws.Range("A3").Offset(i, 1)
          sum2014 = total
          End If
     Next
     If Worksheets("New Charges").Range("C3").Offset(i, 0) = someaccount Then
          Worksheets("New Charges").Range("E3").Offset(i, 0) = total
     End If
     For i = 1 To 5
          If Worksheets("New Charges").Range("C3").Offset(i, 0).Value = someaccount Then
          Worksheets("New Charges").Range("E3").Offset(i, 0).Value = total
          End If
     Next  

End Function

在VBA中调用函数的示例:

Example of calling the function inside VBA:

Sub testfunction()
sum2014 ("A1151")
sum2014 ("B1808")
sum2014 ("C3711")
sum2014 ("D3265")
End Sub

我可以在VBA中调用该函数，并且可以正常工作.计算出销售额并将其放置在正确的位置.但是我不能在工作簿中调用UDF.我正在使用

I can call the function inside VBA, and it works fine. The sales are calculated and placed in the proper location. But I cannot call the UDF inside of the workbook. I am calling the function with

 sum2014=("A1151") 

任何提示或建议将不胜感激.我可能可以使用do while或do直到循环而不是for循环来清理函数，但现在我只想让函数在工作簿中工作，然后稍后重新编写.

Any hints or suggestions would be greatly appreciated. I could probably clean up the function with do while or do until loops instead of for loops but for right now I would just like to get the function working inside of the workbook then revisit rewriting later.

推荐答案

从工作表中调用的函数遇到问题(如果您尝试使用技巧来解决它)，您面临的直接问题是sum2014 是有效的Excel范围地址(第13403列()，2014行)，因此，当您尝试执行时，会出现 #REF！错误.= SUM2014("A1151").就像您的函数被称为 A1 .

While it is correct that a function called from a sheet may not change sheets (and you may run into problems if you try to use a trick to work around it), your immediate problem is that sum2014 is a valid Excel range address (column 13403 (SUM), row 2014), hence the #REF! error when you try to do =SUM2014("A1151"). It's the same as if your function was called A1.

在公式中可用的名称可能看起来不像有效的Excel范围.选择另一个名称(并确保不要从该函数更改其他单元格，否则将出现 #VAL！错误).

A name usable in a formula may not look like a valid Excel range. Pick another name (and make sure you don't change other cells from that function, otherwise it will be a #VAL! error).

如果您需要从例程中更改各种单元格，请将其作为子对象并从外部事件(例如按钮按下或 Worksheet_Change 处理程序)中调用它，而不是从单元格.

If you need to change various cells from a routine, make it a sub and call it from an external event, such as a button press or a Worksheet_Change handler, but not from a formula in a cell.

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