Excel VBA 函数的 #VALUE 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21833820/
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
#VALUE error with Excel VBA Function
提问by Kezz101
In my Excel spreadsheet I have two columns.
在我的 Excel 电子表格中，我有两列。
- A contains strings with the values 'Yes', 'No' or 'Maybe'.
- B contains strings with a year in.
- A 包含值为“Yes”、“No”或“Maybe”的字符串。
- B 包含带有年份的字符串。
I need a function to determine the number of occurrences of a year in column B, where the equivalent value in column A is 'Yes'.
我需要一个函数来确定 B 列中一年出现的次数，其中 A 列中的等效值为“是”。
I currently have the following code:
我目前有以下代码：
Function CountIfYearAndValue(Rng As Range, YNM As String, Year As String) As Integer
Dim count As Integer
count = 0
For Each c In Rng.Cells
If (StrComp(Abs(c.Value), Year, vbTextCompare) = 0) And (StrComp(Cells(c.Row, A), YMN, vbTextCompare) = 0) Then count = count + 1
Next
CountIfYearAndValue = count
End Function
The idea of this code is that we iterate through every cell in the range given (a range on column B) and check if the year is equal to the Yearparameter. And if the equivalent cell on column A is equal to the YNMparameter we increment the countvariable.
这段代码的想法是我们遍历给定范围内的每个单元格（B 列上的范围）并检查年份是否等于Year参数。如果 A 列上的等效单元格等于YNM参数，我们将增加count变量。
For some reason this code does not work when I use the following parameter:
出于某种原因，当我使用以下参数时，此代码不起作用：
=CountIfYearAndValue('Years'!B1:B7,"Yes","Year 7")
It just does the #VALUEerror and refuses to display any outcome.
它只是执行#VALUE错误并拒绝显示任何结果。
Any help would be much appreciated.
任何帮助将非常感激。
Edit:All of the values in both cells are on of an unformatted datatype ('General') and no cells are blank.
编辑：两个单元格中的所有值都属于未格式化的数据类型（“常规”），并且没有单元格为空白。
回答by
It sounds like you are reinventing the wheel... There already is a built in function (advantage: being much faster than a UDF) that does exactlywhat you are after. It is called COUNTIFS()
听起来你正在重新发明轮子......已经有一个内置函数（优点：比 UDF 快得多），它完全符合你的要求。它被称为COUNTIFS()
All YESes for Year 7in rows 1 to 10.
第1 行到第 10 行中的所有YESes Year 7。
=COUNTIFS(B1:B10, "Year 7",A1:A10, "Yes")
=COUNTIFS(B1:B10, "Year 7",A1:A10, "Yes")
I just had a quick look at your code and I think there are possibly a few reasons why your original code is not working as expected.
我只是快速查看了您的代码，我认为您的原始代码未按预期工作可能有几个原因。
YNMis a valid column name therefore it should not be used as a variable name. You should avoid naming your variables like that - give it a more meaningful nameYNM!=YMNas you had it in your code (see function definition and then the misspelled version in theStrComp()function)Yearis a valid VBA built in function, therefore once again you should avoid using it as a variable name as you're exposing yourself to a naming collision.Add
Option Explicitat the top of your module. This requires you toDimension all you variables. It's always recommended for many many reasons.rngvariable is ofRangetype therefore you do not need to explicitly add the.Cellsproperty to it. Even though it may help in some cases - at a bit more advanced level you may face some runtime type compatibility issues. ( runtime may convert yourrngRange variable to a 2D array etc)Added an explicit conversion in the second
StrComp()function around thec.Offset(0, -1)as you don't want the runtime to (rare but still possible) convert yourYesto aBooleandata type. Explicit conversion to aStringjust gives you that extra protection;p (lol)
YNM是有效的列名，因此不应将其用作变量名。你应该避免这样命名你的变量 - 给它一个更有意义的名字YNM!=YMN正如您在代码中所拥有的那样（请参阅函数定义，然后是StrComp()函数中拼写错误的版本）Year是一个有效的 VBA 内置函数，因此您应该再次避免将它用作变量名，因为您将自己暴露在命名冲突中。添加
Option Explicit在模块的顶部。这要求您Dim考虑所有变量。出于很多原因，它总是被推荐。rng变量是Range类型，因此您不需要显式地.Cells向它添加属性。即使在某些情况下它可能会有所帮助 - 在更高级的级别上，您可能会遇到一些运行时类型兼容性问题。（运行时可能会将您的rngRange 变量转换为二维数组等）在第二个
StrComp()函数中添加了显式转换，c.Offset(0, -1)因为您不希望运行时（罕见但仍然可能）将您Yes的Boolean数据类型转换为数据类型。显式转换为 aString只是给你额外的保护;p（笑）
therefore, something like this returns the correct value
因此，这样的事情会返回正确的值
Function CountIfYearAndValue(rng As Range, choice As String, myYear As String) As Long
Dim count As Long
count = 0
Dim c As Range
For Each c In rng
If (StrComp(c, myYear, vbTextCompare) = 0) And (StrComp(CStr(c.Offset(0, -1)), choice, vbTextCompare) = 0) Then
count = count + 1
End If
Next c
CountIfYearAndValue = count
End Function
Right, I hope this helps you understand bits and pieces :) any questions please leave a comment
是的，我希望这可以帮助您理解零碎:) 任何问题请发表评论
