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

0 votes

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

Apr 1, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

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

answered Apr 1, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

How do I insert a WebP-image (".jpg") in Excel using VBA?

It's not currently on the list of ...READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

How do I run a VBA Sub routine continuously when working in a Workbook and not only when the Workbook is opened?

on Thisworkbook, put: Private Sub Workbook_Open() Call checkPW(True) End Sub Then ...READ MORE

answered Jan 14, 2023 in Others by narikkadan
• 63,720 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP