VBA in Excel zum Umbenennen von Strings

Function KillZero(ByVal Str As String) As Integer
Select Case Str
Case "EP"  
KillZero = 5
Case "DE"  
KillZero = 4
'Case "Bl" ....  
End Select

End Function

Sub Main()
Dim Str As String
Str = InputBox("Stringeingabe")  
FirstTwo = Left(Str, 2)
LastTwo = Right(Str, 2)
cutnull = KillZero(FirstTwo)
LenMid = Len(Str) - 4 - cutnull
Middle = Mid(Str, 3 + cutnull, LenMid)
Res = FirstTwo & "_" & Middle & "_" & LastTwo  
MsgBox Res
End Sub

Const LinkCol = "K"             'Spalte mit den Links  
Const VNumCol = "C"             'Spalte mit ("EP000000900709B1"...)  
Const VNumRng = "C2:C"          'Bereich ab ("EP000000900709B1"...)  

Call CreateHyperlinks

Private Sub CreateHyperlinks()
    Dim c As Object, i As Integer, s As String, s1 As String, s2 As String, s3 As String

    For Each c In Range(VNumRng & GetEndLine(ActiveSheet, VNumCol))
        If Len(c) > 3 And IsEmpty(Cells(c.Row, LinkCol)) Then
            If IsNumeric(Mid(Right(c, 2), 1, 1)) Then i = 1 Else i = 2
    
            s1 = Left(c, 2) & "_":  s2 = Mid(c, 3, Len(c) - 2 - i):  s3 = "_" & Right(c, i)  
    
            For i = 1 To Len(s2)
                If Mid(s2, i, 1) <> "0" Then s2 = Mid(s2, i):  Exit For  
            Next
    
            s = ThisWorkbook.Path & "\" & s1 & s2 & s3 & ".pdf"  
            
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(c.Row, LinkCol), Address:=s, TextToDisplay:="Link"  
        End If
    Next
End Sub