Share via

Access VBA - confused by Byref Argument Type Mismatch

Anonymous
2025-04-13T23:17:47+00:00

I am attempting to call a function that also passes by data in ByRef variable. Everything is a type string and the calling sub has the receiving variable set as type string. Yet I am getting the Byref mismatch message. The ByVal is the string variable passed in and the 3 Byref strings are passed out. Also, the function returned value is a String.

Images and a sample of the code are below. What am I doing wrong?

Public Sub CheckboxClicked(checkboxname As String) 

Dim strFormPrefix, strAlphaNumText, strAlphaNumInteger, strFormName As String 

strFormPrefix = "" 

strAlphaNumText = "" 

strAlphaNumInteger = "" 

strFormName = GetFormNameFromPrefix(checkboxname, strFormPrefix, strAlphaNumText, strAlphaNumInteger) 

Select Case strFormName 

    Case "CreateBurialSourceTableFormat" 

        CreateBurialSourceTableFormat\_CheckBoxClicked checkboxname 

    Case Else 

End Select 

MsgBox checkboxname

End Sub

Public Function GetFormNameFromPrefix(ByVal strPrefix As String, ByRef FormPrefix As String, ByRef AlphaNumText As String, ByRef AlphanumInteger As String) As String

Microsoft 365 and Office | Access | For home | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2025-04-14T00:52:50+00:00

    To elaborate on theDBguy:

    strFormPrefix is indeed a Variant, as you dimmed it.

    Debug.Print VarType(strFormPrefix)

    0 '0=vbEmpty

    Passing such undefined variable to GetFormNameFromPrefix causes the error: VBA does not know what the data type will be at runtime, so it fails at compile time.

    The fix is:

    Dim strFormPrefix As String

    Beyond that, you seem to be somewhat random about the use of ByRef/ByVal.

    Here are some guidelines:

    1. If you don't specify ByRef/ByVal, then it is ByRef by default.
    2. If the procedure does not modify the argument, or modifies it but doesn't mean for the calling proc to know that value, declare as ByVal.
    3. Only declare as ByRef if you mean the calling procedure to know the new value you're changing in the called proc.
    4. Objects (e.g. recordsets, controls, forms) should be declared ByRef, showing your understanding that objects are always passed as pointers, not as their (potentially very large) actual object bytes.

    Was this answer helpful?

    0 comments
  2. DBG 11,711 Reputation points Volunteer Moderator
    2025-04-13T23:29:19+00:00

    When you do something like this:

    Dim var1, var2, var3 As String

    Only var3 is declared as a string variable. The rest are created as a Variant variable.

    Not sure, but maybe that's part of the issue causing your problem.

    Was this answer helpful?

    0 comments