Excel Range to Array to Addline array

[Anonymous]

Addline takes two three-element array of doubles. my data i want to use is in a 3 wide column in excel. its very easy to put into an array.

Dim arpt As Variant
arpt = Range("B17:D28")

this is just my prototype, I am trying to work out a general method to use with different products. what i really want is dynamic naming of variables. googling that, i dont think its available in VBA and everyone says to use arrays.

the range above has 12 lines, i have hard coded these to show (myself) it works, but i am trying to avoid writing 12 or later many more

dim pt1 (0 to 2) as double

which is the normal input for addline.

i cannot just put the addline in a loop as i am running down the excel list. the points do not all connect. sometimes i want to draw a circle at a location. that code has to be written for the application.

the ideal would be to dynamically create variable names  such as 

for i = 1 to 50

dim (strcat "pt" & ctos(i)) (0 to 2) as double

 next i

creating suitable pt1, pt2 etc  and then assign values from the array in a similar way.

i could get the variable names from the spreadsheet cell if that would work.

has anybody solved this problem? it seems like a general holdup to drawing from excel. i can rather easily in excel make lists of points that represent corners to draw to, centers of circles, etc.

this below does work but its a bit tedious also.

Dim i As Integer
Dim lineobj As AcadLine
Dim arpt As Variant
arpt = Range("B17:D28")

Dim pt1(1 To 3) As Double
Dim pt2(1 To 3) As Double

For i = 1 To 3
pt1(i) = arpt(1, i)
pt2(i) = arpt(2, i)
Next

Set lineobj = ACAD.ActiveDocument.ModelSpace.AddLine(pt1, pt2)

 edit: the range is B17 to D28, the format is misinterpreting by colon as smiley face. not happy. 😉

[Anonymous]

I bet that >>VBProject object<< way could make the trick of finally dealing with dynamic variables names. but it looks like a really tricky way.

as to a more conventional way, I should have more informations about your goals along with some examples

till then I'd say that using arrays and loops should get you in the way of virtually having dynamic variables names, where you can use some array fields to store both the virtual "name" and function of your variable.

for instance some (n,5) dimensioned array could keep the variable name in its first column and that point function ("circle", "line", ...) in its second one. and the coordinates in the last three ones. so that you could loop through that array and choose to work with its proper rows looking at the first two columns.

in that way you could also have excel VBA make the work of extracting (filtering) proper rows from a table and then having them passed to some specific "AddSomething()" vba routine (AddCircle(), AddLine(), ...)  or to a general one (AddEverything()) where a "select case" could filter the right code block to call.

finally, maybe a "class" way could help you also. but this is just a "feeling" since I'm not quite used at that.

[Anonymous]

i think you have the idea. here is the latest brute force method. i dont really object to the last group of addline statements. That will vary from project. But the first two, the p# declarations and filling the variables, that should be routine and encompass any number of points in the array.

this code in excel, and the range is B17 to D28

it draws a box, then draws some lines interior to the box that represent framing members. the cut list is generated by excel and autocad is used for assembly in the shop. there are more complex designs later.

Sub linedrawtest2()
Dim ACAD As AcadApplication
On Error Resume Next
Set ACAD = GetObject(, "AutoCAD.Application")
On Error GoTo 0
If ACAD Is Nothing Then
Set ACAD = New AcadApplication
ACAD.Visible = True
End If

Dim lineobj As AcadLine

Dim arpt As Variant
arpt = Range("B17:D28")

Dim p1(1 To 3) As Double
Dim p2(1 To 3) As Double
Dim p3(1 To 3) As Double
Dim p4(1 To 3) As Double
Dim p5(1 To 3) As Double
Dim p6(1 To 3) As Double
Dim p7(1 To 3) As Double
Dim p8(1 To 3) As Double
Dim p9(1 To 3) As Double
Dim p10(1 To 3) As Double
Dim p11(1 To 3) As Double
Dim p12(1 To 3) As Double

Dim i As Integer
For i = 1 To 3
p1(i) = arpt(1, i)
p2(i) = arpt(2, i)
p3(i) = arpt(3, i)
p4(i) = arpt(4, i)
p5(i) = arpt(5, i)
p6(i) = arpt(6, i)
p7(i) = arpt(7, i)
p8(i) = arpt(8, i)
p9(i) = arpt(9, i)
p10(i) = arpt(10, i)
p11(i) = arpt(11, i)
p12(i) = arpt(12, i)
Next

Set lineobj = ACAD.ActiveDocument.ModelSpace.AddLine(p1, p2)
Set lineobj = ACAD.ActiveDocument.ModelSpace.AddLine(p2, p3)
Set lineobj = ACAD.ActiveDocument.ModelSpace.AddLine(p3, p4)
Set lineobj = ACAD.ActiveDocument.ModelSpace.AddLine(p4, p1)
Set lineobj = ACAD.ActiveDocument.ModelSpace.AddLine(p5, p6)
Set lineobj = ACAD.ActiveDocument.ModelSpace.AddLine(p7, p8)
Set lineobj = ACAD.ActiveDocument.ModelSpace.AddLine(p9, p10)
Set lineobj = ACAD.ActiveDocument.ModelSpace.AddLine(p11, p12)

End Sub