Coil spring VB image

[Dacspring]

Hi
Does anyone know if possible or have a sample VB image of a coil spring which I can use to graphically represent the dimensions of a coil spring?
Inside diameter
Number of coils
Length of spring
Dimension of the wire diameter

i would like the spring image to dynamically change as I change the data table.

Any help would be greatly appreciated

[mdmackillop]

Interesting little project. My best effort. You can set the scale factor in the code and remove it from the worksheet.

[Dacspring]

Wow thank you!

[paulked]

Absolute brilliance!

[SamT]

WOW! Indeed.

IMO that would make a great KB entry or Article.

The only critique I can offer is to edit the variables so even beginners can easily see what is going on. To that end. I give you this suggestion to use as you see fit, should you take up the effort.

That was fun.

[mdmackillop]

Thanks Both,
I had played around a bit to even out the pitches but not converted to "easy reader" version

Sub SpringCoilSolid(Coils, Height, Diam, Thick, Endd)
    Dim shp As Shape, d As Shape
    Dim i As Long, Lft As Long, Tp1 As Long, Tp2 As Long, Tp3 As Long, Topp As Long, Mid As Long
    Dim a As Single, x As Single, L As Single, Pitch As Single
    Dim Diag As Single, Factor As Single
    Dim k As Long
    Dim clr
    
    Factor = [C7]
    Lft = 200: Topp = 20
      
    ActiveSheet.DrawingObjects.Delete
        
    Height = Height * Factor
    Diam = Diam * Factor
    Thick = Thick * Factor
    
    Mid = Height - (2 * Endd * Thick)
    Pitch = Mid / (2 * Coils) - 3
    Diag = (Diam ^ 2 + Pitch ^ 2) ^ 0.5
    a = Application.Acos(Diam / Diag)
    x = a * (180 / 3.14159)
    L = 0.5 * (Diag - Diam)


    'Back coils
    For i = 1 To 2 * Coils - 4 Step 2
        k = k + 1
        Tp = Topp + ((Endd - 1) * Thick) + (i - 1) * Pitch
        Set d = ActiveSheet.Shapes.AddShape(msoShapeFlowchartTerminator, Lft - L, Tp + (Pitch / 2), Diag, Thick)
        d.Fill.ForeColor.Brightness = -0.25
        d.IncrementRotation x
    Next i
    
    'Front coils
    For i = 2 To 2 * Coils - 4 Step 2
        k = k + 1
        Tp = Topp + ((Endd - 1) * Thick) + (i - 1) * Pitch
        Set d = ActiveSheet.Shapes.AddShape(msoShapeFlowchartTerminator, Lft - L, Tp + (Pitch / 2), Diag, Thick)
        d.IncrementRotation -x
    Next i
    
    'Top coils
    For i = 1 To Endd
    Tp1 = Topp + (i - 1) * Thick
    ActiveSheet.Shapes.AddShape msoShapeFlowchartTerminator, Lft, Tp1, Diam, Thick
    Next i
      
    'Bottom Coils
    For i = 1 To Endd
    Tp3 = Tp + i * Thick + Pitch - Thick
    ActiveSheet.Shapes.AddShape msoShapeFlowchartTerminator, Lft, Tp3, Diam, Thick
    Next i
    
End Sub

[snb]

@mdmac

chapeau !

Suggestions:

- since you use separate macros, I'd store them in the codemodule of each worksheet. No macromodules required.
- instead of passing separate ranges you can pass the values of 1 range
- the number of variables can be reduced to 2 (a counter & an array)
- the number of loops can be reduced to 1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2:C7")) Is Nothing Then M_SpringCoil Range("C2:C10").Value
End Sub

Sub M_SpringCoil(sn)
    If ActiveSheet.Shapes.Count > 0 Then
        ActiveSheet.Shapes.SelectAll
        Selection.Delete
    End If
    
    For j = 2 To 4
       sn(j, 1) = sn(j, 1) * sn(6, 1)
    Next
  
    sn(7, 1) = (sn(3, 1) ^ 2 + (sn(2, 1) / sn(1, 1)) ^ 2) ^ 0.5
    sn(8, 1) = Application.Acos(sn(3, 1) / sn(7, 1)) * (180 / 3.14159)

    For j = 1 To sn(1, 1)
       sn(9, 1) = 20 + (j - 1) * (sn(2, 1) / sn(1, 1))
       
       ActiveSheet.Shapes.AddShape 69, 200, sn(9, 1), sn(3, 1), sn(4, 1)
       If j = sn(1, 1) Then Exit For
       
       With ActiveSheet.Shapes.AddShape(69, 200 - 0.5 * (sn(7, 1) - sn(3, 1)), sn(9, 1) + (sn(2, 1) / sn(1, 1) / 2), sn(7, 1), sn(4, 1))
         .IncrementRotation sn(8, 1)
         .Fill.ForeColor.Brightness = -0.25
       End With
    Next
End Sub

[Dacspring]

Thank you for these suggestions, way above my head however to edit the codemodule. Would you please insert into a sample sheet for me?

[Dacspring]

This is the image or components of this image that I'd like to usespringl.jpg

[mdmackillop]

Hi Dacspring
I think you're getting beyond the scope of a free help forum; in any event, if you have specific requirements, these should be stated up front.

@snb
Thanks for that. I'll need to try and implement that method for Post #6 to get my head around your arrays.

[snb]

Here's the file.

Finally 1 macro could suffice, but in this case I stuck to 2.

[snb]

As expected: 1 macro suffices.
See attached file

[Dacspring]

Thank you for the contributions so far, perhaps if anyone is willing to consult their services commercially they could inbox me
I'd like to implement this theory onto a machine CNC interface at some point.

[snb]

In Post #12 I uploaded a revised file with a revised formula, so the spring height won't change if adding/reducing open coils.