Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in

Learning Exel VBA

Status
Not open for further replies.
normm

normm

Structural
Jan 29, 2008
74
0
0
GB
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.
 
Replies continue below

Recommended for you

Sort by date Sort by votes
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



as for your request,

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
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
(This subroutine was originally developed by the above-mentioned Jon Peltier, then slightly extended and corrected by various Eng-Tips contributors.)


[sub][ ]—————————————————————————————————[/sub]
[sup]Engineering mathematician / analyst.[ ] See my profile for more details.[/sup]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.











Doug Jenkins
Interactive Design Services
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kevz
  • Locked
  • Question
ETABS API using Excel VBA
Replies
2
Views
11
IDS
IDS
Shazeb Mirza
  • Locked
  • Question
self learning 1
Replies
5
Views
6
socks15
socks15
AliBaig1991
  • Locked
  • Question
Learning the art/science of structural idealization
Replies
2
Views
3
SWComposites
SWComposites
MegaStructures
  • Locked
  • Question
Learning Programming for Structural Engineering 12
2 3
Replies
55
Views
56
1503-44
1503-44
MidwestSE
  • Locked
  • Question
Resources for learning structural design in new industries?
Replies
4
Views
5
JoshPlumSE
JoshPlumSE
Back
Top