Using VB graphics commands in VBA

Question

Using VB graphics commands in VBA

vbengineer (Mechanical)

(OP)

2 Jun 08 19:58

I am attempting to use the graphics commands/methods described in Rod Stephens "Visual Basic Graphics Programming" in VBA with Excel. I'm hoping to be able to add a graphics window that shows the relative positions and orientations of a couple of rectangles that represent two colliding vehicles. The positions and orientations are based on data listed on Sheet1 of an Excel workbook.

In an attempt to get my feet wet, I created a new module in the VB Editor and borrowed some simple code from the book that draws a rectangle using the Line command after changing the scale using the ScaleX and ScaleY commands. Excel/VBA did not like the Scale commands but did not seem to mind the Line command.

So I commented out the ScaleX/Y commands and filled numbers directly in the Line command. The code executes successfully (at least no errors) but I never get a window that opens up to display the rectangle.

'wid = ScaleX(5, vbPixels, ScaleMode)
'hgt = ScaleY(5, vbPixels, ScaleMode)
wid = 50
hgt = 50
Line (10, 10)-Step(wid, hgt), vbRed, BF

Am I missing something in the availability of the VB commands in VBA? Shouldn't I have all of VB available to me?

Maybe this is so simple that I'm just missing the boat on this? I am a novice at the graphics part of the VBA development, but have done a bit of programming in VBA .

I am running Excel 2003 on a Vista desktop.

I appreciate and help.

electricpete (Electrical) · Answer 1 · 2008-06-02 20:46:48

According to OReilly's VB reference, "line" is a method of the printer object. (Perhaps it is a method of some other object, but I haven't found any... it is certainly not a method of a chart or chartojbect).

Also according to OReilly's VB reference, the printer object (and hence its line method) is not available in VBA.

Quote:
Since it's part of the VB library, the Printer object isn't available to VBA applications. When you write a VBA (as opposed to VB) program, you simply make use of the host application's own built-in printing functionality.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 2 · 2008-06-02 21:01:38

But check out the shapes.addline method for worksheets and charts.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

electricpete (Electrical) · Answer 3 · 2008-06-02 21:05:41

If you have a worksheet named "Sheet1" and a chartsheet named "Chart1",try the following:

Quote:
Sub excellines()
ULHx = 10
ULHy = 10
wid = 50
hgt = 50

Sheets("Chart1").Shapes.AddLine(ULHx, ULHy, ULHx + wid, ULHy + hgt).Select
Sheets("Sheet1").Shapes.AddLine(ULHx, ULHy, ULHx + wid, ULHy + hgt).Select

End Sub

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

IRstuff (Aerospace) · Answer 4 · 2008-06-02 21:24:34

The basic answer is no, you do not have all of VB in VBA. Why should you? VBA, Visual Basic for Application, is supposed to be a macro language for writing macros for existing Windows applications.

VB is a standalone Windows programming language.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

IDS (Civil/Environmental) · Answer 5 · 2008-06-02 22:14:29

You might like to keep an eye on my blog:
http://newtonexcelbach.wordpress.com/2008/06/01/drawing-in-excel-1/

The current post deals with scaling drawings in an XY chart, but I will be dealing with drawing shape objects using VBA over the coming weeks.

For your application I think using an XY chart is worth considering. The advantage is that you can get the chart updated without any VBA, and you can (if you want) have it on its own sheet. A disadvantage is you can't easily shade the shapes, and adding text, arrows etc is more difficult.

Doug Jenkins
Interactive Design Services
www.interactiveds.com.au

vbengineer (Mechanical) · Answer 6 · 2008-06-03 08:13:02

Thanks all for the suggestions. I will try a few of the suggestions, particularly the charting capabilities and see if I can satisfy my needs.

However, if I would like to be a little more aggressive in the future, beyond a couple of simple rectangles, is it possible to link the full version of VB with the data in an Excel workbook to do some true animation? Maybe create an executable program in VB that accesses the data in an open Excel workbook and animates the data in some fashion. Please advise.

insult2injury (Mechanical) · Answer 7 · 2008-06-03 08:26:39

Yes, you can read the data into VB from any file if you know how it's delimited.

I2I

IRstuff (Aerospace) · Answer 8 · 2008-06-03 12:37:50

IF you save the data as CSV, it's essentially a text file, readable by most programming languages.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

geekEE (Electrical) · Answer 9 · 2008-06-03 16:37:50

In [thread770-211542: 3D graphics with Excel], there's a link to an article on 3d animation in Excel spreadsheets.
http://www.gamasutra.com/view/feature/3563/microsoft_excel_revolutionary_3d_.php

Glenn

IDS (Civil/Environmental) · Answer 10 · 2008-06-04 08:33:43

" is it possible to link the full version of VB with the data in an Excel workbook to do some true animation? Maybe create an executable program in VB that accesses the data in an open Excel workbook and animates the data in some fashion. Please advise."

Yes, it is possible, but it is also possible, more easily, with VBA. Don't underestimate VBA (have a look at the link from geekEE.

Doug Jenkins
Interactive Design Services
www.interactiveds.com.au

MiketheEngineer (Structural) · Answer 11 · 2008-06-04 17:46:17

Also - try Tek-tips.com - sister website to this. There are some real geeks over there that know what they are doing.

pipewelder1999 (Industrial) · Answer 12 · 2008-06-20 18:26:11

You may want to try the VB.net express edition. You may be able to create an application in VB.net that uses the object model for excel to read the data.

The express editions are free and have more features than VBA. I have not messed with mine yet but I do know t hat there is a graphics object.

Just a thought.

Gerald Austin
Iuka, Mississippi
http://www.weldinginspectionsvcs.com

IDS (Civil/Environmental) · Answer 13 · 2008-06-21 21:50:12

Using VB to work with the Excel object model stikes me as a hard way to go about things.

I have posted a simple demonstartion of an animation in Excel, using VBA, on my blog:

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

It's just a circle bouncing around inside a box, but it should be fairly easy to amend it to do what was wanted in the original post in this thread.

You have to dowload the file to see the animation by the way. I haven't worked out how to convert it to run on the Net yet.

Doug Jenkins
Interactive Design Services
www.interactiveds.com.au

vbengineer (Mechanical) · Answer 14 · 2008-06-23 10:30:13

I really appreciate the various responses that have been provided. Several of them appear to be targeted at my needs and I need to start evaluating them.

I would like to keep things in the VBA/Excel environment and the post at www.gamasutra.com and at newtonexcelback.com look pretty interesting.

I still welcome any additional ideas if anyone wants to post. I will continue to monitor this thread.

Thanks again.

Matt Brach
Brach Engineering
www.brachengineering.com

Come Join Us!

Posting Guidelines

Using VB graphics commands in VBA