Excel: Working with named Range.NumberFormat in VBA: "General" vs. "Standard"

A couple questions about Range.NumberFormat here--hopefully someone has ready answers they can provide, as searching the web is tough with all the generic terms.

A little background: I'm trying to eliminate "random" Error 1004 codes: Unable to set the NumberFormat property of the Range class is the message. The error is occurring when I try to set the .NumberFormat of a late-bound object to a named format, mainly working with "General" or "Standard". The object, in 99% of cases is Application.Selection. Also please note, the most common cause of this error--protection on the sheet or workbook--does not apply here.

1. From Excel 2003 to 2007, Microsoft would appear to have introduced a bug in VBA. If in the immediate window I type ? Selection.NumberFormat and hit Enter, I get "General". If I do the same thing with certain addins (not all, not predictable) running a macro with a breakpoint, I usually get "Standard". What is "Standard"? Where does that come from? I want to pick up a number format from one cell, and drop it on another; at random moments, I cannot apply "Standard" in this way, and its result is not like General. (In Excel 2003, it never appears in the immediate window, and fails systematically to apply.)

2. Even if I convert all instances of "Standard" to "General", I still sometimes get an error on 开发者_StackOverflow中文版MyObject.NumberFormat = "General". I have read in a couple of places that one is better off applying the underlying format of the named format, i.e. General applies to something (as I suppose standard does); what is the syntax to do that in Excel VBA?

Thanks in advance for your help.

---

This probably isn't a full solution, but it's a start:

I believe "Standard" is what is used instead of "General" in certain languages. You should compare your results with .NumberFormatLocal.

For the solution part, if you're trying to set NumberFormat to "General" and getting errors then just set the NumberForamt to an empty string. This will automatically make the NumberFormat General/Standard.

selection.NumberFormat = ""