vbNullString

[Digitborn.com]

Hello,

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

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

Are you saving memory or anything else?

 

[RoryA]

No, it's not. If you put any formula in a cell, it will never be considered empty.

The charting gurus often ask for a BLANK() function (like there is in PowerPivot) that would be the equivalent of an empty cell so that charts would ignore the value instead of plotting 0, but no joy so far.

 

[TakenItEasy]

Thanks for the prompt reply.

So are you saying that the existence of a formula within a cell precludes it from being null or that there's currently no method to provide a null value?

So creating a function called Nothing to which you assigned a null string then having the excel formula use that function for it's output would result in some kind of error?

 

[RoryA]

So are you saying that the existence of a formula within a cell precludes it from being null or that there's currently no method to provide a null value?

Both really.

So creating a function called Nothing to which you assigned a null string then having the excel formula use that function for it's output would result in some kind of error?

Not an error, no. It would be no different from just using "" in a formula.

 

[TakenItEasy]

Not the answer I was expecting because my second guess would have been a 0 not "" based on this blog, which I found after posting here.

Not that it's important because neither case provides a solution.

Still you're answer saved me a lot of time in chasing this down and providing some clarity. Thanks so much for your help.

 

[DocAElstein]

____________.....But wot ( what ) about Empty.......

I was goggling and hit this Thread amongst others....

_ So I am playing around “capturing Arrays” that may contain a lot of elements many whose contents I do not want. ( But i may need to keep the large array in it’s original “captured” size and access the values I do want from it as an when I want to ..) .
_. I appreciate it is a small point, but I thought before I started I would just check the “best” way to “get rid” of stuff that I do not want.

Hence the interest in these alternatives....
= “”
= vbNullString
= Nothing
And wot ( what ) about empty..
= Empty.

_ ...So as I understand it, simplifying, ..... vbNullString and “”, can be used and tested for identically. There are some subtle speed and memory advantages of using vbNullString. All this is discussed here , and in the links given and elsewhere to be found by googling.
_ ...But what about Empty? In the code lines below I did a bit ( or Lot ) of experimenting... seems i can use and test for Empty identically to vbNullString and “” . ( This also goes for the case of putting / making a variable Empty and then checking to see if it is equal to “” – That returns True, which surprised me a bit – I thought a Variable showing ( containing ) a zero length string of “” would not be Empty, by anyone’s Declaring definition... or Wot ( What ) ever....
_ Nothing is a bit out of place here, that is setting ( re setting ) an Object as not yet set. ( Maybe "pseudo code".... vbNullString=CStr(Nothing)


Questions:
_ 1) I wonder if this is all splitting hairs: If my large Array allocates Variant Types to all it’s elements ( typically I have to do that ) , then whether I have vbNullString , “” , or Empty is splitting hairs. ..
_ or maybe not.... if some strings i do not want are very big, and if there are a lot of them.....( maybe one day one might be an Object.. that i guess would be sensible to “get rid of”... mostly i just have values, some of which could be a very long string... ) ?

_ 2) My conclusion would be when I look for certain search criteria to find the few values I do want, then i do it with a If to get at those Element values ( I want to get at them to change them a bit ) and include a Else where I let all the unwanted Element value = Empty.
_ I see no advantage of using “” of vbNullString. Or have i missed a subtle point?

Thanks
Alan

Demo Code:

