Row() function use in EXCEL VBA

I have User Defined Function - getRefi(Row()).
I want to assign this function as value to some sepecific cells in worksheet.
I wrote a function as given below, but excel is not recognises the Row() funciton, while it works fine in workbook.

Please Login or Register  to view this content.

please add code tags to your post

According to http://msdn.microsoft.com/en-us/libr.../ff836235.aspx the Row() worksheet function is NOT on the list of worksheet functions available to VBA. It seems likely that you will need a different approach to get the row number.

If this is really a function procedure that you intend to call from a spreadsheet cell, I would get rid of the input boxes and pass that information to the function through the argument list. I would probably also structure the other input and output differently.

If this is not intended to be called from a spreadsheet cell, I would probably change it to a sub procedure.

c.row will give you the number you want

You haven't specifically dimensioned c as anything, so it defaults to a variant, which can hold just about any data type. So when you let c=sheet("result reader")..., it probably assumes you mean that c should equal the value of whatever is in that cell. To see this, step through your code, with a watch set for c. I expect under "type" it will say something like "variant/double" or "variant/string" depending on what is in that cell.

What you want is to set c as an object reference to the specified range/cell object. In order to do this, you must use the Set statement.

Please Login or Register  to view this content.

. You might search VBA help for the set statement for further details. Using the set statement, I would expect you to see "variant/range" for the c's data type.