What is the best way to draw in Excel using VBA?

Question

I'm a civil engineer designing a program that allows the user to define number of cross sections of a roadway and then calculate the quantity of the different materials used to build the roadway layers. I need to be able to plot a representation of the cross section that the users has defined. I'm not sure if this would be best accomplished by plotting various series on a chart, or drawing shape objects. Does anyone have any thoughts?

Answer 1

Yeah, not only is Excel pretty good for this, it's also pretty common to use it for this. The Newton Excel Bach blog may be where you want to spend some good time - it's an Excel for engineers site. He's got a great series on drawing with Excel. Here's one that addresses your immediate question: Drawing in Excel 7 – Creating drawings from coordinates

Answer 2

Since I know nothing about your problem domain or your programming skills, I can only give some general thoughts:

Excel is really good for modeling and building certain prototypes. Modeling this problem and building some charts by hand should give you and your users a good idea if the Excel solution is going to fly. If you can't get the graphics you want I would look elsewhere. Perhaps Visual Studio and Visual Basic or C#. These have mature drawing capabilities and also charting controls in recent editions.

Excel VBA has a pretty good programming layer for charts. You can also draw custom objects with VBA. I have not done this but I am sure there are references on the web. In any event, if the manually built Excel prototype looks good, it might be worthwhile to automate it with VBA.

Another factor is how many, and of what skill set will the users be? Fewer users, who know Excel pretty well make a case for using Excel. Supporting a large number of users could become onerous as it is possible to change the code in an individual file.

Finally, how long will this application be around? Versioning Excel applications can be done, but it easier to do this with more sophisticated programming environments. Also if you are going to continue to add features you might run into a wall with VBA's feature set. Hope this helps.