vbNullString

Digitborn.com · Dec 29, 2008

Hello,

What's the difference when you use vbNullString or "". For example:

TextBox1.Text = " "
TextBox1.Text = "vbNullString"

Are you saving memory or anything else?

sijpie · Feb 4, 2016

RoryA said:
As a matter of interest, what was your test code? Unless you know that your variable was never assigned a value, or was cleared explicitly using vbNullString, I'd be surprised if you see greater efficiency using vbNullString since the address will not be the same - i.e. StrPtr won't return 0 for your variable.

As best I recall the last time I looked at it, you're talking hundredths of a second to do 1m comparisons whichever way you choose, so I'll probably save myself some typing.

I also don't believe that explicitly setting objects to nothing will resolve any memory leaks you might experience - that doesn't actually trigger garbage collection directly.

My test code was two loops doing a 100,000 times looping through a small array (200 elements) with empties, numbers and strings and then just to clear the variables from the processor addresses do some small calculation. One loop using Len() the other vbNullstring. I would then swap the loops' position and rerun, as for some reason that sometimes makes a difference.
Difference is 5,300 ms for the Len() and 3,900 ms for the vbNullstring.

I am doing monte-carlo simulations with similar quantities of looping and array(row) sizes. So 1.5 seconds difference on a 30 second run helps.

below my code plus some results.
Alan you can see in there that I did test vbEmpty vs vbNullstring for both numbers and strings, and there is a difference there! for strings vbNullstring is faster, for numbers vbEmpty.

Enjoy.

Option Explicit Private Type SYSTEMTIME wYear As Integer wMonth As Integer wDayOfWeek As Integer wDay As Integer wHour As Integer wMinute As Integer wSecond As Integer wMilliseconds As Integer End Type Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME) Function MyTimer() As Long 'In the Function where you need find diff Dim sSysTime As SYSTEMTIME Dim lStartSec As Long, iCurrentSec As Long GetLocalTime sSysTime lStartSec = CLng(sSysTime.wSecond) * 1000 + sSysTime.wMilliseconds MyTimer = lStartSec End Function Private Sub testt() Dim l1 As Long, l2 As Long, li As Long, l3 As Long, r1, C1, lS, lT, lU, lV Dim l4 As Long, l5 As Long, dAr() As Double, d1 As Double, d2 As Double Dim vP As Variant, vP2 As Variant, v1, v2, v1b, v2b, v3, v4, vAr Const C = 100000 vP = Range("a1:a200") li = UBound(vP, 1) l1 = MyTimer l1 = MyTimer For lV = 1 To C For r1 = 1 To li If Not Len(vP(r1, 1)) Then C1 = C1 + 1 End If Next r1 Next lV l2 = MyTimer For lV = 1 To C For r1 = 1 To li If vP(r1, 1) = vbNullString Then C1 = C1 + 1 End If Next r1 Next lV l3 = MyTimer Debug.Print "1: " & l2 - l1 & vbCrLf & "2: " & l3 - l2 '& ", " & lT '& vbCrLf & "3: " & l4 - l3 & ", " & lU & vbCrLf & "4: " & l5 - l4 & ", " & lV End Sub '============================================================= ' Tested: ' dealing with strings: ' strcomp(sX,sY) vs sX = sY - strcomp 50% of = ' If Len(sX) then vs If sX = vbNullstring - vbNullstring 70% of Len() ' if x <> vbEmpty vs if sx <> vbnullstring - vbNullstring 70% of vbEmpty ' if strcomp(x,vbnullstring) vs sx = vbNullstring - =vbNullstring 90% of srtrcmp ' dealing with numbers: ' x (<)> 0 vs x<> vbnullstring - > 0 30%-50% ' x > 0 vs x - x 30% ' x <> vbEmpty vs x <> vbNullstring - vbEmpty 50% ' x <> vbEmpty vs x <> 0 - ~ ' x <> vbEmpty vs x - x 30% ' variant vs long vs double - variant ~ long, variant 30% faster double ' j & i loops ' v(j,1)=v(j,1)+ x(i) vs a=a+x(i) inside i loop with v(j,1)=a: a=0 inside j loop ' - 2nd construction 70% faster ' wsRE.Cells.Find("*", Cells(1, 1) vs lRCount = Application.WorksheetFunction.Max(Cells(Rows.Count, lCOLITEM).End(xlUp).Row, _ ' Cells(Rows.Count, lCOLOPS).End(xlUp).Row, Cells(Rows.Count, lCOLRISK).End(xlUp).Row) ' - rows.count end(xlup) much much faster ' ' if elseif, elseif end if vs Select Case - If select case 20% faster

