Compile error: ByRef argument type mismatch in function call

USAMax

Well-known Member
Joined
May 31, 2006
Messages
826
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
May 31, 2006
Messages
826
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
May 31, 2006
Messages
826
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.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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...

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

Watch MrExcel Video

Forum statistics

Threads
1,095,364
Messages
5,444,025
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top