excel - 寻找有关使用哪个循环的建议?
问题描述
我已经构建了一个工具,可以在宏运行时生成另一个电子表格。为了将该工作表成功上传到系统中,需要以特定方式对其进行格式化。为了防止人们无法正确使用该工具并弄乱新电子表格的格式,我进行了一系列检查。
每一个检查一个单元格是否有一个特定的字符串(“请设置!”),如果有,则弹出一个 msgbox 并停止宏。这个想法是它通过每个检查,直到它遇到一个失败,或者全部通过并运行代码以生成新工作表。目前它有点啰嗦,我正在寻找一个循环让它更整洁。
If Worksheets("Input").Range("F7").Value = "Please Set!" Then
MsgBox "Please complete all the fields!", vbCritical
Exit Sub
End If
If Worksheets("Input").Range("F9").Value = "Please Set!" Then
MsgBox "Please complete all the fields!", vbCritical
Exit Sub
End If
If Worksheets("Input").Range("F13").Value = "Please Set!" Then
MsgBox "Please complete all the fields!", vbCritical
Exit Sub
End If
If Worksheets("Input").Range("F17").Value = "Please Set!" Then
MsgBox "Please complete all the fields!", vbCritical
Exit Sub
End If
If Worksheets("Input").Range("F21").Value = "Please Set!" Then
MsgBox "Please complete all the fields!", vbCritical
Exit Sub
End If
If Worksheets("Input").Range("L9").Value = "Please Set!" Then
MsgBox "Please complete all the fields!", vbCritical
Exit Sub
End If
If Worksheets("Input").Range("L13").Value = "Please Set!" Then
MsgBox "Please complete all the fields!", vbCritical
Exit Sub
End If
If Worksheets("Input").Range("L17").Value = "Please Set!" Then
MsgBox "Please complete all the fields!", vbCritical
Exit Sub
End If
If Worksheets("Input").Range("L21").Value = "Please Set!" Then
MsgBox "Please complete all the fields!", vbCritical
Exit Sub
在此之后还有一个 else 和生成工作表的代码。
一切都按预期工作,但上面的代码非常庞大,我知道有更好的方法。我研究了不同的循环,但我是 VBA 的新手,所以我不确定哪一个最适合将它浓缩成。任何帮助都会很棒!:)
解决方案
只需将要检查的地址放入数组并循环遍历该数组以检查值。
Option Explicit
Public Sub ValidateFields()
Dim AddressesToCheck() As Variant
AddressesToCheck = Array("F7", "F9") 'add all addresses here
Dim Addr As Variant
For Each Addr In AddressesToCheck
If Worksheets("Input").Range(Addr).Value = "Please Set!" Then
MsgBox "Please complete all the fields!", vbCritical
Exit For
End If
Next Addr
End Sub
根据评论编辑:
为了使您的主要程序对ValidateFields
将其转换为 aFunction
而不是 a的结果做出反应Sub
。
Option Explicit
Public Function IsFieldsValid() As Boolean
Dim AddressesToCheck() As Variant
AddressesToCheck = Array("F7", "F9") 'add all addresses here
IsFieldsValid = True 'set all fields valid until an invalid field is found.
Dim Addr As Variant
For Each Addr In AddressesToCheck
If Worksheets("Input").Range(Addr).Value = "Please Set!" Then
MsgBox "Please complete all the fields!", vbCritical
IsFieldsValid = False
Exit For
End If
Next Addr
End Function
因此,您可以使用结果对您的主要过程做出反应,例如……</p>
Public Sub MainProcedure()
If IsFieldsValid Then '<< this will validate the fields and return `True` if they are all valid or `False` if one (or more) are not valid.
'do stuff if validation passed
Else
'do other stuff if one (or more) fields are not valid
Exit Sub 'for example abort main procedure
End If
End Sub