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
Code:
[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]
