Help with Range.Value2, Range.Text and UDFs

[JsjsLim]

Hi,

I'm creating an Excel application level solution (using AddIn and UDF
classes).

I have 2 UDFs: Cache() and AnotherFunction().

The Cache() function inits/creates a cache on a remote server, which returns
an ID that can be referenced by AnotherFunction().

The AddIn is set to find all Cache() functions and run them when a workbook
is loaded (ensure that the caches are initialized on the server). Of course,
any AnotherFunctions() functions that references the Cache() function will
also update (when the Application.Calculation is set to Auto).

But here's the scenario that's baffling me:

AddIn:
Finds a Cache() function, inserts it. I then do a quick test to check the
values of the range (Range.Value2, Range.Text, Range.Formula), all which are
correctly populated.

UDF:
Since an AnotherFunction() function refers to the Cache() function, the
AnotherFunction's UDF is executed once the Cache() function returns. However,
when I try to retrieve the Range.Value2 from the parameter, I get null. Only
Range.Text and Range.Formula is populated.

Am I missing a step? Why is Value2 null in the UDF, but correctly populated
in the AddIn?

Please help.

Thanks.

 

[Charles Williams]

..Value and .Value2 get set to Empty during a calculation event when the cell
is uncalculated.
see http://www.decisionmodels.com/calcsecretsj.htm

..Text only gets reset after calculation when the formatting layer gets
refreshed. Using .text in Addins or UDFs is dangerous because:
- you may not get the current value if it has not yet been refreshed
- if the user changes the formatting you may get an unexpected value


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Joel]

I can't tell which classes the data structure belong to by your description
and which classes are private and which classes are public. Usually excel
defaults all classes as private. If a data structure is shared between two
different classes it must be made public.

 

[JsjsLim]

What's happened here is:

I have an addin class (deals with the office fluent ribbon bar, custom task
panes, etc), and a UDF class (adds user-defined functions into Excel).

The UDF class provides 2 functions, one of which returns a value that needs
to be used by the other (in my case, a cache function that returns a cache
id).

The other function accepts the value in (either as a Excel Range object, or
a string) via the parameter. If the value is an Excel Range, my code uses the
range.Value2 property to retrieve the value in the range.

--------------

At my addin level: when a workbook is loaded, it is scanned for these
functions. If the addin finds a cache function, the function is executed
again. This then sets off a chain event (due to Excel's Auto Calculation),
where any dependent functions are updated as well (including the other
function that I've mentioned above).

The problem is, when the range (containing the cache function) is passed
into the other function, the range.Value2 property returns null, even though
I'm expecting it to be populated.

Addin code:

private void Application_WorkbookOpen(Workbook workbook)
{
foreach(Worksheet worksheet in workbook.Worksheets)
{
// simple sample of finding the first cache formula and updating its
value
Range cacheRange = worksheet.UsedRange.Find("=Cache", missing,
missing...);

// Reinsert formula
cacheRange.Formula = cacheRange.Formula;

// If you were to get the value of cacheRange.Value2, you'll see
that the
// it is NOT null, and correctly populated with the returned cache ID
// Debug.Assert(cacheRange.Formula!=null);
}
}

UDF code:
public string Cache(...)
{
string cacheID = _client.InitializeCache();
return cacheID;
}

public string AnotherFunction(object cacheID,...)
{
string strCacheID;
if (cacheID is string)
{
// String argument.
strCacheID = (string)cacheID;
}
else if (cacheID is Range)
{
// Excel range argument
// we need to extract the value from the range
strCacheID = ((Range) cacheID).Value2;

// note: But here's where my problem starts. Value2 always returns
null, even
// note: though it is clearly populated in the AddIn
}
}


I hope my examples will make things a bit clearer. I know it's confusing,
since it spans multiple classes, and I really appreciate any help.

Thanks

 

[Joel]

ClassID needs to be a public variable. It is only defined inside the class
cache. Even though cashe is public is doesn't mean the casheid is also
public.

 

[JsjsLim]

I think there's been a misunderstanding.

I'll try to explain my situation again:

I have created 2 Excel functions.
1: Cache(), which returns a cache id
2: AnotherFunction(cacheId,...), which accepts a cache id as an argument

Assume:
A1: =Cache()
A2: =AnotherFunction(A1,...)

When the value in A1 updates (eg, =Cache() returns a new id), the
AnotherFunction() gets called, with the A1 Excel Range object as the argument.

AnotherFunction() then proceeds to extract the value from the Range object
(via Range.Value2 property).

My problem:

I have an Excel addin which calls the =Cache() function when the workbook is
loaded. Immediately after calling it, the A1 Excel range object is in the
following state:

Range.Formula: "=Cache()"
Range.Text: "Cache_id_1"
Range.Value2: "Cache_id_1"

When the AnotherFunction() is called, the Excel range object is in the
following state:

Range.Formula: "=Cache()"
Range.Text: "Cache_id_1"
Range.Value2: null

AnotherFunction() is accepting an Excel Range object (not one of my custom
class), which is passed in by Excel because of the =AnotherFunction(A1,...)
formula.

So my question is, why are 2 objects (that supposedly represent the same A1
cell) have different Value2 values? What could cause it? And I how do I
overcome it?

Thanks

 

[Joel]

how many worksheets do you have in the workbook? The workbookopen is
checking each worksheet. You are probably seeing the CacheRange truefor the
1st worksheet.

The UDF function are looking at the worksheet where the worksheet formula is
located and not another worksheet.

I also wouldn't declare a variable called worksheet in the following statement

foreach(Worksheet worksheet in workbook.Worksheets)