Excel vba работа с датами

Урок 15 по VBA – функции работы с датой и временем

И так, в этой по своей природе унылой публикации я кратко рассмотрю vba функции даты и времени, которые позволяют получить или установить параметры даты и времени, задать таймер выполнения заданного блока кода или сценария в целом. Также в конце статьи будет затронута функция vba языка format. Которая отвечает за форматирование данных.

Функции VBA даты и времени

Date () – позволяет получить значение текущей даты, установленной в системе.

Time () – вернет установленное в системе время

Now () – комбинированная функция, так как она позволяет получить значение системной даты и времени.

DateAdd (интервал, число, дата) – позволяет прибавить к заданной дате (параметр “дата”) указанное количество лет, кварталов, месяцев или секунд. На выходе мы получим следующее значение: дата + (число * интервал).

DateDiff (интервал, дата, дата2) – позволяет получить разницу между заданными датами, например. В годах, месяцах и так далее, разница определяется параметром “интервал”.

DatePart (интервал, дата) – позволяет получить заданную часть даты, например, только значение года, месяца или дня недели. Результат возврата регулируется параметром “интервал”.

DateSerial (год, месяц, день) – данная функция vba принимает три числовые значения, по которым возвращается дата.

DateValue (строка) – аналог предыдущей функции, но тут в качестве параметра мы передаем не числа. А строковое значение, на основе которого будет возвращаться дата, vba работа с датами.

Day (дата) – позволяет получить значение текущего дня (если отсутствует параметр “дата”) или для заданной даты.

Year (дата) – позволяет получить значение текущего года (если отсутствует параметр “дата”) или для заданной даты.

Month (дата) – позволяет получить значение текущего месяца (если отсутствует параметр “дата”) или для заданной даты.

Weekday (дата) – позволяет получить значение текущей недели (если отсутствует параметр “дата”) или для заданной даты.

Hour (время) – позволяет получить текущее значение часов (если отсутствует параметр “время”) или для заданного времени, vba дата и время.

Minute (время) – позволяет получить текущее значение минут (если отсутствует параметр “время”) или для заданного времени.

Second (время) – позволяет получить текущее значение секунд (если отсутствует параметр “время”) или для заданного времени.

Timer () – удобная функция для определения промежутка времени, ушедшего на выполнение заданного блока кода. Сама функция возвращает целое количество секунд, которые прошли начиная с полуночи.

TimeSerial (часы, минуты, секунды) – позволяет получить время, заданное тремя параметрами

TimeValue (строка) – работает аналогично предыдущей функции, но для получения времени, передается не целое значение, а строковое.

MonthName (числовое значение) – VBA функция позволяет получить название месяца, в качестве параметра указываем номер месяца.

WeekDay (дата) – задает возвращает имя месяца словами по его номеру. Возвращаемое значение зависит от региональных настроек. Если они русские, то вернется русское название месяца.

Помимо указанных выше vba функций даты и времени, можно использовать и такие вариации:

Date (дата) – позволяет задать системную дату

Time (время) – позволяет задать системное время.

В приведенных выше функциях даты и времени vba языка используется параметр “интервал”, который может принимать следующие значения:

VBA функции форматирование данных

Для форматирования данных в языке VBA используется функция Format , которая позволяет сформировать данные любого встроенного типа, используя заданный образец. Общий синтаксис функции format:

Format (Выражение, [“формат”, [первый день недели, [первая неделя года]]]) – тут параметр “выражение” является любым допустимым значением. Формат – необязательный параметр, который задает формат данных, должен заключаться в кавычки. Остальные два параметра также являются необязательными, и задают день недели, который нужно считать первым, и первую неделю года.

