Strings as Symbols

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.

In code
X0=0
X1=A
Y0=0
Y1=B

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)
Err.Clear
Else
ar(i) = Evaluate(str)
End If

On Error GoTo 0

Next i

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.