Excel VBA的行为在不同的语言 [英] Excel VBA behaves different in different languages

问题描述

Win-XP，Excel 2003

我有一个名称定义的范围，由一个查询填充。相同的命名范围形成单元格验证的源（单元格下拉列表）。在我的VBA中，该范围通过名为LOVL2的范围对象访问。

每次该查询重新填充该范围时，其名称将重新定义为包含所有行I通过查询获得。这是用语句

  LOVL2.CurrentRegion.Name = LOVL2.Name.Name 
  

只要MS Office语言设置为英文，该语句工作正常，但是当MS Office语言设置为法语时，该语句失败。我收到错误1004无效的名称

任何人有一个想法，只有当MS Office语言设置为FRENCH而不是在英语时是什么原因？ （可能是;对，在对象内）

提前感谢MikeD

---

编辑2010年8月12日

REAL根本原因现在很清楚：

范围的名称是L2PoP，它在英文版本中被识别为范围的有效名称 - 您可以转到任何空白的表单，选择范围并将其命名为L2PoP。 p>

将您的用户语言设置为FRENCH，转到任何空白的表单，选择范围并将其命名为L2PoP，并显示Nom non valide的错误。 p>

所以真正的治愈措施是给法语和英语所接受的范围提供不同的名字

我只能猜测是什么导致的，这可能与前两个字符看起来像一个单元格地址的事实有关，另一方面，A1PoP是一个有效的名称，而L2Foo和L1Foo无效。

奇怪，但.....

解决方案

由查询填充

通过查询表？

查询表的结果范围已经有一个名称，它是查询表本身的名称（有点消毒）（在属性对话框中设置）。这意味着你不需要重新定义任何东西。

如果你这样做，那么尝试这个代码：

  Sub asfgsdfg（）
 
 Dim n As Name 
设置n = ThisWorkbook.Names（LOVL2）
 
'或者在本地名称的情况下，
'Set n = ThisWorkbook.Worksheets（工作表）。名称（LOVL2）
 
 ChangeNamedRangeAddress n，n.RefersToRange.CurrentRegion 
 
 End Sub 
 
公共Sub ChangeNamedRangeAddress（ByVal n As Name，ByVal NewRange As Range）
 n.RefersTo =='&替换（n.RefersToRange.Worksheet.Name，'，''）& ！ &安培; NewRange.Address（True，True，xlA1）
 End Sub 
  

---

编辑

关于后续行动...最奇怪和有趣的。

尝试使用前导下划线或其他东西？

Win-XP, Excel 2003

I have a range defined by a name, which is filled by a query. The same named range forms the source of a cell validation (in-cell dropdown list). In my VBA this range is accessed via a range object named LOVL2.

Each time the range is re-filled by the query, its name is redefined to include all rows I obtained through the query. This is done with statement

LOVL2.CurrentRegion.Name = LOVL2.Name.Name

the statement works fine as long as MS Office language is set to English, but the statement fails when MS office language is set to French .... I get Error 1004 "Invalid Name"

Anyone got an idea what is causing this only when MS Office language is set to FRENCH but not while in ENGLISH? (maybe problem with ";" vs "," within the object ??)

Thanks in advance MikeD

---

edit 12-Aug-2010

the REAL root cause is clear now:

the range's name is "L2PoP" which in the ENGLISH version is recognized as a valid name for a range - in that you can go to any empty sheet, select a range and name it "L2PoP".

Set your user language to FRENCH, go to any empty sheet, select a range and name it "L2PoP", and you get an error saying "Nom non valide".

so the real curing action is to give a different name to the range which is accepted by both French and English

I can only speculate about what is causing this, it may have to do with the fact that the first 2 characters look like a cell address, on the other hand "A1PoP " is a valid name, whereas "L2Foo" and "L1Foo" are invalid.

strange, but .....

解决方案

which is filled by a query

By a query table?
A query table's result range already has a name which is the name (a bit sanitised) of the query table itself (set in the properties dialog). Which means you don't need to redefine anything.

And if you do, then try this code:

Sub asfgsdfg()

  Dim n As Name
  Set n = ThisWorkbook.Names("LOVL2")

  'Or in case of a local name,
  'Set n = ThisWorkbook.Worksheets("The worksheet").Names("LOVL2")

  ChangeNamedRangeAddress n, n.RefersToRange.CurrentRegion

End Sub

Public Sub ChangeNamedRangeAddress(ByVal n As Name, ByVal NewRange As Range)
  n.RefersTo = "='" & Replace(n.RefersToRange.Worksheet.Name, "'", "''") & "'!" & NewRange.Address(True, True, xlA1)
End Sub

---

EDIT

In regard to the follow-up... Most strange and amusing.

Try using a leading underscore or something?

这篇关于Excel VBA的行为在不同的语言的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！