I've never really understood why VPNs are used. I know it stands for Virtual Private Network, but in simple terms how does it work and what is it used for?
It's used to allow secure remote access to a network. The second paragraph from the Wikipedia article sums it up nicely:
It encapsulates data transfers between two or more networked devices which are not on the same private network so as to keep the transferred data private from other devices on one or more intervening local or wide area networks.
It effectively makes the remote machine part of host network for access to resources and other machines on that network. So you can use the company e-mail, intranet and printers for example.
Find out more on Wikipedia
In a nutshell it connects two private networks securely through a public network. VPN
Both answers are right, it can be used to join two networks together, or just to connect single clients to a network or single server over public connection using a secure private tunnel. When the connection is made, all computers involved act as if they are on a private LAN. In the past, when I traveled for work, I used one on my home server so that I could connect from my hotel room to my server and stream music off of it as if I were still at home. This was just a simple one, using OpenVPN. It of course can be used to make networks at two locations on different sides of the country for a large corporation accessible to each other as if they were in the same building.
Apart from the other technical/conceptual answers given, which are perfect, I'll give you an example to illustrate the need of a VPN:
Imagine you need two computers to be connected in the same LAN for some reason, e.g. you use software in your work-laptop that needs to access your company server through the LAN to check for license.
Every day you go to the office, connect the laptop to the LAN, and you use the that software. So far, so good.
Now suppose one day you want, sorry, you need to work from home, so you don't go to the office that day. Now suppose you really needed to do some work, you start that application and "bang" Error: cannot access license server! Contact the administrator, Bill (that geek from the IT department), you'll find it in phone number 555-2347276, he knows this stuff (it would be awesome to get a message that nice, right?).
There, you need a VPN, to connect your laptop at home, through Internet, to your workplace LAN so you can access the server and run your applications as if you were at the office.