This project has moved and is read-only. For the latest updates, please go here.

Named Constants

Oct 13, 2014 at 11:29 PM

I have a fairly simple question. I am using ClosedXML in one of my projects. I just found out that ClosedXML can actually evaluate formulas after doing a simple search. What I have been doing until now is using Excel interop to evaluate the formulas using a PasteSpecial then using ClosedXML to read the results from a temporary sheet.

I am using very simple formulas, essentially Cell1 + Cell2 * Cell3, etc. and maybe some of the basic built in Excel functions. I know in the past that ClosedXML relied on the ValueCached property for values to be returned. I do not want to rely on this ValueCached, because 99% of the time it is not there. This does not seem to be an issue now.

My question is this: If I define a named constant, for example A4 = 1 and then in Excel define it as "constant", can ClosedXML then evaluate the formula using named constants? What about complex named constants (A4 + A5 + A6 / A1) = "constant)? Another cell then has the formula: B2 + B5 + constant. It seems as though ClosedXML is evaluating this, because if I then go get the cell using ClosedXML, the value is correct; HOWEVER, I notice that the "ValueCached" property is filled in with the same value. Is ClosedXML relying on value cached in this case that may or may not be there for some cells? Like I said, I do not want to rely on ValueCached. I want the formulas to be evaluate every time no matter what I throw at it. I realize not all Excel functions are supported; that is a given. Will ClosedXML evaluate every time with the simple formulas I am giving it?

If ClosedXML does not support this case, I understand and will subsequently stay away from named constants since I can just reference the actual cell. I just know that a bunch of the guys I work with happen to like named constants, because they are much easier to reference in multiple pages.

Oct 14, 2014 at 2:37 AM
By constants, do you mean named ranges? If so then yes, you can include named ranges in your formulas and ClosedXML will evaluate them (as long as it uses known formulas). It will not use ValueCached.

Let me know if this answers your question...
Oct 14, 2014 at 3:03 AM
I was looking at named ranges. I think that they are defined in a similar way. For a named range, you click on a range of cells and then in (usually) the top left, you can put the name. However, there is actually a button in Excel in formulas (for 2013) called "Define name" where you click a cell, press the button, and put a name in there.

I just took a look using the "name manager" in Excel and it looks like either if you use the define name button or use the box in the upper left, both constants show up exactly the same and can be used in the same way.

I guess that answers the question that ClosedXML therefore supports what I need. Im guessing the ValueCached is just showing up for whatever implementation defined reason; should not have to worry about my formulas not being evaluated.