I am not familiar with the Pi camera, but here is what I have found to work with some small digital cameras.
The cameras in this post are the ones with a lens that is threaded onto the body, in front of the sensor. They have a set screw to hold the lens in place and usually some very weak glue. Once you find a screwdriver small enough, loosen the set screw and twist the lens to move it further away from the camera body
Originally, the lens is set to focus everything from a few feet to infinity.
Now we get to the math. This is known as the "Lens Equation":
1/f = 1/do + 1/di
f = Focal length of the lens
d0 = distance to the object you are looking at (from the center of the lens)
di = distance of the image (from center of lens to the camera sensor)
To get a larger image, more magnification, increase the distance between the lens and the image sensor, that is (di) in the above equation. Since the focal length of the lens (f) has not changed, the only way to satisfy the above equation is to decrease (d0), the distance between the lens and the object; move the object closer.
Or you could just put a magnifying glass between the camera and the object -- or mount the camera on a microscope in place of your eye.
Please let us know what works for you. We like to learn too!
Cheers,
Tom
p.s. A further explanation can be found at:
https://www.physicsclassroom.com/class/refrn/Lesson-5/The-Mathematics-of-Lenses
p.s. If you are familiar with electrical circuits, the Lens equation is also the equation for two parallel resistors