Параметр формат может принимать следующие значения:

  • General Number – числовое значение без выделения разрядов тысяч, например, 12150,2003
  • Currency – денежный формат, выделяются тысячные разряды и два знака после запятой, например, 255,33р.
  • Fixed – числовое значение без выделения разрядов тысяч и двумя знаками после запятой, например, 22439,12.
  • Standart – числовое значение, которое умножается на 100, при этом, остаются два знака после запятой и символ процента, например, 55,63%.
  • Scientific – числовой формат для научных вычисление, например, 5,23Е+03
  • Yes/No – данный параметр определяет, что функция вернет “Да” если форматированное выражение содержит строку Нет, или ненулевое значение и “Нет” в противном случае.
  • True/False – аналог предыдущего параметра, но для строк “Истина” и “Ложь”.
  • On/Off – для строк вида “Вкл” и “Выкл”.
  • General Date – форматирование выражения в соответствии с установленными в системе стандартами даты и времени, например, 10.11.02 12:25:50
  • Long Date – вывод полного формата даты, в зависимости от установок в системе, например, 10 октября 2013 г, vba функции даты.
  • Medium Date – средний формат дат, например, 10-Окт-13.
  • Short Date – короткий вывод формата даты, например, 10.10.13.
  • Long Time – вывод в формате времени (часы, минуты, секунды), например, 12:20:40.
  • Medium Time – 12 часовой формат времени (часы, минуты), например, 05:30.
  • Short Time – 24 часовой формат времени (часы, минуты), например, 17:30.

Спасибо за внимание. Автор блога Владимир Баталий

Источник: scriptcoding.ru

VBA Excel. Автоматическое заполнение интервала дат (периода)

Автоматическое заполнение интервала дат при открытии диалогового окна для выбора периода, а также при нажатии кнопок: «Сегодня», «Вчера», «Этот месяц», «Прошлый месяц», «Прошлый год» с помощью VBA Excel. Скачать файл с примером.

Диалоговое окно для выбора периода

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

Читайте также:  Как в excel обновить ссылки

Пример диалогового окна для выбора периода

Диалоговое окно для выбора периода

Для вызова пользовательской формы используется вставленная на рабочий лист кнопка из панели «Элементы ActiveX». Расположить ее лучше на закрепленной верхней строке листа, чтобы кнопка не уезжала за пределы экрана при прокрутке.

Итак, перечислим названия объектов (элементов управления), которые будут задействованы в приведенных примерах кода:

  • пользовательская форма (диалоговое окно выбора периода) – UserForm1;
  • текстовое поле для ввода начальной даты – TextBox1;
  • текстовое поле для ввода конечной даты – TextBox2;
  • кнопка «Сегодня» – CommandButton1;
  • кнопка «Вчера» – CommandButton2;
  • кнопка «Текущий месяц» – CommandButton3;
  • кнопка «Прошлый месяц»* – CommandButton4;
  • кнопка «Прошлый год»** – CommandButton5;
  • кнопка для вызова диалогового окна, расположенная на рабочем листе – Button1***.

*Выражение «Прошлый месяц» используется в значении «Предыдущий месяц».
**Выражение «Прошлый год» используется в значении «Предыдущий год».
***По-умолчанию кнопка на рабочем листе была создана с именем CommandButton1, но была изменена, чтобы отличалась по названию от кнопки «Сегодня».

Открытие диалогового окна с заполненным периодом

Код вызова пользовательской формы находится в модуле листа, которому принадлежит кнопка Button1. При ее нажатии происходит заполнение формы прошедшим интервалом текущего месяца (с первого числа месяца по текущую дату включительно) и отображение диалогового окна на экране.

Источник: vremya-ne-zhdet.ru

Функция DateValue

Возвращает значение типа Variant ( Date).

Аргумент ” Дата ” обычно является строковое выражение ом, представляющим дату от 1 января 100 г. по 31 декабря 9999 г. Однако Дата также может быть любой выражение, который может представлять дату, время или дату и время в указанном диапазоне.

Если аргумент ” Дата ” содержит только числа, разделенные действительными разделители даты, функция ДАТАЗНАЧ распознает порядок месяца, дня и года согласно короткому формату даты, заданному для вашей системы. Функция ДАТАЗНАЧ также распознает однозначные даты, содержащие названия месяцев, в длинной или сокращенной форме. Например, в дополнение к распознаванию 12/30/1991 и 12/30/91, функция ДАТАЗНАЧ также распознает 30 декабря, 1991 и Dec 30, 1991.

Если в аргументе дата год опущен, DateValue использует текущий год из системной даты компьютера.

Если аргумент дата включает данные о времени, DateValue не возвращает их. Однако если дата включает недопустимые данные о времени (например, “89:98”), возникает ошибка.

Примечание: Если в свойстве Calendar задан григорианский календарь, аргумент дата следует указывать соответствующим образом. Если используется календарь Хиджра, дата должна соответствовать ему. Если дата указана по календарю Хиджра, аргумент дата является переменной String, представляющей дату с 01.01.100 (2 августа 718 года по григорианскому календарю) по 03.04.9666 (31 декабря 9999 года по григорианскому календарю).

