Thanks for all the replies and suggestions.
Re not working with the ignition - I don't expect them to work when the ignition is off, but I was trying to establish whether any of your cars have working horns with the ignition on but no engine running. Maybe the horns only work when the engine is running (which is probably the only scenario you would need them to work)? But strange that they try to work by making a weak noise when ignition is on and no engine running.
Having already removed the horn push, cleaned the copper strip (which from what Richard V6 said is the earth) and checked with a militmeter that it is working OK, plus having cleaned and checked the connectors to the horns, I think the next step is to remove the steering wheel and check the brass slip ring and the connectors/wiring from the light switch (purple/black) as RichardV6 suggests.
The battery was new last year and is regularly connected to my battery optimiser, so should be OK. The number 6 fuse is OK (although replaced to be sure) and connections clean.
