World Map Excel Chart

[S M C]

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.

58

[obed_cruz]

Hi, SMC

It would be great to see that project.

Thank you.

[S M C]

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]

Hi S M C,

:cool:

:cheers:

[obed_cruz]

Sam, is an interesting job, thanks for sharing.

Grettings.
:cheers:

[know_ware]

I have been teaching a similar class for a long time. See http://www.lulu.com/product/ebook/geographic-analysis-of-data-using-ms-excel/15308990.

But, I LOVE yours. It is perfect for the highlevel view that many executives require.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=316705#p316705 (https://eileenslounge.com/viewtopic.php?p=316705#p316705)
https://eileenslounge.com/viewtopic.php?p=316704#p316704 (https://eileenslounge.com/viewtopic.php?p=316704#p316704)
https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597 (https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597)
https://eileenslounge.com/viewtopic.php?p=316412#p316412 (https://eileenslounge.com/viewtopic.php?p=316412#p316412)
https://eileenslounge.com/viewtopic.php?p=316254#p316254 (https://eileenslounge.com/viewtopic.php?p=316254#p316254)
https://eileenslounge.com/viewtopic.php?p=316280#p316280 (https://eileenslounge.com/viewtopic.php?p=316280#p316280)
https://eileenslounge.com/viewtopic.php?p=315915#p315915 (https://eileenslounge.com/viewtopic.php?p=315915#p315915)
https://eileenslounge.com/viewtopic.php?p=315512#p315512 (https://eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315744#p315744 (https://eileenslounge.com/viewtopic.php?p=315744#p315744)
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512 (https://www.eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315680#p315680 (https://eileenslounge.com/viewtopic.php?p=315680#p315680)
https://eileenslounge.com/viewtopic.php?p=315743#p315743 (https://eileenslounge.com/viewtopic.php?p=315743#p315743)
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326 (https://www.eileenslounge.com/viewtopic.php?p=315326#p315326)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40752)
https://eileenslounge.com/viewtopic.php?p=314950#p314950 (https://eileenslounge.com/viewtopic.php?p=314950#p314950)
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940 (https://www.eileenslounge.com/viewtopic.php?p=314940#p314940)
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926 (https://www.eileenslounge.com/viewtopic.php?p=314926#p314926)
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920 (https://www.eileenslounge.com/viewtopic.php?p=314920#p314920)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[S M C]

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

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

[S M C]

know_ware, could you post a working sample here?

[S M C]

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
Private Sub Worksheet_Change(ByVal Target As Range) and below that add
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]

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=0B9it86HdgWZBQUJocGdGaDRJakE

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).

[danderson2692]

I have added to the workbook created by S M C to add views of regions. Now, there are graphs for:

World
Africa/Middle East
Asia/Middle East
Central/South America
Europe
North America
Oceana
USA
USA-Midwest
USA-Northeast
USA-Mid Atlantic
USA-Southeast
USA-South
USA-Southwest
USA-Northwest
USA-Central
Germany
U.K.

I have taken out some of the duplicate graphs, but you can add those back from the original file by copying them in. I have changed a couple of sheet labels in the macro, but the rest is the same way as S M C wrote it.
As far as upgrading it from here goes, I think making additional graphs for more countries/regions would be good, and creating a macro that can handle large numbers of locations. The plot currently has a maximum of 256 points, so finding a way to add more would be good as well (maybe overlaying multiple plots).

Here is the file:
https://docs.google.com/open?id=0B9it86HdgWZBQUJocGdGaDRJakE

[Admin]

Hi danderson2692,

Welcome to ExcelFox !!

If you could attach excel files of different versions, that would be great.

[S M C]

danderson2692, great work with the charts mate. Loved the initiative. And yes, thanks for sharing it here.