ByRef Argument Type Mismatch - problem with Range.

[syparth]

I have three functions .
The main function which gets called from Workbook is "getRefi"| (as given below).
it s second line - SecuredLastRange = getLastValueInList(SecuredFirstRange) - throws the error, Can you please tell me what is worng in my VBA code ?

Public Function getRefi(rownum As Integer) As String
SecuredFirstRange = Sheets("Data").Range("K5")
SecuredLastRange = getLastValueInList(SecuredFirstRange) -- This line throws , ByRef Argument Type mismatch
End Function

Public Function getLastValueInList(TimeBucketData As Range) As Range
Do While hasNextRow(TimeBucketData)
TimeBucketData = TimeBucketData.Offset(1, 0)
Loop
getLastValueInList = TimeBucketData
End Function

Public Function hasNextRow(rownum As Range) As Boolean
If rownum.Offset(1, 0) <> "" Then
hasNextRow = False
Else
hasNextRow = True
End If
End Function

[JosephP]

please add code tags to your post

you have not declared the variable as a range or assigned a range to it

Please Login or Register  to view this content.

[syparth]

Thanks a lot , it s been resolved.
If you can resolve one confusion over this issue.
Do we need to declare all the variables using "Dim" in EXCEL VBA and set their valye using "Set"? As many times i do not declare them but still it works and with "Set" i used to set the values.

[JosephP]

generally you don't need to but it is recommended by most

if you wish to pass data byref to your routines and that type is not Variant then you will need to declare it (if the data needs to be changed by the called routine)

[syparth]

Thanks a lot. Thankyou.

[JosephP]

I didn't see your edit

you have to use Set when you are dealing with object variables (like ranges, worksheets, workbooks) but not with data types (integers, strings, dates)

[syparth]

Ok, Thanks,
I edited to add "Set" question.
Thank you very much.

[Colin Legg]

Do we need to declare all the variables using "Dim" in EXCEL VBA and set their valye using "Set"?

For Object types (eg Range, Worksheet) you need to use the Set keyword. There's a Let keyword for literal types (eg String, Integer) but it's optional and is usually omitted in VBA code.

If you have an Option Explicit statement at the top of your code module then you must declare your variables. This is a good practice because declaring your variables will help you avoid hard to spot errors in your code such as typos and it will improve your understanding of what the code is really doing. Using Option Explicit can be a bit frustrating when you first start writing VBA because the VBA IDE might complain and give you some compile errors: I encourage you to persevere with it and we are always here to help you if you're not sure what the problem is. You'll be a better coder for it.

[syparth]

Thank you so much for kind support.
I thank this forum.