Option Explicit
[color=blue]Sub[/color] NullNuttyThinks() [color=darkgreen]'http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html[/color]
10  [color=darkgreen]'Declare all variables so as to see what VBA may give 'em[/color]
20  [color=blue]Dim[/color] VBNullNuThink [color=blue]As[/color] [color=blue]Variant[/color], StringZeroLen [color=blue]As[/color] [color=blue]Variant[/color], LNuThink [color=blue]As[/color] [color=blue]Variant[/color], SNuThink [color=blue]As[/color] [color=blue]Variant[/color], CnvrtNuThinkToStr [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'Choose Variant and see what VBA "gives 'em"[/color]
30  [color=blue]Dim[/color] VBaZero [color=blue]As[/color] Variant: [color=blue]Let[/color] VBaZero = 0 [color=darkgreen]'Put here for completeness, but i think we know 0 is a number just like 1  2   etc. so ignoor this ..for now... unless we need it later...[/color]
40  [color=darkgreen]'[/color]
50  'Give 'em all a Null Nut Think
60  [color=blue]Let[/color] VBNullNuThink = vbNullString
70  [color=blue]Let[/color] StringZeroLen = "" [color=darkgreen]'I bet this must be a string as "parens" is wot ( what ) says "hey, here comes a string"[/color]
80  [color=darkgreen]'Let LNuThink = Nothing'Don't work, so leave Empty for now[/color]
90  [color=blue]Set[/color] SNuThink = [color=blue]Nothing[/color]
95  [color=darkgreen]'Let CnvrtNuThinkToStr = CStr(Nothing) 'Just an idea.. did not work[/color]
100 [color=darkgreen]'[/color]
110 'Highlight the variables, Hit Shift+F9 to get them displayed in a watch window...
120 [color=darkgreen]'Click in left Margin at Line Number 210 to put a stop point ...[/color]
130 [color=darkgreen]'Run Code and see what Vba Puts in, and what type within the Variant Variable allocated for each[/color]
140 [color=darkgreen]'Results:[/color]

150 'VBNullNuThink    "", Type String within Variant Variable
160 [color=darkgreen]'StringZeroLen     "", Type String within Variant Variable[/color]
170 [color=darkgreen]'LNuThink     Empty, Type [color=blue]Empty[/color][/color]
180 [color=darkgreen]'SNuThink     Nothing, Type Object within Variant Variable[/color]
190
200 [color=darkgreen]'Try the If   = ""     test[/color]
210     [color=blue]If[/color] VBNullNuThink = "" [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = """"" [color=darkgreen]' = ""[/color]
220     'If VBNullNuThink = Nothing Then MsgBox prompt:="VBNullNuThink = Nothing" ' Complile Error
230     [color=blue]If[/color] StringZeroLen = "" [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = """"" [color=darkgreen]' = ""[/color]
240     'If StringZeroLen = Nothing Then MsgBox prompt:="StringZeroLen is Nothing" ' Complile Error
250     [color=blue]If[/color] LNuThink = "" [color=blue]Then[/color] MsgBox prompt:="LNuThink = """"" [color=darkgreen]' = ""[/color]
260     'If LNuThink = Nothing Then MsgBox prompt:="LNuThink is Nothing" ' Complile Error
270     [color=darkgreen]'If SNuThink = "" Then MsgBox prompt:="LNuThink is """"" ' Is ""'RunTime Eror[/color]
280     [color=darkgreen]'If SNuThink = Nothing Then MsgBox prompt:="LNuThink is Nothing" 'Compile Erorr[/color]
290     [color=blue]If[/color] SNuThink [color=blue]Is[/color] [color=blue]Nothing[/color] [color=blue]Then[/color] MsgBox prompt:="LNuThink is Nothing" [color=darkgreen]' SNuThing IS Nothing![/color]
300     [color=blue]If[/color] VBNullNuThink = vbNullString [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = vbNullString" [color=darkgreen]' = ""[/color]
310     [color=blue]If[/color] StringZeroLen = vbNullString [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = vbNullString" ' = ""
320     [color=blue]If[/color] LNuThink = vbNullString [color=blue]Then[/color] MsgBox prompt:="LNuThink = vbNullString" [color=darkgreen]' = ""[/color]
330     [color=blue]If[/color] VBNullNuThink = Empty [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = [color=blue]Empty[/color]" ' = ""
340     [color=blue]If[/color] StringZeroLen = Empty [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = [color=blue]Empty[/color]" [color=darkgreen]' = ""[/color]
350     [color=blue]If[/color] LNuThink = Empty [color=blue]Then[/color] MsgBox prompt:="LNuThink = [color=blue]Empty[/color]" ' = ""
360
370 [color=darkgreen]'Try the If Len(  )=0   test[/color]
380     [color=blue]If[/color] Len(VBNullNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length VBNullNuThink = 0  " [color=darkgreen]' Works[/color]
390     [color=blue]If[/color] Len(StringZeroLen) = 0 [color=blue]Then[/color] MsgBox prompt:=" length StringZeroLen = 0  " ' Works
400     [color=blue]If[/color] Len(LNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length LNuThink = 0  " [color=darkgreen]' Works[/color]
410
420 'Click in left Margin at Line  560  to put a stop there.
430 [color=darkgreen]'Run next part of code and see what Vba Puts in and  type within the Variant Variable allocated for each[/color]
440 [color=blue]Let[/color] VBNullNuThink = [color=blue]Empty[/color] [color=darkgreen]'[/color]
450 [color=blue]Let[/color] StringZeroLen = [color=blue]Empty[/color] '
460 [color=blue]Let[/color] LNuThink = [color=blue]Empty[/color] [color=darkgreen]'[/color]
470 [color=blue]Let[/color] SNuThink = Empty '
480
490 [color=darkgreen]'Results:[/color]
500 'VBNullNuThink     Empty, Type Empty within the Variant Variable
510 [color=darkgreen]'StringZeroLen     Empty, Type [color=blue]Empty[/color] within the Variant Variable[/color]
520 [color=darkgreen]'LNuThink      Empty, Type Empty within the Variant Variable[/color]
530 [color=darkgreen]'SNuThink      Empty, Type [color=blue]Empty[/color] within the Variant Variable[/color]
540 [color=darkgreen]'[/color]
550 'Try the If   = ""     test
560     [color=blue]If[/color] VBNullNuThink = "" [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = """"" [color=darkgreen]' = ""[/color]
570     [color=blue]If[/color] StringZeroLen = "" [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = """"" ' = ""
580     If LNuThink = "" [color=blue]Then[/color] MsgBox prompt:="LNuThink = """"" [color=darkgreen]' = ""[/color]
590     '[color=blue]If[/color] SNuThink Is Nothing Then MsgBox prompt:="LNuThink is Nothing" ' Errors this time as no Object is there
600     [color=blue]If[/color] SNuThink = "" [color=blue]Then[/color] MsgBox prompt:="LNuThink = """"" [color=darkgreen]' = ""  THIS WORKS NOW[/color]
610 [color=darkgreen]'[/color]
620     [color=blue]If[/color] VBNullNuThink = vbNullString [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = vbNullString" ' = ""
630     If VBNullNuThink = Empty [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = Empty " [color=darkgreen]' = ""[/color]
640
650 'Try the [color=blue]If[/color] Len(  )=0   test
660     [color=blue]If[/color] Len(VBNullNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length VBNullNuThink = 0  " [color=darkgreen]' Works[/color]
670     [color=blue]If[/color] Len(StringZeroLen) = 0 [color=blue]Then[/color] MsgBox prompt:=" length StringZeroLen = 0  " ' Works
680     If Len(LNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length LNuThink = 0  " [color=darkgreen]' Works[/color]
690     If Len(SNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length LSNuThink = 0  " ' THIS WORKS NOW
700
710 [color=darkgreen]'Same again but with everything in an Array, as may be the practice with me...[/color]
720 [color=blue]Dim[/color] arrStuff(1 [color=blue]To[/color] 4) [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'Variant elements as before so as to see what VBA gives me[/color]
730 [color=blue]Let[/color] arrStuff(1) = vbNullString
740 [color=blue]Let[/color] arrStuff(2) = ""
750 [color=darkgreen]'Let arrStuff(3) =....deliberately not set[/color]
760 [color=blue]Set[/color] arrStuff(4) = [color=blue]Nothing[/color]
770 [color=darkgreen]' Put stop at Line   860 , run code[/color]
780 [color=darkgreen]'Highlight any arrStuff, hit Shift+F9, click on + box to reveal Array contents[/color]
790 [color=darkgreen]'Results:[/color]
800 'arrStuff(1)     "", Type String
810 [color=darkgreen]'arrStuff(2)     "", Type String[/color]
820 [color=darkgreen]'arrStuff(3)     Empty, Type Empty[/color]
830 [color=darkgreen]'arrStuff(4)     Nothing, Type object[/color]
840 [color=darkgreen]'[/color]
850 'Put Empty in all elements,
855 [color=darkgreen]'Put stop at End Sub, run code, look again at Array contents[/color]
860 [color=blue]Dim[/color] x [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'Loop Bound variable Count for "columns" in Array[/color]
870     [color=blue]For[/color] x = 1 [color=blue]To[/color] 4 [color=blue]Step[/color] 1
880     [color=blue]Let[/color] arrStuff(x) = Empty
890     [color=blue]Next[/color] x
900 [color=darkgreen]'Results:[/color]
910 'All Elements are    Empty, Type Empty in Variant Variable

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

 

[DocAElstein]

( Bump ) on Post #15
________ .....But what about Empty......

It is a very small point, but I just have a gut feeling that it may be more tidy to make an Array Element I don’t want as “Empty”, like what it is if I never put anything in it.. .
But a key to it “working” is what I determined experimentally in the last Post, that an empty Array Element responds as
True
to an
If ( Array Element ) = “”
or
If ( Array Element ) = vbNullString

and visa versa..


_. This concerns me a bit..
_ ( i ) One reason if i have declared and then let a variable = vbNullString or = “”, then it is somehow to my way of thinking not empty: Somewhere it must contain the info saying whether it is a vbNullStrng or “”. Otherwise it cannot tell how to store it. – It has been discussed previously that there are in this case differences in how it is stored....


_ ( iI ) Another reason that concerns me, - I thought I read somewhere that I cannot do
If ( Array Element ) = Nothing
My experiments confirm this.. The correct Format is
If ( Array Element ) Is Nothing
_.. and note that in this case the Array Element must be of Type Object.
_. This makes sense – in plain English I cannot = nothing. I can be ( like I is ) nothing.

Correspondingly i have syntaxly OK
If ( Array Element ) Is Empty
But This seems not to work saying that an Object is required. Even for various cases using specific objects the following...
__ ... __If ( Object ) Is Empty __ .....
____ .... _____ still does not work.

My conclusions would be that VBA has some strange concepts:

Nothing is a “Pseudo” Object not existing or not set yet. Or maybe a “pseudo” Method or Property which is used to 'release' an object variable from memory ###

Empty is a “Pseudo” “Value”, such as a String or Number or Formula not existing, or not given a “Value” yet. For a String this will be to all intents and purposes “” or vbNullString.
For a number I think it is equivalent to 0...
VBA is a bit unclear here . In the immediate window, a String let to be a vbNullString is given as “” in the Immediate Window, so is a string let to be “”. A String let to be Empty is given as empty. A Double, for example, let to be Empty is given as 0 .. An Object not yet Set or Set to Nothing is given as Nothing. .. A Variant let to be Empty is given as empty . Most Variables other than those of an Object are given as empty before any are let to be anything

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

So I am just looking for ideas any suggested advantages / disadvantages from anyone concerning using

= Empty
rather than
= vbNullString __ or __ = “”

....Maybe, just guessing....
_ 1) one example of an advantage....., I may have no strange Variable type incompatibility later in some cases.... ... but
_ 1a) here i note i may be relying on some implied implicit . For example , that this appears to work
Dim objSNuThink As Object
Set objSNuThink = Cells(90, 1) ' Set to Range object of an empty cell
If objSNuThink = Empty Then .................

Is really doing this

Dim objSNuThink As Object
Set objSNuThink = Cells(90, 1) ' Set to Range object of an empty cell
If objSNuThink.Value = Empty Then .................

_ 1b) In the first part code below i have a “better success rate, that is to say less incompatibility errors with
= Empty
compared to
= vbNullString

_1c) I note finally I may be splitting hairs... - when I actually “capture”, .. my Variant Types somehow coerces the comparison to work.......as shown in Part 2 of the code


_2) Given that there are some Memory and Speed advantages of vbNullString compared with “” , then I am guessing there may be some similar advantages of using Empty instead.


Alan

Code specific for this Post.

Row\Col	E​
90​	AString
91​	20​

[color=blue]Sub[/color] NotNuThink()
[color=darkgreen]'First Part.[/color]
[color=blue]Dim[/color] Var1 [color=blue]As[/color] [color=blue]String[/color], Var2 [color=blue]As[/color] [color=blue]Long[/color], Var3 [color=blue]As[/color] Range, var4 [color=blue]As[/color] Range, Var5 [color=blue]As[/color] Range
[color=blue]Let[/color] Var1 = "AString" [color=darkgreen]' " Has told VBA to make it a String Type[/color]
[color=blue]Let[/color] Var2 = 45 [color=darkgreen]'[/color]
[color=blue]Set[/color] Var3 = Range("E90:E91") '
[color=blue]Set[/color] var4 = Range("E90") [color=darkgreen]'[/color]
[color=blue]Set[/color] Var5 = Range("E91")
    [color=blue]If[/color] Var1 = [color=blue]Empty[/color] [color=blue]Then[/color] MsgBox prompt:="Var1 = Empty "
    [color=blue]If[/color] Var2 = Empty [color=blue]Then[/color] MsgBox prompt:="Var2 = Empty "
[color=darkgreen]'    If Var3 = [color=blue]Empty[/color] Then MsgBox prompt:="Var3 = Empty " ' Will not work as Element Field is returned so Type incompatibility[/color]
    [color=blue]If[/color] var4 = [color=blue]Empty[/color] [color=blue]Then[/color] MsgBox prompt:="Var4 = Empty " [color=darkgreen]' Works because implicitly Var4.Value is used which is[/color]
    [color=blue]If[/color] Var5 = Empty [color=blue]Then[/color] MsgBox prompt:="Var5 = Empty "
    
    [color=blue]If[/color] Var1 = vbNullString [color=blue]Then[/color] MsgBox prompt:="Var1 = Empty "
[color=darkgreen]'    If Var2 = vbNullString Then MsgBox prompt:="Var2 = Empty " ' Type incompatibility[/color]
[color=darkgreen]'    If Var3 = vbNullString Then MsgBox prompt:="Var3 = Empty " ' Will not work as Element Field is returned so Type incompatibility[/color]
    [color=blue]If[/color] var4 = vbNullString [color=blue]Then[/color] MsgBox prompt:="Var4 = [color=blue]Empty[/color] "
    [color=blue]If[/color] Var5 = vbNullString [color=blue]Then[/color] MsgBox prompt:="Var5 = Empty "
    
[color=darkgreen]'Second Part. Array "capture"[/color]
[color=blue]Dim[/color] arrIn() [color=blue]As[/color] [color=blue]Variant[/color]
[color=blue]Let[/color] arrIn() = Range("E90:E91").Value
    [color=blue]If[/color] arrIn(1, 1) = Empty [color=blue]Then[/color] MsgBox prompt:="arrIn(1, 1) = Empty "
    [color=blue]If[/color] arrIn(2, 1) = Empty [color=blue]Then[/color] MsgBox prompt:="arrIn(2, 1) = Empty "
    
    [color=blue]If[/color] arrIn(1, 1) = vbNullString [color=blue]Then[/color] MsgBox prompt:="arrIn(1, 1) = Empty "
    [color=blue]If[/color] arrIn(2, 1) = vbNullString [color=blue]Then[/color] MsgBox prompt:="arrIn(2, 1) = Empty " [color=darkgreen]'  my variant Types somehow coerces the comparison to work[/color]
    [color=blue]If[/color] [color=blue]CDbl[/color](arrIn(2, 1)) = vbNullString [color=blue]Then[/color] MsgBox prompt:="arrIn(2, 1) = Empty " [color=darkgreen]' Type incompatibility[/color]

[color=blue]End[/color] Sub

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

Code again from last Post with some extra bits discussed in this post ( and a few typo corrections! )

[color=blue]Sub[/color] NullNuttyThinks() [color=darkgreen]'http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html[/color]
10  [color=darkgreen]'Declare all variables so as to see what VBA may give 'em[/color]
20  [color=blue]Dim[/color] VBNullNuThink [color=blue]As[/color] [color=blue]Variant[/color], StringZeroLen [color=blue]As[/color] [color=blue]Variant[/color], LNuThink [color=blue]As[/color] [color=blue]Variant[/color], SNuThink [color=blue]As[/color] [color=blue]Variant[/color], CnvrtNuThinkToStr [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'Choose Variant and see what VBA "gives 'em"[/color]
30  [color=blue]Dim[/color] VBaZero [color=blue]As[/color] Variant: [color=blue]Let[/color] VBaZero = 0 [color=darkgreen]'Put here for completeness, but i think we know 0 is a number just like 1  2   etc. so ignoor this ..for now... unless we need it later...[/color]
40  [color=darkgreen]'[/color]
50  'Give 'em all a Null Nut Think
60  [color=blue]Let[/color] VBNullNuThink = vbNullString
70  [color=blue]Let[/color] StringZeroLen = "" [color=darkgreen]'I bet this must be a string as "parens" is wot ( what ) says "hey, here comes a string"[/color]
80  [color=darkgreen]'Let LNuThink = Nothing'Don't work, so leave Empty for now[/color]
85  [color=darkgreen]'Set LNuThink  is nothing'Don't work either[/color]
90  [color=blue]Set[/color] SNuThink = [color=blue]Nothing[/color] [color=darkgreen]' Note this makes it an object!![/color]
95  [color=darkgreen]'Let CnvrtNuThinkToStr = CStr(Nothing) 'Just an idea.. did not work[/color]
97  [color=darkgreen]'Set SNuThink = Empty' Run time error incompatible type[/color]
100 [color=darkgreen]'[/color]
101 'Give some Empties
102 [color=blue]Dim[/color] Db [color=blue]As[/color] Double: [color=blue]Let[/color] Db = [color=blue]Empty[/color]
104 [color=blue]Dim[/color] AEmptyVrnt [color=blue]As[/color] Variant: [color=blue]Let[/color] A[color=blue]Empty[/color]Vrnt = Empty
110 [color=darkgreen]'Highlight the variables, Hit Shift+F9 to get them displayed in a watch window...[/color]
120 [color=darkgreen]'Click in left Margin at Line Number 210 to put a stop point ...[/color]
130 [color=darkgreen]'Run Code and see what Vba Puts in, and what type within the Variant Variable allocated for each[/color]
140 [color=darkgreen]'Results:[/color]
150 'VBNullNuThink    "", Type String within Variant Variable
160 [color=darkgreen]'StringZeroLen     "", Type String within Variant Variable[/color]
170 [color=darkgreen]'LNuThink     Empty, Type Empty[/color]
180 [color=darkgreen]'SNuThink     Nothing, Type Object within Variant Variable[/color]
182 [color=darkgreen]'Db            0[/color]
190
200 [color=darkgreen]'Try the If   = ""     test[/color]
210     [color=blue]If[/color] VBNullNuThink = "" [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = """"" [color=darkgreen]' = ""[/color]
220     'If VBNullNuThink = Nothing Then MsgBox prompt:="VBNullNuThink = Nothing" ' Complile Error
225     [color=darkgreen]'If VBNullNuThink Is Nothing Then MsgBox prompt:="VBNullNuThink = Nothing" ' Run Time error - "object needed"[/color]
230     [color=blue]If[/color] StringZeroLen = "" [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = """"" [color=darkgreen]' = ""[/color]
240     'If StringZeroLen = Nothing Then MsgBox prompt:="StringZeroLen is Nothing" ' Complile Error
250     [color=blue]If[/color] LNuThink = "" [color=blue]Then[/color] MsgBox prompt:="LNuThink = """"" [color=darkgreen]' = ""[/color]
260     'If LNuThink = Nothing Then MsgBox prompt:="LNuThink is Nothing" ' Complile Error
265     [color=darkgreen]'If LNuThink Is Empty Then MsgBox prompt:="LNuThink is Empty" ' Run Time error - "object needed"[/color]
270     [color=darkgreen]'If SNuThink = "" Then MsgBox prompt:="SNuThink is """"" ' Is ""'RunTime Eror[/color]
280     [color=darkgreen]'If SNuThink = Nothing Then MsgBox prompt:="SNuThink is Nothing" 'Compile Erorr[/color]
290     [color=blue]If[/color] SNuThink [color=blue]Is[/color] [color=blue]Nothing[/color] [color=blue]Then[/color] MsgBox prompt:="SNuThink is Nothing" [color=darkgreen]' SNuThing IS Nothing![/color]
291     [color=darkgreen]'If SNuThink Is Empty Then MsgBox prompt:="SNuThink is Empty" ' Run Time error - "object needed"[/color]
292     [color=darkgreen]'If SNuThink = Empty Then MsgBox prompt:="SNuThink is Empty" ' Rin Time Error - object not Set[/color]
293     Set SNuThink = Cells(90, 1) [color=darkgreen]'[color=blue]Set[/color] to Range object of empy cell[/color]
294     [color=darkgreen]'If SNuThink Is Empty Then MsgBox prompt:="SNuThink is Empty" ' Run Time error - "object needed"[/color]
295 [color=blue]Dim[/color] objSNuThink [color=blue]As[/color] [color=blue]Object[/color], rngSNuThink [color=blue]As[/color] Range [color=darkgreen]' Both become Objects Object Variables of type Range[/color]
296 [color=blue]Set[/color] objSNuThink = Cells(90, 1): Set rngSNuThink = Cells(90, 1) [color=darkgreen]'[color=blue]Set[/color] to Range object of an empty cell[/color]
297     [color=darkgreen]'If objSNuThink Is Empty Then MsgBox prompt:="objSNuThink is Empty" ' Run Time error - "object needed"[/color]
298     [color=darkgreen]'If rngSNuThink Is [color=blue]Empty[/color] Then MsgBox prompt:="rngSNuThink is Empty" ' Run Time error - "object needed"[/color]
299     [color=blue]If[/color] objSNuThink = Empty [color=blue]Then[/color] MsgBox prompt:="objSNuThink = [color=blue]Empty[/color]": [color=blue]If[/color] objSNuThink.Value = Empty [color=blue]Then[/color] MsgBox prompt:="objSNuThink = [color=blue]Empty[/color]"
300     [color=blue]If[/color] VBNullNuThink = vbNullString [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = vbNullString" [color=darkgreen]' = ""[/color]
310     [color=blue]If[/color] StringZeroLen = vbNullString [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = vbNullString" ' = ""
320     [color=blue]If[/color] LNuThink = vbNullString [color=blue]Then[/color] MsgBox prompt:="LNuThink = vbNullString" [color=darkgreen]' = ""[/color]
330     [color=blue]If[/color] VBNullNuThink = Empty [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = [color=blue]Empty[/color]" ' = ""
340     [color=blue]If[/color] StringZeroLen = Empty [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = [color=blue]Empty[/color]" [color=darkgreen]' = ""[/color]
350     [color=blue]If[/color] LNuThink = [color=blue]Empty[/color] [color=blue]Then[/color] MsgBox prompt:="LNuThink = [color=blue]Empty[/color]" ' = ""
360
370 [color=darkgreen]'Try the If Len(  )=0   test[/color]
380     [color=blue]If[/color] Len(VBNullNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length VBNullNuThink = 0  " [color=darkgreen]' Works[/color]
390     [color=blue]If[/color] Len(StringZeroLen) = 0 [color=blue]Then[/color] MsgBox prompt:=" length StringZeroLen = 0  " ' Works
400     [color=blue]If[/color] Len(LNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length LNuThink = 0  " [color=darkgreen]' Works[/color]
410
420 'Click in left Margin at Line  560  to put a stop there.
430 [color=darkgreen]'Run next part of code and see what Vba Puts in and  type within the Variant Variable allocated for each[/color]
440 [color=blue]Let[/color] VBNullNuThink = [color=blue]Empty[/color] [color=darkgreen]'[/color]
450 [color=blue]Let[/color] StringZeroLen = Empty '
460 [color=blue]Let[/color] LNuThink = Empty [color=darkgreen]'[/color]
470 [color=blue]Let[/color] SNuThink = Empty '
480
490 [color=darkgreen]'Results:[/color]
500 'VBNullNuThink     Empty, Type [color=blue]Empty[/color] within the Variant Variable
510 [color=darkgreen]'StringZeroLen     Empty, Type Empty within the Variant Variable[/color]
520 [color=darkgreen]'LNuThink      Empty, Type Empty within the Variant Variable[/color]
530 [color=darkgreen]'SNuThink      Empty, Type [color=blue]Empty[/color] within the Variant Variable[/color]
540 [color=darkgreen]'[/color]
550 'Try the If   = ""     test
560     [color=blue]If[/color] VBNullNuThink = "" [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = """"" [color=darkgreen]' = ""[/color]
570     [color=blue]If[/color] StringZeroLen = "" [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = """"" ' = ""
580     [color=blue]If[/color] LNuThink = "" [color=blue]Then[/color] MsgBox prompt:="LNuThink = """"" [color=darkgreen]' = ""[/color]
585     If LNuThink [color=blue]Is[/color] Empty [color=blue]Then[/color] MsgBox prompt:="LNuThink Is Empty "
590     [color=darkgreen]'[color=blue]If[/color] SNuThink Is Nothing Then MsgBox prompt:="LNuThink is Nothing" ' Errors this time as no Object is there[/color]
600     If SNuThink = "" [color=blue]Then[/color] MsgBox prompt:="LNuThink = """"" [color=darkgreen]' = ""  THIS WORKS NOW[/color]
602     [color=darkgreen]'[color=blue]If[/color] SNuThink Is Empty Then MsgBox prompt:="SNuThink Is Empty "  'Run time error  - "needs an Object"[/color]
604     [color=blue]If[/color] IsEmpty(SNuThink) [color=blue]Then[/color] MsgBox prompt:="SNuThink Is Empty " [color=darkgreen]'Works...[/color]
610 '
620     [color=blue]If[/color] VBNullNuThink = vbNullString [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = vbNullString" [color=darkgreen]' = ""[/color]
630     If VBNullNuThink = Empty [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = [color=blue]Empty[/color] " ' = ""
632     [color=darkgreen]'[color=blue]If[/color] VBNullNuThink Is Empty Then MsgBox prompt:="VBNullNuThink Is Empty " 'Run time error  - "needs an Object"[/color]
634     If IsEmpty(VBNullNuThink) [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink Is Empty " [color=darkgreen]'Works...[/color]
640
650 'Try the [color=blue]If[/color] Len(  )=0   test
660     [color=blue]If[/color] Len(VBNullNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length VBNullNuThink = 0  " [color=darkgreen]' Works[/color]
670     [color=blue]If[/color] Len(StringZeroLen) = 0 [color=blue]Then[/color] MsgBox prompt:=" length StringZeroLen = 0  " ' Works
680     If Len(LNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length LNuThink = 0  " [color=darkgreen]' Works[/color]
690     If Len(SNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length LSNuThink = 0  " ' THIS WORKS NOW
700
710 [color=darkgreen]'Same again but with everything in an Array, as may be the practice with me...[/color]
720 [color=blue]Dim[/color] arrStuff(1 [color=blue]To[/color] 4) [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'Variant elements as before so as to see what VBA gives me[/color]
730 [color=blue]Let[/color] arrStuff(1) = vbNullString
740 [color=blue]Let[/color] arrStuff(2) = ""
750 [color=darkgreen]'Let arrStuff(3) =....deliberately not set[/color]
760 Set arrStuff(4) = [color=blue]Nothing[/color]
770 [color=darkgreen]' Put stop at Line   860 , run code[/color]
780 [color=darkgreen]'Highlight any arrStuff, hit Shift+F9, click on + box to reveal Array contents[/color]
790 [color=darkgreen]'Results:[/color]
800 'arrStuff(1)     "", Type String
810 [color=darkgreen]'arrStuff(2)     "", Type String[/color]
820 [color=darkgreen]'arrStuff(3)     Empty, Type Empty[/color]
830 [color=darkgreen]'arrStuff(4)     Nothing, Type object[/color]
840 [color=darkgreen]'[/color]
850 'Put Empty in all elements,
855 [color=darkgreen]'Put stop at End Sub, run code, look again at Array contents[/color]
860 [color=blue]Dim[/color] x [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'Loop Bound variable Count for "columns" in Array[/color]
870     [color=blue]For[/color] x = 1 [color=blue]To[/color] 4 [color=blue]Step[/color] 1
880     [color=blue]Let[/color] arrStuff(x) = Empty
890     [color=blue]Next[/color] x
900 [color=darkgreen]'Results:[/color]
910 'All Elements are    Empty, Type Empty in Variant Variable
920 [color=darkgreen]'[/color]


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

 

[sijpie]

VBA also has the terms vbEmpty and vbNull. All of them are different. If you look in the locals window at the values in an Array you can see that VBA shows a difference in the cells assigned different values or read from an emtpy range.
And if you use Empty or Null cells in calculations assuming they are 0 you sometimes get errors

personally After doing some speed testing I found that checking for vbNullstring is a lot faster then checking Len(). So this seems one difference in VB and VBA.

But there is no time difference in using vbNullstring or vbEmpty. So if you want to test your array cells for strings, I'd test them against vbNullstring.

 

[DocAElstein]

Hi sijpie,

VBA also has the terms vbEmpty and vbNull. All of them are different. If you look in the locals window at the values in an Array you can see that VBA shows a difference in the cells assigned different values or read from an empty range.
And if you use Empty or Null cells in calculations assuming they are 0 you sometimes get errors
personally After doing some speed testing I found that checking for vbNullstring is a lot faster then checking Len(). So this seems one difference in VB and VBA.
But there is no time difference in using vbNullstring or vbEmpty. So if you want to test your array cells for strings, I'd test them against vbNullstring.

Thanks very much for replying to the Thread and helping me clarify things a bit. Much appreciated. You are adding to and confirming some of my ideas based on experiments in Posts #15 and #16 which is very helpful.

I guess I am trying to find out exactly what different forms of “Not having anything in it” are. And the plus and minus of using the different “Not having anything in it” seem endless. So thanks for adding some info here. And thanks for confirming some of the ideas i was having....From my posts #15 and #16 you will see i was tackling (or trying to tackle ) vbEmpty.
vbNull. I had also seen but hesitated to make my ramblings even less readable!!!

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

A nice Summary would be useful, I am trying to get one clear in my head based on all that i have read...This is my current attempt: ( I would love any profi comment or corrections to this and my final conclusion for my application. )

So Looking at the* 4-5 ( *or just 4 -5 )*** ways of “Not having anything in it”

= Nothing ___ = “” _____ = vbNullString _____ = Empty _____ = vbNull

= Nothing

_*** get this one out of the way first as the general consensus is that it does not quite belong here...
_ maybe helpful in the understanding could be to think of it as one outcome of the attempt to make VBA appear what it is not really, that is to say a true OOP ( Object orientated programming ) . Maybe think of = Nothing being a “pseudo” .Method or .Property,- arguably better syntaxly as ( pseudo code)
Object.Nothing
_ A further conceptual Idea, only to be considered if one really understands well error handling is (again Pseudo code)......
______ On Nothing GoTo 0
___________.. the idea being here we go to an imaginary point or situation before starting running the code where we have complied but not run and so not Set the object.
_ Further the fact that a Variant Variable put = Nothing becomes an Object, suggests even more abstractly that Nothing itself is a sort of Object.
_ . So in words maybe = Nothing is ..” used to 'release' an Object Variable from memory...”

= “” and = vbNullString
_ Most discussions tend to suggest there is very little difference in these. Both are, in simple English, :- "strings of zero length". One could think of = “” as actually being just that: A string of zero length. Whereas = vbNullString is a sort of special thing to say or indicate that the string is of zero length rather than specifically allocating in a variable the space for a zero length string. Some subtle memory and speed advantages may be present in choosing = vbNullString . - But this may, as suggested and discussed, be further influenced by using VB or VBA or checking for len() rather than If etc. etc..

= Empty
This will make a variable appear as it was before being Let or Set. It Would be used then, for example, as in my case,, if i may be looking at / testing for other things then a String.

_..
Note again at this point, that VBA is very imprecise, and as noted previously use of or checking for any of the above three, ( = Empty , = “” and = vbNullString ), makes no distinguish apparently between them, regardless of how they may be represented in the Immediate Window. Very strange and possibly again a result form the attempt to make VBA appear what it is not really, that is to say a true OOP language



= Null

A new one on me and a bit difficult to grasp. Something along the wording of “....A Value indicating that a variable contains no valid data....” . I find a bit difficult to see how to achieve this in a code, other than the direct assignment of ( pseudo code )
Let Variable = Null
_.. But I note that this time VBA does make a distinct note of this thing and appears to test specifically for it. So in a code, after I assign a value to the variable ( or set an object) i can let it to = Null and a comparison against = Empty , = “” and = vbNullString will give a False. This makes sense of course. But why VBA does not do the same for ( Empty ) compared to ( vbNullString or = "" ) does not ( to me ! )


_....
And so for my Application. _...... ( Summarised again : Tidying up an Array to remove unwanted things before pasting into a Spreadsheet using the VBA “one liner” type code line ( pseudo code )
Let Range(TopLeftCellWhereOutputToGo).resize(Ubounds(Array()).Value=Array() )
____ _ .......For my application it looks like letting the unwanted stuff = Empty and be done with it is the best solution. This is based on my Experiments and your comments , (but noting i am testing for Numbers strings or possibly even Objects.... ). .... .... so.. based on my comments and yours which confirmed and add to some of my experiments.....

...... in the locals window at the values in an Array you can see that VBA shows a difference in the cells assigned different values or read from an empty Range.
And if you use Empty or Null cells in calculations assuming they are 0 you sometimes get errors........
no time difference in using vbNullstring or vbEmpty. So if you want to test your array cells for strings......

_........

_..At the end of the day, I guess, Trying to figure out exactly what VBA is doing is probably not too healthy... “....down that road leads to madness!!...” – not my words, but those often said to me by experienced programmers...
For example:...
_ (i) I just saw that applying the VBA Split Function to the value of an empty cell did not give me an error: instead it returned an Array of dimension 0 To -1. In the immediate window ( Ctrl G ) I am shown 1 element in such an Array with a value of “No variable” - !?!?.... a 5th version of something “Not having anything in it” *
_ (ii) and there is a =VBNull. I found it by mistake from a typo !!!... I do not feel like finding out what that is ( yet! ).... No “google” info at all on that!!... a 6th version of something “Not having anything in it” ?!? * ..... ( Let a Variant variable = VBNull and it becomes... a Long 1 !?! )
_(iii) You have for example suggested with you speed tests that the advantage of checking Len() is not there in VBA as suggested in this Thread already in the earlier Posts. – I guess that is explained by the references to VB rather than VBA in referenced sites in those Posts...maybe......

Thanks again for your reply
Alan

_________________________________..


P.s. the big messy test code again with some more bits and typo corrections

[color=blue]Sub[/color] NullNuttyThinks() [color=darkgreen]'http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html   http://www.mrexcel.com/forum/excel-questions/42495-better-explanation-null-empty-nothing-solved.html[/color]
10  [color=darkgreen]'Declare all variables so as to see what VBA may give 'em[/color]
20  [color=blue]Dim[/color] VBNullNuThink [color=blue]As[/color] [color=blue]Variant[/color], StringZeroLen [color=blue]As[/color] [color=blue]Variant[/color], LNuThink [color=blue]As[/color] [color=blue]Variant[/color], SNuThink [color=blue]As[/color] [color=blue]Variant[/color], CnvrtNuThinkToStr [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'Choose Variant and see what VBA "gives 'em"[/color]
30  [color=blue]Dim[/color] VBaZero [color=blue]As[/color] Variant: [color=blue]Let[/color] VBaZero = 0 [color=darkgreen]'Put here for completeness, but i think we know 0 is a number just like 1  2   etc. so ignoor this ..for now... unless we need it later...[/color]
40  [color=darkgreen]'[/color]
50  'Give 'em all a Null Nut Think
60  [color=blue]Let[/color] VBNullNuThink = vb[color=blue]Null[/color]String
70  [color=blue]Let[/color] StringZeroLen = "" [color=darkgreen]'I bet this must be a string as "quotes" is wot ( what ) says "hey, here comes a string"[/color]
80  [color=darkgreen]'Let LNuThink = Nothing'Don't work, so leave Empty for now[/color]
85  [color=darkgreen]'Set LNuThink  is nothing'Don't work either[/color]
90  [color=blue]Set[/color] SNuThink = [color=blue]Nothing[/color] [color=darkgreen]' Note this makes it an object!![/color]
95  [color=darkgreen]'Let CnvrtNuThinkToStr = CStr(Nothing) 'Just an idea.. did not work[/color]
97  [color=darkgreen]'Set SNuThink = Empty' Run time error incompatible type[/color]
98  [color=blue]Let[/color] SNuThink = Null [color=darkgreen]'Works[/color]
99  [color=blue]Set[/color] SNuThink = [color=blue]Nothing[/color]
100 [color=darkgreen]'[/color]
101 'Give some Empties
102 [color=blue]Dim[/color] Db [color=blue]As[/color] Double: [color=blue]Let[/color] Db = [color=blue]Empty[/color]
104 [color=blue]Dim[/color] AEmptyVrnt [color=blue]As[/color] Variant: [color=blue]Let[/color] A[color=blue]Empty[/color]Vrnt = Empty
110 [color=darkgreen]'Highlight the variables, Hit Shift+F9 to get them displayed in a watch window...[/color]
120 [color=darkgreen]'Click in left Margin at Line Number 210 to put a stop point ...[/color]
130 [color=darkgreen]'Run Code and see what Vba Puts in, and what type within the Variant Variable allocated for each[/color]
140 [color=darkgreen]'Results:[/color]
150 'VBNullNuThink    "", Type String within Variant Variable
160 [color=darkgreen]'StringZeroLen     "", Type String within Variant Variable[/color]
170 [color=darkgreen]'LNuThink     Empty, Type Empty[/color]
180 [color=darkgreen]'SNuThink     Nothing, Type Object within Variant Variable[/color]
182 [color=darkgreen]'Db            0[/color]
190
200 [color=darkgreen]'Try the If   = ""     test[/color]
210     [color=blue]If[/color] VBNullNuThink = "" [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = """"" [color=darkgreen]' = ""[/color]
220     'If VBNullNuThink = Nothing Then MsgBox prompt:="VBNullNuThink = Nothing" ' Complile Error
225     [color=darkgreen]'If VBNullNuThink Is Nothing Then MsgBox prompt:="VBNullNuThink = Nothing" ' Run Time error - "object needed"[/color]
226 [color=blue]Let[/color] VBNullNuThink = Null
227     [color=blue]If[/color] VBNullNuThink = "" [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = Null" [color=darkgreen]' = Null[/color]
228 [color=blue]Let[/color] VBNullNuThink = vbNullString: [color=blue]Let[/color] VBNullNuThink = VBNull
229     [color=blue]If[/color] VBNullNuThink = VBNull [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = VBNull, that is to say 1" [color=darkgreen]' = 1[/color]
230 [color=blue]Let[/color] VBNullNuThink = vb[color=blue]Null[/color]String
235     [color=blue]If[/color] StringZeroLen = "" [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = """"" [color=darkgreen]' = Null[/color]
240     'If StringZeroLen = Nothing Then MsgBox prompt:="StringZeroLen is Nothing" ' Complile Error
250     [color=blue]If[/color] LNuThink = "" [color=blue]Then[/color] MsgBox prompt:="LNuThink = """"" [color=darkgreen]' = ""[/color]
260     'If LNuThink = Nothing Then MsgBox prompt:="LNuThink is Nothing" ' Complile Error
265     [color=darkgreen]'If LNuThink Is Empty Then MsgBox prompt:="LNuThink is Empty" ' Run Time error - "object needed"[/color]
270     [color=darkgreen]'If SNuThink = "" Then MsgBox prompt:="SNuThink is """"" ' Is ""'RunTime Eror[/color]
280     [color=darkgreen]'If SNuThink = Nothing Then MsgBox prompt:="SNuThink is Nothing" 'Compile Erorr[/color]
290     [color=blue]If[/color] SNuThink [color=blue]Is[/color] [color=blue]Nothing[/color] [color=blue]Then[/color] MsgBox prompt:="SNuThink is Nothing" [color=darkgreen]' SNuThing IS Nothing![/color]
291     [color=darkgreen]'If SNuThink Is Empty Then MsgBox prompt:="SNuThink is Empty" ' Run Time error - "object needed"[/color]
292     [color=darkgreen]'If SNuThink = Empty Then MsgBox prompt:="SNuThink is Empty" ' Rin Time Error - object not Set[/color]
293     Set SNuThink = Cells(90, 1) [color=darkgreen]'[color=blue]Set[/color] to Range Object of empty cell[/color]
294     [color=darkgreen]'If SNuThink Is Empty Then MsgBox prompt:="SNuThink is Empty" ' Run Time error - "object needed"[/color]
295 [color=blue]Dim[/color] objSNuThink [color=blue]As[/color] [color=blue]Object[/color], rngSNuThink [color=blue]As[/color] Range [color=darkgreen]' Both become Objects Object Variables of type Range[/color]
296 [color=blue]Set[/color] objSNuThink = Cells(90, 1): Set rngSNuThink = Cells(90, 1) [color=darkgreen]'[color=blue]Set[/color] to Range object of an empty cell[/color]
297     [color=darkgreen]'If objSNuThink Is Empty Then MsgBox prompt:="objSNuThink is Empty" ' Run Time error - "object needed"[/color]
298     [color=darkgreen]'If rngSNuThink Is [color=blue]Empty[/color] Then MsgBox prompt:="rngSNuThink is Empty" ' Run Time error - "object needed"[/color]
299     [color=blue]If[/color] objSNuThink = Empty [color=blue]Then[/color] MsgBox prompt:="objSNuThink = [color=blue]Empty[/color]": [color=blue]If[/color] objSNuThink.Value = Empty [color=blue]Then[/color] MsgBox prompt:="objSNuThink = [color=blue]Empty[/color]"
305     [color=blue]If[/color] VBNullNuThink = vbNullString [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = vbNullString" [color=darkgreen]' = ""[/color]
310     [color=blue]If[/color] StringZeroLen = vbNullString [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = vbNullString" ' = ""
320     [color=blue]If[/color] LNuThink = vbNullString [color=blue]Then[/color] MsgBox prompt:="LNuThink = vbNullString" [color=darkgreen]' = ""[/color]
330     [color=blue]If[/color] VBNullNuThink = Empty [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = [color=blue]Empty[/color]" ' = ""
340     [color=blue]If[/color] StringZeroLen = Empty [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = [color=blue]Empty[/color]" [color=darkgreen]' = ""[/color]
350     [color=blue]If[/color] LNuThink = Empty [color=blue]Then[/color] MsgBox prompt:="LNuThink = Empty" ' = ""
355     [color=blue]If[/color] StringZeroLen = Null [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = VBNull" [color=darkgreen]' = ""[/color]
356 [color=blue]Let[/color] StringZeroLen = ""
357 [color=blue]Let[/color] StringZeroLen = [color=blue]Empty[/color]
358     [color=blue]If[/color] StringZeroLen = VBNull [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = VBNull" [color=darkgreen]' =[color=blue]Empty[/color][/color]
359 [color=blue]On[/color] [color=blue]Error[/color] [color=blue]Resume[/color] [color=blue]Next[/color]
360 [color=blue]Let[/color] StringZeroLen = 1 / 0
361 [color=blue]On[/color] [color=blue]Error[/color] [color=blue]GoTo[/color] 0 [color=darkgreen]'Reset error[/color]
362     [color=blue]If[/color] StringZeroLen = VBNull [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = VBNull" ' =[color=blue]Empty[/color]
370 [color=darkgreen]'Try the If Len(  )=0   test[/color]
380     [color=blue]If[/color] Len(VBNullNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length VBNullNuThink = 0  " [color=darkgreen]' Works[/color]
390     [color=blue]If[/color] Len(StringZeroLen) = 0 [color=blue]Then[/color] MsgBox prompt:=" length StringZeroLen = 0  " ' Works
400     [color=blue]If[/color] Len(LNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length LNuThink = 0  " [color=darkgreen]' Works[/color]
410
420 'Click in left Margin at Line  560  to put a stop there.
430 [color=darkgreen]'Run next part of code and see what Vba Puts in and  type within the Variant Variable allocated for each[/color]
440 [color=blue]Let[/color] VBNullNuThink = [color=blue]Empty[/color] [color=darkgreen]'[/color]
450 [color=blue]Let[/color] StringZeroLen = Empty '
460 [color=blue]Let[/color] LNuThink = Empty [color=darkgreen]'[/color]
470 [color=blue]Let[/color] SNuThink = Empty '
480
490 [color=darkgreen]'Results:[/color]
500 'VBNullNuThink     Empty, Type [color=blue]Empty[/color] within the Variant Variable
510 [color=darkgreen]'StringZeroLen     Empty, Type Empty within the Variant Variable[/color]
520 [color=darkgreen]'LNuThink      Empty, Type Empty within the Variant Variable[/color]
530 [color=darkgreen]'SNuThink      Empty, Type [color=blue]Empty[/color] within the Variant Variable[/color]
540 [color=darkgreen]'[/color]
550 'Try the If   = ""     test
560     [color=blue]If[/color] VBNullNuThink = "" [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = """"" [color=darkgreen]' = ""[/color]
570     [color=blue]If[/color] StringZeroLen = "" [color=blue]Then[/color] MsgBox prompt:="StringZeroLen = """"" ' = ""
580     [color=blue]If[/color] LNuThink = "" [color=blue]Then[/color] MsgBox prompt:="LNuThink = """"" [color=darkgreen]' = ""[/color]
585     If LNuThink [color=blue]Is[/color] Empty [color=blue]Then[/color] MsgBox prompt:="LNuThink Is Empty "
590     [color=darkgreen]'[color=blue]If[/color] SNuThink Is Nothing Then MsgBox prompt:="LNuThink is Nothing" ' Errors this time as no Object is there[/color]
600     If SNuThink = "" [color=blue]Then[/color] MsgBox prompt:="LNuThink = """"" [color=darkgreen]' = ""  THIS WORKS NOW[/color]
602     [color=darkgreen]'[color=blue]If[/color] SNuThink Is Empty Then MsgBox prompt:="SNuThink Is Empty "  'Run time error  - "needs an Object"[/color]
604     [color=blue]If[/color] IsEmpty(SNuThink) [color=blue]Then[/color] MsgBox prompt:="SNuThink Is Empty " [color=darkgreen]'Works...[/color]
610 '
620     [color=blue]If[/color] VBNullNuThink = vbNullString [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = vbNullString" [color=darkgreen]' = ""[/color]
630     If VBNullNuThink = Empty [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink = [color=blue]Empty[/color] " ' = ""
632     [color=darkgreen]'[color=blue]If[/color] VBNullNuThink Is Empty Then MsgBox prompt:="VBNullNuThink Is Empty " 'Run time error  - "needs an Object"[/color]
634     If IsEmpty(VBNullNuThink) [color=blue]Then[/color] MsgBox prompt:="VBNullNuThink Is Empty " [color=darkgreen]'Works...[/color]
640
650 'Try the [color=blue]If[/color] Len(  )=0   test
660     [color=blue]If[/color] Len(VBNullNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length VBNullNuThink = 0  " [color=darkgreen]' Works[/color]
670     [color=blue]If[/color] Len(StringZeroLen) = 0 [color=blue]Then[/color] MsgBox prompt:=" length StringZeroLen = 0  " ' Works
680     If Len(LNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length LNuThink = 0  " [color=darkgreen]' Works[/color]
690     If Len(SNuThink) = 0 [color=blue]Then[/color] MsgBox prompt:=" length LSNuThink = 0  " ' THIS WORKS NOW
700
710 [color=darkgreen]'Same again but with everything in an Array, as may be the practice with me...[/color]
720 [color=blue]Dim[/color] arrStuff(1 [color=blue]To[/color] 4) [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'Variant elements as before so as to see what VBA gives me[/color]
730 [color=blue]Let[/color] arrStuff(1) = vbNullString
740 [color=blue]Let[/color] arrStuff(2) = ""
750 [color=darkgreen]'Let arrStuff(3) =....deliberately not set[/color]
760 Set arrStuff(4) = [color=blue]Nothing[/color]
770 [color=darkgreen]' Put stop at Line   860 , run code[/color]
780 [color=darkgreen]'Highlight any arrStuff, hit Shift+F9, click on + box to reveal Array contents[/color]
790 [color=darkgreen]'Results:[/color]
800 'arrStuff(1)     "", Type String
810 [color=darkgreen]'arrStuff(2)     "", Type String[/color]
820 [color=darkgreen]'arrStuff(3)     Empty, Type Empty[/color]
830 [color=darkgreen]'arrStuff(4)     Nothing, Type object[/color]
840 [color=darkgreen]'[/color]
850 'Put Empty in all elements,
855 [color=darkgreen]'Put stop at End Sub, run code, look again at Array contents[/color]
860 [color=blue]Dim[/color] x [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'Loop Bound variable Count for "columns" in Array[/color]
870     [color=blue]For[/color] x = 1 [color=blue]To[/color] 4 [color=blue]Step[/color] 1
880     [color=blue]Let[/color] arrStuff(x) = Empty
890     [color=blue]Next[/color] x
900 [color=darkgreen]'Results:[/color]
910 'All Elements are    Empty, Type Empty in Variant Variable
920 [color=darkgreen]'[/color]


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

 

[sijpie]

Some more information on vbNullString and Nothing

You need to understand the concept of pointers in programming languages.

A pointer is an address in memory. When I create a string and pass the string to a variable or to a function (as argument), I am not passing the string at all. All I am doing is passing the address (pointer) of the start of the string. (You can force this behaviour with arguments by specifying byRef:

Sub MyMacro(byRef iInt as Integer)

Here the macro gets a pointer to the integer passed and so when the sub changes the iInt, when the sub finishes the caller function has the new value in the integer it passed to the sub.

OK, so to make the processing of code more efficient, when a variable gets created, what is created is a pointer to the variable. And as these pointers need to point somewhere, VBA lets them point to:

Dim sStr as String    ' Initially points to address space that VBA calls vbNullString
Dim objObject as Object ' any object, initially points to an address space called Nothing
Dim iInt as integer, lLong a Long  ' initially points to address space that contains 0
Dim vVar as variant  ' Don't know for sure but should point to an empty variant structure.

So when you manipulate these things, then they will get assigned a new memory space and the pointer will be updated to point to this memory space. With Ints and Longs it is unlikely that the memory space will change after that, but with a string for instance the memory space may not fit and will have to be updated.

A string is held in VBA memory starting with the length of the string and then the string itself. (In C this is not the case, the string starts with the first character and ends with a null character.)

So this explains some of the efficiencies in comparing

if I compare a string with "", then a really smart precompiler would have turned the "" into vbNullstring before passing it to the compiler. However VBA does not seem to do that so the difference in

If sStr = vbNullString Then ...
'and
If sStr = "" Then ...

is that in the first case the program only needs to check if the address of sStr = the address of vbNullString, which is a very quick check
in the second case a constant string of zero length is created to hold "". And then the string at sStr and this zero length string are compared. This involves far more work.

Some people say that

If Len(sStr) = 0 Then

is the fastest way. With a simple time test loop I found that for VBA this is not the case. I think that this is because as I mention above, testing for vbNullString only involves checking if the address is the same as vbNullstring address, whereas testing the length involves more steps: first the address needs to be passed to function Len() which checks the length at the start of this address. Then this needs to be compared with 0 (or false if you use Not). Two extra steps.
As with any Objects (be it WorkSheet or Shape) when initialised they point to address space Nothing and only when assigned to an actual object does the pointer point somewhere else. So if you test an object with

if wsWS is Nothing

it is easy for the program to see if it points to this initial Nothing address or not. And setting an object to Nothing tells the system that the memory space occupied previously is free. (And apparently there is some memory leakage in VBA where allegedly objects which are not set back to Nothing keep this memory space flagged as being in use, even after the program finishes.)

Hope this helps shed a bit more light on your endeavour

 

[DocAElstein]

Thanks again sijpie, for the reply. I think I am understanding what you are saying.

_1 ) I am seeing now clearly your arguments for using a check ( in VBA ) for vbNullString when checking ( comparing ) for a string of .. “Not having anything in it”… And I guess there may still be some minor memory advantages of “Emptying” a string with = vbNullString rather than = “” .

_2) I am thinking now that when I use my = Empty , applied to the different variable types , it will give them ( back ) the pointer to the . “Not having anything in it”… memory space appropriate for the type. I wonder then if my comparison using Empty will automatically make a comparison with the appropriate . “Not having anything in it”… And a bit of a guess is there is initially little speed disadvantege here.....maybe...
_2a) I am not 100% clear if your described memory space for vbNullString and = ”” are the same. I think my experiments suggest they are. – Or I expect the comparison of either these two to = Empty would not give the same results. I mean if i have a String Let to = “”, or = vbNullString then the comparison to = Empty is always True. If the addresses of the two = “”, or = vbNullString were different then i do not see how the comparison would always give the same results.
_2b) For the case of Objects and Longs / Integers etc. I follow your arguments and the 1 appropriate “Not having anything in it”… memory space is compared when using = Empty.
_2c)(i) I wonder if there is a difference in the speed of comparing against = Empty, rather than the appropriate = vbNullString or = 0 or Is Nothing.

_2c)(ii) I wonder if when using Variants ( as i often do in my Array Elements ) the speed of comparing against = Empty is different from the case of comparing the other variable types against = Empty. ( As mentioned to avoid possible type mismatch errors i will probably have to use the comparison against = Empty anyway )

_3) it is all suggesting to me my conclusion of using = Empty to “empty” my Variants, and then any comparison to use against = Empty is OK... or maybe not!! - . I can maybe see that there might be theoretically an advantage of putting a Variant Array Element = to something else, such as, for example, = vbNullString to “empty it” and then do the appropriate comparison if/ when necessary, in this example against vbNullString
It is all a bit too advanced for me at this stage to know the answer to 3. And i expect even experienced users such as yourself often need to experiment to see the answer to this. If in the future I have any speed results i will post.
_3a) I guess i may be asking here if a vbNullString uses less space and is quicker to do a comparison for as an “Empty” Variant. I guess the answer to that is almost certainly yes.
_3b) But then again, say my variant with stuff in i wanted to get rid of could contain a Number, Object or String, I would initially have to use= Empty ( which works on all without any Type mismatch error ), and then use the = vbNullString, or whichever pointer to the . “Not having anything in it”…

Thanks again.

Alan

P.s. A minor point..

.....(And apparently there is some memory leakage in VBA where allegedly objects which are not set back to Nothing keep this memory space flagged as being in use, even after the program finishes.)....

.. this could explain the “good practice” of putting ( setting) an Object to = Nothing at the end of a code.