用于数据验证的Mystery Excel分号模式，由于未知原因停止识别标准逗号 [英] Mystery Excel semicolon mode for data validation, stops recognizeing standard comma for unknown reason

问题描述

在Excel VBA中，如何设置单元格验证，使其在所有语言环境中都能正常工作?

In Excel VBA how can I set cell validation so that it works in all locales?

Dim mySheet As Worksheet
Set mySheet = ThisWorkbook.Worksheets("Sheet 1")
Dim myRange As Range    
Dim validValues as String
validValues = "a;b;c"
Set myRange = mySheet.Cells(2, 2)
myRange.Validation.Add _
    Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=validValues

但是我已经看到其他语言环境需要用逗号分隔

validValues = "a,b,c"

基本上，我需要的是这个，但是我不知道如何确定在所有语言环境中都可以使用的'id'.

Essentially what I need is this, but I don't know how to determine 'id' in way that works in all locales.

' Internationalized delimiter
Dim id as String
id = ???
validValues = "a" & id & "b" & id & "c"

似乎有一个我不理解并且无法可靠复制的Excel 分号模式" .我有一个进入此模式的Excel实例，并且其中运行的所有代码都使用分号而不是逗号.我在这种情况下写了一个项目，并且有效，如果不是我现在必须重写的所有代码，我会以为我只是犯了一个错误.

There seems to be a "semicolon mode" of Excel that I do not understand and which I cannot reproduce reliably. I had a certain instance of Excel that got into this mode and all code run inside it worked with semicolon and not comma. I wrote a project in this instance and it worked, I would have thought I just made a mistake if not for all the code that I now have to rewrite...

为了弄清楚这一点的任何提示，我们深表感谢.

重新启动Excel后，它已将其模式更改为逗号，我已经使用了一段时间，但是现在这个问题再次出现，Excel开始使用';'.(分号)代替，"(逗号)作为我的数据验证的定界符.

After restarting Excel it changed it's mode to comma, I've been using it for a while but now this problem hit me again, Excel started using ';' (semicolon) instead of ',' (comma) as delimiter for my data validations.

这次我拍了一张截图.数据验证设置为列表".

This time I took a screenshot. Data validation is set to List.

来源设置为:

zero,one,both

这是结果，因为您可以看到Excel不在乎我的逗号，并且全部显示在同一行:

This is the result, as you can see Excel does not care about my commas and displays all on the same line:

设置了这样的源就可以了:

With source set like this it works:

zero;one;both

结果:

为什么Excel会执行此操作对我来说还是个谜，但我确实需要找到一种方法来确保这种情况永远不会发生在我的客户身上.任何帮助表示赞赏！

It is a mystery to me why Excel does this but I really need to find a way to make sure this never happens to my customer. Any help appreciated!

推荐答案

我刚刚处理过类似的问题.我找不到发生这种情况的原因，所以我没有好的解决方案.但是，我找到了解决该问题的方法.

I have just dealed with a similar problem. I couldn't find a reason for why this happens, so I don't have a solution for good. Yet, I found a way around the problem that could be of use.

您唯一需要做的就是在添加验证之前为分配有效值.

The only thing that you would need is to assign a valid value to the validated range before adding the validation.

运行代码后，如果 .Formula1 参数与分号一起使用，则完全没有问题.如果不是，则范围的 .Validation.Value 属性返回 False ，您可以将其用作触发器来重新运行代码，用分号分隔的列表替换为用逗号分隔的一个.然后重新运行验证.

After running your code, if the .Formula1 argument worked with semicolons, then there is no problem at all. If it didn't, the .Validation.Value property of the range returns False and you can use that as a trigger to rerun your code, replacing the semicolon separated list by a comma separated one. Then just rerun the validation.

例如:

myRange.value = "a"    'Assuming "a" is a valid value contained in the validvalues string
If myRange.Validation.Value = False Then
    validvalues = replace(validvalues,";",",")
    myRange.validation.modify Formula1:= validvalues
End if

然后，如果您不想显示默认值，则可以清除范围值.

Then you can clear the range value if you do not wish to have a default value shown.

这篇关于用于数据验证的Mystery Excel分号模式，由于未知原因停止识别标准逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！