Draw graph/shape from excel-VBA 1

[elogesh]

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

 

[IDS]

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:

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:

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/

 

[elogesh]

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

 

[IDS]

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/

 

[elogesh]

Hi,

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

elogesh

 

[SkipVought]

hi,

Just a tip....

    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