RoryA · Feb 4, 2016

OK, so it seems to make quite a difference if you use Len on a Variant rather than a String. (IsEmpty is definitely a bit faster).

DocAElstein · Feb 4, 2016

Hi sijpie,
Thanks very much for coming back, and for giving some results including stuff on the ___ Empty.
Much appreciated.
I have not had time to do extensive comparisons or to understand exactly what you are doing and i could not quite follow all your results, (- and I have no ideas about monte-carlo simulations for example )

_ . But i think i get the general idea, - you are “capturing” a range with a “one liner” ( so getting Variant types ) to an Array, then doing some tests on speed of a comparison for looking to see if each element has a “ Not there. Zero length string, 0 “ etc... So that is along the lines of what I am doing. I mean in particular the “one liner” capture means you have Variant Elements, like wot ( what ) I do

_. I guess doing extensive test would take ages as you would need to consider and vary what types are in the captured range. As i did a quick check on different ranges with different Types then i saw changes in a few runs of your code, but then i got different results from one run to the other anyway so it is difficult to always see changes. And as you mention swapping around the loops may effect things. ( Also in other speed tests i ever did i often found it was wise to repeat whatever i did first. That often seemed to give more consistent comparisons, but i guess for 100000 that can be neglected )
_. I will come back if I Have time to look a bit better, but the following is just intended as a quick bit of feedback. ( I think the variations one can try are probably endless........

_. I did some experiments:
I include my code version of your Codes which i used.....

For my experiments I have 6 Loops allowing for comparisons of
If Not Len(vP(r1, 1))
If vP(r1, 1) = vbNullString
If vP(r1, 1) =""
If vP(r1, 1) = Empty
If IsEmpty(vP(r1, 1))
If vP(r1, 1) = vbEmpty

Manual changes are made to allow for 4 data type ranges,
Mixed ( Range("a1:a200") )
All Empty ( Range("b1:b200") )
All Numbers ( Range("c1:c200") )
All Strings ( Range("d1:d200") )

_Row\^Col	C	D
1	1	String
2	2	String
3	3	String
4	4	String
5	5	String
6	6	String
7	7	String
8	8	String
9	9	String
10	10	String

In addition 2 runs are done for each Range for a difference in the Array Declaration
Dim vP() As Variant
Or
Dim vP As Variant
To make all those changes line 30 and line 90 must be changed manually appropriately

So in total there are
6 x ( 4 x 2 ) = 48 results given below:
( I gave up timing a version not using Arrays as it took To F__king Long )

Results:
Mixed Cells
Dim vP As Variant

If Not Len(vP(r1, 1)):-----11992
If vP(r1, 1) = vbNullString : 7195
If vP(r1, 1) ="" :------------6820
If vP(r1, 1) = Empty:------6458
If IsEmpty(vP(r1, 1)):------4380
If vP(r1, 1) = vbEmpty:----11001
_..........................................

Mixed Cells
Dim vP() As Variant

If Not Len(vP(r1, 1)):-----7819
If vP(r1, 1) = vbNullString : 3448
If vP(r1, 1) ="" :---------- 3174
If vP(r1, 1) = Empty:-----2898
If IsEmpty(vP(r1, 1)):-----3158
If vP(r1, 1) = vbEmpty:--7001
_.........................................................................

Mixed cells
Use Cells instead of vP ( or vP() )
.. TFL ( Gave up waiting )
_........................................................................

Empty Cells
Dim vP As Variant

If Not Len(vP(r1, 1)):----11786
If vP(r1, 1) = vbNullString : 5218
If vP(r1, 1) ="" :-----------5181
If vP(r1, 1) = Empty:-----4961
If IsEmpty(vP(r1, 1)):-----5017
If vP(r1, 1) = vbEmpty:--5066
_..........................................

Empty Cells
Dim vP() As Variant

If Not Len(vP(r1, 1)):-------9350
If vP(r1, 1) = vbNullString : 3225
If vP(r1, 1) ="" :----------- 3155
If vP(r1, 1) = Empty:-----2881
If IsEmpty(vP(r1, 1)):----3251
If vP(r1, 1) = vbEmpty:-----3021
_............................................................................

Numbers in all cells
Dim vP As Variant

If Not Len(vP(r1, 1)):-----21903
If vP(r1, 1) = vbNullString : 21283
If vP(r1, 1) ="" :---------20423
If vP(r1, 1) = Empty:----4516
If IsEmpty(vP(r1, 1)):-----4096
If vP(r1, 1) = vbEmpty:--5692
_.................................................

Numbers in all cells
Dim vP() As Variant

If Not Len(vP(r1, 1)):----19005
If vP(r1, 1) = vbNullString : 20179
If vP(r1, 1) ="" :----------18387
If vP(r1, 1) = Empty:------2321
If IsEmpty(vP(r1, 1)):------2421
If vP(r1, 1) = vbEmpty:---2398
_..................................................................................

Strings in all cells
Dim vP As Variant

If Not Len(vP(r1, 1)):----9572
If vP(r1, 1) = vbNullString : 9286
If vP(r1, 1) ="" :----------8795
If vP(r1, 1) = Empty:-----7751
If IsEmpty(vP(r1, 1)):-----3764
If vP(r1, 1) = vbEmpty:--24356
_...............................................
Strings in all cells
Dim vP() As Variant

If Not Len(vP(r1, 1)):---3549
If vP(r1, 1) = vbNullString : 3789
If vP(r1, 1) ="" :---------2678
If vP(r1, 1) = Empty:----2351
If IsEmpty(vP(r1, 1)):----2697
If vP(r1, 1) = vbEmpty:---15990

_................................................................................

Just some very quick, not extensive conclusions and I stress each time you run the numbers change a bit.....

_1 )
Dim vP()
instead of
Dim vP
Always seems better. I always thought it was, but I am not sure here why.

