I have a multiple cells in an Excel Spreadsheet that all call a user defined
function. There is a case where a different action (calling VBA code in
Excel) that gets data back and needs to update the text of those cells that
have the formula.

I have the data that need in the array and I know the cells that need their
text updated. I can not use Range.Text since that is readonly. If I call
Range.Value = values(i) that updates the text of the cell, but it also wipes
out the formula. Is there a way to programmatically update the text of a
cell without wiping out the formula? When a formula produces an error, it
puts text in the cell without wiping out the formula, so it must be possible.

You seem to have a misunderstanding.
The formula produces the text - even the error values.

Just to illustrate:
if you wanted your udf to produce an error value you would use

Public MyFunct(r as Range)
if r is nothing then
Myfunct = cvErr(xlErrRef)
exit function

' other code
end Function
as an example

or just to show from the immediate window:

activecell.Value = cvErr(xlErrRef)
? activecell.Text
#REF!

So a cell can either contain a formula or a constant value. If you assign a
constant, it overwrites the formula. Sounds like you need to modify your UDF
to produce the information you need or put textboxes over your cells that
contain the text you want to see.

--
Regards,
Tom Ogilvy


"J. Caplan" wrote:

I have a multiple cells in an Excel Spreadsheet that all call a user defined
function. There is a case where a different action (calling VBA code in
Excel) that gets data back and needs to update the text of those cells that
have the formula.

I have the data that need in the array and I know the cells that need their
text updated. I can not use Range.Text since that is readonly. If I call
Range.Value = values(i) that updates the text of the cell, but it also wipes
out the formula. Is there a way to programmatically update the text of a
cell without wiping out the formula? When a formula produces an error, it
puts text in the cell without wiping out the formula, so it must be possible.

Actually, the reason I am trying to do this is because I have a user defined
formula written in VB.Net using VSTO. This formula takes in name, hits a
database, and then returns back another attribute from the DB based on that
name. I have VBA code in Excel that goes through and recalculates the
selected cells.

The problem is that I want to avoid multiple hits to the database. To do
this, in the VBA code, I gather up all of the information needed from the
selected cells, pass the array of values to the VB.Net code and then call to
the DB once and get back a set of values. That set of values is then passed
back to the VBA Excel code in an array. At this point I wanted to update the
selected cells with their results, but not have to recalc the formulas since
it was done once for all cells.

My next step is to build a cache from the single DB call that my formula
looks in first, but I didn't want to go down the road if not needed.

"Tom Ogilvy" wrote:

You seem to have a misunderstanding.
The formula produces the text - even the error values.

Just to illustrate:
if you wanted your udf to produce an error value you would use

Public MyFunct(r as Range)
if r is nothing then
Myfunct = cvErr(xlErrRef)
exit function

' other code
end Function
as an example

or just to show from the immediate window:

activecell.Value = cvErr(xlErrRef)
? activecell.Text
#REF!

So a cell can either contain a formula or a constant value. If you assign a
constant, it overwrites the formula. Sounds like you need to modify your UDF
to produce the information you need or put textboxes over your cells that
contain the text you want to see.

--
Regards,
Tom Ogilvy


"J. Caplan" wrote:

I have a multiple cells in an Excel Spreadsheet that all call a user defined
function. There is a case where a different action (calling VBA code in
Excel) that gets data back and needs to update the text of those cells that
have the formula.

I have the data that need in the array and I know the cells that need their
text updated. I can not use Range.Text since that is readonly. If I call
Range.Value = values(i) that updates the text of the cell, but it also wipes
out the formula. Is there a way to programmatically update the text of a
cell without wiping out the formula? When a formula produces an error, it
puts text in the cell without wiping out the formula, so it must be possible.

Or you could create an array formula if the cells are contiguous.

Chip Pearson talks about this for VBA UDF's for information:

http://www.cpearson.com/Excel/Return...ysFromVBA.aspx

--
Regards,
Tom Ogilvy



"J. Caplan" wrote:

Actually, the reason I am trying to do this is because I have a user defined
formula written in VB.Net using VSTO. This formula takes in name, hits a
database, and then returns back another attribute from the DB based on that
name. I have VBA code in Excel that goes through and recalculates the
selected cells.

