How to extract numbers for calculation when entering a string of text into a cell

Try change

    Dim od As Double, id As Double, ln As Double
    od = CDbl(Replace(dimensions(0), "mm", ""))
    id = CDbl(Replace(dimensions(1), "mm", ""))
    ln = CDbl(Replace(dimensions(2), "mm", ""))

to

    Dim od As Double, id As Double, ln As Double
    od = Val(Mid$(dimensions(0), 3))
    id = Val(dimensions(1))
    ln = Val(dimensions(2))

With Regular Expression

Function calPP(inputStr As String) As Double
    With CreateObject("VBScript.RegExp")
        .Pattern = "\D*(\d+(\.\d+)?)\*(\d+(\.\d+)?)mm\*(\d+(\.\d+)?)mmL"
        If .test(inputStr) Then
            calPP = Evaluate(.Replace(inputStr, "(($1-$3)*$5*8.96)/1000"))
        End If
    End With
End Function