What is the equivalent of "!=" in Excel VBA?

Question

The problem is that != does not work as a function in excel vba.

I want to be able to use

If strTest != "" Then instead of If strTest = "" Then

Is there another approach to do this besides !=?

My function to mimic != is

Sub test()

Dim intTest As Integer
Dim strTest As String

intTest = 5

strTest = CStr(intTest) ' convert

Range("A" + strTest) = "5"



    For i = 1 To 10
        Cells(i, 1) = i

        If strTest = "" Then
            Cells(i, 1) = i
        End If

    Next i


End Sub

What is your question? Why `!=` doesn't work in vba, or what is the inequality operator in vba? — GSerg, Jul 21 '12 at 20:56

score 162 · Accepted Answer · answered Jul 21 '12 at 20:57

162

Because the inequality operator in VBA is <>

If strTest <> "" Then
    .....

the operator != is used in C#, C++.

answered Jul 21 '12 at 20:57

Steve

213,761
22
232
286

score 29 · Answer 2 · answered Jul 21 '12 at 21:04

29

In VBA, the != operator is the Not operator, like this:

If Not strTest = "" Then ...

answered Jul 21 '12 at 21:04

frenchie

51,731
109
304
510

6

This is incorrect. `Not` is the logical inversion operator, which corresponds to `!` in C-style languages. – Zev Spitz Jan 19 '17 at 21:55

score 7 · Answer 3 · edited Feb 09 '15 at 13:42

7

Just a note. If you want to compare a string with "" ,in your case, use

If LEN(str) > 0 Then

or even just

If LEN(str) Then

instead.

edited Feb 09 '15 at 13:42

brettdj

54,857
16
114
177

answered Dec 12 '14 at 08:18

Đức Thanh Nguyễn

9,127
3
21
27

6

I know to people somewhat new to VBA, this answer may seem weird, but this, believe it or not, is FAR more efficient than checking against `<> ""` – LimaNightHawk Jan 29 '15 at 16:52
8

Fun fact to back this answer: Visual Basic and Pascal languages store strings with their length in the beginning and the content itself right after that. C-based and Java languages, on the other hand, do not store the length and have the '\0' (null) terminator to signal that the string ended. Because of that, getting the length in VBA is fast -- it's just reading an integer from memory -- and is slow in Java -- you need to iterate through the string. – Paulo Avelar Feb 08 '16 at 19:40
1

@LimaNightHawk *far* more efficient? Can you elaborate on that please? I guess that most modern compilers would catch the pattern `<> ""` and produce the same p-code as `Len(str)`. – Roland Oct 04 '17 at 15:28
@Roland This has to do with how VBA stores strings in memory. I'll let you Google for a fuller explanation, but in short, part of how strings are stored is that the first bytes store the length of the string, then the following bytes store the characters: `[3][C][A][T]`. An "empty" string has a `[0]` for the first bytes and checking the `Len` allows the code to just check and compare Integers. Additionally, when you do a `= ""` that second `""` has to be allocated as its own string first in memory, then compared to your target string. – LimaNightHawk Oct 05 '17 at 16:41
1

@Roland: That would be an easy optimization, but, apparently, the VBA compiler does not do it. [Someone did a benchmark](https://roymacleanvba.wordpress.com/2010/01/14/performance-strings/) with the result that `Len(str) > 0` is about twice as fast as `str <> ""` for 10 million iterations. That said, we are talking about *extreme* micro-optimization here (0.36 vs 0.72 seconds for 10 million iterations), so I'll definitely stick with the more readable `str <> ""`. – Heinzi Jul 11 '19 at 15:49
@Heinzi ,I agree with you. – Waleed Apr 30 '22 at 07:48

score 2 · Answer 4 · edited Oct 30 '12 at 12:23

2

Try to use <> instead of !=.

edited Oct 30 '12 at 12:23

brimborium

9,362
9
48
76

answered Oct 30 '12 at 12:04

Steve

39
4

What is the equivalent of "!=" in Excel VBA?

4 Answers4