1

I was wondering what kind of types of Value2 can be returned. I am asking this question since it seems that the type of Value2 is always an object, but the actual underlying type does not always seem to be the same. For example: given that cell A1 contains “1234”, the following line of code fails on my computer:

string targetValue = (string)Application.get_Range(“A1”).Value2

However, if the cell A1 contains “abcdef”, the previous line succeeds. Now, I have seen the type double and the type string. My question is, are there more possible types that can result in an error? Does Value2 have for example integers? Or have I caught them all?

I have read the documentation, but I can't seem to find all possible supported types.

PS: I am using Visual Studio 2017, Excel 2016 and C# to create my add-in.

Documentation regarding Ranges

Snowflake
  • 2,869
  • 3
  • 22
  • 44

1 Answers1

0

The only difference between the Value2 property and the Value property is that the Value2 property doesn't use the Currency and Date data types. You can return values formatted with these data types as floating-point numbers by using the Double data type.

See Range.Value2 Property

Ch.
  • 29
  • 7
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • 1
    I am aware that the Value2 property is a Variant in VBA, but in C#, we do not seem to have Variant as native types.But if I infer correctly from your answer, Value2 can only contain a double or a string, am I correct? – Snowflake May 10 '18 at 17:47
  • @Snowflake a similar version of variant exist in C# and it's called `Dynamic. On the other side `object` is also very similar to variant. It can be anything.It's up to you to unboxed the value when you want to what you want. – Franck May 10 '18 at 17:52
  • @Snowflake Data types are different in Excel and VBA. In particular, the Date and Currency data types do not really exist in Excel, but are converted to those VBA data types when you read the `.Value` property of a range object. So `Value2` can contain empty, string, error, number (double) or boolean. Ted Williams has an excellent discussion of [Value, Value2 and text](https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/) – Ron Rosenfeld May 10 '18 at 17:53
  • 1
    Based on some further experimentation, I came to the conclusion that VTSO C# in Excel only has several types, null, string, int (which is only gotten through an error, I haven't been able to get this using any other way), double or boolean. – Snowflake May 10 '18 at 18:10
  • @Snowflake Suggest you post a new question with the specific code that is causing the problem, along with examples of data, expected output, actual output and any error messages – Ron Rosenfeld May 10 '18 at 18:12
  • @Ron, I am not having a particular problem, but I only wanted to say that if your formula contains an error, like "=AAAAA", which results in a #NAME error in Excel, it would result in an int in the C# VTSO code. – Snowflake May 10 '18 at 18:20
  • @Snowflake And if you have an `int32` you can infer that there is an error in the cell! See @Mike Rosenbloom's answer in [How to know if a cell has an error in C#](https://stackoverflow.com/questions/2424718/how-to-know-if-a-cell-has-an-error-in-the-formula-in-c-sharp) – Ron Rosenfeld May 10 '18 at 18:36