Изменить формат даты vba excel

Обновлено: 23.01.2025

Когда мы из кода VBA Excel записываем в ячейку текстовое или другое значение, оно отображается в формате, присвоенном данной ячейке. Это может быть формат:

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

Если ячейка содержит текстовое значение, его начертание можно форматировать по отдельным частям (подстрокам). Такое форматирование доступно как в ручном режиме на рабочем листе, так и из кода VBA Excel.

У объекта Range есть свойство Font (шрифт), которое отвечает за форматирование (начертание) визуально отображаемого текста в ячейках рабочего листа. Его применение вызывает объект Font, который в свою очередь обладает собственным набором свойств, отвечающих за конкретный стиль начертания отображаемого значения.

Применение стилей к ячейкам листа

В Excel изначально установлено множество встроенных стилей. Найти их можно в меню Стили ячеек, которая расположена на вкладке Главная -> Стили.

Откроется галерея стилей (рисунок справа).

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

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

VBA-макрос: заливка, шрифт, линии границ, ширина столбцов и высота строк

В процессе запыления данных сотрудниками отдела на некоторых листах были изменены форматы ячеек:

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

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

Макросы Excel прекрасно справляются с форматированием ячеек на рабочих листах. Кроме того, делают это быстро и в полностью автоматическом режиме. Воспользуемся этими преимуществами и для решения данной задачи напишем свой код VBA-макроса. Он поможет нам быстро и безопасно сбрасывать форматы на исходный предварительно заданный в шаблоне главной таблицы.

Чтобы написать свой код макроса откройте специальный VBA-редактор в Excel: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» или нажмите комбинацию клавиш ALT+F11:

В редакторе создайте новый модуль выбрав инструмент «Insert»-«Module» и введите в него такой VBA-код макроса:

Sub SbrosFormat()
If TypeName(Selection) <> "Range" Then Exit Sub
With Selection
.HorizontalAlignment = xlVAlignCenter
.VerticalAlignment = xlVAlignCenter
.WrapText = True
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexAutomatic
.Columns.AutoFit
.Rows.AutoFit
End With
End Sub

Теперь если нам нужно сбросить форматирование таблицы на исходный формат отображения ее данных, выделите диапазон ячеек A1:E20 и запустите макрос: «РАЗРАБОЧТИК»-«Код»-«Макросы»-«SbrosFormat»-«Выполнить». Результат работы макроса изображен ниже на рисунке:

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

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

Функция возвращает текущую системную дату.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете функцию, она производит следующий вывод.

The Value of a : 19/07/2014

Функция, которая возвращает текущую системную дату.

CDate

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

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете функцию, она производит следующий вывод.

The Value of a : 1/01/2020
The Value of b : 31/12/2050

Функция, которая преобразует данный вход в дату.

DateAdd

Функция, которая возвращает дату, к которой был добавлен указанный временной интервал.

Синтаксис

Параметр/Описание

Интервал - требуемый параметр. Он может принимать следующие значения.
d - день года
м - месяц года
y - год года
yyyy - year
w - день недели
ww - неделя
q - квартал
час - час
м - минута
s - секунда
Номер - требуемый параметр. Он может принимать как положительные, так и отрицательные параметры.
Дата - требуемый параметр. Вариант или литерал, представляющий дату добавления интервала.

пример

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : 27/06/1895
Line 2 : 27/09/1894
Line 3 : 27/07/1894
Line 4 : 28/06/1894
Line 5 : 28/06/1894
Line 6 : 28/06/1894
Line 7 : 4/07/1894
Line 8 : 1/01/2013 1:00:00 PM
Line 9 : 1/01/2013 12:01:00 PM
Line 10 : 1/01/2013 12:00:01 PM
Line 11 : 27/06/1893
Line 12 : 27/03/1894
Line 13 : 27/05/1894
Line 14 : 26/06/1894
Line 15 : 26/06/1894
Line 16 : 26/06/1894
Line 17 : 20/06/1894
Line 18 : 1/01/2013 11:00:00 AM
Line 19 : 1/01/2013 11:59:00 AM
Line 20 : 1/01/2013 11:59:59 AM

Функция, которая возвращает дату, к которой был добавлен указанный временной интервал.

DateDiff