Примеры запросов

Выберите “ДАТАЗНАЧ” ([DateTime]) как Выражение1 из Продуктсалес;

Возвращает значения даты поля “Дата и время” в коротком формате даты без сведений о времени.

Выберите параметр ДАТАЗНАЧ ([DateTime]) AS Невдате, Count (Продуктсалес. DateTime) в качестве Каунтофдатетиме от Продуктсалес GROUP BY ДАТАЗНАЧ ([DateTime]);

Возвращает значения даты поля “Дата и время” в коротком формате даты без сведений о времени.

Пример VBA

Примечание: В примерах ниже показано, как использовать эту функцию в модуле Visual Basic для приложений (VBA). Чтобы получить дополнительные сведения о работе с VBA, выберите Справочник разработчика в раскрывающемся списке рядом с полем Поиск и введите одно или несколько слов в поле поиска.

В этом примере функция DateValue используется для преобразования строки в дату. Вы также можете использовать литералы даты, чтобы непосредственно назначить дату переменной типа Variant или Date, например MyDate = #12/2/69#.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник: support.office.com

Функции для работы с датой и временем

Функции для работы с числовыми значениями

Функций для работы с числовыми значениями в VBA очень много. Используются они реже, чем строковые функции, но во многих ситуациях без них не обойтись. Еще один момент: если вы программируете на языке VBA, то, скорее всего, на вашем компьютере установлен Microsoft Office с Excel. В Excel есть свой собственный мощный набор встроенных функций для работы с числовыми значениями, которые вполне доступны из VBA. Если вы в моем списке не найдете ничего подходящего для вашей ситуации, возможно имеет смысл воспользоваться функциями Excel.

Кроме того, если в окне Надстройки(меню Сервис | Надстройки) установить флажок напротив строки Пакет анализа, в Excel будет добавлен дополнительный набор аналитических научных и финансовых функций, а если в том же окне установить флажок напротив Analysis ToolPak — VBA, то эти функции станут доступны из VBA (только внутри Excel, в котором установлена эта надстройка).

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

Читайте также:  Как в эксель выделить все

– Abs() — эта функция возвращает абсолютное значение переданного ей числа (то же число, но без знака). Например, Abs(3) и Abs(−3) вернут одно и то же значение 3. Обычно используется тогда, когда нам нужно определить разницу между двумя числами, но при этом мы не знаем, какое число — первое или второе — больше. Результат вычитания может быть и положительным и отрицательным. Чтобы он был только положительным, используется эта функция.

– Int(), Fix() и Round() — позволяют по-разному округлять числа. Int() возвращает ближайшее меньшее целое, Fix() отбрасывает дробную часть, Round() округляет до указанного количества знаков после запятой. При этом Round() работает не совсем правильно, в чем легко убедиться:

MsgBox Round(2.505, 2)

Поэтому на практике для округления лучше использовать Format():

