Дата в Variant Array, вставленная в Excel, иногда теряет день

Следующий код Excel VBA сравнивает эффект накопления часов (1/24 дня) в массиве Variant с элементами Date и в массиве Date. У меня есть столбцы C, E, G, отформатированные как Date и D, F, H как Number с большим количеством dps. В столбцах A и B я воспроизвел расчет с использованием функций рабочего листа. Вы, вероятно, не поверите, пока не попробуете, но последний вывод из массива Variant Date на 1 день раньше. Числовое значение, которое VBA сообщает как 39814,9999999999, преобразуется в 39814 в Excel.

Изменить: когда я говорю столбцы C, E, G в формате даты, я имею в виду пользовательский «дд/мм/гггг чч: мм: сс.00».

Кто-нибудь видел это раньше? Но что еще более важно, как мне узнать, чему я могу доверять при использовании Вариантов? У меня есть десятки тысяч строк, которые нужно поддерживать, и массивы Variant используются везде, где нам нужно выполнять интенсивную обработку данных Excel.

Sub FillDatesBug()
Dim dtHours() As Date
Dim vHours As Variant
Dim vHours2 As Variant
Dim dtHour As Date
Dim dHour As Double
Dim i As Long

ReDim dtHours(1 To 25, 1 To 1)
ReDim vHours(1 To 25, 1 To 1)
ReDim vHours2(1 To 25, 1 To 1)

dtHour = CDate(1 / 24)
dHour = 1 / 24
dtHours(1, 1) = CDate(39814)
vHours(1, 1) = CDate(39814)
vHours2(1, 1) = 39814#

For i = 2 To 25
    dtHours(i, 1) = dtHours(i - 1, 1) + dtHour
    vHours(i, 1) = vHours(i - 1, 1) + dtHour
    vHours2(i, 1) = vHours2(i - 1, 1) + dHour
Next i

Range("C2:C26").Value = dtHours
Range("D2:D26").Value = dtHours
Range("E2:E26").Value = vHours
Range("F2:F26").Value = vHours
Range("G2:G26").Value = vHours2
Range("H2:H26").Value = vHours2

Range("C28").Value = "dtHours(25,1) = " & dtHours(25, 1) & " or " & CDbl(dtHours(25, 1))
Range("E28").Value = "vHours(25,1) = " & vHours(25, 1) & " or " & CDbl(vHours(25, 1))
Range("G28").Value = "vHours2(25,1) = " & Format(vHours2(25, 1), "dd/mm/yyyy hh:mm:ss") & " or " & vHours2(25, 1)
End Sub

А вот скриншот — у меня нет представителя, чтобы показать его, по всей видимости.

Как видите, массив Date и массив Variant с элементами Double дают правильные ответы (включая совершенно нормальную и понятную числовую ошибку). Некоторые значения в массиве Variant с элементами Date неправильно переводятся в Excel.

Первый человек, который действительно увидит, что я здесь говорю, получит мою вечную благодарность.


date excel vba variants
person Boddle    schedule 23.01.2017    source источник

Ответы (2)


arrow_upward
0
arrow_downward

ИЗМЕНИТЬ (лучшее объяснение). Я видел что-то подобное в прошлом, но не помню подробностей. В этом конкретном случае это, по крайней мере частично, связано с некоторыми неявными преобразованиями данных, происходящими при использовании типов данных Variant и записи результатов на рабочий лист.

Одно исправление, если вы должны использовать массивы Variant, состоит в том, чтобы округлить до желаемого уровня точности. Таким образом, вы должны внести следующие изменения, чтобы округлить, например, до миллисекунд:

For i = 2 To 25
    vHours(i, 1) = Round((vHours(i - 1, 1) + dtHour) * 86400000, 0) / 86400000
    dtHours(i, 1) = Round((dtHours(i - 1, 1) + dtHour) * 86400000, 0) / 86400000
Next I

ИЗМЕНИТЬ Дополнительное замечание

Если массив вариантов записывается обратно на лист с использованием свойства Range.Value2 вместо свойства Range.Value, дата, отображаемая в ячейке листа, будет правильной. Однако дата, отображаемая в строке формул, будет неверной (на один день раньше, чем ожидалось). Похоже, это проблема Excel, поскольку значение, такое как 39814.99999999999, введенное непосредственно в ячейку, также будет демонстрировать разные даты в строке формул и ячейке рабочего листа.

Казалось бы, самым безопасным и быстрым способом решения этой (и других проблем) будет использование свойства Value2 при чтении/записи между вариантными массивами и рабочим листом. Интересное обсуждение здесь, а также по указанной ссылке Уильямс в своем ответе.

Если вы хотите, чтобы даты в строке формул и ячейке листа совпадали, я не вижу другого варианта, кроме округления. Но это может и не понадобиться.

