How do I fix this code

[Macro_Nerd99]

I found this formula online that works great to extract a 6 digit code from a cell string: =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),6)

However, I'm trying to use it in VBA code and extract it from a variable instead.
However, I get a syntax error when attempting this. How do I fix it?

Public Function Employee_ID() As String
Employee_ID =MID(thisworkbook.name,MIN(FIND({0,1,2,3,4,5,6,7,8,9},thisworkbook.name&1234567890)),6)
End Function

 

[Fluff]

How about

Public Function Employee_ID() As String
Employee_ID = Evaluate("MID(" & ThisWorkbook.Name & ",MIN(FIND({0,1,2,3,4,5,6,7,8,9}," & ThisWorkbook.Name & "&1234567890)),6)")
End Function

 

[Macro_Nerd99]

How about

Public Function Employee_ID() As String
Employee_ID = Evaluate("MID(" & ThisWorkbook.Name & ",MIN(FIND({0,1,2,3,4,5,6,7,8,9}," & ThisWorkbook.Name & "&1234567890)),6)")
End Function

I get a "Type mismatch" runtime error

 

[Fluff]

Oops. forgot the extra quotes, try

Public Function Employee_ID() As String
Employee_ID = Evaluate("MID(""" & ThisWorkbook.Name & """,MIN(FIND({0,1,2,3,4,5,6,7,8,9},""" & ThisWorkbook.Name & """&1234567890)),6)")
End Function

 

[Macro_Nerd99]

Oops. forgot the extra quotes, try

Public Function Employee_ID() As String
Employee_ID = Evaluate("MID(""" & ThisWorkbook.Name & """,MIN(FIND({0,1,2,3,4,5,6,7,8,9},""" & ThisWorkbook.Name & """&1234567890)),6)")
End Function

Now I get an "overflow" error. Thanks for your quick replies.

 

[Macro_Nerd99]

Now I get an "overflow" error. Thanks for your quick replies.

Oh, nevermind. This error was on me. Your code worked PERFECT!. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[smozgur]

Your code worked PERFECT!. Thank you.

The marked solution post has been changed accordingly.