检查文本框是否存在 vba（使用名称）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44257145/
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
check if textbox exists vba (using name)
提问by George
I am using Ms-Access and I created a userform which has a number of Textboxes on it. The boxes are named: Box1, Box2, Box3 ...
我正在使用 Ms-Access 并创建了一个用户表单，其中包含许多文本框。这些盒子被命名为：Box1、Box2、Box3 ...
I need to loop through all boxes, but I don't know which is the last one. To avoid looping through all userform controls I thought of trying the following:
我需要遍历所有框，但我不知道哪个是最后一个。为了避免遍历所有用户窗体控件，我想尝试以下操作：
For i =1 To 20
if Me.Conrtols("Box" & i).value = MyCondition Then
'do stuff
Next i
This errors at Box6, which is the first box not found. Is there a way to capture this error and exit the loop when it happens.
这个错误出现在 Box6，这是第一个没有找到的盒子。有没有办法捕获这个错误并在它发生时退出循环。
I know I could use On Errorbut I 'd rather capture this specific instance with code instead.
我知道我可以使用，On Error但我宁愿用代码来捕获这个特定的实例。
Thanks, George
谢谢，乔治
回答by CommonSense
A Controlscollection is a simplified collection of controls (obviously) and share a same order as a placement order of controls.
甲Controls集合是控件（明显）的简化集合并共享相同的顺序的控制一个放置顺序。
First of all, even a creatable collection object lacks methods such as Existsor Contains, hence you need a function with error handling to checking/pulling widget from a collection.
首先，即使是可创建的集合对象也缺少诸如Existsor 之类的方法Contains，因此您需要一个具有错误处理功能的函数来检查/拉取集合中的小部件。
Public Function ExistsWidget(ByVal Name As String) As Boolean
On Error Resume Next
ExistsWidget = Not Me.Controls(Name) Is Nothing
On Error GoTo 0
End Function
If you really doesnt like "ask forgiveness not permission"option you can pull entire ordered collection of your textboxes (and/or check existance by name in another loop with similar logic).
如果您真的不喜欢“请求宽恕而不是许可”选项，您可以拉取文本框的整个有序集合（和/或在具有类似逻辑的另一个循环中按名称检查存在）。
Public Function PullBoxes() As Collection
Dim Control As MSForms.Control
Set PullBoxes = New Collection
For Each Control In Me.Controls
If TypeOf Control Is MSForms.TextBox And _
Left(Control.Name, 3) = "Box" Then
Call PullBoxes.Add(Control)
End If
Next
End Function
Since names of widgets are unique - you can return a Dictionaryfrom that function with (Control.Name, Control) pairs inside and able to check existance of widget by name properly w/o an error suppression.
There'sa good guide to Dictionaryif it's a new information for you.
由于小部件的名称是唯一的 - 您可以Dictionary从该函数返回一个，其中包含 (Control.Name, Control) 对，并且能够通过名称正确检查小部件的存在，而无需错误抑制。
如果它对您来说是新信息，这里有一个很好的指南Dictionary。
Anyway, no matter what object you choose, if user (or code) is unable to create more of thoose textboxes - you can convert this Functionabove to a Static Property Getor just to a Property Getwith Staticcollection inside, so you iterate over all controls only once (e.g. on UserForm_Initializeevent)!
无论如何，无论您选择什么对象，如果用户（或代码）无法创建更多那些文本框 - 您可以将Function上面的转换为 aStatic Property Get或仅转换为内部的Property GetwithStatic集合，因此您只迭代所有控件一次（例如在UserForm_Initialize事件）！
Public Property Get Boxes() As Collection
Static PreservedBoxes As Collection
'There's no loop, but call to PullBoxes to reduce duplicate code in answer
If PreservedBoxes Is Nothing Then _
Set PreservedBoxes = PullBoxes
Set Boxes = PreservedBoxes
End Property
After all, the last created TextBoxwith name Box*will be:
毕竟，最后TextBox使用 name创建的Box*将是：
Public Function LastCreatedBox() As MSForms.TextBox
Dim Boxes As Collection
Set Boxes = PullBoxes
With Boxes
If .Count <> 0 Then _
Set LastCreatedBox = Boxes(.Count)
End With
End Function
I think that now things are clearer to you! Cheers!
我想现在事情对你来说更清楚了！干杯!
Note: All code are definitely a bunch of methods/properties of your form, hence all stuff should be placed inside of form module.
注意：所有代码肯定是表单的一堆方法/属性，因此所有东西都应该放在表单模块中。
回答by Vityata
Long story short - you cannot do what you want with VBA.
However, there is a good way to go around it - make a boolean formula, that checks whether the object exists, using the On Error. Thus, your code will not be spoiled with it.
长话短说 - 你不能用 VBA 做你想做的事。但是，有一个很好的方法来解决它 - 制作一个布尔公式，检查对象是否存在，使用On Error. 因此，您的代码不会被它破坏。
Function ControlExists(ControlName As String, FormCheck As Form) As Boolean
Dim strTest As String
On Error Resume Next
strTest = FormCheck(ControlName).Name
ControlExists = (Err.Number = 0)
End Function
Taken from here:http://www.tek-tips.com/viewthread.cfm?qid=1029435
取自这里：http: //www.tek-tips.com/viewthread.cfm?qid=1029435
To see the whole code working, check it like this:
要查看整个代码的工作情况，请按如下方式检查：
Option Explicit
Sub TestMe()
Dim i As Long
For i = 1 To 20
If fnBlnExists("Label" & i, UserForm1) Then
Debug.Print UserForm1.Controls(CStr("Label" & i)).Name & " EXISTS"
Else
Debug.Print "Does Not exist!"
End If
Next i
End Sub
Public Function fnBlnExists(ControlName As String, ByRef FormCheck As UserForm) As Boolean
Dim strTest As String
On Error Resume Next
strTest = FormCheck(ControlName).Name
fnBlnExists = (Err.Number = 0)
End Function
回答by Gary Evans
I would suggest testing the existence in another procedure per below: -
我建议在下面的另一个程序中测试是否存在：-
Private Sub Command1_Click()
Dim i As Long
i = 1
Do Until Not BoxExists(i)
If Me.Conrtols("Box" & i).Value = MyCondition Then
'Do stuff
End If
i = i + 1
Next
End Sub
Private Function BoxExists(ByVal LngID As Long) As Boolean
Dim Ctrl As Control
On Error GoTo ErrorHandle
Set Ctrl = Me.Controls("BoX" & LngID)
Set Ctrl = Nothing
BoxExists = True
Exit Function
ErrorHandle:
Err.Clear
End Function
In the above, BoxExistsonly returns true if the box does exists.
在上面，BoxExists只有当框确实存在时才返回真。
回答by Kostas K.
You have taken an incorrect approach here.
您在这里采取了不正确的方法。
If you want to limit the loop, you can loop only in the section your controls reside e.g. Detail. You can use the ControlTypeproperty to limit controls to TextBox.
如果你想限制循环，你只能在你的控件所在的部分循环，例如Detail。您可以使用该ControlType属性将控件限制为 TextBox。
Dim ctl As Control
For Each ctl In Me.Detail.Controls
If ctl.ControlType = acTextBox Then
If ctl.Value = MyCondition Then
'do stuff
End If
End If
Next ctl
I believe the loop will be faster than checking if the control name exists through a helper function and an On Error Resume Next.
我相信循环会比通过辅助函数和On Error Resume Next.
But this only a personal opinion.
但这只是个人意见。