person Ron Rosenfeld    schedule 23.01.2017
comment
Я не уверен, что вы достаточно внимательно прочитали пост. Я знаком с эффектами арифметики с конечной точностью. Чего я не ожидаю, так это того, что VBA говорит, что значение равно 39814,9999999999, Excel говорит, что значение равно 39814. Не 39815. Также обратите внимание, что массив Date дает правильный результат, а массив Date in Variant - нет. - person Boddle; 24.01.2017
comment
Массив вариантов хранит значение как двойное. 1/24 = 0,041666666666... ​​39814+ 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 ► 39814.99999999999... Когда это значение отформатировано как дата, Excel не округляет до ближайшей даты; он смотрит только на целую часть. - person Ron Rosenfeld; 24.01.2017
comment
Неправильный. (Правильно, если вы показываете только часть даты, но я не показываю только часть даты.) Если бы вы действительно запустили код, вы бы увидели, что первая запись в произвольном формате даты отображается как 01/01/2009 00:00:00.00 , следующий как 01.01.2009 01:00:00.00 ... 24-й как 01.01.2009 23:00:00.00, а последний как 01.01.2009 00:00:00.00. Отформатированные как число, те же записи отображаются как 39814.000..., 39814.0416...,..., 39814.9583..., 39814.000... VBA сообщает все значения одинаково, кроме последнего, о котором он сообщает как 39814.9999999999. Поверь мне, я не нуб. Запустите код. Это ошибка. - person Boddle; 24.01.2017
comment
Я запустил ваш код, но до этого AM, отчасти из-за форматирования моего рабочего листа, не понимал значения начала серии с Date по сравнению с Double. Я подозреваю, что проблема заключается в неявных преобразованиях типов. Я склонен использовать определенный тип данных, если мне не нужен массив Variant, но все же кажется, что если вы хотите использовать массивы Variant, вам может потребоваться округление до желаемого уровня точности. - person Ron Rosenfeld; 24.01.2017
comment
@Boddle Я также должен отметить, что изменения, которые вы сделали после того, как я впервые прочитал ваш пост, позволили мне более четко понять вашу точку зрения. - person Ron Rosenfeld; 24.01.2017
comment
Хорошо. Дело в том, что вам нужно использовать массив Variant, когда вы получаете данные из рабочего листа с помощью array = range.Value (потому что пользователь мог поместить неправильный тип данных в ячейку или может иметь допустимый выбор типа данных, например. число или ключевое слово). И значение даты (или текст, который Excel считает датой, если формат является общим) поместит дату в массив. И теперь я потерял уверенность в том, что будет делать Excel, когда я верну его обратно. Я мог бы округлить все даты, да, но на самом деле я до сих пор не знаю, какой уровень округления всегда будет работать. Может быть, было бы менее глупо принудительно преобразовать в Double с помощью CDbl? - person Boddle; 24.01.2017

arrow_upward
0
arrow_downward

Мой вывод: небезопасно копировать массив Variant, содержащий элементы Date, в Excel, используя range.Value = array. Существует ошибка, которая возникает только в этом случае и, по-видимому, только тогда, когда значение очень близко к целому числу и предположительно меньше его.

Ошибка есть в Excel 2003, 2007, 2010, 2013... 2016 еще не ставил.

Массив Date в порядке, и массив Variant, содержащий Doubles, в порядке. Массив Variant, содержащий Dates, может быть преобразован в Double по одному элементу за раз с помощью

array(i,j) = CDbl(array(i,j))

а потом снова безопасно. Арифметика в VBA идентична во всех трех случаях — даты являются двойными внутри VBA, и вы можете использовать их взаимозаменяемо в арифметических операторах. Помимо форматирования, даты также являются двойными для Excel. Это просто передача от одного к другому определенным образом запускает плохой бит внутреннего кода преобразования, который округляет дробную часть вверх, а целую часть вниз.

РЕДАКТИРОВАТЬ: предложение Рона использовать .Value2 вместо .Value работает и намного аккуратнее (и быстрее), чем преобразование в Double. Спасибо, Рон. Глобальный поиск и замена, поехали...

person Boddle    schedule 24.01.2017
comment
Я могу подтвердить, что это поведение также присутствует в Excel 2016. - person Ron Rosenfeld; 26.01.2017
comment
См. мое последнее редактирование в моем ответе для дополнительного наблюдения относительно использования свойства Value2. - person Ron Rosenfeld; 26.01.2017
comment
О, на моем компьютере массив вариантов, преобразованный в удвоение, будет отображать правильную дату в ячейке листа; НО неправильная дата по-прежнему отображается в строке формул. - person Ron Rosenfeld; 26.01.2017
comment
Я полагаю, что это может быть ошибка в Excel, потому что, если вы введете значение, такое как 38715,9999999999, в какую-либо ячейку напрямую, вы увидите ту же проблему - другое отображение в ячейке и строке формул. - person Ron Rosenfeld; 26.01.2017
comment
Это очень странно. Но я не думаю, что это вызовет проблемы в коде. - person Boddle; 26.01.2017