( _2) Always work with Arrays if you can , ( But I think we all know that ) )

_3) “___Empty” to use generally seems a good idea, but
_3a) The “extra” work for Vba in using
= Empty
Instead of
IsEmpty(___)
Seems here only to come up for Strings in one case**. In fact, for other types, = Empty is very slightly better?! That was a bit weird.... but I guess the String is the more difficult for VBA to convert to first before the comparison, maybe....anyway.. .....looks for now that IsEmpty(___) is still the one for me to go for.. ( or maybe not - .....if....**
**_3b)
_.....I use , as i do ,
Dim Array() ,
not
Dim Array
Then on average = Empty is still better..... Weird ... and another dimension of complexity.... For an Array, only if I “house it in an Variant”, does the comparison IsEmpty(___) on an Element within the Array within show up to be quicker..... maybe wot ( what ) it sees first is the key: A Variant or an Array.

_4) If Not Len(____ is just about the worst of the kinds of null/nullable data check ways, and vbNullString is slightly longer than =”” !!!! wierd !!!

_.............................

At the end of the day, to be sure, I guess you need to do endless tests and average results for your typical test data... But those were some interesting initial results.... Maybe I will come back to the Thread.... in a year or two....

Alan

P.s. @ sijpie I have a feeling that vbEmpty is a constant with value 0 – „….. not any kind of null/nullable data type…….“. Are You sure You meant vbEmpty, not = Empty. I think you would have been doing a comparison with a ( little ) Long Number, i.e. 0, if you used vbEmpty. That would explain your better results with the comparison with Longs on that one..

P. P.s. @ Rory If anyone asks, I shall say that you can definitely pass an Array ByVal and that = Empty is definitely a bit faster, but i shall tell them that I think am wrong... .. because Rory said so.... ..... .... ....PoxyByCopVyByORefiginalFiddlyPiddlyFooPoo=Empty.... ...

Codes

Code:

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=darkgreen]'Stuff for sijpie  speed tests[/color]
[color=blue]Private[/color] [color=blue]Type[/color] SYSTEMTIME
        wYear [color=blue]As[/color] [color=blue]Integer[/color]
        wMonth [color=blue]As[/color] [color=blue]Integer[/color]
        wDayOfWeek [color=blue]As[/color] [color=blue]Integer[/color]
        wDay [color=blue]As[/color] [color=blue]Integer[/color]
        wHour [color=blue]As[/color] [color=blue]Integer[/color]
        wMinute [color=blue]As[/color] [color=blue]Integer[/color]
        wSecond [color=blue]As[/color] [color=blue]Integer[/color]
        wMilliseconds [color=blue]As[/color] [color=blue]Integer[/color]
[color=blue]End[/color] [color=blue]Type[/color]

[color=blue]Private[/color] [color=blue]Declare[/color] [color=blue]Sub[/color] GetLocalTime [color=blue]Lib[/color] "kernel32" (lpSystemTime [color=blue]As[/color] SYSTEMTIME)

Code:

[color=darkgreen]'[/color]
[color=blue]Private[/color] [color=blue]Sub[/color] Alansijpie_teste_it() '    sijpie,     http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html
10  [color=blue]Dim[/color] l1 [color=blue]As[/color] [color=blue]Long[/color], l2&, l3&, li&, r1&, C1, lV& [color=darkgreen]', lS, lT, lU, lV     '   http://www.excelforum.com/excel-programming-vba-macros/1100751-excel-vba-to-copy-and-paste-from-horizontal-to-vertical-format-2.html#post4194972[/color]
20  [color=blue]Dim[/color] l4 [color=blue]As[/color] [color=blue]Long[/color], l5&, l6&, L7& [color=darkgreen]', dAr() As Double, d1 [color=blue]As[/color] Double, d2 As Double[/color]
30  [color=blue]Dim[/color] vP() As [color=blue]Variant[/color] [color=darkgreen]', vP2 [color=blue]As[/color] Variant, v1, v2, v1b, v2b, v3, v4, vAr[/color]
[color=darkgreen]'Dim vP [color=blue]As[/color] Variant[/color]
40
50  [color=blue]Const[/color] C = 100000
60
70
80  [color=darkgreen]'Various test ranges[/color]
90  [color=blue]Let[/color] vP() = Worksheets.Item(1).Range("a1:a200").Value [color=darkgreen]'Mixed type Range[/color]
[color=darkgreen]'Let vP = Worksheets.Item(1).Range("a1:a200").Value[/color]
[color=darkgreen]'vP() = Worksheets.Item(4).Range("b1:b200").Value ' All cells Empty[/color]
[color=darkgreen]'vP = Worksheets.Item(4).Range("b1:b200").Value[/color]
[color=darkgreen]'vP() = Worksheets.Item(4).Range("c1:c200").Value ' Numbers in all cells[/color]
[color=darkgreen]'vP = Worksheets.Item(4).Range("c1:c200").Value[/color]
[color=darkgreen]'vP() = Worksheets.Item(4).Range("d1:d200").Value ' Strings in all cells[/color]
[color=darkgreen]'vP = Worksheets.Item(4).Range("d1:d200").Value[/color]

