Since most programmes are coded against the original architecture they won't notice the additional memory - but if they are being run with other programmes there will be less paging so they will seem more responsive if you looked at them side by side on machines with and without the switch.
Some will have an actual speed increase depending on how big their 'footprint' is and what level of data is managed internally - so for example a graphics programme like photoshop or something that does video encoding will (probably) have been recoded to take advantage of whatever memory is available.
I believe that any update specifically for Vista WILL behave better (but mileage may vary) whereas the picture is less clear for prior OS.
So - Extra memory is still better if you are running several programs at once, if you are running Vista (which needs all the help you can give it) or if its a modern version of a memory intensive application such as Video or photo editing - (or running a server, in particular virtual servers, though in that case you would NOT use the switch - just cram in a lot of ram
If your majority use is surfing, email and office programs you probably only notice the difference if you compared two machines side by side.