Функция, которая возвращает разницу между двумя заданными временными интервалами.

Синтаксис

DateDiff(interval, date1, date2 [,firstdayofweek[, firstweekofyear]])

Параметр/Описание

Интервал - требуемый параметр. Он может принимать следующие значения.
d - день года
м - месяц года
y - год года
yyyy - year
w - день недели
ww - неделя
q - квартал
час - час
м - минута
s - секунда
Date1 и Date2 - Необходимые параметры.
Firstdayofweek - необязательный параметр. Задает первый день недели. Он может принимать следующие значения.
0 = vbUseSystemDayOfWeek - настройка API поддержки национальных языков (NLS)
1 = vbSunday - воскресенье
2 = vbMonday - понедельник
3 = vbTuesday - вторник
4 = vbWednesday - среда
5 = vbThursday - четверг
6 = vbFriday - пятница
7 = vbSaturday - суббота
Firstdayofyear - необязательный параметр. Указывает на первый день года. Он может принимать следующие значения.
0 = vbUseSystem - настройка API поддержки национальных языков (NLS)
1 = vbFirstJan1 - начать с недели, в которой происходит 1 января (по умолчанию)
2 = vbFirstFourDays - Начните с недели, которая имеет не менее четырех дней в новом году
3 = vbFirstFullWeek - начните с первой полной недели нового года

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : 1
Line 2 : 4
Line 3 : 12
Line 4 : 365
Line 5 : 365
Line 6 : 52
Line 7 : 52
Line 8 : 8783
Line 9 : 527039
Line 10 : 31622340

Функция, которая возвращает разницу между двумя периодами времени.

DatePart

Функция, возвращающая определенную часть данной даты.

Синтаксис

Параметр/Описание

Интервал - требуемый параметр. Он может принимать следующие значения.
d - день года
м - месяц года
y - год года
yyyy - year
w - день недели
ww - неделя
q - квартал
час - час
м - минута
s - секунда
Date1 - обязательный параметр.
Firstdayofweek - необязательный параметр. Задает первый день недели. Он может принимать следующие значения.
0 = vbUseSystemDayOfWeek - настройка API поддержки национальных языков (NLS)
1 = vbSunday - воскресенье
2 = vbMonday - понедельник
3 = vbTuesday - вторник
4 = vbWednesday - среда
5 = vbThursday - четверг
6 = vbFriday - пятница
7 = vbSaturday - суббота
Firstdayofyear - необязательный параметр. Указывает на первый день года. Он может принимать следующие значения.
0 = vbUseSystem - настройка API поддержки национальных языков (NLS)
1 = vbFirstJan1 - начать с недели, в которой происходит 1 января (по умолчанию)
2 = vbFirstFourDays - Начните с недели, которая имеет не менее четырех дней в новом году
3 = vbFirstFullWeek - начните с первой полной недели нового года

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : 1
Line 2 : 15
Line 3 : 3
Line 4 : 1

Функция, возвращающая указанную часть заданного значения даты ввода.

DateSerial

Функция, которая возвращает дату для заданных параметров дня, месяца и года.

Синтаксис

Параметр/Описание

Год - требуемый параметр. Число от 100 до 9999 или числовое выражение. Значения от 0 до 99 интерпретируются как годы с 1900 по 1999 год. Для всех аргументов за другой год используйте полный четырехзначный год.
Месяц - требуемый параметр. Он также может быть в форме выражения, которое должно варьироваться от 1 до 12.
День - требуемый параметр. Он также может быть в форме выражения, которое должно варьироваться от 1 до 31.

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Функция, которая возвращает действительную дату для данного года, месяца и даты.

FormatDateTime

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

Синтаксис

Параметр/Описание

Дата - требуемый параметр.
Формат - необязательный параметр. Значение, определяющее формат даты или времени, который будет использоваться. Он может принимать следующие значения.
0 = vbGeneralDate - Default
1 = vbLongDate - дата возврата
2 = vbShortDate - Дата возврата
3 = vbLongTime - возвращает время
4 = vbShortTime - возвращает время

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : 15/08/2013 8:25:00 PM
Line 2 : Thursday, 15 August 2013
Line 3 : 15/08/2013
Line 4 : 8:25:00 PM
Line 5 : 20:25

