Creating a 3D column map

nesnen

New Member
Joined
Nov 14, 2007
Messages
25
Here is my data:
1 3 4 1 5 2
2 4 7 1 0 2
3 4 1 8 2 1
I want to create a 3D column map so that the height of column in the location of the cell is proportional to the value in the cell. For example column height at (2,1) = 3*10 and at (5,2) = 0*10. Run the macro below to see what im trying to do.

Note: The following macro will do it but it draws the map upside down and it impossible to clearly see the heights of all the bars:

Sub ColumnMap2()

For Each c In ActiveCell.CurrentRegion.Cells
magnitude = Abs(c.Value)
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 100 + (10 * c.Column), (150 + (10 * c.Row)), 10, 10).Select
Selection.ShapeRange.ThreeD.Visible = msoTrue
Selection.ShapeRange.ThreeD.Depth = 10 * magnitude
Selection.ShapeRange.ThreeD.SetExtrusionDirection msoExtrusionBottomRight
Next c

Map = ActiveSheet.Shapes.SelectAll()
Selection.Group

End Sub

Is there a way to flip the map? Or can you think of a better way of doing this?

Thanks for your help
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,004
Office Version
  1. 365
Welcome to the board.

Have you considered making a 3D-Column chart (in the chart wizard under column charts, it's the last one listed in Excel 2002)?

I think that may save you from reinventing the wheel...
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Neat idea, but why not just use a 3-D column chart? That would seem to me to be a whole lot easier than this.

<sup>edit</sup> Dang it! Took too long playing with the code. Kinda neat really, btw. Howdy, Matt! <sub>/edit</sub>
 

nesnen

New Member
Joined
Nov 14, 2007
Messages
25
I would use the 3D-column chart If I could figure out how to get rid of the axis walls and set the columns right next to each other like the macro does. I would like to do it with a macro because it will allow me to easily change the properties of the map. the real data that Im dealing with is 100x3 and it is just big black rectangle when I choose the 3D-column chart.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

...If I could figure out how to get rid of the axis walls and set the columns right next to each other...
~nesnen
Ah, shucks, that's right simple there, pard.

Drop in the 3-D column chart. Right-click the walls and pick Clear from the popup menu. Same song, second verse w/ a right-click on the gridlines. And the Z-axis? Yup, yoouuu guessed it! Kill da legend if'n ya gotter and don' wanner.

Now jus' right-click [here a right-click, there a right-click, everywhere a right-click, old McDonald had a mouse...sorry, I got distracted], erm, right-click a column in the chart and Format Data Series... from the popup. Options tab (last one) and drop dee ol' Gap Depth and Gap Width down ta nil and that oughtter getcha within spittin' distance o' what you were lookin' fer.

OH! Well, I just re-read the 100 × 3 thing. Lemme see what a 3-D looks like that is that big. But the above may still help.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
If you go into the 3-D View... dialog and check the Right angle axes it seems to turn out about like your macro's output.
 

nesnen

New Member
Joined
Nov 14, 2007
Messages
25
Thanks for your help but I still like doing it with the macro... to flip the map use a negative depth:

Selection.ShapeRange.ThreeD.Depth = -10 * magnitude

Before writing the macro I saw a post that was looking to do a similar 3d column map for a semiconductor wafer, but i can't find it any more. If i remember right it had a picture of the desired map and ended in a conclusion to find another software to do it with.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,670
Messages
5,654,649
Members
418,146
Latest member
Shnn028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top