Compile error: ByRef argument type mismatch in function call

[USAMax]

I have had this problem before but never in something so simple. In this case it is looking at the variable DataRowCount that is defined as an Integer in the function and assigned the value of 1 in the subroutine. In the subroutine I get the Compile error and it highlights the variable. Can someone tell me why?

Function UpdateWeekDetailsSheet(ProjectName As String, FirstRowOfData As Integer, DataRowCount As Integer, FirstDateColumn As Integer)
Sheets(WeekDetails).Select
Cells.Find(What:=ProjectName, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate 'Search for the Project Name
End Function



Sub TestCall()
Dim CurRow As Integer 'Current Row is used when searching rows
DataRowCount = 1
CurRow = ActiveCell.Row
Do While Cells(ActiveCell.Row + 1, 2) <> ""
DataRowCount = DataRowCount + 1
Loop
Call UpdateWeekDetailsSheet(Cells(ActiveCell.Row - 1, 1), ActiveCell.Row, DataRowCount, ActiveCell.Column)
End Sub

 

[Jonmo1]

Add this line in the SUB,
Dim DataRowCount As Integer

Since you didn't Dim that variable in the SUB, then according to the SUB it's a Variant
That means VBA decides for you what datatype it will be when you use it in the loop.
It's probably being used as Double, or maybe Long..


FYI, I would suggest using the LONG datatype for Row# Variables.
Integer is limited to somewhere around 32000, while there are over 65000 available rows.

 

[USAMax]

The program hasn't even gotten as far as the worksheet. As soon as it runs I get the error. This is the most basic application I have ever gotten the error so I thought it would be the best one to use to get the answer. I keep asking myself, "In what universe is the number 1 not an integer?" For now I have DataRowCount set as a Variant.

 

[Jonmo1]

When you Run the SUB, DataRowCount is a variable IN THAT SUB...
VBA doesn't look ahead to the FUNCTION to see what it's dimmed as there...
So in the SUB it's a Variant.
Then you're passing a variant to the funciton which is expecting an Integer - mismatch.

Add the line to the SUB
Dim DataRowCount As Integer

 

[USAMax]

Thank you that does make it more clear but I have two other variables defined as Integers, FirstRowOfData and FirstDateColumn. I have to assume these are not affected as they are only defined within the call? It just sounds strange that I would Dim the variable in the Sub and Function.

Also, most of my data is finance and almost never goes beyond 1000 rows and 256 columns so an integer should do the trick.

 

[USAMax]

You rock Jonmo1!

 

[Jonmo1]

They are 2 different variables. One in the SUB, and one in the FUNCTION.
They BOTH must be the same, because the Function is expecting an Integer

Maybe this will help with that point...

Function Test(FnVariable As Integer)
Test = FnVariable * 2
End Function

Sub Test2()
Dim SubVariable As Integer
Dim Rslt As Integer

SubVariable = 12
Rslt = Test(SubVariable)
Msgbox Rslt
End Sub