Excel VBA错误，在工作表和文本框上带有日期 [英] Excel vba error with dates on sheet and textbox

问题描述

我住在澳大利亚，我们使用d / mm / yyyy日期格式。我正在尝试使用Excel中的VBA制作一个用户窗体，该窗体将读取一个单元格（A1），并在文本框中显示它。然后，用户可以在另一个文本框中输入日期，并将该日期设置回cell（A1）。我的问题是，当我从Cell（A1）读取日期时，文本框中的日期从d / mm / yyyy更改为m / dd / yyyy。我已经使用了
= Format（DateCurrent_Tbx.Value， d / mmm / yy），但是没有区别。我在Windows 7 SP1 64位计算机上使用Excel 2010 32位，并将计算机设置为英语（澳大利亚）时间格式。 Cell（A1）设置为自定义格式 d / mm / yyyy

I live in Australia and we use the d/mm/yyyy date format. I am trying to make a userform with VBA in excel that will read the a cell (A1), display it in a textbox. The user can then enter a date in another textbox and set that date back to cell(A1). The problem I have is that when I am reading the date from Cell(A1) is changes from d/mm/yyyy to m/dd/yyyy in the textbox. I have used the " = Format(DateCurrent_Tbx.Value, "d/mmm/yy")" but it makes no difference. I am using Excel 2010 32bit, on a Windows 7 SP1 64Bit Computer, and have the computer set to English (Australian) time format. Cell(A1) is set to custom formatting "d/mm/yyyy"

由于我正在使用的文件的VBA代码很长，因此我已经复制了较小的用户窗体中的错误（附加的excel文件）。

As the VBA code for the file I'm using is very long, I've replicated the error in a smaller userform (attached excel file).

带有以下内容的Excel文件示例错误

Private Sub LockInput_Cmd_Click()
SetDate_Cmd.Caption = InputDate_Tbx.Value
SetDate_Cmd.Caption = Format(SetDate_Cmd.Caption, "d/mmm/yy")
End Sub

Private Sub SetDate_Cmd_Click()
ThisWorkbook.Sheets(1).Range("A1").Value = SetDate_Cmd.Caption
DateCurrent_Tbx.Value = ThisWorkbook.Sheets(1).Range("A1").Value
DateCurrent_Tbx.Value = Format(DateCurrent_Tbx.Value, "d/mmm/yy")

End Sub

Private Sub UserForm_Initialize()
DateCurrent_Tbx.Value = ThisWorkbook.Sheets(1).Range("A1").Value
DateCurrent_Tbx.Value = Format(DateCurrent_Tbx.Value, "d/mmm/yy")
End Sub

我一直在绞尽脑汁，在网上搜索了几天都无济于事。我希望我已经足够清楚地解释了事情。

I have been racking my head and searching the web for days to no avail. I hope I have explained things clearly enough.

推荐答案

看起来像日期和实际日期以及EN的字符串有些混乱-以美国为中心的VBA默认MDY语言环境无法缓解该问题。

There is some confusion with strings that look like dates and actual dates and the EN-US-centric VBA default MDY locale is doing nothing to alleviate that.

首先，A1中的日期为0到42,225之间的数字（2015年8月9日为42,225）；没有更多或更少。您可以将其装扮成d / mm / yyyy，而不管计算机系统在什么语言环境下运行。我的游戏在EN-US下运行，如果我使用d / mm / yyyy的数字格式掩码，则可以很容易地显示日期为2015年9月8日。要将显示的日期从A1作为字符串返回到文本框，请使用范围.Text属性。这是单元格中显示的文本。它是只读的，因此您不能在其中填充 9/08/2015字符串。

First off, the date in A1 is a number between 0 and 42,225 (Aug 9, 2015 is 42,225); nothing more or less. You can dress it up as d/mm/yyyy regardless of what locale your computer system is running under. Mine runs under EN-US and I have no trouble displaying a date as 9/08/2015 if I use a number format mask of d/mm/yyyy. To get the displayed date from A1 back into the textbox AS A STRING use the Range.Text property. This is the displayed text that is in a cell. It is read-only so you cannot stuff a "9/08/2015" string back into it.

Private Sub UserForm_Initialize()
    DateCurrent_Tbx.Value = ThisWorkbook.Sheets(1).Range("A1").TEXT
End Sub

就用户输入的看起来像日期的字符串而言，您可能不得不信任您的用户才能做正确的事情。如果可以依靠它们以DMY格式输入，则可以使用 DateSerial函数。

As far as user input of a 'string-that-looks-like-a-date' is concerned, you may have to trust your users a bit to do the right thing. If they can be relied upon to input in a DMY format, you can split the pieces and put them together properly using the DateSerial function.

Private Sub LockInput_Cmd_Click()
    dim dt as date, vDT as variant
    vDT = split(InputDate_Tbx.Value, chr(47))  'split the string-date on the forward slash
    dt = DateSerial(vDT(2), vDT(1), vDT(0))   'create a real date
    SetDate_Cmd.Caption = Format(dt, "d/mmm/yy")   'use any format mask you want to create another string-date
End Sub

Private Sub SetDate_Cmd_Click()
    dim dt as date, vDT as variant
    vDT = split(SetDate_Cmd.Caption, chr(47))  'split the string-date on the forward slash
    dt = DateSerial(vDT(2), vDT(1), vDT(0))   'create a real date
    ThisWorkbook.Sheets(1).Range("A1").Value = dt  'put the actual date back into A1
End Sub

据 CDate函数，我会避免使用它。如果将 19/08/2015塞入CDate（在VBE的即时窗口中将其作为？CDate（ 19/08/2015）尝试），它将正确解释时间为2015年8月19日，但这仅是因为没有其他选择！如果对？CDate（ 9/08/2015）尝试相同的操作，则最终结果为2015年9月8日，因为只要有选择，CDate都会选择MDY的EN-US语言环境。最好不要依靠这一。我相信 DateValue函数至少是不可靠的。

As far as the CDate function is concerned, I would avoid its use. If you stuff "19/08/2015" into CDate (try it in the VBE's Immediate window as ?CDate("19/08/2015")) it will correctly interpret it as 19-Aug-2015 but that is only because IT HAS NO OTHER CHOICE! If you try the same with ?CDate("9/08/2015") you end up with 08-Sep-2015 because whenever there is a choice, CDate opts for the EN-US locale of MDY. Best not to rely on this one. I believe the DateValue function is at least as unreliable.

这篇关于Excel VBA错误，在工作表和文本框上带有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！