CODEWITHSUNDEEP
vbams-access
John S
John S

Reputation: 8331

Why is this VBA if statement not working?

I have this simple little piece of vba code that I would expect to return "Got to condition two" but instead it falls all the way to "three". Why?

Dim testValue as String
testValue = Null

If testValue = 8888 Then
    Debug.Print "Got to condition one"
ElseIf testValue = Null Then
    Debug.Print "Got to condition two"
Else
    Debug.Print "Got to condition three"
End If

Upvotes: 0

Views: 1164

Answers (2)

Benjamin Pollack
Benjamin Pollack

Reputation: 28410

There are two things going on here:

  1. First, Null, in VBA, represents database nulls, and as a result, isn't equal to anything—even itself. To check whether something is Null, you have to use the IsNull function.
  2. But since Null is for databases, it's probably not what you wanted. You probably instead want to set testValue to Nothing, which is the VBA "no value assigned" value. Nothing is also not a simple type, so even if you're trying to check for whether something is Nothing, you can't use =; instead, you should write ElseIf testValue Is Nothing

Upvotes: 4

David Zemens
David Zemens

Reputation: 53623

Try this. You can't put Null in a string variable, you should be getting an error on the testValue = Null assignment.

Sub Test()
Dim testValue As Variant
testValue = Null

If testValue = 8888 Then
    Debug.Print "Got to condition one"
ElseIf IsNull(testValue) Then
    Debug.Print "Got to condition two"
Else
    Debug.Print "Got to condition three"
End If
End Sub

Upvotes: 1

Related Questions