×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using VB graphics commands in VBA

Using VB graphics commands in VBA

Using VB graphics commands in VBA

(OP)
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.

RE: Using VB graphics commands in VBA

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.

RE: Using VB graphics commands in VBA

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

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

RE: Using VB graphics commands in VBA

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.

RE: Using VB graphics commands in VBA

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

RE: Using VB graphics commands in VBA

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
 

RE: Using VB graphics commands in VBA

(OP)
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.

 

RE: Using VB graphics commands in VBA

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

I2I

RE: Using VB graphics commands in VBA

" 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
 

RE: Using VB graphics commands in VBA

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

RE: Using VB graphics commands in VBA

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

RE: Using VB graphics commands in VBA

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
 

RE: Using VB graphics commands in VBA

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Research Report - How Engineers are Using Remote Access
Remote access enables engineers to work from anywhere provided they have an internet connection. We surveyed our audience of engineers, designers and product managers to learn how they use remote access within their organizations. We wanted to know which industries have adopted remote access, which software they are using, and what features matter most. Download Now
eBook - Managing the Context of Product Complexity Using the Digital Twin
Keeping track of changes to complex products is difficult—think Aerospace & Defense equipment, new generations of commercial aircraft, and software-based automobiles. A new way to managing the digital context of the physical product is required and the answer is the Digital Twin. This ebook explores the opportunity available for Operations and Maintenance for the Digital Twin. Download Now
White Paper - Trends in Industrial Filtration
Substantial progress has been made in filtration technologies in recent years. New filter media materials, designs and processes have led to filters that are more efficient, reliable, compact and longer lasting. This white paper will discuss the various trends that are impacting operational responsibilities of MROs today and the resources that are available for staying up-to-date on the latest filtration solutions. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close