Why is this VBA if statement not working?

Question

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

I get an error on the second line, invalid use of `Null` (you can't assign this to a string. — David Zemens, Apr 16 '14 at 15:29
possible duplicate of [Textbox null problem](http://stackoverflow.com/questions/5662751/textbox-null-problem) — Dmitry Pavliv, Apr 16 '14 at 15:31

Benjamin Pollack · Accepted Answer · 2014-04-16T16:08:17.323

4

There are two things going on here:

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

edited Apr 16 '14 at 16:08

answered Apr 16 '14 at 15:32

Benjamin Pollack

27,594
16
81
105

1) what is the `None` here: `Set testValue = None`? It doesn't compiles. 2) you can't use `Set testValue` and `testValue Is Nothing` with `Dim testValue As String` – Dmitry Pavliv Apr 16 '14 at 15:41
@simoco I just axed the code snippet; I write Python at my day job and basically wrote a lovely hybrid of VBA and Python. Sorry about that. – Benjamin Pollack Apr 16 '14 at 16:09
no problem, but your second item still not work: you can't test `testValue Is Nothing` if `testValue` has `String` type.. – Dmitry Pavliv Apr 16 '14 at 16:11

score 1 · Answer 2 · answered Apr 16 '14 at 15:31

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

Why is this VBA if statement not working?

2 Answers2