請查看我在此之后發布的代碼。它更簡潔地隔離了問題。
我試圖捕獲一個引數錯誤，即如果 Excel 用戶為 intTestVar 輸入一個布林值，它應該是一個整數。VBA 將 0 或 -1 條目（這是合法的）解釋為布林值。如果用戶輸入一個布林值（非法），它會將其解釋為一個整數，即 -1 或 0。我知道如果您將布林值轉換為整數，它將是 -1 或 0，但我沒有轉換它。
Public Function TestA(filler As Integer, Optional intTestVar As Integer = 0) As Integer
Dim intTrap As Integer
intTrap = 0
On Error GoTo ErrorH
Debug.Print "how VBA sees the argument variable " & intTestVar & " type = " & VarType(intTestVar)
If VarType(intTestVar) = vbBoolean Then
intTrap = 1
GoTo ErrorH
End If
'in case that doesn't work
If intTestVar = False Or intTestVar = True Then
intTrap = 2
GoTo ErrorH
End If
'in case that doesn't work
If IsNumeric(intTestVar) = False Then
intTrap = 3
GoTo ErrorH
End If
TestA = 10 * intTestVar
Exit Function
ErrorH:
Debug.Print intTrap & " Trapped the Bugger "
Resume Next
End Function
____________________________________________________________
This is how it responded to different values for intTestVar - filler is just a dummy = 5
Entering 6 return 60
how VBA sees the argument variable 6 type = 2
Entering -1 returned 0
how VBA sees the argument variable -1 type = 2
2 Trapped the Bugger
2 Trapped the Bugger
Entering True Returned 0
how VBA sees the argument variable -1 type = 2
2 Trapped the Bugger
2 Trapped the Bugger
Entering False Returned 0
how VBA sees the argument variable 0 type = 2
2 Trapped the Bugger
2 Trapped the Bugger
As a side, I don't know why the error handler repeats the error message, resume next should clear the error...??
uj5u.com熱心網友回復：
考慮一下：
這是您的相同代碼，但我添加了一些Debug.Print陳述句和一個測驗例程：
Public Function TestA(filler As Integer, Optional intTestVar As Integer = 0) As Integer
Debug.Print "------------------------------------------------------------------"
Dim intTrap As Integer
intTrap = 0
On Error GoTo ErrorH
Debug.Print "how VBA sees the argument variable " & intTestVar & " type = " & VarType(intTestVar)
If VarType(intTestVar) = vbBoolean Then
intTrap = 1
GoTo ErrorH
End If
'in case that doesn't work
If intTestVar = False Or intTestVar = True Then
intTrap = 2
GoTo ErrorH
End If
'in case that doesn't work
If IsNumeric(intTestVar) = False Then
intTrap = 3
GoTo ErrorH
End If
TestA = 10 * intTestVar
Exit Function
ErrorH:
Debug.Print intTrap & " Trapped the Bugger "
Debug.Print Err.Number & vbTab & Err.Description
Resume Next
End Function
'-----------------------------------------
Sub test()
Debug.Print TestA(5, -1)
Debug.Print TestA(5, 5)
Debug.Print TestA(5, True)
Debug.Print TestA(5, False)
End Sub
測驗（）的輸出：
------------------------------------------------------------------
how VBA sees the argument variable -1 type = 2
2 Trapped the Bugger
0
2 Trapped the Bugger
20 Resume without error
0
------------------------------------------------------------------
how VBA sees the argument variable 5 type = 2
50
------------------------------------------------------------------
how VBA sees the argument variable -1 type = 2
2 Trapped the Bugger
0
2 Trapped the Bugger
20 Resume without error
0
------------------------------------------------------------------
how VBA sees the argument variable 0 type = 2
2 Trapped the Bugger
0
2 Trapped the Bugger
20 Resume without error
0
想法
所以......你得到了重復錯誤例程，因為當你點擊它時你實際上并沒有錯誤resume next。你一開始只是因為你曾經GoTo ErrorH強迫它在那里，但沒有錯誤。
至于布爾誘捕......我沒有看到一個問題，在那里，因為它似乎True并False分別回傳-1，0，和他們是公認的變數型別2（整數）。我想我不太明白你的問題，如果這不能回答它，也許你可以澄清它。
uj5u.com熱心網友回復：
澄清..
感謝您的回答，我現在看到“沒有錯誤”的問題。至于申報問題..
問題在于，盡管被宣告為整數，VBA 并未始終將變數視為整數或始終將其視為布林值。輸入 6 應回傳 60，但輸入 -1 應回傳 -10，但輸入 -1 會被第二個測驗運算式捕獲，詢問 -1 等于 True 還是 False。由于它是一個整數，它不能等于 True 或 False。
我這樣做的原因是為了捕獲將布林值放在整數 intTestvar 的引數位置的錯誤。我實際上是在一個更復雜的函式中執行此操作，其中有 6 個引數，其中兩個是布林值。用戶很容易不小心在這個位置放置一個布林值，而沒有陷阱的例程將以意想不到的方式執行該功能。
據我了解，在 VBA 中，布林值和整數是兩種不同的抽象，除非您故意將布林值轉換為整數，否則不能互換使用，例如，ala Cbool。如果我宣告它是變體而不是整數，那么當我在布爾運算式中使用它時，我可能會看到 VBA 將它視為布林值，但我宣告它為整數型別，而 VBA 說它是型別 2（它是整數）。
uj5u.com熱心網友回復：
Option Explicit
Public Function TestA(filler As Integer, Optional intTestVar As Integer = 0) As Integer
'This test function demonstrates a VBA bug. It is interpreting the declared integers 0 and -1 as boolean
'in some expressions but not all expressions involving a boolean related test.
Dim intTrap As Integer
intTrap = 0
TestA = 999
Debug.Print "This is how VBA sees the integer argument: inTestVar = " & intTestVar & " type = " & VarType(intTestVar)
On Error GoTo ErrorH
If VarType(intTestVar) = vbBoolean Then
intTrap = 1
GoTo ErrorH
End If
'this is the problem statement. The If test should be False but VBA interprets as True
If intTestVar = False Or intTestVar = True Then
intTrap = 2
GoTo ErrorH
End If
TestA = 10 * intTestVar
Debug.Print "TestA returns: " & TestA
Debug.Print " "
Debug.Print " "
Exit Function
ErrorH:
Debug.Print "Trap " & intTrap & " Trapped Error: inTestvar treated as boolean " & " TESTA Returns " & TestA
Debug.Print " "
Debug.Print " "
End Function
*I entered 8 and then -1 for the argument intTestVar with these results.*
This is how VBA sees the integer argument: inTestVar = 8 type = 2
TestA returns: 80
This is how VBA sees the integer argument: inTestVar = -1 type = 2
Trap 2 Trapped Error: inTestvar treated as boolean TESTA Returns 999
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/407582.html
標籤：