Excel VBA - 在查找中使用左函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18340536/
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
Excel VBA - using left function with find
提问by jcv
I'm trying to use the worksheet function "left" in conjunction with "find" to determine a string variable in VBA. However, excel interprets the find function as a VBA function, which yields an error message when resolving.
我正在尝试将工作表函数“left”与“find”结合使用来确定 VBA 中的字符串变量。但是，excel将find函数解释为VBA函数，在解析时会产生错误信息。
Below my code:
在我的代码下面：
...
Else
MSa = MSa & Left(Range("D22"), Find(".", Range("D22")) - 1) & " " & Range("D25").Value & "."
Range("Q7").Value = MSa
End If
...
where MSa is a string. Can you please advise as to how I can best solve this problem?
其中 MSa 是一个字符串。你能告诉我如何最好地解决这个问题吗？
Many thanks in advance.
提前谢谢了。
采纳答案by tigeravatar
The InStr method is the equivalent of the worksheet function Find, to use it:
InStr 方法等效于工作表函数 Find，使用它：
MSa = MSa & Left(Range("D22"), InStr(Range("D22"), ".") - 1) & " " & Range("D25").Value & "."
回答by AndASM
The short answer is to use Application.WorksheetFunction.Find. However I wouldn't use that. Read below.
简短的回答是使用Application.WorksheetFunction.Find. 但是我不会使用它。参见下文。
So on Sheet1 I enter abc.definto cell A1.
In another cell I enter =LEFT(A1,FIND(".",A1)-1)and get the value abc.
所以在 Sheet1 上，我进入abc.def单元格 A1。
在另一个单元格中，我输入=LEFT(A1,FIND(".",A1)-1)并获取值abc。
To call the same functions in VBA I would write
要在 VBA 中调用相同的函数，我会写
variable = Left(Sheet1.[A1],Application.WorksheetFunction.Find(".",Sheet1.[A1])-1)
Calling Excel worksheet functions from VBA does have some disadvantages though (it's harder to debug and slower). It's better to use the native VBA function InStr which works much like the worksheet function Find. The parameters are in a different order, but the results are the same. So I would write
但是，从 VBA 调用 Excel 工作表函数确实有一些缺点（调试起来更困难且速度更慢）。最好使用本机 VBA 函数 InStr，它的工作方式与工作表函数 Find 非常相似。参数的顺序不同，但结果是一样的。所以我会写
variable = Left(Sheet1.[A1],InStr(Sheet1.[A1],".")-1)
回答by Declan_K
In order to use the worksheet function Findyou need to fully qualify it as follows;
为了使用工作表功能，Find您需要按如下方式完全限定它；
Application.WorksheetFunction.Find
Using Findon its on and VBA will assume that it is a VBA function. Same goes for match.
使用Findon its on 和 VBA 将假定它是一个 VBA 函数。也一样match。
