How Do I Correct a ByRef Mismatch Error in VBA for Worksheet Change Sub in Excel

Question

I'm attempting to automate a procedure that requires pressing the "Repair UPC" button on an Excel Spreadsheet at the moment. If a text-formatted string is 12 characters or longer, this button will erase the last digit and change the number format to "0-00000-00000." The vast majority of UPCs this sheet will deal with adhere to the UPC-A standard, but like many systems, ours ignores the last check digit; as a result, it must be trimmed to 11 for a VLOOKUP to function correctly. Some must be left intact since they adhere to the 13-digit EAN standard.

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.

Answer 1

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...