PDA

View Full Version : [SOLVED] ByRef argument type mismatch



CuriousGeorg
07-29-2014, 06:49 AM
Good afternoon I'm wondering if you can help me resolve my issue.

I use the following code and get the "Compile error: ByRef argument type mismatch" error, and (imaxrow) is highlighted.



Private Sub nretained_click()
Dim imaxrow As Long


Application.ScreenUpdating = False


Call fClearReport
imaxrow = fgetreportretained2


Call nFormatReportRetained(imaxrow)


Application.ScreenUpdating = True


End Sub


nFormatReportRetained refers to:




Function nFormatReportRetained(imaxrow As Long)
Dim iRow As Long


If imaxrow = 0 Then
Exit Function
End If
iRow = 5

With Range("D2")
.Value = "Non " & fGetTextDate(SDate) & " - " & fGetTextDate(EDate)
.Font.Name = " Sans Bold"
.Font.Size = 16
.Font.Color = rsaPurple
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter

End With


Range("A4") =
Range("B4") =
Range("C4") =
Range("D4") =
Range("E4") =
Range("F4") =
Range("G4") =
(etc until "t4")


'Format The Headers
Range("A4:T4").Interior.Color = Purple
Range("A4:T4").Font.Color = White


Range("A" & iRow & ":S" & imaxrow).Borders(1).Color = Purple5
Range("A" & iRow & ":S" & imaxrow).Borders(2).Color = Purple5
Range("A" & iRow & ":S" & imaxrow).Borders(3).Color = Purple5
Range("A" & iRow & ":S" & imaxrow).Borders(4).Color = Purple5


Range("A" & iRow & ":S" & imaxrow).HorizontalAlignment = xlCenter


Columns("C:C").NumberFormat = "dd/mmm/yyyy"
Columns("D:D").NumberFormat = "dd/mmm/yyyy hh:mm "
Columns("G:G").NumberFormat = "dd/mmm/yyyy hh:mm"
Columns("S:S").NumberFormat = "dd/mmm/yyyy hh:mm"
Do Until iRow > imaxrow
Range("A" & iRow & ":S" & iRow).Interior.Color = Cream
iRow = iRow + 1
If iRow <= imaxrow Then
Range("A" & iRow & ":S" & iRow).Interior.Color = White
End If
iRow = iRow + 1
Loop


imaxrow = imaxrow + 1


Rows(imaxrow & ":1048576").EntireRow.Hidden = True
Columns("T:XFD").EntireColumn.Hidden = True


End Function




I cant see where Ive made the error. Tried a few things but really cant be getting away from that

p45cal
07-29-2014, 06:59 AM
aren't you missing a couple of arguments here:
Call nFormatReportRetained(imaxrow)
Shouldn't there be something for SDate and EDate?
Call nFormatReportRetained(~~~,~~~,imaxrow)
Also, although it should work fine, using imaxrow as a variable name in the arguments to call the function and calling it the same thing within the function is just confusing.
Finally, that function should really be a sub as functions are really not designed to do stuff, more just to return a value.

later…
I knew I wasn't seeing things:
12039

CuriousGeorg
07-29-2014, 07:14 AM
Apologies for the late adjustment to that sub i noticed that was an issue but I still pulled back an error. It was still in fact Edate and Sdate that were still causing the problem so pulled them out.

thanks for that. it seems to me when i pull it onto here and ask a question i half answer my own question.

Interesting regarding the sub though.. thanks for that.