Remkomplekty.ru

IT Новости из мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Dateadd vba excel

Урок 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 (Выражение, [“формат”, [первый день недели, [первая неделя года]]]) – тут параметр “выражение” является любым допустимым значением. Формат – необязательный параметр, который задает формат данных, должен заключаться в кавычки. Остальные два параметра также являются необязательными, и задают день недели, который нужно считать первым, и первую неделю года.

Читать еще:  Код vba в excel

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

  • 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.

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

Open Notes

Обо всём, что мне интересно

Полезные команды VBA

Пополняемый список полезных отрывков кода VBA для выполнения часто востребованных действий в MS Excel.

Служебные команды для ускорения скорости выполнения макроса:

‘Отключение отображения выполняемых действий Application.ScreenUpdating = False ‘Предотвращение появления предупреждающих сообщений Application.DisplayAlerts = False ‘Предотвращение появления предупреждения об обновлении связей данных Application.AskToUpdateLinks = False ‘Очистка буфера обмена Application.CutCopyMode = False

Проверка имени пользователя, запустившего макрос:

Чтобы проверить, какой пользователь открыл книгу Excel можно использовать один из следующих вариантов:

If Application.UserName = «Имя_автора_документа» Then .

If Environ(«username») = «user» Then .

Поиск последней строки таблицы:

Set myWSheet = ThisWorkbook.Sheets(«Имя_листа») With myWSheet ‘Определение индекса последней строки таблицы lastRow = .Cells(Rows.Count, 1).End(xlUp).Row ‘Определение значения в ячейки последней строке столбца A lastARow = .Range(«A» & lastRow).Value End With

Замена формулы на значение:

Добавление нового листа с именем после всех существующих:

Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = «Имя_листа»

Как узнать последний день предыдущего месяца:

LastMonthDay = DateAdd(«d», -1, DateSerial(Year(dtDate), Month(dtDate), 1))

Определение оставшихся дней месяца:

dToEndOfMonth = DateDiff(«d», dFrom, DateAdd(«d», -1, _ DateSerial(Year(dFrom), Month(dFrom) + 1, 1)))

Номер текущего дня в неделе (воскресенье — первый день):

DayOfWeek = DatePart(«w», dToday)

Создание нового файла из текущего:

pathNewBook = «C:Temp» nameNewBook = «Имя_нового_файла.xls» Workbooks.Add ActiveWorkbook.SaveAs Filename:=pathNewBook & nameNewBook ActiveWorkbook.Close True

Сохранить текущий файл в формате CSV

Чтобы при сохранении файла в формате CSV, вместо запятых в качестве разделителя использовалась точка с запятой, следует использовать подобный код:

ActiveWorkbook.SaveAs FileName:=»Name.csv», FileFormat:=xlCSV, _ CreateBackup:=False, Local:=True ActiveWorkbook.Saved = True ActiveWorkbook.Close True

Копирование данных из одного файла в другой:

wbPath = «C:Temp» wbName = «Имя_файла_откуда_копируем.xls» Workbooks.Open (wbPath & wbName) Set WB = Workbooks(wbName) WB.Sheets(«Лист 1»).Range(«A1:С10»).Copy Sheet(«Лист_в_текущем_файле»).Range(«A2»).PasteSpecial xlPasteValues

Чтобы открыть файл только для чтения, следует использовать:

Workbooks.Open (Filename:=wbPath & wbName, ReadOnly:=True)

Предотвращение ошибки при неудачном поиске значения в таблице:

Set DateRowObj = WB.Sheets(«Имя_листа»).Range(«A:A»)._ Find(What:=dtToAsDate, LookIn:=xlFormulas) If (DateRowObj Is Nothing) Then WB.Close False MsgBox «Данные не найдены.» Else DateRow = DateRowObj.Row ‘Номер строки с искомым значением End If

Как получить имя активной книги Excel без его расширения (без .xls либо без .xlsx):

wbName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, «.») — 1)

Читать еще:  Подсчет символов в тексте excel

Проверка существования файла:

fPath = «C:Temp» fName = «Файл.txt» If Dir(fPath & fName) = «» Then MsgBox «Файл не найден:» & Chr(13) & fPath & fName Exit Sub End If

Кнопка, скрывающая/разворачивающая часть таблицы:

Private Sub tbVid_Click() Application.ScreenUpdating = False If tbVid Then tbVid.Caption = «Скрыть» ActiveSheet.Rows(«2:29»).Hidden = False Else tbVid.Caption = «Развернуть» ActiveSheet.Rows(«2:29»).Hidden = True End If End Sub

Обновление сводной таблицы:

currPath = ThisWorkbook.Path currWBName = ThisWorkbook.Name ListName.PivotTables(«СводнаяТаблица1»).ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=currPath & «[» & _ currWBName & «]Лист1!R1C1:R10C5»)

Обращение к элементам Frame:

