The basic process works as intended here. I agree that if the code is an accurate representation, the problem is
Call ReplaceHyperlinkURL("W:\", "E:\My Documents\")
Sub ReplaceHyperlinkURL()
End Sub
You are using a sub with the same name as the main sub to call it and yet the call is outside the second sub.
Code:
Sub RunReplaceURL()
Call ReplaceHyperlinkURL("W:\", "E:\My Documents\")
End Sub
as suggested will fix it.
Note that the process is case sensitive, and while it is unlikely that your hyperlinks contain 'w:\' in lower case, it is not impossible and it won't work for those links. In which case either correct for case in the main sub or call it a second time with lower case 'w:\'.
Use the # button and paste your code between the markers in inserts to keep the format or your code.