#Value! question

[blubberbo]

hey everyone,

when I try to run the simple VBA code:

Please Login or Register  to view this content.

it gives me a #VALUE! error... any ideas whats wrong?

Thanks a lot

[ChemistB]

A function (user defined function) is used to return a value into the cell where the function is used (or within a program where the function is used). Your code is returning a value to cell A2. That would be a subroutine, not a function. Replace "Function" with "Sub" and it will work

Edit: If it is not your intention to return 2 to cell A2, exactly what are you trying to accomplish?

[vandan_tanna]

following will return 5 to the caller. Like ChemistB pointed out, function can not act on cells other than the caller cell.

Please Login or Register  to view this content.

[blubberbo]

hm, ok thank you for the input. I am trying to add this piece of code to a function to store a variable in a cell so I can call on it later even after the workbook is closed

[ChemistB]

I am trying to add this piece of code to a function to store a variable in a cell so I can call on it later even after the workbook is closed

You can place a "value" in a cell with either a subroutine or a UDF. You can use a subroutine to insert a constant or even a formula within a cell (as you were doing with your initial code). Case 1: a subroutine which you can call from the macro window or using a button. This one places the value of 2 into cell A2 of whichever sheet was active when the code was run

Please Login or Register  to view this content.

Case 2: a UDF (user defined function) will, as Vandan pointed out, will only return a value to the cell it is imbedded in. So with this code in a module, if you put in cell A2 =omg(), you'll return 2 to that cell.

Please Login or Register  to view this content.

Hope that helped.

[blubberbo]

yes thank you very much for the clarification!