GBA-GC connectivity has hardly been Nintendo's most popular idea, but finding games that use it well is well worth it.
Some games like Zelda Wind Waker/Four Swords and Splinter Cell use it without you needing a GBA game or some use it only when you connect the GBA game to the GC game.
These usually are the ones that aren't as good, because it usually just unlocks bonus content (although sometimes good-Metroid Prime connected to Metroid Fusion gives you the NES Metroid on the GC game). It's usually not worth buying the GBA game for the extra content unless you are a big fan (Pokemon is a bit of an exception though- the GBA games really do make Colosseum better as you can play multiplayer).
Here are the games that use the cable and what connecting does (all are without GBA game unless it says otherwise):Animal Crossing: very good, access island and play NES games on GBA as opposed to TV, also you can design patterns on the move. View island on GBA while you are not playing the main game.
Zelda 4 Swords Adventures: The best use of it, no GBA game required, but 2-4 GBA's necessary for multiplayer.
Metroid Prime/Fusion: Unlock NES Metroid on GC, and also a new suit for Samus (the one she wears in Fusion). Nothing for GBA game though.
Splinter Cell: gives you a radar on GBA and a Sticky bomb weapon. New levels on GBA game if you connect that.
Zelda WW: Maps, mini games, help on dungeons, hidden statues to find in each dungeon. See Tingle's friends on WW.
WarioWare inc: Just acts as an extra controller.
Final Fantasy CC: Needed on multiplayer so everyone can play without disturbing everyone to change weapon etc.
Pac Man VS: Person who is pac man gets the GBA and can see the whole area on it, while the ghost players can only see a small area on the TV.
Pokemon Colosseum with GBA game: Update berry program (fixes a glitch that would othewise stop berrys growing after a year or something) and upload/download your GBA/GC team and battle friends.
Pokemon Channel: Update berry program and unlock Jirachi for your GBA game too (it's basically the "Mew" of Pokemon Ruby/Sapphire).
That's all I can think of! If you own a GBA and GC and know friends that own GBA's too, then I can recommend investing in 4 Swords and a few cables!