Замена #ДЕЛ/0! в диапазоне:

Selection.Replace What:=»#DIV/0!», Replacement:=»», LookAt:=xlPart,_ SearchOrder:=xlByRows, MatchCase:=False,_ SearchFormat:=False, ReplaceFormat:=False

Количество строк в отфильтрованной таблице:

Быстро убрать лишние пробелы в диапазоне:

Программно снять защиту с листа:

Работа с диапазоном

Умножить диапазон на число:

ThisWorkbook.Sheets(1).Range(«A1:A10») = _ ThisWorkbook.Sheets(1).Evaluate(«A1:A10» & «*80»)

Добавить ко всем значениям диапазона строку:

ThisWorkbook.Range(«A1:A10»).Value = _ Evaluate(«=»»» & addTxt & «»» & » & ThisWorkbook.Range(«A1:A10»).Address)

Сортировка выбранного столбца в сводной таблице

Col = Selection.Column ‘Номер выбранного столбца ColMax = ActiveSheet.PivotTables(«СводнаяТаблица»).PivotColumnAxis. _ PivotLines.Count If Col — 1 <= ColMax And Col 1 Then ActiveSheet.PivotTables(«СводнаяТаблица»).PivotFields(«Label»).AutoSort _ xlDescending, » «, ActiveSheet.PivotTables(«СводнаяТаблица»). _ PivotColumnAxis.PivotLines(Col — 1), 1 End If

Счетчик времени выполнения процедуры

‘Счётчик, ставится в начале процедуры StartUpdDate = Now ‘Сообщение, выводится в конце процедуры MsgBox «Данные обновлены за » & Fix(1440 * (Now – StartUpdDate)) & » мин. » & 86400 * (Now – StartUpdDate) Mod 60 & » сек.»

Функция транслитерации с русского на английский

Function Translit(Txt As String) As String Txt = Txt Rus = Array(«ий», «ый», «ъе», «ъя», «ъю», _ «ъё», «ье», «ья», «ью», «ьё», «а», «б», «в», «г», _ «д», «е», «ё», «ж», «з», «и», «й», «к», «л», _ «м», «н», «о», «п», «р», «с», «т», «у», «ф», «х», _ «ц», «ч», «ш», «щ», «ъ», «ы», «ь», «э», «ю», «я», _ «ИЙ», «ЫЙ», «ЪЕ», «ЪЯ», «ЪЮ», _ «ЪЁ», «ЬЕ», «ЬЯ», «ЬЮ», «ЬЁ», «А», «Б», «В», «Г», _ «Д», «Е», «Ё», «Ж», «З», «И», «Й», «К», «Л», _ «М», «Н», «О», «П», «Р», «С», «Т», «У», «Ф», «Х», _ «Ц», «Ч», «Ш», «Щ», «Ъ», «Ы», «Ь», «Э», «Ю», «Я», _ » «, «_», «?», _ «a», «b», «c», «d», «e», «f», «g», «h», «i», «j», «k», «l», «m», _ «n», «o», «p», «q», «r», «s», «t», «u», «v», «w», «x», «y», «z», ««», «»») Eng = Array(«y», «y», «ye», «ya», «yu», _ «yo», «ye», «ya», «yu», «yo», «a», «b», «v», «g», _ «d», «e», «yo», «zh», «z», «i», «y», «k», «l», «m», _ «n», «o», «p», «r», «s», «t», «u», «f», «h», «ts», _ «ch», «sh», «sch», «», «y», «», «eh», «u», «ya», _ «Y», «Y», «Ye», «Ya», «Yu», _ «Yo», «Ye», «Ya», «Yu», «Yo», «A», «B», «V», «G», _ «D», «E», «Yo», «Zh», «Z», «I», «Y», «K», «L», «M», _ «N», «O», «P», «R», «S», «T», «U», «F», «H», «Ts», _ «Ch», «Sh», «Sch», «», «Y», «», «Eh», «U», «Ya», _ » «, «_», «?», _ «a», «b», «c», «d», «e», «f», «g», «h», «i», «j», «k», «l», «m», _ «n», «o», «p», «q», «r», «s», «t», «u», «v», «w», «x», «y», «z», «», «») For i = 1 To Len(Txt) с = Mid(Txt, i, 1) flag = 0 For J = 0 To 116 If Rus(J) = с Then outchr = Eng(J) flag = 1 Exit For End If Next J If flag Then outstr = outstr & outchr Else outstr = outstr & с Next i Translit = outstr End Function

Поиск файлов в папке

Dim strDirPath, strMaskSearch, strFileName as String strDirPath = «C:/test/» ‘Папка поиска strMaskSearch = «*.xls*» ‘Маска поиска ‘Получаем первый файл соответствующий шаблону strFileName = Dir(strDirPath & strMaskSearch) Do While strFileName <> «» ‘До тех пор пока файлы «не закончатся» MsgBox strFileName strFileName = Dir ‘Следующий файл Loop

