How Do I Correct a ByRef Mismatch Error in VBA for Worksheet_Change Sub in Excel?

I am trying to automate a process that currently involves clicking a button on an Excel Sheet called "Fix UPC". This button looks at a text-formatted string to see if it is 12 characters long, if so, it will delete the last digit and then set the number format to "0-00000-00000". The vast majority of the UPCs this sheet will deal with follow the UPC-A standard, but like a lot of systems, ours ignores the last check digit, hence the need to trim it to 11 so a VLOOKUP will work properly. A few follow the 13-digit EAN standard so those need to be left intact.

Right now I have the following function located in a module named Helpers:

Public Function deleteCheckDigit(theUPC) As String
    If Len(theUPC) = 12 Then
        deleteCheckDigit = Left(theUPC, 11)
    Else
        deleteCheckDigit = theUPC
    End If
End Function

The Private Sub on the Worksheet is:

Private Sub Worksheet_Change(ByVal Target As Range)
    theCol = Helpers.GetColumnFromAddress(Target.Address)
    theValue = Range(Target.Address).Value
    
    If theCol = "A" Then
        If Len(theValue) = 12 Then
            Target.Value = Helpers.deleteCheckDigit(theValue)
            Range(Target.Address).NumberFormat = "0-00000-00000"
        End If
    End If
End Sub

When I enter in a UPC and hit Enter, Excel throws a Compile Error stating ByRef argument type mismatch. I tested VarType(theValue) and it returns 8 meaning it is a string, so I am not seeing what is causing this error.

Note: I have this set up with an If/Then block to check the column letter because there is some other modifications being done to different columns.

Len of a string returns what you need. But Len of a number returns differently...

So, it would be good to declare the function parameter deleteCheckDigit(theUPC As String) and and call it casting the argument
like deleteCheckDigit(CStr(theValue)), to be sure that a String is sent to the function.

It will also be good to ALWAYS declare all used variables, respectively:

  Dim theCol As Sring
  Dim theValue As String

Doing it and using Option Explicit on top of the module, you are warned if a typo will appear, by mistake. Something as theVal. In such a case you can call the function like deleteCheckDigit(theValue), theValue being already converted to String.

About the dispute regarding using of the column letter or number, it depends on the people skills to debug the code. They are similar and I also prefer using the column number, but using its letter may be more eloquent for a less skilled operator trying to understand and debug the code...