27

In VBA, will it make any difference if I compare a string, or similar, against vbNullString instead of against an empty string; ""? If so what differences are there between the two?

eirikdaude
  • 3,106
  • 6
  • 25
  • 50
  • See Rob Bovey's comment at http://dailydoseofexcel.com/archives/2008/06/26/identify-empty-cells-in-vba/ – brettdj Sep 07 '15 at 13:05

3 Answers3

29

vbNullString and "" are different. Here is a webpage exerpt that describes the memory usage differences.

"This is the usual way to clear a string variable.

Text$ = ""

What a waste! First of all, the string "" takes 6 bytes of RAM each time you use it. Consider the alternative:

Text$ = vbNullString

So what is this? vbNullString is a special VB constant that denotes a null string. The "" literal is an empty string. There's an important difference. An empty string is a real string. A null string is not. It is just a zero. If you know the C language, vbNullString is the equivalent of NULL.

For most purposes, vbNullString is equivalent to "" in VB. The only practical difference is that vbNullString is faster to assign and process and it takes less memory.

If you call some non-VB API or component, test the calls with vbNullString before distributing your application. The function you're calling might not check for a NULL string, in which case it might crash. Non-VB functions should check for NULL before processing a string parameter. With bad luck, the particular function you're calling does not do that. In this case, use "". Usually APIs do support vbNullString and they can even perform better with it!"

The rest of the article has additional information on optimizing strings that may be insightful as well.

Full webpage: http://www.aivosto.com/vbtips/stringopt.html

GoeschWho
  • 306
  • 3
  • 3
13

This comparison states that assigning "" to a variable uses 6 bytes of memory whereas using vbNullString will not use any memory.

Personally, I prefer to evaluate the length of a string. If length is 0, then we also arrive at the conclusion that the string is a vbNullString or "". This method is accepted as being the quickest method of checking for a vbNullString.

If Len(string) = 0 Then

You can read a Len vs vbNullString vs "" comparison here.

luke_t
  • 2,935
  • 4
  • 22
  • 38
  • Looks good, unless someone else chimes in with more information, I'll mark this as the solution. However, that last article you linked to indicates there is a difference, though the author does not know what it is. It could of course be something as simple as the memory it takes up when assigned to a variable, as you indicate earlier in your answer. – eirikdaude Sep 07 '15 at 11:14
  • 1
    There is a difference, `""` is an existing string of zero length, `vbNullString` is a null pointer of type `String` (does not point to a string). For the purpose of comparing VB [hides the difference](http://stackoverflow.com/questions/37035754/what-does-vb6-initialize-a-static-integer-to/37035879#comment61678413_37035879), but sometimes you want to [unhide it](http://stackoverflow.com/a/20909528/11683). – GSerg Jun 13 '16 at 18:50
  • The 2nd link is dead: [here is an archive](https://web.archive.org/web/20140712133312/http://www.jpsoftwaretech.com/len-vs-blank-strings-vs-vbnullstring/) – sam-6174 Dec 17 '18 at 17:28
1
LenB(Variable) = 0

is SIGNIFICANTLY faster than

Variable = "" or Variable = vbNullString

I went through a project and replaced all instances of

If Variable = "" then

with

If LenB(Variable) = 0 then

and all instances of

Variable = ""

with

Variable = vbNullString

and saw significant performance increases, especially when used in big loops

I know this is an old thread but I found it and it helped me out a lot. Hopefully it will be helpful to someone else as well

Zac
  • 19
  • 1
  • It is a well know fact that the comparison of `Len(s)` with zero is the fastest way of checking if the string has something in it (which is because the length of the string is stored before its beginning). It is also mentioned in an [existing answer](https://stackoverflow.com/a/32436078/11683) posted 8 years ago. So why repost the same 8 years later? – GSerg Apr 17 '23 at 22:16