Problem with Function that Returns Selected Row in a Specified Worksheet

[Dirigo]

I wish my function to return the row number of the selected cell in a specified inactive worksheet ("Clients"). Instead my function is returning the row number of the active cell of the worksheet I'm working on.

When I run the function as a macro it returns the desired value.

Please Login or Register  to view this content.

The var MyRow is returning the active row number of the sheet I've inserted the function into, not the row number of the selected cell in the "Clients" worksheet. However, the macro version of this program works as I would expect and returns the desired row number:

Please Login or Register  to view this content.

Thanks.

[Leith Ross]

Hello Dirigo,

Welcome to the Forum!

I tried the macro on my machine and it works. Did you place the macro in a VBA module or in a Worksheet module? It needs to be in a VBA module to work correctly.

[Dirigo]

Hello Dirigo,

Welcome to the Forum!

I tried the macro on my machine and it works. Did you place the macro in a VBA module or in a Worksheet module? It needs to be in a VBA module to work correctly.

Thanks.

The function is placed in the Modules Folder of my workbook with all the other macros so I think it's in the right place.

[Leith Ross]

Hello Dirigo,

You have it in the right place. Is this the only macro in the workbook that isn't working correctly?

[Dirigo]

Hello Dirigo,

You have it in the right place. Is this the only macro in the workbook that isn't working correctly?

Yes, everything else seems to be working correctly. When I run the function from the VBA editor, it seems to work fine. But when I place it in a cell as =MyFunction() and update the value, the var MyRow incorrectly reflects the row of the cell in the active worksheet, not the "Clients" worksheet.

This is my first User Defined Function. Is there something I'm missing?

BTW, earlier I thought the function was properly working in another workbook, but now I cannot even replicate that.

[Leith Ross]

Helo Dirigo,

I just remembered when using UDFs there are limitations on what you can do. Basically, any VBA function that moves the worksheet cursor internally will produce an error. You can not activate another sheet in a UDF. You retrieve a cell's value using the Worksheet Index function but a sheet needs to be activated before you can retrieve the ActiveCell. The example below will return the value of cell A5 to the cell with the UDF. Confusing, I know.

Please Login or Register  to view this content.

[Dirigo]

Helo Dirigo,

I just remembered when using UDFs there are limitations on what you can do. Basically, any VBA function that moves the worksheet cursor internally will produce an error. You can not activate another sheet in a UDF. You retrieve a cell's value using the Worksheet Index function but a sheet needs to be activated before you can retrieve the ActiveCell. The example below will return the value of cell A5 to the cell with the UDF. Confusing, I know.

Darn. At least I won't waste any more time on that method. I guess I will pass the row number to the function as a public variable defined in a macro.

Thanks.