Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Byref argument type mismatch

  1. #1
    Join Date
    11th March 2005
    Posts
    8

    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.

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th May 2004
    Location
    central Europe
    Posts
    773

    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.

  3. #3
    Join Date
    11th March 2005
    Posts
    8

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st August 2010
    Posts
    17

    Re: Byref argument type mismatch

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,676

    Re: Byref argument type mismatch

    VB:
    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

  6. #6
    Join Date
    1st August 2011
    Posts
    2

    Re: Byref argument type mismatch

    Hello,

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

    VB:
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    1st August 2011
    Posts
    2

    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.


    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,924

    Re: Byref argument type mismatch

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

  9. #9
    Join Date
    5th February 2013
    Posts
    2

    Re: Byref argument type mismatch

    Quote 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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Type Mismatch Error
    By draconians in forum Excel and/or Email Help
    Replies: 4
    Last Post: May 25th, 2007, 00:35
  2. Type Mismatch
    By billyj in forum EXCEL HELP
    Replies: 1
    Last Post: March 17th, 2005, 06:19
  3. Type Mismatch....
    By coreyt in forum EXCEL HELP
    Replies: 2
    Last Post: December 16th, 2004, 03:47
  4. Type mismatch
    By gixxer in forum EXCEL HELP
    Replies: 1
    Last Post: April 7th, 2003, 12:27
  5. Type mismatch
    By Fluppe in forum EXCEL HELP
    Replies: 4
    Last Post: February 26th, 2003, 22:28

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno