Excel vba format функция — Мир ПК

Функция Format

Возвращает значение типа Variant ( String), которое содержит выражение, отформатированное в соответствии с указаниями, содержащимися в выражении формата.

Предупреждение Возникла проблема с использованием этой функции. Последний понедельник в некоторых годовых годах может быть возвращен в неделю 53, когда она должна быть неделей 1. Дополнительные сведения и временное решение можно найти в статье функции Format и datepart могут возвращать неверный номер недели для последнего месяца в году.

Format( выражение [ , формат ] [ , первый_день_недели ] [ , первая_неделя_года ] )

Функция Format имеет следующие аргументы:

Обязательный аргумент. Любое допустимое выражение.

Необязательный. Допустимое именованное или определяемое пользователем выражение формата.

Необязательный. Константа, задающая первый день недели.

Необязательный аргумент. Константа, задающая первую неделю года.

Аргумент Format может использовать разнообразные параметры, в зависимости от типа данных аргумента Expression . Дополнительные сведения см. в статьях, указанных в таблице ниже.

Аргумент первый_день_недели может принимать следующие значения:

Используются параметры API NLS.

Воскресенье (по умолчанию)

Аргумент первая_неделя_года может принимать следующие значения:

Используются параметры API NLS.

Начинается с недели, на которую приходится 1 января (по умолчанию).

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

Первой неделей считается первая полная неделя года.

Используйте стандартные именованные числовые форматы или создайте пользовательские числовые форматы.

Значения даты и времени

Используйте стандартные именованные форматы даты и времени или создайте пользовательские форматы даты и времени.

Серийные номера даты и времени

Используйте форматы даты и времени или числовые форматы.

Создайте пользовательские форматы строк.

Если вы попытаетесь отформатировать число без задания формата, функция Format будет работать аналогично функции Str, хотя и с поддержкой международных форматов. Однако положительные числа, отформатированные как строки с использованием функции Format, не будут включать начальный пробел, зарезервированный для знака значения; с другой стороны, в случае преобразования с помощью функции Str начальный пробел сохранится.

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

Примечание: Если для свойства Calendar задано значение Gregorian, а для аргумента формат указано форматирование даты, предоставленное выражение должно иметь тип Gregorian. Если для свойства Calendar в Visual Basic задано значение Hijri, выражение должно иметь тип Hijri.

Если используется григорианский календарь, значение символов выражения формат не изменяется. Если используется календарь Хиджра, все символы формата даты (например, dddd, mmmm, yyyy) имеют то же значение, но применяются к календарю Хиджра. Символы формата остаются английскими; символы, отображаемые в текстовом виде (например, AM и PM), выводят строку (на английском или арабском языке), связанную с этим символом. Диапазон некоторых символов при использовании календаря Хиджра изменяется.

Отображает полное название месяца (у названий месяцев Хиджра нет сокращений).

Примеры

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

В этом примере в источнике записей отчета есть поле «Дата», содержащее дату изменения каждой конкретной записи и использующееся в отчете. Во время выполнения отчета результаты фильтруются и отображаются только те записи, в которых значение недели в поле «Дата» (Format([Дата],»ww»)) равно прошлой неделе (Format(Now(),»ww»)-1).

Использование функции Format в коде VBA

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

В данном примере показаны различные варианты использования функции Format для форматирования значений с помощью предварительно заданных форматов и форматов, определенных пользователем. Для разделителя даты ( /), разделителя времени ( 🙂 и литералов AM/ PM фактические отформатированные выходные данные, отображаемые системой, зависят от ее региональных параметров. В среде разработки для отображения времени и даты используется краткий формат времени и даты, заданный в региональных параметрах программы. При отображении в работающей программе применяется краткий формат даты и времени из региональных параметров системы, который может отличаться от региональных параметров программы. В этом примере используется локаль «Английский (США)».

MyTime и MyDate отображаются в среде разработки с использованием текущих системных параметров для краткого формата времени и даты.

Использование функции Format с пустыми строками

В Microsoft Access версии 2,0 и более ранних версиях можно использовать функцию Format , чтобы возвращать одно значение для пустая строка, а другое — для значения. Например, вы можете использовать выражение формата, например следующее, с функцией Format , чтобы вернуть соответствующее строковое значение из кода:

В Microsoft Access 97 и более поздних версиях необходимо отдельно проверить, не равно ли значение Null, а затем вернуть соответствующее значение с учетом результата. Например, можно использовать функцию IIf в выражении с функцией Format:

varX = IIf(IsNull(varStrX),»Null», Format(varStrX, «@;ZLS»))

Данное изменение применимо только тогда, когда функция Format используется для форматирования строк в зависимости от того, пустая ли это строка или строка со значением Null. Другие выражения форматирования, используемые с функцией Format, действуют так же, как в предыдущих версиях.

При преобразовании базы данных из Microsoft Access 2.0 или более ранней версии в Microsoft Access 2002 или более поздней версии необходимо изменить текст программы так, чтобы проверка для случая Null выполнялась отдельно.

Использование встроенных форматов для функции Format

Использование встроенных форматов для функции Format

В табл. 11.2 приводятся именованные встроенные форматы для данных различных типов в VBA. Используйте их как аргумент в функции Format. He забудьте при этом заключить в кавычки имя выбранного вами формата (из первого столбца таблицы).

Таблица 11.2. Именованные форматы для использования с функцией Format

Пример (соответствует установке языка Русский в панели управления)

Число без выделения разрядов тысяч

Число с выделением разрядов тысяч и с двумя знаками после десятичного разделителя, а также с символом денежной единицы

Как минимум один знак слева и два знака справа от десятичного разделителя (без выделения разрядов тысяч)

Число с выделением разрядов тысяч и как 1 минимум одним знаком слева и двумя знаками справа от десятичного разделителя

Число, умноженное на 100, с двумя знаками справа от десятичного разделителя и знаком процентов (%) справа

Число в стандартном виде для научных расчетов

Нет, если значение равно 0, иначе Да

Ложь, если значение равно 0, иначе Истина

Выкл, если значение равно 0, иначе Вкл

Дата и/или время, в зависимости от значения, представленного в соответствии с установками в панели управления

Дата в виде, заданном в панели управления для полного формата даты

Дата в виде, заданном в панели для среднего формата даты

Дата в виде, заданном в панели управления для краткого формата даты

Время (часы, минуты и секунды) в виде, заданном в панели управления для полного формата времени

Время (часы и минуты) в 12-часовом формате с метками до/после полудня, заданными в панели управления

Время (часы и минуты) в 24-часовом формате

Можно создать свои собственные форматы, собрав их из символов, имеющих специальные значения для аргумента формат. Например, чтобы отобразить строки текста символами верхнего или нижнего регистров, используйте функцию Format с аргументами «>» или «

MsgBox «Сейчас » & Format(Now, «h:nn») & _

«. Это время » & Iif(Format(Now, «a/p») = «a», _

«до обеда.», «после обеда.»)

Если вы выполните этот программный код до обеда, оператор отобразит на экране строку типа «Сейчас 9:07. Это время до обеда». После обеда вы получите нечто похожее на сообщение, показанное на рис. 11.1. (Кстати, функция MsgBox обсуждается ниже в этой же главе в разделе «Отображение окон сообщений».)

Рис. 11.1. Окно сообщения

Теперь объяснения. В первой функции Format аргументом формат является «h:nn».

Символ h соответствует стандарту отображения часа одной цифрой, когда имеется в виду время до 10:00. После двоеточия пп задает отображение минут с незначащим нулем, если прошло меньше 10 минут после начала часа.

Вторая из используемых функций Format вложена в функцию Iif. Ввиду того, что аргументом формат в данном случае является «а/р», возвращаемыми значениями будут просто либо «а», либо «р», в зависимости от того, превышает время 12 часов дня или нет. Обычно а/р используется как компонент более длинной строки аргумента формат, но в данном случае результат функции видеть не требуется, и он обрабатывается функцией Iif.

Функция Iif — это миниатюрная версия оператора If. Then (обсуждаемого в деталях в главе 32). Синтаксис этой функции следующий:

Iif (выражение, результатЕслиИстина, результатЕслиЛожь)

В переводе на «человеческий» язык функция работает следующим образом: если выражение есть истина, то функция возвращает значение результатЕслиИстина, если же выражение есть ложь, то возвращается значение результатЕслиЛожь.

В прелыдущем примере выражением для проверки было

Format(Now, «а/р») = «а»

Поэтому если функция Format возвращает значение а, данное выражение есть Истина.

Если же она возвращает любое другое значение, включая р, то выражение есть Ложь. Затем VBA вычисляет результат функции Iif на основе того, является ли значение выражения истиной или ложью. Если это значение Истина, функция Iif возвращает до обеда, а если Ложь, то после обеда.

В VBA 6 были добавлены несколько функций, подобных функции Format, — Format Number, Format DateTime, Format Currency и Format Percent — для работы сданными соответствующих типов. Подобно оригинальной функции Format, каждая из этих функций-наследниц возвращает строку с форматированным представлением исходного значения. Ввиду того, что эти новые функции по умолчанию возвращают результаты, нужные вам чаще всего, эти функции могут оказаться в использовании удобнее, чем сама функция Format. Вкратце их работу можно описать следующим образом.

* FormatNumber(число,ЧислоЗнаковПослеЗапятой, ОтображатьНезначащийНуль, ИспользоватьСкобкиДляОтрицательныхЧисел, ГруппироватьРазряды). Возвращает значение числа- единственного обязательного аргумента- в виде форматированной строки. Если опустить необязательные аргументы, функция FormatNumber отформатирует число на основе установок по умолчанию, заданных на вкладке Числа окна контрольной панели Язык и стандарты Windows. В предположении, что языком Windows выбран русский, вы получите число вида 132 328,55. Необязательные аргументы функции FormatNumber позволяют изменить заданные по умолчанию значения для числа знаков после запятой, отображения незначащего нуля перед запятой для чисел, меньших 1, представления отрицательных чисел в скобках и отделения групп разрядов одной от другой (например, пробелами).

* Format DateTime (дата, формат). Конвертирует значение даты в форматированную строку. Если аргумент формат не указан, строка форматируется в соответствии с системными установками для краткого формата даты и длинного формата времени, заданными на вкладках Дата и Время панели управления Язык и стандарты Windows. Здесь аргумент формат является числом (а не строкой, как в функции Format), но его можно задавать, используя именованные константы вида vbLongDate или vbShort Time.

* Format Currency (число). Возвращает значение числа, отформатированного как денежное, в соответствии с текущими установками в панели управления. В остальном функция Format Currency работает подобно функции FormatNumber и имеет те же необязательные аргументы.

Format Percent (число). Умножает число на 100 и добавляет знак процентов (например, 0,05 превращается в 5,00%). Функция Format Percent имеет те же необязательные аргументы, что и функция FormatNumber. Чтобы не выводить знаки дробной части, задайте аргумент ЧислоЗнаковПослеЗапятой равным 0 (например, Format Percent ( .05, 0)).

Как упоминалось в главе 9, VBA автоматически конвертирует данные одних типов в другие «на лету». Такие автоматические преобразования, конечно, удобны, да и получаемые при этом результаты обычно соответствуют желаемым. Правда, при этом нужно обязательно подчеркнуть слово обычно.

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

* быть уверенным, что VBA выполняет именно то преобразование, которое нужно;

* выполнить преобразования, которые не выполняются в VBA автоматически;

* сделать свой программный код яснее.

Для конвертирования данных в VBA предусмотрена целая группа функций — CBool, Cbyte, CCur и т.д. — своих для каждого из встроенных типов данных, за исключением Object. Например, в результате выполнения оператора boolMaybe = CBool (123) переменная bool Maybe будет содержать значение True (вообще любое число, не равное нулю, в результате даст True, наверное потому, что любое такое число ассоциируется с чем-то существующим).

Замечу, однако, что, кроме превращения самой операции конвертирования в явную, эти функции не делают ничего такого, что не может предложить VBA при автоматическом конвертировании. (Об особенностях функции СDec см. в главе 9.)

Функции Fix и intотбрасывают дробную часть любого заданного им числа, возвращая целое число. Но, в отличие от функций CInt и CLng, они не выполняют привычного округления — например, int(4 . 98 9) возвращает 4, а не 5.

Результаты этих двух функций отличаются только при обработке отрицательных чисел.

Тогда intвозвращает ближайшее к значению аргумента меньшее целое число, a Fix просто отбрасывает дробную часть аргумента.

Функции Hex и Oct преобразуют стандартные десятичные целые числа в строки, содержащие соответственно их шестнадцатеричные и восьмеричные эквиваленты.

Возможность явного конвертирования в обратном направлении в VBA не предусмотрена, но зато вы можете напечатать буквальные шестнадцатеричные или восьмеричные значения и позволить редактору Visual Basic превратить их в десятичные автоматически. При этом любой шестнадцатеричный или восьмеричный литерал должен предваряться специальным кодом: шестнадцатеричный – кодом &Н, а восьмеричный — кодом &О (это буква О, а не цифра 0). Например, в результате выполнения оператора intBas es = 10+&О12 + &НА значением переменной intBases будет число 30, как и должно быть (это очевидно).

Некоторые функции VBA конвертируют числа в строки. К таким функциям относятся следующие.

* CStr. Превращает данные любых типов (кроме типа Object ), включая числовые, в соответствующую им строку. Вывод форматируется в соответствии с установками заданными в панели управления Язык и стандарты Windows. Например, во Франции CStr ( 200.02) на выходе выдаст строку «200, 02». Здесь следует заметить, что хотя результирующая строка и соответствует региональным установкам, подаваемые на вход числовые данные должны быть представлены в формате, соответствующем английскому языку США.

* Str. Конвертирует число в строку, но всегда форматирует строку в соответствии со стандартом английского языка США с точкой в качестве десятичного разделителя.

* Функции конвертирования в числовые типы данных. Конвертируют строки в соответствующие числовые значения, но только тогда, когда все символы в строке распознаются как допустимые для чисел. В данном случае тоже все зависит от установок в панели управления. Например, в России CDbl («200, 02р. «) в результате дает 200, 02; в США тот же оператор порождает ошибку, но CDbl ( «$200 . 02» ) прекрасно работает.

* Val. Конвертирует числа в строках в числовые значения, останавливаясь там, где встречается первый символ, недопустимый для чисел. Независимо от установок панели управления, распознает только цифры и десятичную точку (а не запятую, например). Однако игнорирует пробелы, символы табуляции и переходы на новую строку.

Так, выражение Val («28 190.43 12 by 14 «) в результате даст 28190,4312.

Chr. Конвертирует числовой ANSI-код в соответствующий символ. Используйте эту функцию, когда в строку нужно поместить символ, который нельзя напечатать.

Asc. Примерно соответствует обратной к функции Chr — возвращает числовой код первого символа в строке.

The VBA Format Function

Description

The VBA Format function applies a specified format to an expression and returns the result as a string.

The syntax of the function is:

Where the function arguments are:

The expression that you want to format.

An optional argument specifying the format that is to be applied to the Expression .

This can be a user-defined format or one of the predefined named formats listed below:

Predefined Date Formats:

Expression	—
[Format]	—

Format	Description
General Date	—	Displays a date as defined in your system’s General Date setting. If a date only, no time is displayed; If a time only, no date is displayed.
Long Date	—	Displays a date as defined in your system’s Long Date settings.
Medium Date	—	Displays a date as defined in your system’s Medium Date settings.
Short Date	—	Displays a date as defined in your system’s Short Date settings.
Long Time	—	Displays a time as defined in your system’s Long Time settings.
Medium Time	—	Displays a time as defined in your system’s Medium Time settings.
Short Time	—	Displays a time as defined in your system’s Short Time settings.

Predefined Number Formats:

Format	Description
General Number	—	Displays a number as it is entered.
Currency	—	Displays a number with a currency symbol, using the thousand separator and decimal places, as defined in your system’s currency setting.
Euro	—	Displays a number as a currency, with the euro currency symbol.
Fixed	—	Displays at least one digit to the left of the decimal place and follows the system settings for the number of decimal places to the right of the decimal place.
Standard	—	Displays the thousand separator and follows the standard system settings for the number of digits displayed at either side of the decimal place.
Percent	—	Displays a number multiplied by 100 and followed by the percent symbol; The format follows the standard system settings for the number of digits displayed at either side of the decimal place.
Scientific	—	Displays a number using scientific notation.
Yes/No	—	Displays No if the number is equal to zero or Yes otherwise.
True/False	—	Displays False if the number is equal to zero or True otherwise.
On/Off	—	Displays Off if the number is equal to zero or On otherwise.

If the [Format] argument is omitted, the function uses the system ‘General’ format for the Expression data type.

[FirstDayOfWeek]—

An optional FirstDayOfWeek enumeration value, specifying the weekday that should be used as the first day of the week.

This can have any of the following values:

vbUseSystemDayOfWeek	—	The first day of the week is as specified in your system settings
vbSunday	—	Sunday
vbMonday	—	Monday
vbTuesday	—	Tuesday
vbWednesday	—	Wednesday
vbThursday	—	Thursday
vbFriday	—	Friday
vbSaturday	—	Saturday

If omitted, the [FirstDayOfWeek] argument uses the default value vbSunday.

[FirstWeekOfYear]—

An optional FirstWeekOfYear enumeration value, specifying the week that should be used as the first week of the year.

This can have any of the following values:

vbSystem	—	The first week of the year is as specified in your system settings
vbFirstJan1	—	The week in which Jan 1st occurs
vbFirstFourDays	—	The first week that contains at least four days in the new year
vbFirstFullWeek	—	The first full week in the new year

If omitted, the [FirstWeekOfYear] argument uses the default value vbFirstJan1.

VBA Format Function Examples

Example 1 — Format VBA Dates and Times

The following VBA code shows how the VBA Format function can be used to format the date and time 12/31/2015 12:00:00 in five different ways.

dt5 = Format( #12/31/2015 12:00:00 PM#, «dddd mm/dd/yyyy hh:mm:ss» )
‘ dt5 is now equal to the String «Thursday 12/31/2015 12:00:00».

Note that, in the above examples:

• In the first call to the Format function, the [Format] argument is omitted. Therefore, the current system default date/time format is used.
• The second and third calls to the Format function use the predefined formats «Long Date» and «Medium Time». These format definitions will vary, according to your system date/time settings.
• The last two calls to the Format function have been supplied with user-defined formats.

Example 2 — Format VBA Numbers and Currencies

In the following VBA code, the VBA Format function is used to format the numeric values 50000 and 0.88 in different ways.

str5 = Format( 0.88, «0.0» )
‘ str5 is now equal to the String «0.9».

Note that, in the above examples:

• In the first call to the Format function, the [Format] argument is omitted. Therefore the current system default number format is used.
• The second and third calls to the Format function use the predefined formats «Currency» and «Percentage». These format definitions may vary, according to your system settings.
• The last two calls to the Format function have been supplied with user-defined formats.

Example 3 — Format VBA Strings

The following example shows the VBA Format function used with two different format options for Strings.

str2 = Format( «123456789», «@@@-@@@-@@@» )
‘ str2 is now equal to the String «123-456-789».

In the above VBA code:

• In the first call to the Format function, the «>» character forces the supplied text to be formatted in upper case.
• In the second call to the Format function, the «@» character represents a displayed character and the «-» character is text to be inserted in the specified positions of the String.

VBA Format

Excel VBA Format Function

Format function in VBA is used to format the given values in the desired format, this function can be used for formatting dates or numbers or any trigonometrical values, this function has basically two mandatory arguments, one is the input which is taken in the form of a string and the second argument is the type of format we want to use for example if we use Format (.99,” Percent”) this will give us the result as 99%.

In VBA we need to use the function called “FORMAT” to apply to format to cells. Excel formatting is one of the important concepts to master. The common formatting techniques we all use in our daily workplace are “date format, time format, number formatting, and other important formatting codes”. In regular excel worksheet we just simply hit the format cell option and perform the formatting duty by applying the appropriate formatting code. However, in VBA this isn’t that straight forward like our worksheet technique.

Syntax

• Expression: This is nothing but the value we want to format. In VAB technicality it is called as Expression.
• [Format]: What is the format you want to apply to the expression you have selected? We have two kinds of formatting here, one is the user-defined format and the second one is the built-in format.
  Here we have VBA Date Formats, Number Formats, and Text Formats.
  VBA Date Formats has a short date, long date, medium date, and general date.
  Number Formats has Currency, Standard, Percentage, Scientific, Yes or No, True or False, and On or Off
• [First Day of the Week]: What is the first day of your week? We can select any day from the list. Below is the list of day and appropriate codes.

• [First Week of the Year]: What is the first week of the year? This specifies the week that should be used as the very first week of the year.

How to Use?

Ok, let’s apply this function practically to understand the functionality of FORMAT function. Assume you have the number 8072.56489 and you want to apply number formatting to it. Follow the below steps to apply number formatting to it.

Step 1: Start a macro and define the variable as “string” data type.

Code:

Step 2: Assign a value to k as our number i.e. 8072.56489

Code:

Step 3: Show “k” value in the VBA message box.

Code:

Step 4: If you run this macro we will get below result.

The result is as it is we assigned the value to variable “k”. But we need to apply some formatting to this number make it beautiful.

Step 5: Instead of directly assigning a value to “k” let’s use the FORMAT function.

Code:

Step 6: Now for Expression assign the number 8072.56489.

Code:

Step 7: In the formatting option we can either use built-in format or we can use our own formatting code. Now I will use built-in formatting style as “Standard”.

Code:

Step 8: Now run this code and see the result of the message box.

Ok, we have got comma (,) as thousand separators and decimal is rounded up to two digits only.

Like this, we can use many other built-in formatting styles to apply the formatting. Below are some of the codes I have applied.

#1 – Currency Format

Code:

Result:

#2 – Fixed Format

Code:

Result:

#3 – Percent Format

Code:

Result:

#4 – User-Defined Formats

Ok, now we will see some of the user-defined formats.

Code:

Result:

Code:

Result:

#5 – Date FORMAT

We have seen some of the important numbers of formatting techniques. Now we will how to use Excel VBA FORMAT function to format dates.

I have written code to show the result of the date through the variable.

Code:

When I run this code I would not get an accurate date rather result is pathetic.

In order to get the accurate dates, we need to assign the date format to it. First thing is we need to do is supply the date in double-quotes and apply the date format.

Code:

If run this code now, I will get a proper long date.

“Long Date” is a built-in format, similarly you can use “short date”, and “medium date” options.

Things to Remember

• The value returned by FORMAT function is the string.
• We can also use our own date, time, and number formatting codes like how we use in worksheet formatting.
• FORMAT is a VBA function and available only in VBA not in the worksheet.

Recommended Articles

This has been a guide to VBA Format Function. Here we learned how to use VBA Format Function for currency, fixed, percentage and date formatting along with some practical examples and downloadable excel template. Below are some useful excel articles related to VBA –