Announcement

Collapse
No announcement yet.

Byref argument type mismatch

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Byref argument type mismatch

    Hi, this is puzzling me for a while now.

    When I create a function and use arguments it often gives me the error message like Byref argument type mismatch.

    Please look at the following code that produces the error.

    Code:
    Private Sub CommandButton1_Click()
    Dim InputSheet, OutputSheet As Worksheet
    Dim InputSheetName, OutputSheetName As String
    
    InputSheetName = "SheetInput"
    OutputSheetName = "SheetOutput"
    
    Set InputSheet = Worksheets(InputSheetName)
    Set OutputSheet = Worksheets(OutputSheetName)
    
    Call CopyCells(InputSheet, OutputSheet)
    
    InputSheet = Nothing
    OutputSheetName = Nothing
    
    End Sub
    
    Sub CopyCells(InputSheet As Worksheet, OutputSheet As Worksheet)
    For a = 1 To 10
        For b = 2 To 4
            OutputSheet.Cells( a , b) = InputSheet.Cells(a, b)
        Next
    Next
    
    End Sub
    It gives the error on the InputSheet argument of CopyCells sub. Both items are declared as the same type. Any ideas

    I've had this happening on strings and other types as well. Changing it to variant (in the called sub) usually solves it, but it's not a very nice and clean solution.

  • #2
    Re: Byref argument type mismatch

    in the private sub you do not declarethem the same.

    Dim InputSheetName as string, OutputSheetName As String

    should do it
    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Comment


    • #3
      Re: Byref argument type mismatch

      Thanx, that does it. I allways thought that the statement

      dim a,b as integer would declare them both as integer.

      Apparantly not.

      Comment


      • #4
        Re: Byref argument type mismatch

        hi can any one help me how can i ask a question ? a new thread??

        Comment


        • #5
          Re: Byref argument type mismatch

          Code:
          Dim InputSheet, OutputSheet As Worksheet
          Dim InputSheetName, OutputSheetName As String
          Only the LAST argument is assigned others are variant

          hi can any one help me how can i ask a question ? a new thread??
          Yes
          Triumph without peril brings no glory: Just try

          Comment


          • #6
            Re: Byref argument type mismatch

            Hello,

            I get this "ByRef Argument Type Mismatch" error on function getNonUniqueRecord calling, by argument "day":

            Code:
            Private Function getNonUniqueRecord(day As String) As Integer
                If listExtra.ListCount = 0 Then
                    getNonUniqueRecord = -1
                Else
                    Dim iter As Integer
                    iter = -1
                    Do
                        iter = iter + 1
                    Loop Until iter = listExtra.ListCount + 1 Or Trim(Mid(listExtra.ListIndex(iter), 1, 2)) = CStr(day)
                    
                    If iter = listExtra.ListCount + 1 Then iter = -1
                    getNonUniqueRecord = iter
                End If
            End Function
            
            
            Private Sub addExtraToList(day, month, tim, ob1, ob2, reason As String)
                Dim res As String
                Dim duplicateIndex As Integer
                'find index in list by day
                duplicateIndex = getNonUniqueRecord(day)  'returns index = -1 if not found or if list is empty
                res = day + " " + Mid(month, 1, 3) + "   tim: " + tim
                If ob1 <> "" And ob1 <> "0" Then res = res + "   OB1: " + ob1
                If ob2 <> "" And ob2 <> "0" Then res = res + "   OB2: " + ob2
                res = res + "   " + reason
                'if duplicate not found
                If duplicateIndex = -1 Then
                    listExtra.AddItem res
                'else modify existing
                Else
                    'delete it and insert new on the same place
                    listExtra.RemoveItem (duplicateIndex)
                    listExtra.AddItem res, duplicateIndex
                End If
            End Sub
            If I declare arguments (in Sub and Function) as Variant, then VBA throws "Run-time error '13': Type mismatch"
            (it doesn't mark error place in this case, but when I comment second condition in "Loop Until" with "day" parameter usage, this error disappears.)

            The only one choice is to write this function "getNonUniqueRecord" as plain code in sub "addExtraToList", but I try to avoid this coding style - it's less readable.

            I hope you guys can help me. Thanks in advance!

            Comment


            • #7
              Re: Byref argument type mismatch

              Ello one more time!

              Using of ByVal in front of parameter helped... Of course, I wrote completely wrong iteration of listBox, but the main problem was solved exactly after ByVal.


              Code:
              Private Function getNonUniqueRecord(ByVal day As String) As Integer
                  getNonUniqueRecord = -1
                  Dim str As String
                  Dim iter As Long
              
              
                  For iter = 0 To Me.listExtra.ListCount - 1
                      str = Me.listExtra.Column(0, iter)
                      If Trim(Mid(str, 1, 2)) = day Then getNonUniqueRecord = iter
                  Next iter
              End Function

              Comment


              • #8
                Re: Byref argument type mismatch

                Please read the rules. Start your own thread if you have questions.
                Reafidy

                Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                Comment


                • #9
                  Re: Byref argument type mismatch

                  Originally posted by Michelangelo View Post
                  Thanx, that does it. I allways thought that the statement

                  dim a,b as integer would declare them both as integer.

                  Apparantly not.
                  This statement clears it up completely. I thought the same thing!
                  Dim a, b as integer

                  is different from

                  Dim a as integer
                  Dim b as integer

                  Comment

                  Working...
                  X