The problem is that I want to avoid multiple hits to the database. To do
this, in the VBA code, I gather up all of the information needed from the
selected cells, pass the array of values to the VB.Net code and then call to
the DB once and get back a set of values. That set of values is then passed
back to the VBA Excel code in an array. At this point I wanted to update the
selected cells with their results, but not have to recalc the formulas since
it was done once for all cells.

My next step is to build a cache from the single DB call that my formula
looks in first, but I didn't want to go down the road if not needed.

"Tom Ogilvy" wrote:

You seem to have a misunderstanding.
The formula produces the text - even the error values.

Just to illustrate:
if you wanted your udf to produce an error value you would use

Public MyFunct(r as Range)
if r is nothing then
Myfunct = cvErr(xlErrRef)
exit function

' other code
end Function
as an example

or just to show from the immediate window:

activecell.Value = cvErr(xlErrRef)
? activecell.Text
#REF!

So a cell can either contain a formula or a constant value. If you assign a
constant, it overwrites the formula. Sounds like you need to modify your UDF
to produce the information you need or put textboxes over your cells that
contain the text you want to see.

--
Regards,
Tom Ogilvy


"J. Caplan" wrote:

I have a multiple cells in an Excel Spreadsheet that all call a user defined
function. There is a case where a different action (calling VBA code in
Excel) that gets data back and needs to update the text of those cells that
have the formula.

I have the data that need in the array and I know the cells that need their
text updated. I can not use Range.Text since that is readonly. If I call
Range.Value = values(i) that updates the text of the cell, but it also wipes
out the formula. Is there a way to programmatically update the text of a
cell without wiping out the formula? When a formula produces an error, it
puts text in the cell without wiping out the formula, so it must be possible.

Unfortunately, there is no guarantee that they will be contiguous. The
formula is meant to be called for a single value, however, the data in the
database changes often. I wanted to provide a way for the user to update the
entire sheet or selected cells and not have to call the DB multiple times.



"Tom Ogilvy" wrote:

Or you could create an array formula if the cells are contiguous.

Chip Pearson talks about this for VBA UDF's for information:

http://www.cpearson.com/Excel/Return...ysFromVBA.aspx

--
Regards,
Tom Ogilvy



"J. Caplan" wrote:

Actually, the reason I am trying to do this is because I have a user defined
formula written in VB.Net using VSTO. This formula takes in name, hits a
database, and then returns back another attribute from the DB based on that
name. I have VBA code in Excel that goes through and recalculates the
selected cells.

The problem is that I want to avoid multiple hits to the database. To do
this, in the VBA code, I gather up all of the information needed from the
selected cells, pass the array of values to the VB.Net code and then call to
the DB once and get back a set of values. That set of values is then passed
back to the VBA Excel code in an array. At this point I wanted to update the
selected cells with their results, but not have to recalc the formulas since
it was done once for all cells.

My next step is to build a cache from the single DB call that my formula
looks in first, but I didn't want to go down the road if not needed.

"Tom Ogilvy" wrote:

You seem to have a misunderstanding.
The formula produces the text - even the error values.

Just to illustrate:
if you wanted your udf to produce an error value you would use

Public MyFunct(r as Range)
if r is nothing then
Myfunct = cvErr(xlErrRef)
exit function

' other code
end Function
as an example

or just to show from the immediate window:

activecell.Value = cvErr(xlErrRef)
? activecell.Text
#REF!

So a cell can either contain a formula or a constant value. If you assign a
constant, it overwrites the formula. Sounds like you need to modify your UDF
to produce the information you need or put textboxes over your cells that
contain the text you want to see.

--
Regards,
Tom Ogilvy


"J. Caplan" wrote:

I have a multiple cells in an Excel Spreadsheet that all call a user defined
function. There is a case where a different action (calling VBA code in
Excel) that gets data back and needs to update the text of those cells that
have the formula.

I have the data that need in the array and I know the cells that need their
text updated. I can not use Range.Text since that is readonly. If I call
Range.Value = values(i) that updates the text of the cell, but it also wipes
out the formula. Is there a way to programmatically update the text of a
cell without wiping out the formula? When a formula produces an error, it
puts text in the cell without wiping out the formula, so it must be possible.