Unable to modify cells in a VBA function
 

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

Unable to modify cells in a VBA function
 

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

Sergio Aguayo wrote:

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

Unable to modify cells in a VBA function
 

http://groups.google.com/group/micro...c001f50dd29036

Hth,

Merjet

Unable to modify cells in a VBA function
 

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,


"Sergio Aguayo" wrote in message
news:op.tot7xchqfbr5x3@dravio-79...
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

Unable to modify cells in a VBA function
 

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

Thanks in advance,

Sergio Aguayo


On Wed, 07 Mar 2007 16:45:22 -0500, Dave Peterson
wrote:

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

Sergio Aguayo wrote:

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




--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/