Experienced Challenge 1: That's a Lot of Jellybeans ... Isn't It?

Office Programs Required

Microsoft PowerPoint 2007

Goldfish Awarded


Deadline for Submission

Wednesday, April 22, 2009 (8:00 AM Pacific Daylight Time)

Files Required

Experienced_challenge_1.pptm in the OfficePalooza Challenge Pack.

Listen, we understand. It’s the opening day of OfficePalooza 2009, and you’re a little nervous. After all, the best and the brightest VBA (Visual Basic for Applications) gurus are competing in OfficePalooza; how can you possibly hold your own against the likes of them?

Relax; you’ll do just fine. And if that doesn’t calm you down, maybe this will: challenge 1 in the Experienced Division is an easy one. Go ahead, complete the challenge, pick up your 5 goldfish, and then worry about the remaining events.

So how easy is challenge 1? Pretty darn easy. To complete this challenge, grab a copy of the file Experienced_challenge_1.pptm, found in the OfficePalooza Challenge Pack . Experienced_challenge_1.pptm is a one-slide PowerPoint presentation that looks a lot like this:

Experienced Challenge 1

After you finish admiring the sheer beauty of this slide you need to get to work; you need to write a VBA subroutine that does two things:

First, it must count – and report back – the number of jellybeans found on the slide. Jellybeans are the little graphics that look like this:

Experienced Challenge 1 

How can your subroutine determine the number of jellybeans found on the slide? Beats us. But we’ll give you a hint: all the jellybeans, and only the jellybeans, are rounded rectangle shapes.

Important. Your subroutine should count all the jellybeans, not just the red ones and not just the green ones. 

Second, there are a lot of blue jellybeans on this slide. As it turns out, we don’t really like blue jellybeans; we like black jellybeans. Therefore, your subroutine also needs to magically turn all the blue jellybeans into black jellybeans. In other words, when the subroutine finishes the slide should look something like this:

Experienced Challenge 1 

To complete the challenge, right-click the Count the Jellybeans button and then click View Code. That will open up the Visual Basic Editor, along with the following subroutine:

Private Sub CommandButton1_Click()

End Sub

All you have to do is enter the code for counting – and transforming – the jellybeans as part of this subroutine. (Note that all the code must be included in this subroutine; this is due to the way we have to test this code.) Your subroutine should count all the jellybeans and then pop up a message box with the final count; the subroutine should also turn all the blue jellybeans into black jellybeans. That’s all that you – and your subroutine – have to do.

See? We told you this first one was easy.

Submitting Your Entry

The OfficePalooza sweepstakes is over, but you’re welcome to try the challenges and learn on your own. Good luck!