vba 多个本地化的相同日期格式

Same date format over several localizations

提问by PeterT

Currently im facing issue that troubles me a lot. I hope that somebody could help me out. I work for big company where are both Office 2007 (32bit) and Office 2010 (64 bit) used. Writing macros to be compatibile through whole company was hard task for me (I've never programmed in VBA before - actually this forum helped me a lot). My task is to maintain one big table in shared Excel sheet. There are several macros and several userforms. Now i will decsribe the problem briefly: Sheet contains two columns with date format (start date and close date). Both values are imported to column form userform's textboxes (commandbutton lunches MsCal -exported to class- which fills those textboxes with date). What I simply need is to have date format as mm/dd/yyyy in both columns in order to perform filtering and other operations. When this values are updated by worker that uses different localization than English U.S. date is entered as dd.mm.yyyy. Thats make proper filtering based on date impossible. I tried to alter formating by:

目前我面临着困扰我很多的问题。我希望有人可以帮助我。我在使用 Office 2007（32 位）和 Office 2010（64 位）的大公司工作。编写与整个公司兼容的宏对我来说是一项艰巨的任务（我以前从未在 VBA 中编程过 - 实际上这个论坛对我帮助很大）。我的任务是在共享 Excel 工作表中维护一张大表。有几个宏和几个用户表单。现在我将简要描述这个问题：工作表包含两列日期格式（开始日期和结束日期）。这两个值都被导入到列表单用户表单的文本框（命令按钮午餐 MsCal - 导出到类 - 用日期填充这些文本框）。我只需要在两列中都将日期格式设为 mm/dd/yyyy，以便执行过滤和其他操作。当此值由使用不同于美国英语本地化的工作人员更新时，日期输入为 dd.mm.yyyy。这使得基于日期的适当过滤变得不可能。我试图通过以下方式改变格式：

UserForm1.TextBox10.Value = Format(Calendar1.Value, "mm/dd/yyyy")

but this piece of code misbehave somehow. On some machines it works, on some of them it is not working. And thats what is giving me headache. How should i proceed now? Is there a way to force excel to use same date format in sheet and ignore localization settings in Windows? Employees dont want to change localization to English U.S. because they are either used to their format, or need it for other applications. Is there a way to temporarily change localization only when this sheet opens? Any advice will be apreciated. Thanks in Advance Peter

但是这段代码以某种方式行为不端。在某些机器上它可以工作，在其中一些机器上它不工作。这就是让我头疼的原因。我现在应该如何进行？有没有办法强制 excel 在工作表中使用相同的日期格式并忽略 Windows 中的本地化设置？员工不想将本地化更改为美国英语，因为他们要么习惯了他们的格式，要么需要它用于其他应用程序。有没有办法仅在此工作表打开时临时更改本地化？任何建议将不胜感激。在此先感谢彼得

采纳答案by CaBieberach

The best you can do is NEVER transform a date variable into Text.

您能做的最好的事情就是永远不要将日期变量转换为文本。

Internally for excel a date is just a consecutive number (Left from the decimal separator are days and right from the decimal separator are hours). So, for example, the 10th of June 2012, for excel is 41188. This date value is independent of the date format set on your computer.

在 Excel 内部，日期只是一个连续的数字（小数点左边是天，小数点右边是小时）。因此，例如，2012 年 6 月 10 日，excel 是41188。此日期值与您计算机上设置的日期格式无关。

Now when it comes to represent dates (for humans to visualize) Excel will format this internal value into a String with the format set in your computer. So, for example if you have US date format in your computer, the date 41188 will be formated as 6/10/2012.

现在，当涉及到表示日期（供人类可视化）时，Excel 会将此内部值格式化为一个字符串，并使用您计算机中设置的格式。因此，例如，如果您的计算机中有美国日期格式，则日期 41188 将被格式化为 6/10/2012。

The big challenge with dates is to input the date in the correct format. When you input a Date as a string ("6/10/2012") then Excel will interpret it depending on the date format set on your computer. If you have US format, then it will thake the first cypher as month, the second as day and the last as the year. If you have a German format, it will read the first as day, the next as month and the last as year. So, the same input ("6/10/2012") for a US Format Excel will read 10th of june as for a German format Excel will read 6th of Oktober.

日期的最大挑战是以正确的格式输入日期。当您将日期作为字符串（“6/10/2012”）输入时，Excel 将根据您计算机上设置的日期格式来解释它。如果你有美国格式，那么它会将第一个密码作为月，第二个作为日，最后一个作为年。如果您有德语格式，它会将第一个读取为日，下一个读取为月，最后一个读取为年。因此，美国格式 Excel 的相同输入（“6/10/2012”）将读取 6 月 10 日，而德国格式 Excel 将读取 10 月 6 日。

In your case, you should NOT format the date inside the Textbox10. For a US format Excel there is no problem, but if you have another date format, where the first cypher is the day instead of the month, you will get the wrong values: Check this example. User inputs 10th of June in a German format Excel (dd.mm.yyyy)

在您的情况下，您不应在 Textbox10 中格式化日期。对于美国格式的 Excel 没有问题，但如果您有另一种日期格式，其中第一个密码是日期而不是月份，您将得到错误的值：检查此示例。用户以德语格式 Excel (dd.mm.yyyy) 输入 6 月 10 日

1. Calendar1.Valueretrieves a date value (41188)
2. Format(Calendar1.Value, "mm/dd/yyyy")transforms the date value into a string "06/10/2012"
3. When using the formated date (STRING), Excel will have to interpret what date it is. Because the computer date format is German, it will read Day:06, Month:10, Year:2012. You will be using day 41070instead of 41188

1. Calendar1.Value检索日期值 ( 41188)
2. Format(Calendar1.Value, "mm/dd/yyyy")将日期值转换为字符串“06/10/2012”
3. 使用格式化日期 (STRING) 时，Excel 必须解释它是什么日期。因为计算机日期格式是德语，所以它会显示 Day:06, Month:10, Year:2012。您将使用第41070天而不是41188

If Calendar1.Valueretrieves a Date variable and you give this date variable into a Date formated column, you will allways get the correct dale in your column and you will be able to filter and sort dates correctly regardless of the date format set inside the Column cells or the format set in the users computer.

如果Calendar1.Value检索日期变量并将此日期变量提供给日期格式的列，您将始终在列中获得正确的 dale，并且无论列单元格内设置的日期格式如何，您都可以正确过滤和排序日期或在用户计算机中设置的格式。

---

---

Now, in your case, the best would be to assign directly the Calendar1.Valueto the required cell. Something like:

现在，在您的情况下，最好的方法是将 直接分配给Calendar1.Value所需的单元格。就像是：

ThisworkBook.WorkSheets("Sheet1").Range("C3").Value= Calendar1.Value

You can still asign Calendar1.Valueinto the TextBox10 for the user to see his selection, but disable the TextBox10 so that the only edit option is the calendar control. And when working with the date, istead of thaking it from the TextBox10, taking it directly from the Calendar1.Value.

您仍然可以指定Calendar1.ValueTextBox10 以供用户查看他的选择，但禁用 TextBox10，以便唯一的编辑选项是日历控件。并且在处理日期时，不是从 TextBox10 中获取它，而是直接从Calendar1.Value.

If you still need to show the selected value from Calendar1 into a textBox then do NOT format the date in the Textbox. Instead, use:

如果您仍然需要将 Calendar1 中的选定值显示到文本框中，则不要在文本框中设置日期格式。相反，使用：

UserForm1.TextBox10.Value = Cstr(Calendar1.Value) 

This way, the user will see the date in the dateformat that he has set in his computer and to which he is used to.

这样，用户将看到他在计算机中设置的并且习惯的日期格式中的日期。

回答by Dan

One solution is to not use the date number format but rather only use the custom format for all your cell dates where you specify "mm/dd/yyyy"as the formatting string. However, in my experience, if your computer's regional settings are set to use "mm/dd/yyyy"then if you try make a custom cell formatting with this same string excel will keep as a dater linked to the computer setting so that doesn't help you. The way I worked around this was to change the date format on my computer, then format the cells as custom "mm/dd/yyyy" and save (and then turn your computer's settings back to how they were.) Now even though excel still claims they are date cells, you'll see that changing the settings on your computer doesn't change the value in the cell.

一种解决方案是不使用日期数字格式，而仅对您指定"mm/dd/yyyy"为格式字符串的所有单元格日期使用自定义格式。但是，根据我的经验，如果您的计算机的区域设置设置为使用，"mm/dd/yyyy"那么如果您尝试使用相同的字符串进行自定义单元格格式设置，excel 将保留为链接到计算机设置的约会者，因此对您没有帮助。我解决这个问题的方法是更改​​计算机上的日期格式，然后将单元格格式化为自定义“mm/dd/yyyy”并保存（然后将计算机的设置恢复为原来的状态。）现在即使 excel 仍然存在声称它们是日期单元格，您会看到更改计算机上的设置不会更改单元格中的值。

I guess another way is to always have a cell next to your date cell that calls the TEXT function. So if you have a date in A1 then in another cell =TEXT(A1, "mm/dd/yyyy")and only refer to this new cell. But that could make your spread sheet very messy.

我想另一种方法是始终在调用 TEXT 函数的日期单元格旁边放置一个单元格。因此，如果您在 A1 中有一个日期，那么在另一个单元格中=TEXT(A1, "mm/dd/yyyy")只引用这个新单元格。但这可能会使您的电子表格非常混乱。

I guess the best solution is to just get you IT dept to set every one in the company's date settings to use the same formats.

我想最好的解决方案是让您的 IT 部门将公司日期设置中的每个人设置为使用相同的格式。