Well, we said Challenge 1 in the Experienced Division would be an easy one, and guess what: we were right. (And to think our manager bet her manager that would never happen!) Although there were a couple minor missteps here and there, at the time we wrote this no one who had tried Challenge 1 had failed to come up with a working solution. If only the Olympic Games were this easy, eh?
So just how easy was Challenge 1? To answer that question, what do you say we take a look at the solution we came up with (which is by no means the only possible solution) and then discuss the code and how it works:
Private Sub CommandButton1_Click()
i = 0
Set myDocument = ActivePresentation.Slides(1)
For Each objShape In myDocument.Shapes
If objShape.AutoShapeType = msoShapeRoundedRectangle Then
i = i + 1
If objShape.Fill.ForeColor.RGB = 12419407 Then
objShape.Fill.BackColor.RGB = RGB(0, 0, 0)
objShape.Fill.ForeColor.RGB = RGB(0, 0, 0)
End If
End If
Next
MsgBox "There are " & i & " jellybeans."
End Sub
As you can see, our solution starts out simply enough (and, to be honest, never gets much more complicated). The first thing we do is set the value of a counter variable named i to 0; we’ll use this variable to keep track of the number of “jellybeans” found on the slide. After initializing the counter variable we then use this line of code to create an object reference to the first (and only) slide in our presentation:
Set myDocument = ActivePresentation.Slides(1)
So now what? Well, we have two tasks to perform here. First, we have to count the number of jellybeans (that is, the number of rounded rectangles) found on the slide. Second, we need to identify all the blue jellybeans and turn them into black jellybeans. How are we supposed to do all that and yet still find time to do the remaining OfficePalooza challenges?
Wow, this is starting to get pretty exciting, isn’t it?
As it turns out, the easiest way to count the total number of jellybeans – and to turn the blue jellybeans into black jellybeans – is to loop through all the shapes found on the slide and then pick out the ones of interest. Getting a collection of all the shapes found on a slide is easy; all we have to do is loop through all the items in the Shapes collection. That’s what this line of code is for:
For Each objShape In myDocument.Shapes
As you no doubt recall, our slide includes other shapes – clip art pictures and command buttons – that are of no interest to us; all we care about are jellybeans. (Which is true even when we aren’t working on Challenge 1.) Because of that, the first thing we do inside the loop is check to see if the shape’s AutoShapeType happens to be a rounded rectangle:
If objShape.AutoShapeType = msoShapeRoundedRectangle Then
What if the shape isn’t a rounded rectangle? That can mean only one thing: it’s not a jellybean. Consequently, we go back to the top of the loop and repeat the process with the next shape in the collection. If the shape is a rounded rectangle, then we simply increment the value of our counter variable by 1:
i = i + 1
By the time we loop through the entire collection we will have, one-by-one, counted up all the jellybeans on the slide. (For those of you scoring at home, there are 90 jellybeans on the slide.)
One down and one to go: we still need to turn the blue jellybeans into black jellybeans. Before we can do that, however, we need to identify which jellybeans are blue and which ones aren’t. That’s what we do here:
If objShape.Fill.ForeColor.RGB = 12419407 Then
This is, admittedly, a kind of crazy-looking line of code, but all we’re doing here is looking at the value of the Fill.ForeColor.RGB property, the property that tells us the fill color of the object. If the fill color is equal to 12419407 then we know we’re dealing with a blue jellybean (as opposed to a red, yellow, green, or orange jellybean).
So how did we know that a fill color of 12419407 represented a blue jellybean? Well, being highly-trained professionals, we simply took a glance at the slide and then calculated the RGB value for the blue jellybeans. When that failed (miserably) we made a copy of the slide and removed all the items except for two blue jellybeans and the command button. We then attached the following subroutine to the command button, a subroutine that reports back the fill color for each object on the slide:
Private Sub CommandButton1_Click()
Set myDocument = ActivePresentation.Slides(1)
For Each objShape In myDocument.Shapes
MsgBox objShape.Fill.ForeColor.RGB
Next
End Sub
When 2 of the 3 message boxes reported back a value of 12419407 we knew that had to represent the fill color of the 2 jellybeans. (In case the suspense is killing you, the fill color for the command button is 16777215.)
If we don’t have a blue jellybean we, again, pop back to the top of the loop. And if we do have a blue jellybean? Then we execute these two lines of code, which change the fill color and line color of the shape to black (which has an RGB value of 0, 0, 0):
objShape.Fill.BackColor.RGB = RGB(0, 0, 0)
objShape.Fill.ForeColor.RGB = RGB(0, 0, 0)
And then it’s back to the top of the loop, where we repeat the entire process with the next shape in the collection. When we’ve finished looping through the collection we simply echo back the number of jellybeans we found (the value of the counter variable i) and then call it a day:
MsgBox "There are " & i & " jellybeans."
Boy, that was easy, wasn’t it?
