Do you know that you can write VBA worksheet functions that modify shapes on a worksheet? I didn’t know this until about an hour ago.
Paste the following UDF into a VBA module:
With ActiveSheet.Shapes(ShapeNumber)
.AutoShapeType = ShapeType
.Visible = Vis
End With
End Function
Then, add a shape to the worksheet and enter this formula into any cell:
=ModifyShape(1,55,TRUE)
The first argument is the shape’s index number. The second argument is a value that represents the shape’s type (values from 1-138 are supported). The last argument determines whether the shape is visible. The arguments, of course, could use cell references. Change the second argument and watch the shape change its shape. Change the third argument to FALSE and watch the shape disappear.
This sort of thing is a lot more useful in Excel 2007, because embedded charts are contained in shapes. Therefore, you can write formulas that manipulate the chart object properties such as size, position, and visibility. Even better, your UDF can even access the Chart object contained in the shape, and manipulate that. Here’s a simple example:
‘ Excel 2007 only
ActiveSheet.Shapes(CName).Chart.ChartType = CType
End Function
This function assumes an embedded chart. It uses two arguments: the ChartObject’s name, and the chart type (e.g., 5 is xlPie, -4100 is xl3DColumn, etc.).
Even better — a function that allows you to specify a chart’s min and max scale values:
‘ Excel 2007 only
With ActiveSheet.Shapes(CName).Chart.Axes(xlValue)
.MinimumScale = lower
.MaximumScale = upper
End With
End Function
The ability to adjust a chart’s axes based on calculations has long been on the Excel wish list. I wonder if Microsoft even knows this is now possible?
This has a huge range of applications John, good pickup.
The first or second M$ Excel for the Mac used to have a Zoom functionality, where you could zoom in on a set of data in a chart and the axis scales adjusted automagically, don’t know if it still does.
Hui…
It’s possible to create a line-chart (or any chart for that matter) from the UDF as this example shows.
This is very good. Simple shapes work great.
But does anybody know how to manipulate SmartArt shapes (diagrams) in Excel 2007 with VBA? I wanted to create simple Organization Chart (hierachy), tried to record macros but nothing is recorded. Google search also does not bring up much.
Thanks,
Aivars
Interesting. We’ve needed VBA procedures in the past to link axis parameters to cells. Hmmm, I guess we still do, in the form of UDFs. At first it sounded like a magic workaround to provide the capabilities we’ve been asking for.
The small charts Rob mentioned have been discussed here in great detail in several posts. It’s a neat capability, but prone to instability, as the shape manipulating UDFs seem to crash and hang from time to time. There are a couple commercial sparkline charting programs that make use of this technique. I tried one for a while, it had a very un-Excel-like interface and was lacking in a number of obvious feature areas, and caused phantom VB projects to remain in the VB Editor after the parent workbooks were closed.
Aivars –
The RTM (Rushed To Manufacture) version of Excel 2007 suffers from a lack of macro recording for many new features. The object browser seems to show the new aspects of the object model, but I’ve found some of the newer properties to be rather obscure. If it’s something that’s compatible with 2000-2003 shapes, I’d suggest recording the macro in the earlier version and pasting it into 2007.
Don’t feel so bad, PowerPoint 2007 has lost the macro recorder altogether. The Microsoft suggestion is to simply open the VB Editor and write the code from scratch!
I’m waiting for comlete documentation of the new object model, but it’s not even available in the online version of help.
I like this a lot. I like Rob van Gelder’s “in cell charting” even more. I have one problem though. These functions appear to re-set my undo list every time they recalculate.
Does anyone else get that, or is it just me? Is there anything I can do about it?
I didn’t notice that, Doug. It’s the same thing that happens when you use an event procedure. Wiping out Undo is a major problem with Excel. It doesn’t happen in Word — and Word even lets you undo a VBA macro. Why can’t Excel do that?
Hey John……
Don’t you have a blog that you could be updating???
You said a week but it already seems like a few months (okay…so it’s been six days….was that a business week or a week week??)
There are a lot of entertainees that are going through serious withdrawal waiting on their entertainer.
Come on back. I miss you (don’t that that personal) and I know I’m not alone.
That’s interesting…
Never really considered even trying to use a UDF on a chart, hmmmm… Wonder what trouble I can stir with that one. Disabling the undo though, that’s probably gonna nix it as a common tool for me. Still, certainly worth loggin away for later ref.
I noticed some comments on wanting formula driven chart axis control. Ya know, if you’re looking for ways to maintain an upper bound on a chart with a formula you could always assign a cell value to a hidden series… 9 outta 10 times that solves it for most. Granted, doesn’t do ya much good if you’re wanting to allow the series to go beyond the axis boundaries.
Aaron –
That’s a good, simple technique for the maximum, but it won’t help for the minimum. Excel tends to set that to zero if the ratio of the min to the max of the data is less than about 5/6.
Given the instability of UDFs that operate on shapes, I think the chart scale UDF is probably an interesting but not preferred alternative to a worksheet_change event pointing at cells containing formulas that derive axis limits. For example:
Link Chart Axis Scale Parameters to Values in Cells (artivle on my site)
http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html
and
Tushar Mehta’s AutoChart Manager add-in
http://tushar-mehta.com/excel/software/autochart/index.html
Hi John, a bit off topic but I just checked out Excel 2007 at Barnes and Noble last night. My name is now in a book! I’m (in)Famous :). Now if only someone in my family actually had a computer or knew what Excel is… Thanks for letting everybody in your acknowledgements.
Charles
I’m glad that i was able to make you famous, Charles. For the record, here’s what it says:
A big thanks is due Charles Chickering, for plagiarizing my code to help the multitudes on the public newsgroups.
I’m writing an application that uses Excel shapes + VBA (subs, not functions) to draw accurate scaled plots of various geometric shapes on the spreadsheet; i.e. circles need to appear as circles, and angles need to stay the specified angle.
I’m having two problems:
1) Lack of documentation – it seems that the only way to discover how the shapes work in detail is trial and error.
2) Changes from 2003 and earlier to 2007; in particular the way that angles for arcs is specified seems to have completely changed.
Does anyone have a good source of documentation of the details of using shapes, and is there any way to handle the changes from 2003 to 2007, other than writing two different sets of code for the affected shapes?
TIA Doug
Hello All,
I have created a VB6 application, that generated an Organization Chart, dynamically and programatically, based on data from a database. It all works great on Word 2003, but when run the same code on a system which has Office 2007 (or both Office 2003 & 2007) installed on it, i get compilation errors, as the Word Object in 2007 does not support same methods for creating shapes etc like Word Object in 2003 had. I have read that the basic architecture has changed in 2007 and SmartArt has been introduced instead.
http://technet.microsoft.com/en-us/library/cc179188.aspx
http://www.eggheadcafe.com/software/aspnet/32228731/organization-chart-in-wor.aspx
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_22488705.html
Kindly let me know how can i make my program work on systems with Office 2007. And if this is not possible, how can i code to create organization chart for Word 2007?
I shall be grateful.
Thank You