Show off your vba project 2

[electricpete]

Just for fun, does anyone have any interesting vba projects they would like to share?

I'll bet the folks on this forum have generated loads of good stuff. Simple utilities? Engineering calcs? etc? I'd be interested to see them.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[MintJulep]

We've run a major project using some VBA that I hacked together.

It automates a whole bunch of repetitive tasks that the administrative task was doing in the past.

It's hard to show off without the whole PowerPoint presentation that shows the whole "before" process that has be automated however.

 

[Golestan]

That is a great idea if we could see some of the neat and tricky vba's. I myself am trying teach myself how to write them. I know Basic and Fortran from a longtime ago, but have not done any vba.
regards,

 

[electricpete]

ok, I've got one.

Attached is a program that I think is practical (if you have kids) and fun.

http://home.comcast.net/~electricpete/eng-tips/vbamathgame/vbamathgame.xls

http://home.comcast.net/~electricpete/eng-tips/vbamathgame/yeah.wav

http://home.comcast.net/~electricpete/eng-tips/vbamathgame/uhoh.wav

http://home.comcast.net/~electricpete/eng-tips/vbamathgame/fanfare.wav

To run it, you would need to download all those files and put them in the same directory, then open the excel file and allow macro's (there is no malicious code in there, I assure you). I tried to run it direct from the web and it seems to get very confused trying to access the wav files.

It is a math game for elementary age kids. It asks math questions (equations) repeatedly and they provide answers. There is a timer displayed which counts down to 0.

Then the results of the game are displayed (LastResults tab), including tabulation of the equations with the wrong answers highlighted and correct answers supplied. The summary is erased/overwritten for every game.

A more permanent summary of the game goes into the TheHistory tab (kind of like high score on a video game), including name of player, number of questions right/wrong etc.

The controls available to change (for example before the game) are:
type of math question (+.-.*,/)
timer setting (how many seconds in a game).
MaxNumber – what is the highest number that you are going to add, subtract, multiply (usually 10 but my 8-year old sets it to 12).
username (for history loggin purposes)

Some feeble attempts at effects:
A wave file plays "yeah" (with clapping) upon entering correct answer (my voice)
A wave file plays a soft "uh-oh" upon incorrect answer (my voice)
A wave file plays trumpet when game is done and summary results displayed.
(by the way, there are three wave files that you need to have available in the same directory).
The timer flashes between purple and white every second during countdown and then red/white during last 15 seconds.

It is almost the same game as described in "Microsoft Excel VBA Programming for the Absolute Beginner" by Duane Birnbaum.

I read his code for ideas, then created my own almost from scratch. I used his code for the timer which is a complicated function to implement (and complicates the logic of the program control since timer essentially runs in parallel with other processes). I added the sounds and the extra sheets for logging LastResults and History.

If you have small kids, I think they'll enjoy it. Although... when I showed it to my daughter today, her excitement lasted only about 10 minutes, then wanted to go on the internet for better games ;( That was a little disheartening considering I spent somewhere between 6-8 hours on this all total. But... I guess I still learned about vba programming, which was my goal.

A few minor puzzles that I still haven't solved:

1 - I wanted to be able to use protection to keep the user limited to the few cells that he needs to change (the operator, the answer, the username, the timersetting, and the maxnumber).

I unlocked these cells as well as the timer and then protected the sheet. This caused a program error whenever the timer tried to change the color of the timer cell (the flashing effect). I'm not sure why this happened because I unlocked that cell. The only way I could get it to work was to turn all protection off.

2 – Is there a way to change the module names to something more descriptive than "module1", "module2" ?


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[ck1999]

electricpete,
if you click on the module name and then click on the properties icon in the toolbar at the top (or hit f4) it will display the properties window and the only property is name

ck1999

 

[electricpete]

Thanks. I had previously tried right-clicking and that brought up "vba project properties" with a "project description" that didn't have any effect on the displayed name when I changed it...so I thought I had explored the properties. Now one mystery solved. (I have to work on getting more familiar with all the tools available in that programming environment).

Here is another small mystery with the program to me: Every time the program is waiting for user input, all of the vba procedures have terminated. They are awoken whenever a second elapses (for the timer update) or whenever the worksheet_change event occurs (signaling user input). The interesting thing to me is why the global vba variables in module1 don't disappear during this waiting period since all the procedures (subroutines) are done. Yet when the program finally terminates and summarizes the results of the game, the variables disappear.

By process of elimination, it can't be the worksheet_change thing that keeps the variables active (since it remains active even after the program finally terminates and summarizes the game), so it must be the timer. I guess the application program is smart enough to figure out that while there is a timer set (via Application.OnTime command), the program is still running and the varibles shouldn't be released (?)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[Clyde38]

Try posting in the VBA forum......

http://www.eng-tips.com/threadminder.cfm?pid=766

 

[electricpete]

I didn't know we had one of those (now I do).
Thx. (a star for you)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.