Din 5480 Spline Calculator — Excel New

DIN 5480 defines involute splines with a 30° pressure angle (module series) and 37.5°/45° for special cases. Key principles:

This is where the user interacts with the sheet. It should be clean and protected to prevent accidental deletion of formulas. Essential inputs include: din 5480 spline calculator excel new

The old way required flipping through PDF tables to find the correct combination of module (m) and number of teeth (z). A modern calculator uses nested XLOOKUP (or INDEX/MATCH) arrays to instantly return the reference diameter and profile shift x*m based on DIN 5480 Table 1. DIN 5480 defines involute splines with a 30°

DIN 5480 defines involute splines with:

Open a new Excel sheet. Set up the cells as described below. Bold items are labels, (Input) indicates user entry, and (Output) indicates formulas/code. Open a new Excel sheet

| Phase | Task | Duration | |-------|------|----------| | 1 | Build input/output UI + formulas | 2 days | | 2 | Tolerance table + VLOOKUP logic | 1 day | | 3 | Ball measurement (M) formulas + Solver | 2 days | | 4 | VBA automation & validation | 2 days | | 5 | Testing against DIN examples | 1 day | | 6 | Documentation & release | 1 day | | Total | | 9 days |

Press Alt + F11 to open the VBA Editor.

Option Explicit
' Main Calculation Subroutine
Public Sub CalculateDIN5480()
    Dim ws As Worksheet
    Set ws = ActiveSheet
' --- 1. Read Inputs ---
    Dim m As Double, z As Long, alphaD As Double
    Dim toleranceClass As String
m = ws.Range("B3").Value
    z = ws.Range("B4").Value
    alphaD = ws.Range("B5").Value
    toleranceClass = ws.Range("B6").Value
' Convert degrees to radians
    Dim alphaRad As Double
    alphaRad = WorksheetFunction.Radians(alphaD)
' --- 2. Basic Geometry ---
    Dim d As Double, db As Double
    d = m * z                     ' Reference Diameter
    db = d * Cos(alphaRad)        ' Base Diameter
' --- 3. Iterative Calculation for Reference Diameter (d_Bez) ---
    ' DIN 5480 uses a reference profile shift to determine d_Bez.
    ' For simplicity in this calculator, we assume the standard case where
    ' the reference diameter equals the pitch diameter (no specific profile shift input).
    ' We calculate the ISO standard involute parameters.
Dim dp As Double
    dp = d ' Pitch diameter equals reference diameter in basic calculation
' --- 4. Root and Tip Diameters (Approximations based on DIN Series) ---
    ' Note: Exact root diameter depends on the tool (hollow milling, hobbing).
    ' This uses standard clearance factors.
    Dim ha As Double, hf As Double
    ha = m      ' Addendum (standard)
    hf = 1.25 * m ' Dedendum (standard clearance 0.25m)
Dim da As Double, df As Double
    df = d - 2 * hf  ' Root Diameter
    da = d + 2 * ha  ' Tip Diameter (External)
' --- 5. Inspection Calculations ---
' A) Span Measurement (Wk)
    ' Calculate number of spans (k)
    Dim k As Double
    k = WorksheetFunction.Round((z * alphaRad / WorksheetFunction.Pi()) + 0.5, 0)
' Calculate Involute Function inv(alpha)
    Dim invAlpha As Double
    invAlpha = Tan(alphaRad) - alphaRad
' Calculate Wk theoretical
    Dim Wk As Double
    Wk = m * Cos(alphaRad) * ((k - 0.5) * WorksheetFunction.Pi() + z * invAlpha)
' B) Measurement Over Pins (M)
    ' Select standard Pin Diameter (D_M)
    ' Rule of thumb: D_M approx 1.728 * m for 30 deg
    Dim Dm As Double
    Dm = 1.728 * m ' Standard pin size
' Calculate M
    Dim invAlphaM As Double, alphaM As Double, cosAlphaM As Double
    Dim M As Double
' Calculate involute angle at pin center
    ' inv(alpha_m) = inv(alpha_D) + D_M / (d_b) - (s / d_b)
    ' Assuming tooth thickness s = m * pi / 2 (Basic)
    Dim s As Double
    s = m * WorksheetFunction.Pi() / 2
invAlphaM = invAlpha + Dm / db - s / db
' Reverse involute function to find alpha_m
    alphaM = ReverseInvolute(invAlphaM)
' Calculate M
    If z Mod 2 = 0 Then
        ' Even teeth
        M = db / Cos(alphaM) + Dm
    Else
        ' Odd teeth
        M = (db / Cos(alphaM)) * Cos(WorksheetFunction.Pi() / (2 * z)) + Dm
    End If
' --- 6. Output to Sheet ---
    ws.Range("B9").Value = d
    ws.Range("B10").Value = db
    ws.Range("B11").Value = dp
    ws.Range("B12").Value = "See Note" ' Form diameter requires complex tool data
    ws.Range("B13").Value = da
    ws.Range("B14").Value = df
ws.Range("B17").Value = k
    ws.Range("B18").Value = Wk
    ws.Range("B19").Value = M
    ws.Range("B20").Value = Dm
MsgBox "Calculation Complete.", vbInformation, "DIN 5480"
End Sub
' Helper Function: Reverse Involute
' Given inv(x), find x (in radians)
Private Function ReverseInvolute(invVal As Double) As Double
    Dim x As Double
    Dim tolerance As Double
    Dim maxIter As Integer
    Dim i As Integer
tolerance = 0.00000001
    maxIter = 20
' Initial Guess (approximation)
    x = Sqr(invVal * 3) ' Crude approximation for small angles
' Newton-Raphson Iteration
    For i = 1 To maxIter
        Dim f As Double, fPrime As Double
        f = Tan(x) - x - invVal
        fPrime = (1 / Cos(x)) ^ 2 - 1
If Abs(f) < tolerance Then Exit For
x = x - f / fPrime
    Next i
ReverseInvolute = x
End Function

Private Sub cmdCalculate_Click()
    CalculateDIN5480
End Sub