You do not need formula. Formula will be evaluated each time even the record is not edited. You can directly fill lookup field on Campaign Member.
$User global variable can be used to get current user ID.
A global merge field type to use when referencing information about the current user. User merge fields can reference
information about the user such as alias, title, and ID. Most of the
fields available on the User standard object are also available on
You can write a workflow rule having criteria as:
Update lookup field directly. Field update would as below: