Need help: Error passing listobject as parameter to a sub

[inddon]

Hello There,

I have a Sub GetReferenceName which gets a value from the another excel table. This Sub uses parameters as List Object, Range and strings.

The above Sub is called from event Worksheet_Change. I am getting an error on this event on calling this Sub

Error Compile: ByRef argument type mismatch
(highlighted code Call GetReferenceName(pTable:=O_MstTbl_LegalEntities)

Could you please help?


Many thanks & regards,
Don


Below code:

Workbook_open

Sub Workbook_open
   Call Assign;
End Sub

Assign

Sub Assign
   Public MstWB As Workbook
   Public MstWSName_LegalEntities As Worksheet
   Public MstTblName_LegalEntities As String
   Public O_MstTbl_LegalEntities As ListObject

   Set MstWB = Workbooks.Open(Filename:=ÄBC.xlsx, ReadOnly:=False, Notify:=False)

   'Set Master Worksheets and Master Legal Entities
    Set MstWSName_LegalEntities = MstWB.Worksheets("LegalEntities")
   MstTblName_LegalEntities = "Tbl_LegalEntities"
   Set O_MstTbl_LegalEntities = MstWSName_LegalEntities.ListObjects(MstTblName_LegalEntities)
End Sub

Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range) 
If Not Intersect(Target, Range("FLegalEntityNumber")) Is Nothing Then
    Call GetReferenceName(pTable:=O_MstTbl_LegalEntities, _
                         pFieldRange:=EntWSDE_Header.Range("FLegalEntityNumber"), _
                         pTableColumnNumber:="Entity Number", _
                         pTableColumnName:="Entity Name")
End if

GetReferenceName

Sub GetReferenceName(ByRef pTable As ListObject, _
                     ByRef pFieldRange As Range, _
                     ByVal pTableColumnNumber As String, _
                     ByVal pTableColumnName As String)
  Dim fnd As Range
  
  Set fnd = pTable.ListColumns(pTableColumnNumber).DataBodyRange.Find(What:=pFieldRange, LookIn:=xlValues, Lookat:=xlWhole)
  If Not fnd Is Nothing Then
     [DLegalEntityName] = Intersect(pTable.ListColumns(pTableColumnName).DataBodyRange, fnd.EntireRow).Value
  Else
     [DLegalEntityName] = "<Name not found in Table Legal Entities.>"
  End If
End Sub

 

[Chihiro]

Global variables should be declared outside of Sub/Function.

Meaning, it should be at the top of the module.

 

[inddon]

Hi Chihiro,

Thank you for your reply.

The Public variables are declared at the beginning of the Module with Option Explicit. My CopyPaste mistake.

Anything wrong you can detect in the code, as to why it is giving a
ByRef argument type mismatch?

Thanks & regards,
Don

 

[Chihiro]

That's very common mistake.

Have a read of below links.
MS documentation - https://docs.microsoft.com/en-us/of...r-Interface-Help/byref-argument-type-mismatch
Chip's excellent article on the subject - http://www.cpearson.com/excel/byrefbyval.aspx

 

[inddon]

Hi Chihiro,

Thank you for the links. I checked but just cannot get it.

Regards,
Don

 

[Chihiro]

I'm headed out for the night, but if you need further help, upload sample workbook with set up.

 

[inddon]

Hi Chihiro,

Thank you for your reply.

The actual workbooks are quite big. Therefore, posted only the code that was not working.

Fortunately, when I extracted only that piece of code which was not working in the sample files, now it just works.

Please find attached the sample workbooks for your reference.

I will have to dig in to the actual workbook and see if there is some initialization of public variables is needed.

Regards,
Don