Функция, которая форматирует дату на основе поставляемых параметров.

IsDate

Функция, возвращающая логическое значение, независимо от того, является ли данный ввод датой.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : True
Line 2 : True
Line 3 : True

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

Функция «День» возвращает число от 1 до 31, которое представляет день указанной даты.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Функция, которая возвращает целое число от 1 до 31, которое представляет день указанной даты.

Month

Функция Month возвращает число от 1 до 12, которое представляет месяц указанной даты.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Функция, которая возвращает целое число от 1 до 12, которое представляет месяц указанной даты.

Функция Год возвращает целое число, которое представляет год указанной даты.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Функция, которая возвращает целое число, которое представляет год указанной даты.

MonthName

Функция MonthName возвращает имя месяца для указанной даты.

Синтаксис

Параметр Описание

Месяц - требуемый параметр. Он определяет номер месяца.
Toabbreviate - необязательный параметр. Булевское значение, указывающее, следует ли сокращать имя месяца. Если оставить пустым, значение по умолчанию будет считаться False.

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : Jan
Line 2 : January
Line 3 : Jul
Line 4 : July

Функция, которая возвращает имя определенного месяца за указанную дату.

WeekDay

Функция WeekDay возвращает целое число от 1 до 7, которое представляет день недели для указанной даты.

Синтаксис

Параметр/Описание

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1: 5 Line 2: 4 Line 3: 4 Line 4: 3 Line 5: 4 Line 6: 5

Функция, которая возвращает целое число (от 1 до 7), которое представляет день недели в течение указанного дня.

WeekDayName

Функция WeekDayName возвращает имя дня недели за указанный день.

Синтаксис WeekdayName(weekday[,abbreviate[,firstdayofweek]])

Weekday - требуемый параметр. Номер дня недели.
Toabbreviate - необязательный параметр. Булевское значение, указывающее, следует ли сокращать имя месяца. Если оставить пустым, значение по умолчанию будет считаться False.
Firstdayofweek - необязательный параметр. Задает первый день недели.
0 = vbUseSystemDayOfWeek - настройка API поддержки национальных языков (NLS)
1 = vbSunday - воскресенье
2 = vbMonday - понедельник
3 = vbTuesday - вторник
4 = vbWednesday - среда
5 = vbThursday - четверг
6 = vbFriday - пятница
7 = vbSaturday - суббота

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : Tuesday
Line 2 : Mon
Line 3 : Sunday
Line 4 : Tue
Line 5 : Sunday

Функция, которая возвращает имя дня недели для указанного дня.

Функция Now возвращает текущую системную дату и время.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод. The Value of a : 19/07/2013 3:04:09 PM

Функция, которая возвращает текущую системную дату и время.

Функция Hour возвращает число от 0 до 23, которое представляет час дня для указанной отметки времени.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1: 15
Line 2: 23
Line 3: 14

Функция, которая возвращает целое число от 0 до 23, которое представляет часовую часть заданного времени.

Minute

Функция Minute возвращает число от 0 до 59, которое представляет минуту часа для указанной отметки времени.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1: 13
Line 2: 43
Line 3: 20

Функция, возвращающая целое число от 0 до 59, которое представляет минутную часть данного времени.

Second

Функция возвращает число от 0 до 59, которое представляет вторую часть часа для указанной отметки времени.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод. Line 1: 25
Line 2: 45
Line 3: 0

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

Функция времени возвращает текущее системное время.

Синтаксис

пример

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1: 3:29:15 PM

Функция, которая возвращает текущее системное время.

Timer

Функция таймера возвращает число секунд и миллисекунд с 12:00.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Time is : 19/07/2013 3:45:53 PM
Timer is: 56753.4

Функция, которая возвращает число секунд и миллисекунд с 12:00.

TimeSerial

Функция TimeSerial возвращает время для указанных часов, минут и вторых значений.

Синтаксис

Параметр/Описание

Hour - обязательный параметр, который представляет собой целое число от 0 до 23 или любое числовое выражение.
Minute - обязательный параметр, который представляет собой целое число от 0 до 59 или любое числовое выражение.
Second - обязательный параметр, который представляет собой целое число от 0 до 59 или любое числовое выражение.

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