Читать еще:  Пересчитать формулы в excel

Функция DATEADD

Эта функция возвращает значение типа datetime, которое получается добавлением к дате date количества интервалов типа datepart, равного number. Например, мы можем к заданной дате добавить любое число лет, дней, часов, минут и т.д. Допустимые значения аргумента datepart приведены ниже и взяты из BOL.

Dayofyear — день года

Пусть сегодня 23/01/2004, и мы хотим узнать, какой день будет через неделю. Мы можем написать

SELECT DATEADD(day, 7, current_timestamp)

SELECT DATEADD(ww, 1, current_timestamp)

В результате получим одно и то же; что-то типа 2004-01-30 19:40:58.923.

Однако мы не можем в этом случае написать

SELECT DATEADD(mm, 1/4, current_timestamp)

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

Кроме того, мы можем использовать вместо CURRENT_TIMESTAMP функцию T-SQL GETDATE() с тем же самым эффектом. Наличие двух идентичных функций поддерживается, видимо, в ожидании последующего развития стандарта.

Пример (схема 4). Определить, какой будет день через неделю после последнего полета.

SELECT DATEADD(day, 7, (SELECT MAX(date) max_date FROM pass_in_trip))

Использование подзапроса в качестве аргумента допустимо, т.к. этот подзапрос возвращает ЕДИНСТВЕННОЕ значение типа datetime.

Похожие главы из других книг:

Функция pthread_rwlock_init

Функция pthread_rwlock_init Первая функция, pthread_rwlock_init, динамически инициализирует блокировку чтения-записи. Ее текст приведен в листинге 8.2.7-8 Присваивание атрибутов с помощью этой функции не поддерживается, поэтому мы проверяем, чтобы указатель attr был нулевым.9-19 Мы

Функция pthread_rwlock_rdlock

Функция pthread_rwlock_rdlock Текст функции pthread_rwlock_rdlock приведен в листинге 8.4.Листинг 8.4. Функция pthread_rwlock_rdlock: получение блокировки на чтение//my_rwlock/pthread_rwlock_rdlock.с1 #include «unpipc.h»2 #include «pthread_rwlock.h»3 int4 pthread_rwlock_rdlock(pthread_rwlock_t *rw)5 <6 int result;7 if (rw->rw_magic != RW_MAGIC)8 return(EINVAL);9 if ((result =

Функция pthread_rwlock_tryrdlock

Функция pthread_rwlock_tryrdlock В листинге 8.5 показана наша реализация функции pthread_rwlock_tryrdlock, которая не вызывает приостановления вызвавшего ее потока.Листинг 8.5. Функция pthread_rwlock_tryrdlock: попытка заблокировать ресурс для чтения//my_rwlock/pthread_rwlock_tryrdlock.с1 #include «unpipc.h»2 #include

Функция pthread_rwlock_wrlock

Функция pthread_rwlock_wrlock Текст функции pthread_rwlock_wrlock приведен в листинге 8.6.11-17 Если ресурс заблокирован на считывание или запись (значение rw_refcount отлично от 0), мы приостанавливаем выполнение потока. Для этого мы увеличиваем rw_nwaitwriters и вызываем pthread_cond_wait с условной переменной

Функция pthread_rwlock_trywrlock

Функция pthread_rwlock_trywrlock Неблокируемая функция pthread_rwlock_trywrlock показана в листинге 8.7.11-14 Если значение счетчика rw_refcount отлично от нуля, блокировка в данный момент уже установлена считывающим или записывающим процессом (это безразлично) и мы возвращаем ошибку с кодом EBUSY. В

Функция SUM

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

Функция uni()

Функция uni() Поиск/замена символа по его юникодному номеру также может быть сделана при помощи функции uni().Пример функции uni(): Boouni(107,32)Designer найдет слово Book

Функция uni()

Функция uni() Поиск/замена символа по его юникодному номеру также может быть сделана при помощи функции uni().Пример функции uni(): Boouni(107,32)Designer найдет слово Book

Хэш-функция.

Хэш-функция. Еще одно важное преимущество использования PGP состоит в том, что PGP применяет так называемую «хэш-функцию», которая действует таким образом, что в том случае какого-либо изменения информации, пусть даже на один бит, результат «хэш-функции» будет совершенно

Функция uni()

Функция uni() Поиск/замена символа по его юникодному номеру также может быть сделана при помощи функции uni().Пример функции uni(): Boouni(107,32)Designer найдет слово Book

Хэш-функция

Хэш-функция Однако описанная выше схема имеет ряд существенных недостатков. Она крайне медлительна и производит слишком большой объём данных — по меньшей мере вдвое больше объёма исходной информации. Улучшением такой схемы становится введение в процесс преобразования

Ссылка на основную публикацию
Adblock
detector