How Can I Replace Null Values in an Array?

ScriptingGuy1

Hey, Scripting Guy! Question

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

— AM

SpacerHey, Scripting Guy! AnswerScript Center

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.

Note to all readers, but especially to those who work for the insurance company. Technically, it was only the garage that burned down; in addition the Scripting Guy who writes this column wasn’t the one was actually burned it down. Officially the cause of the fire was listed as “unknown,” 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 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’d probably try to burn his house down, too.

Note to all readers, but especially to those who work for the insurance company: No, he wouldn’t really do that. He’s just joking. Fortunately, insurance companies are known for their sense of humor.

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

Dim arrNumbers(2, 2)

arrNumbers(0,1) = 100 arrNumbers(1,2) = 200 arrNumbers(2,1) = 300 arrNumbers(2,2) = 400

For x = 0 to 2 For y = 0 to 2 If arrNumbers(x, y) = “” Then arrNumbers(x, y) = “-” End If Next Next

For x = 0 to 2 For y = 0 to 2 Wscript.Echo x & “,” & y & “: ” & arrNumbers(x, y) Next Next

Before we go much further we have to confess something. (No, not to that. We already told you: someone else 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’s OK. After all, to create a 2×2 array we simply use the Dim statement followed by the array name (arrNumbers) and the array dimensions (2×2):

Dim arrNumbers(2, 2)

Suppose we did want to create a 12×12 array. Then all we need to do is change the array dimensions as needed:

Dim arrNumbers(12, 12)

See? No big deal.

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):

.

0

1

2

0

.

.

.

1

.

.

.

2

.

.

.

If we assign item 0,1 the value 100 (which is exactly what we do), that means the grid will look like this:

.

0

1

2

0

.

100

.

1

.

.

.

2

.

.

.

And after we finish assigning values to four different items the grid will look like this:

.

0

1

2

0

.

100

.

1

.

.

200

2

.

300

400

Does that make sense? Excellent. These four lines of code simply assign values to four of the items in the array:

arrNumbers(0,1) = 100
arrNumbers(1,2) = 200
arrNumbers(2,1) = 300
arrNumbers(2,2) = 400

Now, if you studied the grid carefully (and you did study the grid carefully, didn’t 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:

.

0

1

2

0

100

1

200

2

300

400

Can we do that? Of course we can; it’s as easy as burning down a house.

Um, scratch that. Let’s go with “It’s as easy as falling off a log” instead.

So how do we identify array items that have no value, and then replace those empty values with hyphens? Like this:

For x = 0 to 2
    For y = 0 to 2
        If arrNumbers(x, y) = “” Then
            arrNumbers(x, y) = “-”
        End If
    Next
Next

What we’ve 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:

For x = 0 to 2

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:

For y = 0 to 2

What’s the point of all that? Well, we need to run through all the items in the array; those items include the following:

0,0

0,1

0,2

1,0

1,1

1,2

2,0

2,1

2,2

If you follow the logic, you’ll 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’s second dimension. By combining the two loops we end up hitting each and every item in the array.

So then what do we do to each and every item in the array? That’s easy: we burn them to the ground!

No, hey, just kidding. Instead, we look to see if the value of the item is equal to an empty string:

If arrNumbers(x, y) = “” Then

If the value is not equal to an empty string we simply loop around and repeat the process with the next item in the array. If the value is equal to an empty string we assign a hyphen as the new item value and then loop round and repeat the process with the next item in the array. Here’s how we assign a hyphen to the array item:

arrNumbers(x, y) = “-”

And that’s all we have to do. After we’ve 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:

For x = 0 to 2
    For y = 0 to 2
        Wscript.Echo x & “,” & y & “: ” & arrNumbers(x, y)
    Next
Next

That output should look like this:

0,0: –
0,1: 100
0,2: –
1,0: –
1,1: –
1,2: 200
2,0: –
2,1: 300
2,2: 400

As you can see, each empty value in the array has been replaced by a hyphen. It’s just like magic.

Except, for the fact that it isn’t anything at all like magic.

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 the people of the world, and not just to Americans. For example, on July 4, 836 the Pactum Sicardi was signed, bringing peace to the Principality of Benevento and the Duchy of Naples. Likewise, on July 4, 1359 Francesco II Ordelaffi of Forlì surrendered to the Papal commander Gil de Albornoz. The list goes on and on. The Fourth of July: a little something for everyone.

0 comments

Discussion is closed.

Feedback usabilla icon