8:01:02 PM
12:59:59 AM
2:20:18 PM

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

TimeValue

Функция TimeValue преобразует заданную входную строку в допустимое время.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

[/vba] - с первой ячейки столбца М вниз на r_ ячеек

Если хотите на весь столбец, то напишите[vba]

[/vba]Но я бы так не стал делать

Если хотите на 10000 строк, то напишите[vba]

Конечно. А зачем Вам форматировать весь столбец? Только зря увеличивать размер файла
У Вас же в коде написано
[vba] [/vba] - это мы ищем последнюю заполненную ячейку столбца М
[vba]

[/vba] - с первой ячейки столбца М вниз на r_ ячеек

Если хотите на весь столбец, то напишите[vba]

[/vba]Но я бы так не стал делать

Если хотите на 10000 строк, то напишите[vba]

[/vba] - это мы ищем последнюю заполненную ячейку столбца М
[vba]

[/vba] - с первой ячейки столбца М вниз на r_ ячеек

Если хотите на весь столбец, то напишите[vba]

[/vba]Но я бы так не стал делать

Если хотите на 10000 строк, то напишите[vba]

[/vba]Но я бы и так не стал делать Автор - _Boroda_
Дата добавления - 05.07.2017 в 10:13 _Boroda_, я не это имел ввиду. Оно форматирует с 300-от ячеек, например, только 30. Остальные остаются в том формате, в каком были изначально.
_Boroda_, я не это имел ввиду. Оно форматирует с 300-от ячеек, например, только 30. Остальные остаются в том формате, в каком были изначально.
Captain_Hemp Покажите на примере. В файле на Лист1 в столбце М введите свои данные с "неизменяемым" форматом, запустите в этом же файле мой макрос и приложите сюда полученный результат с комментарием типа "в ячейке М36 ничего не поменялось" Покажите на примере. В файле на Лист1 в столбце М введите свои данные с "неизменяемым" форматом, запустите в этом же файле мой макрос и приложите сюда полученный результат с комментарием типа "в ячейке М36 ничего не поменялось" _Boroda_

Что не поменялось дальше М7? У меня файл выглядит так (для увеличения тпните картинку левой мышой)

По-моему все нормально. Что конкретно Вам не нравится?

[p.s.]И не нужно архивировать файлы, у которых размер меньше 100 кб. Такие файлы можно класть сюда и так

[p.s.]И не нужно архивировать файлы, у которых размер меньше 100 кб. Такие файлы можно класть сюда и так _Boroda_

Если представить, что имеются два клиента с разными рег. настройками - американскими и русскими, то функция Format() вернет разные значения строковой переменной (какой идиот придумал формат mm.dd.yyyy?).

кто-то что-то где-то недогоняет (возможно я :))

Пример выполнения программы при американских рег. настройках на приложенном рисунке.

orunbek
За класс - спасибо! Попробую попозже разобраться.

Формат даты которую вводит юзер ДОЛЖЕН СОВПАДАТЬ с настройками системы.
Не ставь тете Маше американские винды, не ставь дяде Джону русские.
Дата введеная в книгу Экселя на виндах с правильно настроеным форматом даты всегда правильно прочитается после копирования книги на винды с другой региональной настройкой.

Если сумеешь ответить на мои вопросы (хотя бы на второй), я тебе подскажу как правильно написать макрос :)

Ну.
1. Имеется система, построенная американцами. Для полной работоспособности она должна стоять на Windows с региональными настройками English(USA). Эта система продана в Прибалтику, где пользователи привыкли вводить данные в формате dd.mm.yyyy, а не mm/dd/yyyy. Экселевский файл с отчетом будет находится на этих же машинах с рег. настройкой Windows - English(USA).
2. Date_as_dd.mm.yyyy - это переменная String. Просто я написал её таким образом, чтобы было понятно, что пользователь будет вводить дату как dd.mm.yyyy, а получилось, что просто запутал народ. Никаких классов нет.
3. Я не знаю, почему у тебя так, но мне хотелось бы узнать: у тебя региональные настройки Windows(!) в обоих случаях одинаковые или нет? Что значит "на натуральном американском Экселе" - это обыкновенный Excel из Office 200x English или это у тебя рег. настройки Windows - English(USA)?

Читайте также: