ByRef argument type mismatch in Excel VBA

I suspect you haven’t set up last_name properly in the caller.

With the statement Worksheets(data_sheet).Range("C2").Value = ProcessString(last_name)

this will only work if last_name is a string, i.e.

Dim last_name as String

appears in the caller somewhere.

The reason for this is that VBA passes in variables by reference by default which means that the data types have to match exactly between caller and callee.

Two fixes:

1) Force ByVal — Change your function to pass variable ByVal:
Public Function ProcessString(ByVal input_string As String) As String, or

2) Dim varname — put Dim last_name As String in the caller before you use it.

(1) works because for ByVal, a copy of input_string is taken when passing to the function which will coerce it into the correct data type. It also leads to better program stability since the function cannot modify the variable in the caller.