Byref argument type mismatch

[Michelangelo]

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.

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.

[h1h]

in the private sub you do not declarethem the same.

Dim InputSheetName as string, OutputSheetName As String

should do it

[Michelangelo]

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

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

Apparantly not.

[Bannout]

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

[PCI]

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

[faetoner]

Hello,

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

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!

[faetoner]

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.

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 

[Reafidy]

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

[sleepy_tynes]

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