li = [color=blue]UBound[/color](vP, 1)
l1 = MyTimer
[color=blue]For[/color] lV = 1 [color=blue]To[/color] C
    [color=blue]For[/color] r1 = 1 [color=blue]To[/color] li
        [color=blue]If[/color] [color=blue]Not[/color] Len(vP(r1, 1)) [color=blue]Then[/color]
        C1 = C1 + 1
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]Next[/color] r1
[color=blue]Next[/color] lV
l2 = MyTimer
[color=blue]For[/color] lV = 1 [color=blue]To[/color] C
    [color=blue]For[/color] r1 = 1 [color=blue]To[/color] li
        [color=blue]If[/color] vP(r1, 1) = vbNullString [color=blue]Then[/color]
        C1 = C1 + 1
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]Next[/color] r1
[color=blue]Next[/color] lV
l3 = MyTimer
    [color=blue]For[/color] lV = 1 [color=blue]To[/color] C
    [color=blue]For[/color] r1 = 1 [color=blue]To[/color] li
        [color=blue]If[/color] vP(r1, 1) = "" [color=blue]Then[/color]
        C1 = C1 + 1
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]Next[/color] r1
[color=blue]Next[/color] lV
l4 = MyTimer
[color=blue]For[/color] lV = 1 [color=blue]To[/color] C
    [color=blue]For[/color] r1 = 1 [color=blue]To[/color] li
        [color=blue]If[/color] vP(r1, 1) = [color=blue]Empty[/color] [color=blue]Then[/color]
        C1 = C1 + 1
        [color=blue]End[/color] [color=blue]If[/color]
    Next r1
Next lV
l5 = MyTimer
[color=blue]For[/color] lV = 1 [color=blue]To[/color] C
    [color=blue]For[/color] r1 = 1 [color=blue]To[/color] li
        [color=blue]If[/color] IsEmpty(vP(r1, 1)) [color=blue]Then[/color]
        C1 = C1 + 1
        [color=blue]End[/color] [color=blue]If[/color]
    Next r1
Next lV
l6 = MyTimer
[color=blue]For[/color] lV = 1 [color=blue]To[/color] C
    [color=blue]For[/color] r1 = 1 [color=blue]To[/color] li
        [color=blue]If[/color] vP(r1, 1) = vbEmpty [color=blue]Then[/color]
        C1 = C1 + 1
        [color=blue]End[/color] [color=blue]If[/color]
    Next r1
