Please look at my code I posted after this. It isolates the problem more succinctly.

I am trying to trap an argument error, i.e. if Excel user puts a boolean in for intTestVar which should be an integer. VBA interprets a 0 or -1 entry ( which is legal) as a boolean. If user enters a boolean (illegal) it interprets it as an integer, i.e -1 or 0. I understand that if you cast a boolean as an integer it will be -1 or 0 but I am not casting it.

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...??

CodePudding user response：

Consider this:

This is your same code, but I added some Debug.Print statements and a test routine:

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

Output of test():

------------------------------------------------------------------
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 

Thoughts

So... you're getting the repeating error routine because you don't actually have an error when you hit that resume next. You got there in the first place just because you used GoTo ErrorH to force it there, but no error.

As for the boolean trapping... I'm not seeing an issue there, because it seems that True and False return -1 and 0 respectively, and they are recognized as variable type 2 (integer). I guess I don't quite see your problem and perhaps you can clarify it if this doesn't answer it.

CodePudding user response：

To clarify..

Thanks for the answer, I see the "no error" problem now. As for the declaration problem..

The problem is that VBA is not treating the variable consistently as an integer or for that matter consistently as a boolean despite being declared as an integer. Entering 6 returns 60 as it should, but entering -1 should return -10 but instead entering -1 gets trapped by the second test expression asking whether -1 equals True or False. Since it is an integer it can not be equal to True or False.

The reason I am doing this is to trap the error of putting a boolean in the argument position for the integer intTestvar. Where I am actually doing this is in a more complicated function with 6 arguments, two of which are boolean. It would be easy for the user to accidentally put a boolean in this position and the routine, without a trap, would perform the function in an unexpected and maybe unnoticed fashion.

It is my understanding that in VBA boolean and integers are two different abstractions and can not be used interchangeably unless you purposely cast the boolean as an integer, ala Cbool for example. If I had declared it variant instead of integer then I might see where VBA would treat it as boolean when I used it in a boolean expression, but I declared it type Integer and VBA says it is type 2 (which is integer).

CodePudding user response：

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