MsgBox Format(2.505, “#,##0.00”)

– Rnd() и команда Randomize — используются для получения случайных значений (очень удобно, например, при генерации имен файлов). Обычный синтаксис при применении Rnd() выглядит так:

случайное_число = Int(минимум + (Rnd() * максимум))

MsgBox (Int(1 + (Rnd() * 100)))

Однако перед вызовом функции Rnd() необходимо выполнить команду Randomize для инициализации генератора случайных чисел.

– Sgn() — позволяет вернуть информацию о знаке числа. Возвращает 1, если число положительное, −1, если отрицательное, и 0, если проверяемое число равно 0.

Без функций даты и времени обычно обойтись очень сложно. Самые важные функции VBA для работы с датой/временем приведены далее.

– Date(), Time(), Now() — возвращают соответственно текущую системную дату, текущее системное время и дату и время одновременно. Установить их можно при помощи одноименного соответствующего оператора, например, так:

– DateAdd() – добавляет к дате указанное количество лет, кварталов, месяцев и так далее до секунд.

– DateDiff() — возвращает разницу между датами (в единицах от лет до секунд).

-DatePart() — очень важная функция, которая возвращает указанную вами часть даты (например, только год, только месяц или только день недели).

– DateSerial() — создает значение даты на основе передаваемых символьных значений. То же самое делает функция DateValue(), но при другом формате принимаемых значений. Аналогичным образом (для времени) работают TimeSerial() и TimeValue().

– Day() (а также Year(), Month(), Weekday(), Hour(), Minute(), Second()) – специализированные заменители функции DatePart(), которые возвращают нужную вам часть даты.

– MonthName() — возвращает имя месяца словами по его номеру. Возвращаемое значение зависит от региональных настроек. Если они русские, то вернется русское название месяца.

– Timer() — возвращает количество секунд, прошедших с полуночи.

Если нужно получить дополнительные возможности работы с датой/временем, то в вашем распоряжении объектная модель Outlook. Например, при помощи ее можно получить информацию о праздниках и рабочих/нерабочих днях большинства стран мира.

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Для студентов недели бывают четные, нечетные и зачетные. 9983 – | 7759 – или читать все.

Источник: studopedia.ru

Работа с датами в Visual Basic / Excel

Очень новичок в работе с Visual Basic / Excel. Я пытаюсь написать быстрый скрипт, который вводит текущее время в один столбец и позволяет пользователю ввести, сколько days/hours/minutes пройдет до Нового времени, и вывести это в другом столбце.

Я уверен, что это не лучший способ сделать это, но то, что у меня есть до сих пор, заключается в следующем. Я отказался от возни с датами, и я просто работаю со временем:

Я получаю ошибку, конечно. Если бы кто-нибудь мог пролить свет на лучший способ сделать это, наряду с функциями, которые я неправильно использую, я был бы очень признателен!

Edit: я бы, конечно, хотел, чтобы сценарий также обрабатывал дни.

3 Ответа

Я думаю, что это возможно, просто используя функции ячейки в Excel, если я правильно вас понял.

Например, вот что вы увидите.

и это то, что находится в каждой ячейке (предполагая, что верхняя левая ячейка-A1).

Описание каждой функции:

  • NOW() возвращает текущую дату и время, отформатированные как дата и время.
  • DATE(year,month,day) возвращает число, представляющее дату в коде даты-времени MS Excel.
  • TIME(hours,minutes,seconds) преобразует часы, минуты и секунды, заданные в виде чисел, в серийный номер Excel, отформатированный в формате времени.

Рассечение уравнения в последней ячейке:

  • A2 -это ячейка, содержащая текущую дату / время (по состоянию на последний расчет рабочего листа).
  • B2 -это введенное пользователем значение для дней.
  • TIME(C2,D2,0) – это функция TIME(), принимающая введенные пользователем значения для часов и минут из ячеек C2 и D2 соответственно.

Это что-то вроде вашей предполагаемой функциональности.

Если вы хотите использовать VBA, единственная проблема с вашим кодом-это функция “Time”. Вместо этого вы можете использовать CDate :

Когда вы ‘Dim’ таким образом, вы должны записать тип данных для каждой переменной. То, как у вас есть это MinutesLeft, является двойным, и все (по умолчанию)является вариантом.

Функция времени, которую вы ищете, – это TimeSerial.

Даты хранятся в виде числа дней, прошедших с определенной даты. Чтобы добавить дни к дате, вы можете просто сложить числа вместе.

Похожие вопросы:

Есть ли какой-либо инструмент или код, который позволил бы мне отображать Excel 2003 Sheet or part of it в форме Visual Basic 6.0, чтобы он мог редактироваться в среде выполнения vb 6.0?

Читайте также:  Как в excel совместить две таблицы в excel

я хочу получить значение столбца A excel sheet1 в некоторую переменную visual basic, а затем после изменения этого значения отправить обратно в следующий лист2

У меня есть Button1_click, чтобы открыть файл Excel, но файл excel скрывается за visual basic. Как просмотреть файл excel и сделать его режимом редактирования, хотя программа visual basic запущена?

Работа на хосте Windows 7 Enterprise x64 с установленной 64-разрядной версией Microsoft Office Professional Plus 2016. Я хотел бы создать Visual Basic script vbscript (файл на моем рабочем столе.

Я пытаюсь использовать Visual Basic в пределах Excel, чтобы использовать макрос, связанный с кнопкой, для фильтрации таблицы, отображающей все строки с датами сегодня или раньше, чем сегодня. Когда.

Я хочу вызвать макрос Excel [т. е. решатель Excel] с помощью visual basic 6.

Я никогда не использовал Visual Basic раньше, но мог бы сделать с указателем на то, с чего начать. У меня есть 750 excel электронных таблиц, которые содержат различные объемы данных разных типов.

Я недавно начал программировать с .NET visual basic, используя visual basic studio. Я также использую excel VBA, чтобы сделать некоторые macros. Я был бы очень признателен, если бы кто-то мог.

Похоже, что Visual Basic не может ссылаться на листы в соответствии с измененными пользователем именами листов. На вкладках рабочего листа можно изменить их имена, но, похоже, Visual Basic.

В Excel 2016 есть опция под (щелкните правой кнопкой мыши линия на диаграмме) > форматировать ряд данных > сглаженная линия, которая делает линию на линейном графике 2D гладкой. При создании.

Источник: coderoad.ru

Как сделать match() работу с датой в excel vba?

У меня проблема с тем, что match() работает в excel VBA. Код:

Значение в ячейке F1 равно 9/1/2008.

Даже если я изменил сентябрь 2008 г. на 9/1/2008, он все равно не возвращает никакого значения.

Есть идея, как это исправить?

Лучше всего использовать .Find() . Это будет вернуть range , если найдено, или nothing , если нет.

Если вы хотите номер столбца:

С захватом не найденного

Причина, по которой Even if I changed Sep 2008 to 9/1/2008, it still doesn’t return any value.

Это потому, что когда есть дата в excel, Excel автоматически преобразует эту дату в числовое значение. То, что вы действительно хотите найти, — это:

Это число — это количество дней между 9/1/2008 и excel по умолчанию 1/1/1900

каждая дата в excel хранится со значением, подобным этому. Таким образом, самый простой способ справиться с этим состоит в том, чтобы преобразовать то, что вы видите как дату, к тому, что Excel видит как дату, используя CDate() .

Это само по себе даст вам неиспользуемую ошибку, что vba не может получить свойство.

Это потому, что Lookup_value может быть значением (числом, текстом или логическим значением) или ссылкой ячейки на число, текст или логическое значение. Нет даты, поэтому просто преобразуйте текущее значение даты в число, чтобы найти соответствующий номер в списке, используя CLng()

Дайте этот снимок, он также будет намного быстрее, чем при использовании альтернативы Find:

Это должно дать ожидаемый результат

Чтобы обработать, когда совпадение не найдено, попробуйте этот Sub:

Таким образом он работает с помощью этого метода:

Nbr,L, C как целое

Datedeb как дата

использовать WorksheetFunction.Match(CDbl(date), range, 0)

В качестве альтернативы используйте Date cell Value2 свойство (которое также будет Double ) вместо Value для ключа поиска.

CLng , предложенный в других ответах, отбросит временную часть Date .

Такая же проблема существует для типа данных Currency , но вы не можете использовать CDbl для него (см. ниже для параметров).

Свойство Range.Value2 (Excel) предполагает, что типы Date и Currency являются «специальными», поскольку они имеют «внутреннее представление», которое резко контрастирует с отображаемым значением. Действительно:

  • Date внутренне представлен как 64-битные (8-байтовые) IEEE числа с плавающей запятой, где целая часть — это дата и дробная часть — это время
  • Currency также является 8-байтным, но обрабатывается как число с фиксированной запятой с 4 дробными цифрами (целое число, масштабируемое на 10 «000)

По-видимому, Match сравнивает эти внутренние значения по соображениям производительности. Поэтому мы должны убедиться, что они, а не читаемые представления, точно соответствуют.

Так как Date уже является внутренним числом с плавающей запятой, CDbl(date) фактически не изменяет данные.

Для типа Currency CDbl изменяет данные, поэтому это не может быть и речи. Поэтому либо

  • используйте точное представление ключа (до 4 дробных цифр) так или иначе, если вам требуется точное совпадение, или
  • сделать ячейки в диапазоне фактически формулами с Round ), если значение для сравнения происходит из другого места и/или вас требуется только равенство 2 дробным цифрам

Я думаю, что могу смело предположить, что значение в F1 является датой. В вашем коде «Sep 2008» есть строка. Вы никогда не сможете получить успешный матч до тех пор, пока ваши типы данных будут непоследовательными.
Если вы ищете дату, убедитесь, что первым параметром является дата.

Вот еще один возможный подход.

Я знаю, что этот пост старый, но у меня была такая же проблема, и я нашел ответ.

Чтобы заставить его работать, сначала нужно сделать VBA таким же форматированием данных, как и в вашей таблице Excel:

Источник: techarks.ru