For gamers, the standalone executable will be pretty useless. However, since the executable is available for free as long as it's for non-commercial use, I see no reason why mod authors couldn't develop free games using the executable.
On the surface, the Unreal engine executable looks like it might be a great platform for aspiring gamer programmers and existing mod teams. However, it's worth noting that GarageGames offers its Torque Game Engine SDK, which includes the entire engine source, for only $100. Torque can be used for commercial projects, too.