Next lV
L7 = MyTimer
Debug.Print "If Not Len(vP(r1, 1)):-------" & l2 - l1 & vbCrLf & "If vP(r1, 1) = vbNullString : " & l3 - l2 & vbCrLf & "If vP(r1, 1) ="""" :------------" & l4 - l3 & vbCrLf & "If vP(r1, 1) = Empty:-------" & l5 - l4 & vbCrLf & "If IsEmpty(vP(r1, 1)):------" & l6 - l5 & vbCrLf & "If vP(r1, 1) = vbEmpty:-----" & L7 - l6  [color=darkgreen]'                     & ", " & lT '& vbCrLf & "3: " & l4 - l3 & ", " & lU & vbCrLf & "4: " & l5 - l4 & ", " & lV[/color]

[color=blue]End[/color] [color=blue]Sub[/color]

[color=darkgreen]'[/color]
[color=blue]Function[/color] MyTimer() [color=blue]As[/color] [color=blue]Long[/color]

    [color=darkgreen]'In the Function where you need find diff[/color]
    [color=blue]Dim[/color] sSysTime [color=blue]As[/color] SYSTEMTIME
    [color=blue]Dim[/color] lStartSec As [color=blue]Long[/color], iCurrentSec As [color=blue]Long[/color]
    
    GetLocalTime sSysTime
    lStartSec = [color=blue]CLng[/color](sSysTime.wSecond) * 1000 + sSysTime.wMilliseconds
    MyTimer = lStartSec
[color=blue]End[/color] [color=blue]Function[/color]

Here is a messy file with all the stuff in, somewhere!!!
https://app.box.com/s/lalxqrzrzjexpb0vmgufxf085elbuern

sijpie · Feb 5, 2016

In the VBA editor press F2, then search for vbEmpty. go to it , you will see it is an enumeration of variable type. vbNull is also in this list. Now search for vbNullstring. When selecting this, you can see that just is a constant, and its value is shown. So indeed vbNullstirng = "", as Rory said. Different from what I thought and mentioned in an earlier post. So testing for "" or vbNullstring should be identical, although your tests show a small consistent difference.

RoryA · Feb 5, 2016

vbNullString is not the same as "" - I don't recall saying it was?

DocAElstein · Feb 5, 2016

@Rory
Hi Rory

RoryA said:
vbNullString is not the same as "" - I don't recall saying it was?

: . I do not think he/ we is referring to you, or even saying that vbNullString is the same as "". He, we, is just suggesting testing for it will / should / may be the same, I think

_......................................
@ sijpie
Hi sijpie

sijpie said:
In the VBA editor press F2, then search for vbEmpty. go to it , you will see it is an enumeration of variable type. vbNull is also in this list. Now search for vbNullstring. When selecting this, you can see that just is a constant, and its value is shown. .....

_... Thanks for that. I do not find the whole F2 Help thing very understandable. A lot of more experienced people tell me it has got progressively worse. ( vbNull is there in the list, but not found in the Binocular search bit above in the F2 Window.. but in the list, as you said which you get when you search for some of the other "Elements from "VbVarType")
So helpful getting clarity on these things. Thanks for that.

Still not quite with it exactly what these thing are...

Rich (BB code):

Sub DebugOutaEnumerationTiddleyKlonfoo()
'Hit Ctrl G in VB Editor after running this:-( then Copy and paste out )
Debug.Print "vbEmpty~~ "; Tab(15); vbEmpty
Debug.Print "vbNull~~~ "; Tab(15); vbNull
Debug.Print "vbError~~ "; Tab(15); vbError
Debug.Print "vbVariant "; Tab(15); vbVariant
Debug.Print "vbArray~~ "; Tab(15); vbArray; " ((&H2000) " & "Got From F2 search Binoculars for vbArray )"
Debug.Print "etc....""VbVarType"""
End Sub

vbEmpty~~ 0
vbNull~~~ 1
vbError~~ 10
vbVariant 12
vbArray~~ 8192 ( (&H2000) Got From F2 search Binoculars for vbArray )
...etc...."VbVarType"

: ....sounds like some vague number Listing identifier ( what you said: "... enumeration of variable ..." ) to use in an argument maybe instead of the actual word.
But anyways I think we get the point they are not too relevant to our ... " null/nullable data - Not having anything in it - assigning/ checking for etc..etc... ..." stuff

Thanks again
Alan

ZacS · Mar 17, 2023

As Colin Legg noted

VBA Code:

LenB(Variable) = 0

is SIGNIFICANTLY faster than

VBA Code:

Variable = "" or Variable = vbNullString

I went through a project and replaced all instances of

VBA Code:

If Variable = "" then

with

VBA Code:

If LenB(Variable) = 0 then

and all instances of

VBA Code:

Variable = ""

with

VBA Code:

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

vbNullString

Digitborn.com

Active Member

sijpie

Well-known Member

Excel Facts

RoryA

MrExcel MVP, Moderator

DocAElstein

Banned user

sijpie

Well-known Member

RoryA

MrExcel MVP, Moderator

DocAElstein

Banned user

ZacS

New Member

Similar threads

Forum statistics

Share this page

vbNullString

Active Member

Well-known Member

Excel Facts

MrExcel MVP, Moderator

Banned user

Well-known Member

MrExcel MVP, Moderator

Banned user

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock