Your Turn - Drawing Tools

David,

I'm a structural engineer, and I use Excel primarily as a calculation organizer. Excel allows me to set up a calculation and easily repeat it or change and manipulate inputs to achieve a desired output.  Combined with data tables and some simple VBA, it can reduce the time required to do something from 2 weeks to 2 hours.  Many spreadsheets I develop are made better by including a scale sketch or drawing that changes and adapts as the inputs are changed.  I've written a fair amount of VBA in class modules to produce structural sketches and drawings using autoshapes.  There are a lot of external programs out there that do this, but I've found that it's too big of a pain to embed them in a spreadsheet.

Excel's biggest problem for me is that it is unaware of units.  99% of the mistakes I make in spreadsheets happen because I try to add apples to oranges.  If Excel could somehow know that 1ft * 1lb = 1ftlb, and that you can't add 1ftlb to 1in, it would become the only tool I use at work.  MathCAD and some other programs do this well, but they just can't move and manage data nearly as well as Excel can.

Additionally, I would just like to say a massive THANK YOU for the improvements in named range functions in the new Excel.  It will make life a lot easier (and a lot of VBA code unnecessary).

If you're interested, I can send you some examples.

Russ

Debra A. Gewand does some amazing stuff using shapes. See:

www.j-walk.com/.../odd22.htm

Here's a link to her Web site:

http://dagoriginaldesigns.com/

Per your second paragraph, "anything that you cannot do today that you would like to be able to do," a few things.

1. Be able to pass and return 3D references to and from VBA functions (udfs). That'd presumably require adding 3D references to Excel's object model. It'd also require providing access to the iterator that the NPV function uses to processes 3D references in well-defined order.

2. Be able to open multiple files in different drives/directories that happen to have the same base filename. That'd require finally ditching one of Excel's worst design 'features', one that distinguishes Excel negatively even from Word and PowerPoint.

3. Add some text processing functions. At the least worksheet equivalents of VBA's StrReverse and InStrRev. Better, substring searching, counting and replacing functions that use VBScript regular expressions.

4. Make the old SQL.REQUEST function a built-in worksheet function and include it in the Application class's WorksheetFunction collection.

5. Make the XLM functions that only returned information, e.g., GET.CELL, callable worksheet functions. These wouldn't need to be added to the Application class's WorksheetFunction collection.

6. In conjunction with #5, add a mechanism for defining and using symbolic constants. The octothorpe, #, is already a syntactic token, appearing at the beginning of text representations of each error value. Couldn't it be expanded to introduce symbolic constants? Doing so would make it possible to use symbolic constants in, e.g., GET.CELL calls that could be subject to internationalization. This might also allow you to add a true #MISSING value that the stats functions and DATP commands would handle sensibly.

7. Add a built-in facility to fetch values from closed workbooks based on dynamically evaluated references into those closed workbooks. See Laurent Longre's MOREFUNC.XLL add-in's INDIRECT.EXT for example.

xcell05.free.fr/english

<<

From time to time I have used drawing tools in Excel for workflow diagrams and such, but it is excruciatingly fiddly.  If you built in any significant fraction of the kind of data-aware functionality found in products such as Visio I would be thrilled, in spite of the fact that many might justifiably call it bloat.

Better 3-d, pls!

Check out Aron Bolds Class for examples... (I can send if needed)

Cheers

Ross

A welcome addition in Excel 2007 would be the ability to insert formulas into OART textboxes and use themed pattern fills (like in cell styles).

--PSBS

Here are some of the things I use drawing objects for. The first two are fun examples, but the last three show images taken from actual client projects.

www.dailydoseofexcel.com/.../scaled-in-cell-charting

(the existing code seems to work in B2TR)

peltiertech.com/.../VBAdraw.html

(existing code almost works in B2TR except the shapes are offset from the charts)

peltiertech.com/.../HeatMap.html

peltiertech.com/.../InCellBarChart.html

peltiertech.com/.../InCellBarChart2.html

Although they aren't technically a shape (but they do use the shape object) I suppose I would like to see a way to autosize comments though the user interface.  Currently I need to run a macro to do it via code.

The closest we've ever come to doing anything real with shapes is to use them as a button the user can click.  I'm sure you've already thought of that one...

I am an Electrical Engineer in the field of Turnkey substation projects..... I used to use Excel as a "poor mans" AutoCad....to draw single line diagrams....Layouts.... Sections etc...for Proposals...

I developed some basic tools that let me automate drawing of these digrams based on user inputs with VBA (way back with Excel 5)

I sent you some sample files...

One limitation with the drawing objects is precision....while I am aware that Excel is not meant for Engineering drawings....It would have been nice if you could give us ability to control dimensions in mm rather than in cm and inches....for gods sake when will you guys drop inches from you measurement system !!

You also need to define a circle object - as an n sided polygon rather than as a regular ellipse (oval)

Sam

I am working on using VBA and line drawing in Excel (my favorite) to draw metes and bounds descriptions found in deeds.  Like then Northwesterly at 44 degrees 120 ft, thence easterly 42 feet  6 inches, etc.  So far it can do polygons, and the macro draws the lot to scale.  I can change the scale and get a closer look if I want to.  It also alerts me when the polygon does not close.  This would mean an error in the description which would have to be resolved.  I am working on making it work on more difficult or unusual descriptions.  Right now it is basic.

I can't think of anything at this time of night that you can do to make this easier for me.  Maybe I will think of a way later.

Thanks for taking an interest.

One new thing I'd like to see is a drawing layer underneath the worksheet layer as well as one above it.

Expanding Stephens comments...

It would be nice... if you could give us control on layers(multiple) ...Switch on/Switc off - like in Autocad...

AutoCad learnt a lot from Excel (sheets(pages), UI etc)

May be Excel can pick up a few things from Autocad....

I would like a new worksheet type for HTML content, like embedding ASP.NET applications within excel, or reporting from Reporting Services

I don't have any useful comments to put here, but I have seen some really bizarre things customers have done with OfficeArt.  

Like the one where a user created a rectangle, and instead of using a black fill, filled the entire region with itty-bitty little lines all crisscrossing and overlapping each other.  I don't know why, it wasn't like it was that hard to figure out how to fill a shape even in the binary formats.  But people do odd things like this.

Another file a customer sent us included hundreds of text boxes of height 0.  That made them all invisible.  In this case it would have been nice to have an easy way to find these objects the user obviously didn't intend to put there in the first place.  Must have been some kind of copy/paste thing that got out of hand.

Just a couple of the more "creative" ways one can use Excel's OfficeArt :)

Actually, in Word (sorry for the switch in application, but this is about OfficeArt right?), drawing objects often disappear of the page for various reasons.  Since we can't see anything that isn't on the page itself, it makes it really hard to get rid of those hidden objects later.

So in general, having a way to get at objects one can neither see nor click on, but without having to resort to VBA, something throught the interface would be nice.  I think you can already do this in Excel, but in Word I couldn't find anything.