If we can interpret strings as symbols, we can store parameter data in spreadsheets.
The basic technique uses VBA statements Replace and Evaluate.
Sub test() Dim A As Double, B As Double, C As Double, D As Double, R As Double Dim str As String str = "A + B * C + D" A = 5 B = 3 C = 2 D = 3 str = Replace(str, "A", A) str = Replace(str, "B", B) str = Replace(str, "C", C) str = Replace(str, "D", D) R = Evaluate(str) MsgBox R End Sub
Lets use a simple polyline box to illustrate the all-in-code method versus the data-in-spreadsheet method.
If the box is A wide (in the x direction) and B tall (in the y direction), and the lower left corner is at 0,0, the points are (0,0) (A,0) (A,B) (0,B)
AddLightWeightPolyline will draw the box if the 8 data entries are given in an array in sequence. The CLOSED property of the polyline object is set to true to draw the line from the last point back to the first point.
Dim pts as variant
Pts = Array (x0, y0, x1, y0, x1, y1, x0, y1)
The array named pts is then handed off to a sub that will convert it to a format AddLightWeightPolyline can handle.
For any complex shape a labeled drawing is necessary. Creating the array is a two-step process. x and y values are found starting from the origin in relation to the parameters A and B (and any others necessary). Then the x and y values are listed as needed in the array in the order that the polyline vertices require. At runtime the x and y values have their formulas evaluated and the results are stored in the array given to the polyline sub to be drawn.
To duplicate this with the data in a spreadsheet, the process has to be collapsed to a single step. We need to store the formulas in the order the polyline requires them, not evaluate them to x and y symbols first.
At first the added complexity of that was tedious. But eventually I learned to enter the formulas per the sketch just doing all the x values, in the order they are to be drawn, then do all the y values, similar to the code process. The benefits of putting the data into a spreadsheet are that we can have a whole sheet of parts, and we can manipulate the data and the finished part easily.
In standard database design, every table has a unique name with one column that has unique values called a primary key. The record (row) is found by finding the unique value in the key column. If we call the spreadsheet table SHAPE then the unique column will be called SHAPE_ID. We find the row data by the name of the shape we are looking for. For instance, the key column is column B, the shape we are looking for is “OSCF”, then
Set rng = ws1.Range(”B1:B30”).Find(”OSCF”)
Find has arguments. If the lookat:=xlWhole is not set, Find will search and find the substring, so that searching for OSCF will stop when OSCF1 is found. Also it is written that VBA saves its last search parameters, and uses them when there are no arguments to the contrary, which could lead to some pretty confounding bugs.
Set rng = ws1.Range(”B1:B30”).Find(”OSCF” , LookIn:=xlValues, lookat:=xlWhole)
in practice variables will be used for the range and search string
Set rng = ws1.Range(shape_key).Find(shape_ID, LookIn:=xlValues, lookat:=xlWhole)
If found, this will set the excel range object to the single cell that contains the found string.
If our data is extended out to the right, and there is a blank column after the last cell, then
First we move the range one cell to the right to pick up the first data cell
Set rng = rng.Offset(0, 1)
Then we extend the range all the way to the right, a variable number of columns depending on the individual record.
Set rng = ws1.Range(rng.Address, rng.End(xlToRight).Address)
Ws1 is a sheet reference.
Now we have the data, whatever it is, in a range object. In the cells are our formulas. Here are the values for OSCF,
A, B, and W are defined by the user at run time. But the strings A, B and W in the spreadsheet are not the symbols A, B and W in VBA.
The range is saved. We count the number of items in it and re-dimension the array to accept it.
icount = rng.Count
ReDim ar(1 To icount)
Now we can loop through the range. Each time through the loop we get the cell value in a string. Then we use VBA Replace to change the string values A, B, W to their symbols which at runtime have actual numerical values. Finally we use VBA Evaluate to do the math and load the result into the array. The finished array is an array of doubles and suitable for the AddLightWeightPollyLine method.
Sub shape_2(shape_ID As String, A As Double, B As Double, C As Double, D As Double, W As Double) Dim i As Integer, icount As Integer Dim str As String Dim rng As Range Dim plineobj As AcadLWPolyline Dim ar() As Double Dim shape_key As String Set ws1 = ThisWorkbook.Worksheets("Shape_Param") shape_key = "B1:B30" Set rng = ws1.Range(shape_key).Find(shape_ID, LookIn:=xlValues, lookat:=xlWhole) If rng Is Nothing Then MsgBox "no find shape_ID in column shape_key" Exit Sub End If 'rng has one cell, offset it one cell right to begin data Set rng = rng.Offset(0, 1) 'extend the range to right to the end of the record Set rng = ws1.Range(rng.Address, rng.End(xlToRight).Address) 'this also works but it seems like it shouldnt 'because excel seems to infer the range default as address 'Set rng = Range(rng, rng.End(xlToRight)) icount = rng.Count ReDim ar(1 To icount) For i = 1 To icount str = rng.Cells(1, i) str = Replace(str, "A", A) str = Replace(str, "B", B) str = Replace(str, "C", C) str = Replace(str, "D", D) str = Replace(str, "W", W) ar(i) = Evaluate(str) Next i Set plineobj = acadDoc.ModelSpace.AddLightWeightPolyline(ar) plineobj.Closed = True plineobj.Update Set g_pline = plineobj End Sub
The created polyline can be a global variable and the calling program can draw several shapes and move, rotate, mirror them into assemblies. For drawing sheet metal end sections, either the part can be drawn with a thickness, a closed polyline, or a single not-closed polyline. Thats the origin of my having two subs Shape_1 and Shape_2. They differ only by whether they close the polyline at the end. I preload 4 variables A,B,C,D, and W which I use for thickness, but if they are not needed, zeros can be passed.
Since this is a spreadsheet, not an actual database, our records can have a variable number of columns, we can skip lines, we can have sections of closed and not closed parts. The only requirement is that the key column contains the name of the part.
Different parts drawn with shape_ID and parameters.
Sub test2_sheet() Call init_draw Dim A As Double, B As Double, C As Double, D As Double, W As Double Dim shape_ID As String A = 2 B = 4.25 C = 0 D = 0 W = 0.0625 shape_ID = "OSCF" ' shape_ID = "ISCF" ' shape_ID = "CAP" ' shape_ID = "FLOOR" 'shape_ID = "FLAT" 'shape_ID = "JCHAN" 'shape_ID = "HANG" Call shape_2(shape_ID, A, B, C, D, W) pt1 = pt(4, 4, 0) g_pline.Move pt0, pt1 End Sub
Assemblies drawn using global polyline methods move, rotate, mirror
Sub test1_sheet() Call init_draw Call shape_2("box", 34, 0.0625, 0, 0, 0) Call shape_2("box", 34, 0.0625, 0, 0, 0) g_pline.Move pt0, pt(0, 3.9375, 0) Call shape_2("box", 1, 3.875, 0, 0, 0) g_pline.Move pt0, pt(0, 0.0625, 0) Call shape_2("box", 1, 3.875, 0, 0, 0) g_pline.Move pt0, pt(33, 0.0625, 0) acadApp.Update End Sub
More complex assemblies can use a selection set to block the result using parameters in the name. So its pretty easy to develop a lot of similar parts with different dimensions.
Sub part_end_view_2(W As Double, Thk As Double) Dim strblk As String strblk = "Panel " & Thk & " x " & W - 1 & " GL" Call init_draw Dim sset As AcadSelectionSet addss "SSBLOCK" Set sset = acadDoc.SelectionSets.Item("SSBLOCK") Call shape_1("FM_SINGLE", W, 0, 0, 0, 0) sset.Select acSelectionSetLast g_pline.Mirror pt(0, Thk / 2, 0), pt(5, Thk / 2, 0) sset.Select acSelectionSetLast Call shape_1("CHAN_SINGLE", 1, Thk - 0.125, 0, 0, 0) g_pline.Move pt0, pt(0, -(Thk - 0.125) / 2, 0) g_pline.Rotate pt0, PI g_pline.Move pt0, pt(1.25, Thk / 2, 0) sset.Select acSelectionSetLast Call shape_1("CHAN_SINGLE", 1, Thk - 0.5, 0, 0, 0) g_pline.Move pt0, pt(0, (-(Thk - 0.5) / 2), 0) g_pline.Move pt0, pt(W - 1.25, Thk / 2, 0) sset.Select acSelectionSetLast make_blk sset, strblk sset.Erase sset.Clear sset.Delete acadDoc.ModelSpace.InsertBlock pt0, strblk, 1, 1, 1, 0 acadApp.Update End Sub
When rotating and moving, sometimes its conceptually easier to go backwards. What point is easiest to reference in the final position? Move the part so that point is at 0,0 then rotate about the origin and move from there. Or you can draw the object in the rotation and position you wish by experimenting with the point values in the spreadsheet.
I have found the Evaluate function in VBA is pretty robust and it doesnt matter whether all the spreadsheet entries have the proper spaces, A+W or A + W both are evaluated correctly.
EDIT:- a bug,
the code ran many times before this error popped up. When it does error, it seems to always be that Evaluate is trying to convert a string representing an integer to a double. Other integers work fine, 6 instead of a 4, input from a form, exact same code, CDBL does not seem to help. Variant does not help. something here i am not getting.
When errors make no sense, i tell people, re-boot.
the downside to sheet driven parts – besides sometimes it does not run – its conceptually simpler to see it in code. parameters in a sheet are more fragile, its much easier to delete data in a sheet than code. protect the sheet if you give it out.
on the other hand, I quickly made several assembly drawing programs using sheet driven parts.
this seems to get past the error, though it needs further looking at.
Dim dbl As Double
For i = 1 To icount
str = rng.Cells(1, i)
str = Replace(str, “A”, A)
str = Replace(str, “B”, B)
str = Replace(str, “C”, C)
str = Replace(str, “D”, D)
str = Replace(str, “W”, W)
On Error Resume Next
dbl = CDbl(Evaluate(str))
If Err Then
ar(i) = CDbl(str)
ar(i) = Evaluate(str)
On Error GoTo 0