Excel将错误的日期值传递给VBA函数 [英] Excel Passing Wrong Date Value to VBA Function

问题描述

我有一个带有日期值的工作表.Excel将它们正确解释为日期.

I have a worksheet with Date values. Excel interprets them correctly as Dates.

我有一个接受Date参数的VBA函数.

I have a VBA function that accepts a Date parameter.

当我从工作表中调用此函数时，有时它可以正常工作，有时却不能.当我在函数中设置断点以检查从不正确的单元格传递给函数的值时，我发现错误的日期传递给了函数-与应该传递给函数的日期相隔一天.时间值正确.

When I call this function from the worksheet, sometimes it works correctly, and sometimes it doesn't. When I set a breakpoint in the function to examine the value passed in to the function from a cell that is incorrect, I discover that the wrong date is passed to the function--it's a day off from what should be passed to the function. The time value is correct.

这发生在工作表上的随机位置.实际上，在昨天无法正常工作的一个单元中，现在它今天可以正常工作.

This occurs in random places on the worksheet. In fact, in one cell that it didn't work correctly in yesterday, it now works correctly today.

该值的时间部分似乎没有什么不同.可能是22:00，08:00，还是一天休息.

The time portion of the value doesn't seem to make a difference. It can be 22:00 hours, 08:00 hours, it's still off by a day.

这对任何人有意义吗?我很困惑.

Does this make any sense to anyone? I am seriously perplexed.

我没有发布任何代码，因为我不认为它是代码问题.但是这是函数签名:

I didn't post any code because I don't find it to be a code issue. But here's the function signature:

Function timeToDecimal(time As Date, semanticTimeFormat As String) As Double

在函数的第一行可执行代码中设置一个断点，我看到 time 例如是 1/1/1900 1:09:25 PM ，当工作表上的时间明显是 1/2/1900 1:09:25 PM .

Setting a breakpoint on the first line of executable code in the function, I see that time is, for example, 1/1/1900 1:09:25 PM, when, on the worksheet it is clearly 1/2/1900 1:09:25 PM.

问题不是我编写的VBA代码.问题是Excel将数据传递给函数.在Excel和VBA之间的界面中是否有一些我不熟悉的怪癖?

The issue isn't the VBA code I've written. The issue is Excel's passing of the data to the function. Is there some quirk I am unfamiliar with in the interface between Excel and VBA?

推荐答案

它将会出现！由于某种原因，excel中存在一个错误，其中1990年被错误地解释为a年-此处提到:

IT WOULD APPEAR! that for some reason there is a bug in excel where 1990 is incorrectly interpreted as a leap year - mentioned here: How to represent a DateTime in Excel in the accepted answer. and I tested on a sheet of my own, putting the dates from 01/01/1900 up to 31/12/1901 and then i used vba like this:

Sub test()


Dim str As String
Dim test As String
Dim test2 As String

test2 = ActiveCell.FormulaR1C1
test = ActiveCell.Value



While Not ActiveCell.FormulaR1C1 = ""
    str = ActiveCell.Value
    ActiveCell.Offset(0, 2).Value = str
    ActiveCell.Offset(1, 0).Activate

Wend



End Sub

，从1900年1月1日至1900年3月1日，日期已过.由于vba的日期都比电子表格中的日期晚了一天:

and from 01/01/1900 - 01/03/1900 thats where dates were off. the dates as a result of vba were all behind a day from what was in the spreadsheet:

01/01/1900 0:00     31/12/1899
02/01/1900 0:00     01/01/1900 0:00
03/01/1900 0:00     01/02/1900 0:00
04/01/1900 0:00     01/03/1900 0:00
05/01/1900 0:00     01/04/1900 0:00
06/01/1900 0:00     01/05/1900 0:00
07/01/1900 0:00     01/06/1900 0:00
08/01/1900 0:00     01/07/1900 0:00
09/01/1900 0:00     01/08/1900 0:00
10/01/1900 0:00     01/09/1900 0:00
11/01/1900 0:00     01/10/1900 0:00
12/01/1900 0:00     01/11/1900 0:00
13/01/1900 0:00 01/12/1900 0:00

但是从1990年3月1日开始，所有日期一直排到我在1901年底停止的地方:

but then starting 01/03/1990 the dates all lined up all the way to where i stopped at the end of 1901:

01/01/1901 0:00     01/01/1901 0:00         
02/01/1901 0:00     01/02/1901 0:00         
03/01/1901 0:00     01/03/1901 0:00         
04/01/1901 0:00     01/04/1901 0:00         
05/01/1901 0:00     01/05/1901 0:00         
06/01/1901 0:00     01/06/1901 0:00          
07/01/1901 0:00     01/07/1901 0:00         
08/01/1901 0:00     01/08/1901 0:00         
09/01/1901 0:00     01/09/1901 0:00         
10/01/1901 0:00     01/10/1901 0:00         
11/01/1901 0:00     01/11/1901 0:00         
12/01/1901 0:00     01/12/1901 0:00         

所以！您可能需要该功能的一部分来检查日期，并且从1899年1月31日到1900年2月28日，您需要将日期提高1.

SO! you'll likely need a part of the function to check the date and from 31/12/1899 to 28/02/1900 you'll need to move the day up 1.

*您会注意到显示的第二组日期在右侧都是mm/dd/yyyy格式-我不知道为什么excel会这么做....

*you'll notice the second set of dates shown, the right hand side, is all formatted mm/dd/yyyy - i have no idea why excel did that....

这篇关于Excel将错误的日期值传递给VBA函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！