World Map Excel Chart

**S M C** · 07-02-2011, 11:35 PM

Alright, I have been working on this assignment, and came up with a nice looking Chart representing all the countries of the globe. Pretty fascinating and intriguing at the same time. Thought this would help the community a great deal. Anybody interested, please put in a post.

World Map Chart.jpg

**obed_cruz** · 07-04-2011, 08:33 PM

**S M C** · 07-09-2011, 04:27 PM

Thanks for the interest Obed. Here's attached the working version. I don't think anything beyond a bubble chart or a scatter chart would be used. So I have coded for the macro to work on both the chart types.

**Admin** · 07-09-2011, 05:01 PM

Hi S M C,

**obed_cruz** · 07-09-2011, 06:12 PM

Sam, is an interesting job, thanks for sharing.

Grettings.

**know_ware** · 07-11-2011, 02:58 AM

**S M C** · 07-13-2011, 12:17 AM

thanks a lot know_ware....glad it help..

Felt the same way about the charts in your site too..... interesting methodology....

**S M C** · 07-13-2011, 12:41 AM

know_ware, could you post a working sample here?

**S M C** · 06-21-2012, 08:42 AM

I have been getting PMs from members who were interested in the World Map Chart. One of the recent queries I received seemed like one that is generic, and a lot of members may be interested to know. I'll quote the query here, without refering to the usedname.

Hi S M C,
I saw your post of the world map (which is awesome) and am wondering how you were able to locate the countries based on longitude and latitude? I can see how you linked the bubbles to the columns of the "DataBase" tab, but I cannot see in the code where you placed the bubbles. I am trying to make a map of the USA and to make it easier for future changes, I would like to be able to add in the longitude and latitude to place the cities instead of manually placing them. I am fairly new to excel, especially vba, so I am not sure if I should even attempt this.

Thanks in advance.

Hi,

You can find the code in the macro editor. If you need to go in to the lines step by step, open the workbook in macro disabled mode. Search for the line

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

and below that add

Code:

stop

. Save and reopen the file in Macro enabled mode.

Now, when you add or remove a country, you will get in to the code. You can now hit F8 to run through each line of code.

On the longitude and latitude, for this to work, you'll need an exact fit to scale map that precisely represents the geographical landscape. You'll then need to fix the chart axes based on the position of the map (which means the starting and ending latitude and longitude of the map, since you are looking to plot a country). I on the other hand have used -180 to 180 and 90 to -90 since I was using the entire globe. So you'll have to figure that out. Plus, you may want to plot the states, so you will need the longitude and latitude of all the states within USA.

**danderson2692** · 07-06-2012, 09:22 PM

I have just finished an add-on to what S M C has already done, adding graphs for continents, countries, and regions. I have not changed any of S M C's code, except to rename some of the sheets. I did delete a couple of the world graphs that were previously included in the file, but they can be added back from S M C's file.

Here are the graphs that are now included:
World, Africa/Middle East, Asia/Middle East, Central/South America, Europe, North America, Oceana, United States, USA-Midwest, USA-Northeast, USA-Mid Atlantic, USA Southeast, USA-South, USA- Southwest, USA-Northwest, USA-Central, Germany, U.K.

Note: to alter the "DUMMY" on any of the graphs to resize the bubbles on each sheet individually, right click on the graph and choose "Select Data" and it is the last point.

Let me know if there is a better place for me to upload the file:
https://docs.google.com/open?id=0B9i...UJocGdGaDRJakE

If anyone wants to continue expanding this file, the best way to get maps of the correct format (straight longitude and latitude) is to find a huge map on google and crop it to the area needed if you cannot already find one of the area you are looking for. Something else to look at is a more efficient macro so more locations can be updated faster (there is a 256 cap on locations with a bubble graph).