Creating a 3D column map

[nesnen]

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

 

[Oaktree]

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]

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.

^edit Dang it! Took too long playing with the code. Kinda neat really, btw. Howdy, Matt! _/edit

 

[nesnen]

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]

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

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]

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.