×
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

Jobs

Draw graph/shape from excel-VBA

Draw graph/shape from excel-VBA

Draw graph/shape from excel-VBA

(OP)
Hi,

I am begineer in Excel-VBA. Need help to plot graph.

I have two set of data points (A,B) and (D,E) corresponding can also be mentioned as column (1,2) and (4,5)starting from row no.6 to row no.13

A B

0 50
1000 50
2000 50
3000 50
4000 50
5000 50
6000 50
7000 50
8000 50


C D

0 150
1000 150
2000 150
3000 150
4000 150
5000 150
6000 150
7000 150
8000 150

Used the below macro to draw horizontal lines. But I need to draw vertical lines. Kindly let me know, how to draw vertical lines. Also how to change line size and line color

Sub Chassis()
Dim triArray(1 To 8, 1 To 2) As Single
Worksheets("sheet3").Activate

triArray(1, 1) = ActiveSheet.Cells(6, 1).Value
triArray(1, 2) = ActiveSheet.Cells(6, 2).Value
triArray(2, 1) = ActiveSheet.Cells(7, 1).Value
triArray(2, 2) = ActiveSheet.Cells(7, 2).Value
triArray(3, 1) = ActiveSheet.Cells(8, 1).Value
triArray(3, 2) = ActiveSheet.Cells(8, 2).Value
triArray(4, 1) = ActiveSheet.Cells(9, 1).Value
triArray(4, 2) = ActiveSheet.Cells(9, 2).Value
triArray(5, 1) = ActiveSheet.Cells(10, 1).Value
triArray(5, 2) = ActiveSheet.Cells(10, 2).Value
triArray(6, 1) = ActiveSheet.Cells(11, 1).Value
triArray(6, 2) = ActiveSheet.Cells(11, 2).Value
triArray(7, 1) = ActiveSheet.Cells(12, 1).Value
triArray(7, 2) = ActiveSheet.Cells(12, 2).Value
triArray(8, 1) = ActiveSheet.Cells(13, 1).Value
triArray(8, 2) = ActiveSheet.Cells(13, 2).Value
Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline triArray


Dim tri1Array(1 To 8, 1 To 2) As Single
tri1Array(1, 1) = ActiveSheet.Cells(6, 4).Value
tri1Array(1, 2) = ActiveSheet.Cells(6, 5).Value
tri1Array(2, 1) = ActiveSheet.Cells(7, 4).Value
tri1Array(2, 2) = ActiveSheet.Cells(7, 5).Value
tri1Array(3, 1) = ActiveSheet.Cells(8, 4).Value
tri1Array(3, 2) = ActiveSheet.Cells(8, 5).Value
tri1Array(4, 1) = ActiveSheet.Cells(9, 4).Value
tri1Array(4, 2) = ActiveSheet.Cells(9, 5).Value
tri1Array(5, 1) = ActiveSheet.Cells(10, 4).Value
tri1Array(5, 2) = ActiveSheet.Cells(10, 5).Value
tri1Array(6, 1) = ActiveSheet.Cells(11, 4).Value
tri1Array(6, 2) = ActiveSheet.Cells(11, 5).Value
tri1Array(7, 1) = ActiveSheet.Cells(12, 4).Value
tri1Array(7, 2) = ActiveSheet.Cells(12, 5).Value
tri1Array(8, 1) = ActiveSheet.Cells(13, 4).Value
tri1Array(8, 2) = ActiveSheet.Cells(13, 5).Value
Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline tri1Array

End Sub

RE: Draw graph/shape from excel-VBA

The shortest answer is to use a chart rather than messing around with shapes, but assuming you have a reason for using shapes rather than a chart the next easiest way would be to just swap the columns on the spreadsheet. You could also swap the column indices for the triArray and tri1Array arrays in the code.

But the code could be much simpler by bringing in the whole array in one operation, rather than cell by cell:

CODE -->

Dim triArray As Variant
    Worksheets("sheet1").Activate
    triArray = Range("A6:B14").Value2
    Set myDocument = Worksheets(2)
    myDocument.Shapes.AddPolyline triArray
   ... 
If you swapped the columns on the spreadsheet that would be all you need to do, but if you want to do it in VBA you can do it with a For..Next loop:

CODE --> vb

Sub Chassis()
Dim inArray As Variant
Dim triArray(1 To 8, 1 To 2) As Single, tri1Array(1 To 8, 1 To 2) As Single, i As Long
    Worksheets("sheet1").Activate

    inArray = Range("A6:B14").Value2
    For i = 1 To 8
        triArray(i, 2) = inArray(i, 1)
        triArray(i, 1) = inArray(i, 2)
    Next i
    Set myDocument = Worksheets(2)
    myDocument.Shapes.AddPolyline triArray

    inArray = Range("D6:E14").Value2
   
    For i = 1 To 8
        tri1Array(i, 2) = inArray(i, 1)
        tri1Array(i, 1) = inArray(i, 2)
    Next i
    Set myDocument = Worksheets(2)
    myDocument.Shapes.AddPolyline tri1Array

End Sub 

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Draw graph/shape from excel-VBA

