Unable to modify cells in a VBA function

[Sergio Aguayo]

Hello

I'm writing a function which should perform its action, put a text of what
it did in a certain cell (passed by the user as a reference parameter) and
return the error status of the action.

That would be something like this:

function foo(parameter1 as string, outcell as string)

<here goes the real functions code>

if success then
Range(outcell).FormulaR1C1 = "all went ok"
foo = "TRUE"
else
Range(outcell).FormulaR1C1 = "something made a boo boo"
foo = "FALSE"
endif

end function

When it goes through the line which uses the range, it throws a 1004
error. I have tried using Formula, Value, Cells(1,1).value, etc but the
same error occurs. However, if i do the same from another function (which
gets called by a menu) it works.

Now i wonder if it's possible to do this, or how exactly, as i've tried
all i can think about.

Thanks in advance,

Sergio Aguayo

 

[Dave Peterson]

UDF's called from worksheet cells can return values to the cell that holds
them. They can't change the value in other cells.

 

[merjet]

http://groups.google.com/group/micr...cells"+function&rnum=1&hl=en#e8c001f50dd29036

Hth,

Merjet

 

[George Nicholson]

1004 means Excel doesn't have enough information to know what you want. You
need to be more specific. In this case it probably wants a worksheet
reference (i.e., SomeSheetRef.Range(outcell)). Excel will generally try to
use ActiveWorksheet as a default for Range if you don't supply it (which is
why it works when called from a UI menu, because there is an active sheet
when its called), but if you try to run that code from the VBE, you'd
frequently get a 1004 error because there isn't an active sheet at that
moment as far as Excel knows. You'd always get a 1004 if you called it while
a Chart sheet was active (no ActiveWorksheet).

HTH,


Hello

I'm writing a function which should perform its action, put a text of what
it did in a certain cell (passed by the user as a reference parameter) and
return the error status of the action.

That would be something like this:

function foo(parameter1 as string, outcell as string)

<here goes the real functions code>

if success then
Range(outcell).FormulaR1C1 = "all went ok"
foo = "TRUE"
else
Range(outcell).FormulaR1C1 = "something made a boo boo"
foo = "FALSE"
endif

end function

When it goes through the line which uses the range, it throws a 1004
error. I have tried using Formula, Value, Cells(1,1).value, etc but the
same error occurs. However, if i do the same from another function (which
gets called by a menu) it works.

Now i wonder if it's possible to do this, or how exactly, as i've tried
all i can think about.

Thanks in advance,

Sergio Aguayo

 

[Sergio Aguayo]

Thanks all for the responses. What about the C API? Can it be done using
the C API?

Thanks in advance,

Sergio Aguayo