{"id":64513,"date":"2007-07-06T00:33:00","date_gmt":"2007-07-06T00:33:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/07\/06\/how-can-i-replace-null-values-in-an-array\/"},"modified":"2007-07-06T00:33:00","modified_gmt":"2007-07-06T00:33:00","slug":"how-can-i-replace-null-values-in-an-array","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-replace-null-values-in-an-array\/","title":{"rendered":"How Can I Replace Null Values in an Array?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I loop through a 12×12 array and replace any null values with a hyphen?

— AM <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, AM. Sorry for the delay in getting back to you; as it turns out, yesterday was Independence Day (more typically referred to as the Fourth of July) in the good old USA, and the Scripting Guys, along with their fellow Microsoft employees, demonstrated their patriotism by taking the day off. As most of you probably know, taking Independence Day off typically means picnics, parades, and fireworks, although a couple years ago the Scripting Guy who writes this column got so swept up in the excitement of the day that he celebrated by burning his house down<\/B><\/A>.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note to all readers, but especially to those who work for the insurance company<\/B>. Technically, it was only the garage that burned down; in addition the Scripting Guy who writes this column wasn\u2019t the one was actually burned it down. Officially the cause of the fire was listed as \u201cunknown,\u201d although the fire inspector strongly suspected that an errant firework shot off by one of the neighbors landed in the garage, smoldered for a day or two, and then caught on fire. Even if that was<\/I> the case, though, the Scripting Guy who writes this column holds no grudges against his neighbors. After all, had the shoe been on the other foot and he lived next door to himself, he\u2019d probably try to burn his house down, too.<\/P>\n

Note to all readers, but especially to those who work for the insurance company: No, he wouldn\u2019t really<\/I> do that. He\u2019s just joking. Fortunately, insurance companies are known for their sense of humor.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

At any rate, if you aren\u2019t from the US you probably didn\u2019t get to see any fireworks yesterday. But that\u2019s OK; here\u2019s the next-best thing to a fireworks display, a script that can loop through an array and replace any empty values with a hyphen:<\/P>

Dim arrNumbers(2, 2)<\/p>\n
arrNumbers(0,1) = 100\narrNumbers(1,2) = 200\narrNumbers(2,1) = 300\narrNumbers(2,2) = 400<\/p>\n
For x = 0 to 2\n    For y = 0 to 2\n        If arrNumbers(x, y) = “” Then\n            arrNumbers(x, y) = “-”\n        End If\n    Next\nNext<\/p>\n
For x = 0 to 2\n    For y = 0 to 2\n        Wscript.Echo x & “,” & y & “: ” & arrNumbers(x, y)\n    Next\nNext\n<\/PRE>\n
Before we go much further we have to confess something. (No, not to that. We already told you: someone else<\/I> burned down the house.) Although AM asked for a 12×12 array our sample script actually creates a 2×2 array. Why? Well, we used a 2×2 array simply so we could display all the array values (and demonstrate that the script actually worked) without having to publish script output that scrolled on for miles and miles. But that\u2019s OK. After all, to create a 2×2 array we simply use the Dim<\/B> statement followed by the array name (arrNumbers) and the array dimensions (2×2):<\/P>
Dim arrNumbers(2, 2)\n<\/PRE>\n
Suppose we did<\/I> want to create a 12×12 array. Then all we need to do is change the array dimensions as needed:<\/P>
Dim arrNumbers(12, 12)\n<\/PRE>\n
See? No big deal.<\/P>\n
After we define the array and its dimensions the next four lines of code are used to assign values to a few of the items in that array. You can think of a 2×2 array as being a grid that looks something like this, with periods representing blank spaces (something we have to do because of the crazy way empty table cells get displayed on TechNet):<\/P>\n\n<\/THEAD>\n\n\n\n\n\n
\n
.<\/P><\/TD>\n
\n
0<\/B><\/P><\/TD>\n
\n
1<\/B><\/P><\/TD>\n
\n
2<\/B><\/P><\/TD><\/TR>\n
\n
0<\/B><\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
.<\/P><\/TD><\/TR>\n
\n
1<\/B><\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
.<\/P><\/TD><\/TR>\n
\n
2<\/B><\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
If we assign item 0,1 the value 100 (which is exactly what we do), that means the grid will look like this:<\/P>\n\n<\/THEAD>\n\n\n\n\n\n
\n
.<\/P><\/TD>\n
\n
0<\/B><\/P><\/TD>\n
\n
1<\/B><\/P><\/TD>\n
\n
2<\/B><\/P><\/TD><\/TR>\n
\n
0<\/B><\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
100<\/P><\/TD>\n
\n
.<\/P><\/TD><\/TR>\n
\n
1<\/B><\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
.<\/P><\/TD><\/TR>\n
\n
2<\/B><\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
And after we finish assigning values to four different items the grid will look like this:<\/P>\n\n<\/THEAD>\n\n\n\n\n\n
\n
.<\/P><\/TD>\n
\n
0<\/B><\/P><\/TD>\n
\n
1<\/B><\/P><\/TD>\n
\n
2<\/B><\/P><\/TD><\/TR>\n
\n
0<\/B><\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
100<\/P><\/TD>\n
\n
.<\/P><\/TD><\/TR>\n
\n
1<\/B><\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
200<\/P><\/TD><\/TR>\n
\n
2<\/B><\/P><\/TD>\n
\n
.<\/P><\/TD>\n
\n
300<\/P><\/TD>\n
\n
400<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Does that make sense? Excellent. These four lines of code simply assign values to four of the items in the array:<\/P>
arrNumbers(0,1) = 100\narrNumbers(1,2) = 200\narrNumbers(2,1) = 300\narrNumbers(2,2) = 400\n<\/PRE>\n
Now, if you studied the grid carefully (and you did<\/I> study the grid carefully, didn\u2019t you?) you might have noticed that several of the items have no value whatsoever; for example, item 0,0 is blank. These are the items AM is interested in; he wants to identify all the empty squares (that is, items in the array that have no value) and replace those values with hyphens. In other words, he wants to modify the grid so that, when the script finishes, it looks like this:<\/P>\n\n<\/THEAD>\n\n\n\n\n\n
\n
.<\/P><\/TD>\n
\n
0<\/B><\/P><\/TD>\n
\n
1<\/B><\/P><\/TD>\n
\n
2<\/B><\/P><\/TD><\/TR>\n
\n
0<\/B><\/P><\/TD>\n
\n
–<\/B><\/P><\/TD>\n
\n
100<\/P><\/TD>\n
\n
–<\/P><\/TD><\/TR>\n
\n
1<\/B><\/P><\/TD>\n
\n
–<\/B><\/P><\/TD>\n
\n
–<\/B><\/P><\/TD>\n
\n
200<\/P><\/TD><\/TR>\n
\n
2<\/B><\/P><\/TD>\n
\n
–<\/B><\/P><\/TD>\n
\n
300<\/P><\/TD>\n
\n
400<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Can we do that? Of course we can; it\u2019s as easy as burning down a house.<\/P>\n
Um, scratch that. Let\u2019s go with \u201cIt\u2019s as easy as falling off a log\u201d instead.<\/P>\n
So how do<\/I> we identify array items that have no value, and then replace those empty values with hyphens? Like this:<\/P>
For x = 0 to 2\n    For y = 0 to 2\n        If arrNumbers(x, y) = “” Then\n            arrNumbers(x, y) = “-”\n        End If\n    Next\nNext\n<\/PRE>\n
What we\u2019ve got here is a nested For loop: a For Next loop inside another For Next loop. We start out with a For Next loop that uses a counter variable named x and that runs from 0 to 2:<\/P>
For x = 0 to 2\n<\/PRE>\n
Inside that loop we then have a second For Next loop that also runs from 0 to 2. The only difference? This loop uses a counter variable named y:<\/P>
For y = 0 to 2\n<\/PRE>\n
What\u2019s the point of all that? Well, we need to run through all the items in the array; those items include the following:<\/P>\n\n\n\n\n\n\n\n\n\n\n\n
\u2022<\/TD>\n\n
0,0<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
0,1<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
0,2<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
1,0<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
1,1<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
1,2<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
2,0<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
2,1<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
2,2<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
If you follow the logic, you\u2019ll see that our first loop covers all the possible values (0, 1, and 2) in the first dimension of the array; the second loop does the same thing for the array\u2019s second dimension. By combining the two loops we end up hitting each and every item in the array.<\/P>\n
So then what do we do<\/I> to each and every item in the array? That\u2019s easy: we burn them to the ground!<\/P>\n
No, hey, just kidding. Instead, we look to see if the value of the item is equal to an empty string:<\/P>
If arrNumbers(x, y) = “” Then\n<\/PRE>\n
If the value is not<\/I> equal to an empty string we simply loop around and repeat the process with the next item in the array. If the value is<\/I> equal to an empty string we assign a hyphen as the new item value and then<\/I> loop round and repeat the process with the next item in the array. Here\u2019s how we assign a hyphen to the array item:<\/P>
arrNumbers(x, y) = “-”\n<\/PRE>\n
And that\u2019s all we have to do. After we\u2019ve looked at and, if necessary, modified each value in the array we then execute this block of code, which simply echoes back each item and its value:<\/P>
For x = 0 to 2\n    For y = 0 to 2\n        Wscript.Echo x & “,” & y & “: ” & arrNumbers(x, y)\n    Next\nNext\n<\/PRE>\n
That output should look like this:<\/P>
0,0: –\n0,1: 100\n0,2: –\n1,0: –\n1,1: –\n1,2: 200\n2,0: –\n2,1: 300\n2,2: 400\n<\/PRE>\n
As you can see, each empty value in the array has been replaced by a hyphen. It\u2019s just like magic.<\/P>\n
Except, for the fact that it isn\u2019t anything at all like magic.<\/P>\n
And there you have it, AM. Before we call it a day we should note, in the spirit of diversity and globalism, that the Fourth of July is important to all<\/I> the people of the world, and not just to Americans. For example, on July 4, 836 the Pactum Sicardi<\/I> was signed, bringing peace to the Principality of Benevento and the Duchy of Naples. Likewise, on July 4, 1359 Francesco II Ordelaffi of Forl\u00ec surrendered to the Papal commander Gil de Albornoz. The list goes on and on. The Fourth of July: a little something for everyone.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I loop through a 12×12 array and replace any null values with a hyphen?— AM Hey, AM. Sorry for the delay in getting back to you; as it turns out, yesterday was Independence Day (more typically referred to as the Fourth of July) in the good old USA, and the […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[18,3,4,5],"class_list":["post-64513","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-arrays-hash-tables-and-dictionary-objects","tag-scripting-guy","tag-scripting-techniques","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I loop through a 12×12 array and replace any null values with a hyphen?— AM Hey, AM. Sorry for the delay in getting back to you; as it turns out, yesterday was Independence Day (more typically referred to as the Fourth of July) in the good old USA, and the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64513","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=64513"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64513\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=64513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64513"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}