(OP)
Hi Doug,

Big Thanks.

The code is compact and made the plotting much more simple.

I am still learning to understand the code being a beginner. I edited the code for additional requirements.

Apart from vertical lines, would like to draw horizontal lines. Basically, I am drawing commercial vehicle automotive frame.The code is given below, is it possible for draw horizontal lines using for loop. Then how to scale down the shape to visualize the drawn shape without dragging down.is there are any options to change line attributes (color, thickness)


Sub Chassis()


Dim inArray As Variant
Dim FSMLArray(1 To 8, 1 To 2) As Single, FSMRArray(1 To 8, 1 To 2) As Single, i As Long
Dim Tri1Array(1 To 2, 1 To 2) As Single
Dim Tri2Array(1 To 2, 1 To 2) As Single
Dim Tri3Array(1 To 2, 1 To 2) As Single
Dim Tri4Array(1 To 2, 1 To 2) As Single
Dim Tri5Array(1 To 2, 1 To 2) As Single
Dim Tri6Array(1 To 2, 1 To 2) As Single
Dim Tri7Array(1 To 2, 1 To 2) As Single
Dim Tri8Array(1 To 2, 1 To 2) As Single


Worksheets("sheet3").Activate

inArray = Range("A6:B14").Value2
For i = 1 To 8
FSMLArray(i, 2) = inArray(i, 1)
FSMLArray(i, 1) = inArray(i, 2)
Next i
Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline FSMLArray

inArray = Range("D6:E14").Value2

For i = 1 To 8
FSMRArray(i, 2) = inArray(i, 1)
FSMRArray(i, 1) = inArray(i, 2)
Next i
Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline FSMRArray



Tri1Array(1, 1) = FSMRArray(1, 1)
Tri1Array(1, 2) = FSMRArray(1, 2)
Tri1Array(2, 1) = FSMLArray(1, 1)
Tri1Array(2, 2) = FSMLArray(1, 2)

Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline Tri1Array



Tri2Array(1, 1) = FSMRArray(2, 1)
Tri2Array(1, 2) = FSMRArray(2, 2)
Tri2Array(2, 1) = FSMLArray(2, 1)
Tri2Array(2, 2) = FSMLArray(2, 2)

Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline Tri2Array





Tri3Array(1, 1) = FSMRArray(3, 1)
Tri3Array(1, 2) = FSMRArray(3, 2)
Tri3Array(2, 1) = FSMLArray(3, 1)
Tri3Array(2, 2) = FSMLArray(3, 2)

Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline Tri3Array






Tri4Array(1, 1) = FSMRArray(4, 1)
Tri4Array(1, 2) = FSMRArray(4, 2)
Tri4Array(2, 1) = FSMLArray(4, 1)
Tri4Array(2, 2) = FSMLArray(4, 2)

Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline Tri4Array


Tri5Array(1, 1) = FSMRArray(5, 1)
Tri5Array(1, 2) = FSMRArray(5, 2)
Tri5Array(2, 1) = FSMLArray(5, 1)
Tri5Array(2, 2) = FSMLArray(5, 2)

Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline Tri5Array


Tri6Array(1, 1) = FSMRArray(6, 1)
Tri6Array(1, 2) = FSMRArray(6, 2)
Tri6Array(2, 1) = FSMLArray(6, 1)
Tri6Array(2, 2) = FSMLArray(6, 2)

Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline Tri6Array



Tri7Array(1, 1) = FSMRArray(7, 1)
Tri7Array(1, 2) = FSMRArray(7, 2)
Tri7Array(2, 1) = FSMLArray(7, 1)
Tri7Array(2, 2) = FSMLArray(7, 2)

Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline Tri7Array



Tri8Array(1, 1) = FSMRArray(8, 1)
Tri8Array(1, 2) = FSMRArray(8, 2)
Tri8Array(2, 1) = FSMLArray(8, 1)
Tri8Array(2, 2) = FSMLArray(8, 2)

Set myDocument = Worksheets(2)
myDocument.Shapes.AddPolyline Tri8Array



End Sub

Thanks once again

RE: Draw graph/shape from excel-VBA

It looks like you are focussing too much on the details and don't have a good understanding of the basics. Instead of starting off trying to generate these shapes with polylines and VBA I suggest you concentrate on generating the XY coordinates then plot them using an XY (scatter) chart, which will automatically scale the lines to fit within the chart. Having done that it will be much easier to work with polylines, if that is the way you decide to go.

Also re-read my previous response. You don't need to generate the coordinates the wrong way round on the spreadsheet, then reverse them with VBA loops!

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Draw graph/shape from excel-VBA

(OP)
Hi,

Thanks for your comments. I am currently exploring with XY(scatter)chart.

elogesh

RE: Draw graph/shape from excel-VBA

hi,

Just a tip....

CODE

Dim inArray, i As Integer, j As Integer

    inArray = Range("A6:B14")

    For i = LBound(inArray, 1) To UBound(inArray, 1)
        For j = LBound(inArray, 2) To UBound(inArray, 2)
           Debug.Print inArray(i, j), i, j
        Next
    Next 

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!


Resources