Learning Exel VBA

[normm]

I am trying to learn and practise VBA coding. I am absolute beginner. I find most references and books written on this subject are written with financial people in mind and soon drop me in subjects like Pivot table etc which are not of my immediate interest. So I have given myself a very simple starter project:

I know the x y co-ordinates of a series of points. Which represent nodes of a shape like rectangle, circle, polygon etc. How can I write a VBA program that will draw the shape to scale from these node points using Excel.

If you can help me writing the steps or help me where can I find VBA code examples of such simple tasks in a book or website, it will get me started and I will appreciate it.

 

[OffshoreWindStructures]

You will get better and faster results if you learn the official terminology used by excel and vba and use them as keywords in searches (Often supplied in the excel tooltips). Microsoft has an official reference page so you don't need to go to financial examples for the basics

https://learn.microsoft.com/en-us/office/vba/api/overview/excel

https://learn.microsoft.com/en-us/o...ted-with-vba-in-office?source=recommendations

as for your request,

https://peltiertech.com/Excel/Charts/VBAdraw.html#TraceXYcode

You won't make much progress if searching for examples is already stumping you , so try to improve that as it will be very beneficial in the long run

 

[JoelTXCive]

Normm - There is a Visual Basic forum on Engineering tips.

I have posted in there before and received a lot of help.

Also, Youtube is a good source of videos on vba. And the website UDEMY has self paced classes. Some of the classes are good; and some bad.....but they are very cheap (in the $20 range), so it's not a huge investment.

I'm a VBA fan.......people keep saying it's a dead programming language, but I disagree!

 

[IRstuff]

I would suggest moving this question to ET's sister site

https://www.tek-tips.com/

which is more oriented to programs and programming.

Alternately, you might consider Python for programming, since it's a bit more user-friendly, and there are gobs of programming libraries, including graphing.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[271828]

I've steered clear of this stuff because of the macro security warnings. Aside from my usage, I'd like to be able to share with colleagues who (like me) aren't experts at directing Office to enable this or disable that. Is there a way to use VB in Excel without triggering that crap?

 

[DanKile]

You could use the xy chart to draw. Or use Worksheet.Shapes.AddPolyline to draw directly on the sheet. Another option is to generate a valid dxf file and open it in any cad program.

If you sign the spreadsheet with certificate that is installed on all the computers that will use it, the security warning should go away for that spreadsheet.

 

[Denial]

The book that got me started with VBA many years ago was "VBA for Dummies".[ ] I have recommended it to various friends since then, and all have found it a great help.

You ask about using a set of defined (X,Y) points to produce a properly scaled plot.[ ] This is not an easy task (and is a feature Microsoft should probably have included as an intrinsic part of Excel). [ ]This site's FAQ section provides a VBA subroutine to impose equal scales on an XY-chart.[ ] See

https://www.eng-tips.com/faqs.cfm?fid=1901

(This subroutine was originally developed by the above-mentioned Jon Peltier, then slightly extended and corrected by various Eng-Tips contributors.)


_{[ ]—————————————————————————————————}
^{Engineering mathematician / analyst.[ ] See my profile for more details.}

 

[JoelTXCive]

Normm - are you also using Autocad?

I have not done it, but you can write programs in AutoCAD's LISP language that will directly draw items for you.

I think you can also write VBA programs that somehow get converted to a LISP routine; and then Autocad will draw.

 

[IDS]

I really don't agree that Tek-Tips is a better place for advice on this. If the aim is to learn VBA for engineering applications, then this site is just the place to help. (There is a VBA forum though, which would be a better place to post next time).

I have a series of posts on my blog on how to use VBA to draw in Excel, covering both use of "shapes" and XY graphs. They are a bit old now (2008), but everything there should still work.

Note that link no. 7 below specifically deals with plotting from a list of coordinates, but they all should be useful.

https://newtonexcelbach.com/2008/06/01/drawing-in-excel-1/

https://newtonexcelbach.com/2008/06/22/drawing-in-excel-2/

https://newtonexcelbach.com/2008/07/04/drawing-in-excel-3/

https://newtonexcelbach.com/2008/07/15/drawing-in-excel-4/

https://newtonexcelbach.com/2008/07/26/drawing-in-excel-5-shape-list/

https://newtonexcelbach.com/2008/09/17/drawing-in-excel-6-getting-shape-properties/

https://newtonexcelbach.com/2008/11/11/drawing-in-excel-7-creating-drawings-from-coordinates/

https://newtonexcelbach.com/2009/01/05/drawing-in-excel-8-scaling-and-trimming/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

One other thing worth mentioning:

The articles above were written in 2008/2009, and say that Excel's macro recording function (in Excel 2007) does not record any actions on shapes. That functionality has been reinstated, so if you want to find out how to draw a line using VBA the easiest way is to switch on the recorder, draw a line, perhaps change its colour and width, then switch off the recorder and examine the code.

There is still a fair bit that will needed to be added for a usable program; converting XY coordinates to screen coordinates for instance, but it is a good start.

More generally, the Excel object model is huge and complex, and the documentation is not always easy to follow, so using the macro recorder can be a great starting point to getting some working code.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/