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") )
[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1

[/td][td][/td][td]

1