Excel Range.MergeCells is sometimes of type DBNull

[David Thielen]

What is going on and what does that mean?

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Jie Wang [MSFT]]

Hi David,

When you say "sometimes", is there any pattern or just random?

Could you show me some code that can reproduce the problem?

The doc only says it will return True if the range or style contains merged
cells. But it doesn't say what is returned if the range or style contains
no merged cells, and the type of this property in Excel is Variant
(translated to Object in .NET), so could be something unexpected.

Please let me know more about the situation and I'll try find out more.

Thanks,

Jie Wang

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jie Wang [MSFT]]

Oh, and I forgot to ask the Excel version.

 

[David Thielen]

Hi David,

When you say "sometimes", is there any pattern or just random?

Could you show me some code that can reproduce the problem?

The doc only says it will return True if the range or style contains merged
cells. But it doesn't say what is returned if the range or style contains
no merged cells, and the type of this property in Excel is Variant
(translated to Object in .NET), so could be something unexpected.

Please let me know more about the situation and I'll try find out more.

Excel 2007. It appears to return True or DBNull. So DBNull is false.

Also try selecting a range that starts in a single cell, but includes
merged cells.

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Jie Wang [MSFT]]

Hi David,

I just did some tests against the property. Here is what I found about the
values:

1. If the Range contains no merged cells, it returns False

2. If the Range contains one (and only one) merged cell, with no other
non-merged cells, it returns True

3. All other situations, like contains more than one merged cells, or a
mixture of merged cell and non-merged cells, it returns Null.

You can test it in Excel using VBA:

Sub Test()
Debug.Print ActiveWindow.RangeSelection.MergeCells
End Sub

So why DBNull?

This is explained in the DBNull Class document:

Additionally, COM interop uses the DBNull class to distinguish between a
VT_NULL variant, which indicates a nonexistent value, and a VT_EMPTY
variant, which indicates an unspecified value.

http://msdn.microsoft.com/en-us/library/system.dbnull.aspx

Hope these information helps.

Best regards,
Jie Wang

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Thielen]

Hi David,

I just did some tests against the property. Here is what I found about the
values:

1. If the Range contains no merged cells, it returns False

2. If the Range contains one (and only one) merged cell, with no other
non-merged cells, it returns True

3. All other situations, like contains more than one merged cells, or a
mixture of merged cell and non-merged cells, it returns Null.

You can test it in Excel using VBA:

Sub Test()
Debug.Print ActiveWindow.RangeSelection.MergeCells
End Sub

So why DBNull?

This is explained in the DBNull Class document:

Additionally, COM interop uses the DBNull class to distinguish between a
VT_NULL variant, which indicates a nonexistent value, and a VT_EMPTY
variant, which indicates an unspecified value.

http://msdn.microsoft.com/en-us/library/system.dbnull.aspx

Hope these information helps.

That makes a lot of sense - thank you.

ps - ask the Office group to put that in the documentation for
MergeCells.

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm