PDA

View Full Version : [SOLVED] Coil spring VB image



Dacspring
05-19-2017, 11:33 PM
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
05-20-2017, 02:58 AM
Interesting little project. My best effort. You can set the scale factor in the code and remove it from the worksheet.

Dacspring
05-20-2017, 09:14 AM
Wow thank you! :)

paulked
06-06-2017, 06:30 PM
Absolute brilliance! :bow:

SamT
06-06-2017, 09:02 PM
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
06-07-2017, 04:46 AM
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
06-07-2017, 06:04 AM
@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
06-07-2017, 06:15 AM
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
06-07-2017, 06:19 AM
This is the image or components of this image that I'd like to use19415

mdmackillop
06-07-2017, 06:56 AM
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
:crying: Thanks for that. I'll need to try and implement that method for Post #6 to get my head around your arrays.

snb
06-07-2017, 12:42 PM
Here's the file.

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

snb
06-08-2017, 01:13 AM
As expected: 1 macro suffices.
See attached file

Dacspring
06-08-2017, 01:29 AM
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
06-08-2017, 02:44 AM
In Post #12 I uploaded a revised file with a revised formula, so the spring height won't change if adding/reducing open coils.