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
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
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 ...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 SubDoug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Draw graph/shape from excel-VBA
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
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
Thanks for your comments. I am currently exploring with XY(scatter)chart.
elogesh
RE: Draw graph/shape from excel-VBA
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