If you are using a point and shoot, you don't have much choice. Although you can purchase attachments that provide additional facilities like macro, zoom or fish eye.
If you have a digital SLR, then a macro lens is very useful. However, they are not all that cheap. Having said that, most cameras provide a degree of macro facility. It's just that you can't get that close with say a 50mm lens.
You can add bellows, extension tubes, or close up filters. The latter tend to reduce the quality due to all of the extra glass and also tend to make images look a little curved. I haven't used bellows, but know people that swear by them. Extension tubes can be quite good, but you need to get much more light in. For me personally, I prefer the macro lens.
If wanting to take pictures through a microscope, there are a number of different options. I have a lens attachment that sits inside one of the eyepieces of my Olympus binocular microscope. I find that this works well for me.