vba 调用 UDF 时，公式中使用的值的数据类型错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14460039/
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
A value used in the formula is of wrong data type when calling a UDF
提问by user2000380
I've been trying hard to figure out what went wrong here. I have two cols which contain string values. I use the third col to call a UDF in the worksheet, but end up getting #Value with error - "A value used in the formula is of wrong data type".
我一直在努力弄清楚这里出了什么问题。我有两个包含字符串值的列。我使用第三个 col 在工作表中调用 UDF，但最终得到 #Value 并出现错误 - “公式中使用的值的数据类型错误”。
Eg:
例如：
Col I Col J
File1 Y
File1 N
File2 Y
File3 N
I tried debugging it, and the input values were passed fine into the function variables. My knowledge in VBA is limited, hoping you guys can help me out in resolving this issue.
我尝试调试它，输入值被很好地传递到函数变量中。我对VBA的了解有限，希望你们能帮我解决这个问题。
Function called in worksheet:
工作表中调用的函数：
=FileCheck(I3,I3:J38)
Code:
代码：
Public Function FileCheck(V As Range, Q As Range) As String
Dim vtest As Variant
Dim i, j, stat As Integer
stat = 0
vtest = Q
For i = 1 To UBound(vtest)
If V = vtest(i, 1) And vtest(i, 2) = "N" Then
For j = 1 To UBound(vtest)
If V = vtest(j, 1) And vtest(j, 2) = "Y" Then
FileCheck = "Y"
stat = 1
End If
Next
If stat = 0 Then
FileCheck = "N"
End
Else: End
End If
ElseIf V = vtest(i, 1) And vtest(i, 2) = "Y" Then
FileCheck = "Y"
End If
Next
End Function
回答by Siddharth Rout
You are getting that error because you are using End.
您收到该错误是因为您正在使用End.
When you want to turn your computer off, do you click on Start Menu ~~> Shut downor do you pull the power cable out directly? :-)
当你想关闭电脑时，是点击Start Menu ~~> Shut down还是直接拔掉电源线？:-)
Using Endis very similar to pulling the cable. Don't use End. Exit the function gracefully. The Endstatement is basically a way of making your program crash, but without showing any error messages.
使用End非常类似于拉电缆。不要使用End. 优雅地退出函数。该End语句基本上是使程序崩溃的一种方式，但不显示任何错误消息。
Try this. Replace these lines
尝试这个。替换这些行
If stat = 0 Then
FileCheck = "N"
End
Else: End
End If
with
和
If stat = 0 Then FileCheck = "N"
Exit Function
EDIT
编辑
BTW, are you trying to find the corresponding value of Col Iin Col J? If yes, then you do not need a UDF for this. You can use Vlookup()
顺便说一句，您是否正在尝试找到Col Iin的相应值Col J？如果是，那么您不需要为此使用 UDF。您可以使用Vlookup()
Use this formula =VLOOKUP(I3,I3:J38,2,0)instead of =FileCheck(I3,I3:J38)
用这个公式=VLOOKUP(I3,I3:J38,2,0)代替=FileCheck(I3,I3:J38)
回答by glh
I think your first input type should be a string not a range:
我认为您的第一个输入类型应该是字符串而不是范围：
V as string
Additional you need to use exit functionwhen you want to end the function however I'm not sure if you want to end the function, end the if statement or the for loops?
exit function当您想结束函数时还需要使用其他方法，但是我不确定您是要结束函数、结束 if 语句还是 for 循环？
