我有以下内容:
Private Sub cmdColumnLetter_Click()
Dim colRange As Range
Set colRange = Excel.Application.InputBox( _
Prompt:="Please Select Any Cell In ""Splitter"" Column", _
Title:="Column", _
Default:=fTableRange.Columns(1).Column, Type:=8)
fColNumber = colRange.Column
TxBoxColumnNum = fColNumber
End Sub
如果用户点击取消,则Set colRange =
失败,因为它希望将对象分配给此变量。我是否必须使用"出错时"结构来防御此错误,或者是否有更优雅的方法?
IMO 分配对象和值的不同语法是 VBA 的一个缺点。
因此,作为返回对象或值InputBox
函数会导致错误。
但是,传递语法的函数参数是相同的。
探索这一点,我的目的和替代解决方案,确实不是比错误处理解决方案更好:
Function SetObject(v, ByVal ExpectedTypeName As String) As Object
If TypeName(v) = ExpectedTypeName Then Set SetObject = v
End Function
Private Sub cmdColumnLetter_Click()
Dim colRange As Range
Set colRange = SetObject(Excel.Application.InputBox("input", Type:=8), "Range")
If Not colRange Is Nothing Then
Debug.Print "..."
End If
End Sub
SetObject
接受对象或值,但如果类型名与预期的对象匹配,则始终重新调整给定对象,如果不匹配,则Nothing
重新调整对象。
是的,你必须 OERN
Sub Test()
Dim colRange As Range
Dim fColNumber As Long, TxBoxColumnNum As Long
On Error Resume Next
Set colRange = Excel.Application.InputBox( _
Prompt:="Please Select Any Cell In ""Splitter"" Column", _
Title:="Column", Default:=fTableRange.Columns(1).Column, Type:=8)
On Error GoTo 0
If colRange Is Nothing Then Exit Sub
fColNumber = colRange.Column
TxBoxColumnNum = fColNumber
End Sub