Foreversoft.ru

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

Vba excel time

Урок 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.
Читать еще:  Максимальное число в ячейке excel

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

VBA Time

Excel VBA Time

VBA TIME function returns the current time as output and can also be used as an input to different macro lines of code. This function does not have any parameter set for it and thus, by far has the simplest syntax as TIME word with empty parentheses which can be seen as below:

The syntax for VBA TIME function:

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

Time()

In fact, there is no need for parentheses as well, you can simply use the word TIME itself in your block of code which allows your system to return the current time. We also have a similar function called NOW() under Excel VBA which returns the current time along with the date. However, in this article, our entire focus would be on using TIME function and looking at different working examples of the same.

How to Use Excel VBA Time Function?

We will learn how to use a VBA Time function with few examples in excel.

VBA Time Function – Example #1

Current System Time Using TIME function:

Follow the below steps to display the current time of your system.

Step 1: Define a sub-procedure.

Code:

Step 2: Define a variable which will be useful to hold the value of time for you. Define it as String.

Code:

Note: The reason behind defining this variable as String is, the output of the TIME function is in a string.

Step 3: Use VBA TIME function to assign current time to the variable defined in the step above using an assignment operator.

Code:

Step 4: As I mentioned earlier, there is no need to add parentheses after TIME function as this is a volatile function. Having said that, the following code will give the equivalent output in comparison with the above code.

Code:

Step 5: Now, use a MsgBox function to display the current time using the display message box.

Code:

Step 6: Run this code by hitting F5 or Run button and see the output. You should get the output as shown in the image below.

Please note that the time reflecting in the image above is the time by which this code is run on the system. While trying it by yourself, your output may vary depending on the time you are working on this code.

VBA Time Function – Example #2

VBA TIME in combination with a Date function to return the current time with Today’s Date:

As it has been informed earlier in the introduction of this article, there is a function called NOW() which gives you current date as well as time in Microsoft Excel. See the screenshot given below.

However, in VBA we don’t have this function working. Therefore, in VBA we have to use a combination of VBA Date and VBA TIME functions to get the current date and current time. VBA Date will give a current date and VBA Time will give current time (which we already have seen in the 1 st example).

Let’s see step by step how we can achieve this.

Step 1: Insert a new module in your Excel VBA script.

Step 2: Define a new sub-procedure by giving a name to the macro.

Code:

Step 3: We wanted our output to be stored in cell A1 of our excel sheet. For that, start writing the code as below.

Code:

Step 4: Now, use a combination of VBA DATE and VBA TIME functions as shown below and assign it’s value to cell A1.

Code:

The DATE function will return the current date of system and TIME function will return the current time. The white space enclosed in double quotes (“ “) allows having a space between date and time in the final output. Two & (and) operators work as concatenating operators which concatenate DATE and TIME with space in it.

Step 5: Run the code by hitting F5 or Run button manually. You will see the output in cell A1 of your worksheet as blow:

Here, again please note that the time reflecting in cell A1 is date and time by which this code is being run by myself. Therefore, when you’ll run this code the outputs will be different.

We also can format the date and time values using NumberFormat function under VBA.

Step 6: Write the code mentioned below in your VBA script.

Читать еще:  Удалить звуковую дорожку из видео онлайн

Code:

Step 7: Hit F5 or Run button manually and you’ll see the output as below in cell A1 of your worksheet.

VBA Time Function – Example #3

VBA TIME function to track the opening time and date of a workbook:

Sometimes, there is one spreadsheet which we open frequently (Like Employee list of an organization) and make changes into it. We might be interested in knowing the date and time on which the changes have been made in the worksheet. VBA Time function can be used to track the date and time every time a workbook is opened. See the steps below to work on the same.

Step 1: Create a new worksheet in your Excel workbook and rename it as per your convenience. I will rename it as (Time_Track).

Step 2: Double click on ThisWorkbook under VBE (Visual Basics Editor).

Step 3: From object drop-down list select Workbook instead of General.

Step 4: You’ll see a new private sub-procedure will get defined under the name Workbook_Open() in VBE.

Code:

Step 5: Write down the below-mentioned code in this macro sub-procedure as shown in the image below:

Code:

Here a variable LB of type Long is defined to find out the cell next to last used cell where the output can be stored. Then, using a combination of VBA DATE and TIME functions, we have assigned a value under that cell.

Step 6: Run the code by hitting F5 or Run button manually and see the output under column A of a worksheet.

This code will store the value of Date and Time every time you open the worksheet Time_Track.

This is it from this article. Let’s wrap the things up by writing some things to remember.

Things to Remember

  • VBA TIME function stores output as a string in excel. However, we also can store it as Date format while defining a variable as Date instead of a string.
  • TIME function does not require any argument in it. It’s a volatile function.
  • Even it does not need parentheses to call the function. Only TIME is enough to get the current time.
  • TIME function by default stores time value in hh:mm:ss format (24 hours clock format). However, sometimes the output format depends on your system time format as well.

Recommended Articles

This has been a guide to VBA Time Function. Here we have discussed how to use Excel VBA Time Functions and also we learned the Combination of date and time as an alternative to Now() Function and tracks the workbook open records along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

All in One Software Development Bundle (600+ Courses, 50+ projects)

VBA Time Function

Excel VBA Time Function

VBA Time Function returns the current time, also the important thing to note is that this function has no arguments in it whatsoever, another important factor to remember is that this function returns the current system time. Using this function we can actually find the actual time taken by the line of codes to complete the process.

TIME is a kind of volatile function, it does not have any syntax to it.

We also have a similar function in excel – NOW () function which inserts both current time as well as the current date in the spreadsheet

TIME ()

We just need to enter the function, in fact, no need to parenthesis to enclose just TIME is enough to insert the current time. The result given by the TIME function is in the string.

How to Use TIME Function in VBA?

Let me show you the example of a simple TIME function. Follow the below steps to create code to use the TIME function.

Step 1: Create a macro.

Code:

Step 2: Declare a variable as String.

Code:

Step 3: Assign a value to this variable through the TIME function.

Code:

Step 4: Now show the result in the message box.

Code:

Run this code using F5 key or manually we will get the current time.

So, when I run this code time was 11.51.54 AM.

Alternative of Now() Function

Combination of Date & Time as an Alternative to NOW Function

As I told at the beginning of the article NOW can insert current date & time. However we can use two other functions as an alternative function to NOW function, those two functions are VBA DATE & VBA TIME functions.

VBA Date will return current date & Time will return current time, so this makes the NOW function. Below is a set of code which will insert the current date & time in cell A1.

Читать еще:  В excel пропала панель инструментов

Code:

This code will insert the current date & time in cell A1.

We can also apply format to these values using FORMAT function. The below code will format the date & time.

Code:

Now the result of this code is as follows.

Track Your Workbook Open Records using Time Function in VBA

Often times we need to know our workbook opening time-frequency. There is a situation where we open the workbook quite often and we make some changes. By tracking the workbook opening time & date we can track workbook opening time.

Create a new sheet and rename it as “Track Sheet”.

Step 1: Double click on This workbook from VBE Editor.

Step 2: Select the workbook from the object drop-down list.

Step 3: As soon as you select this option you can see new macro automatically created by itself in the name “Workbook_Open()”.

Step 4: Inside this macro, we will need to write a code to track the workbook opening date & time.

I have already written code and below is the code for you.

Code:

This will record your workbook opening times like the below one.

Recommended Articles

This has been a gu >. Below are some useful excel articles related to VBA –

VBA Time Function

Excel VBA Time Function

VBA Time Function returns the current time, also the important thing to note is that this function has no arguments in it whatsoever, another important factor to remember is that this function returns the current system time. Using this function we can actually find the actual time taken by the line of codes to complete the process.

TIME is a kind of volatile function, it does not have any syntax to it.

We also have a similar function in excel – NOW () function which inserts both current time as well as the current date in the spreadsheet

TIME ()

We just need to enter the function, in fact, no need to parenthesis to enclose just TIME is enough to insert the current time. The result given by the TIME function is in the string.

How to Use TIME Function in VBA?

Let me show you the example of a simple TIME function. Follow the below steps to create code to use the TIME function.

Step 1: Create a macro.

Code:

Step 2: Declare a variable as String.

Code:

Step 3: Assign a value to this variable through the TIME function.

Code:

Step 4: Now show the result in the message box.

Code:

Run this code using F5 key or manually we will get the current time.

So, when I run this code time was 11.51.54 AM.

Alternative of Now() Function

Combination of Date & Time as an Alternative to NOW Function

As I told at the beginning of the article NOW can insert current date & time. However we can use two other functions as an alternative function to NOW function, those two functions are VBA DATE & VBA TIME functions.

VBA Date will return current date & Time will return current time, so this makes the NOW function. Below is a set of code which will insert the current date & time in cell A1.

Code:

This code will insert the current date & time in cell A1.

We can also apply format to these values using FORMAT function. The below code will format the date & time.

Code:

Now the result of this code is as follows.

Track Your Workbook Open Records using Time Function in VBA

Often times we need to know our workbook opening time-frequency. There is a situation where we open the workbook quite often and we make some changes. By tracking the workbook opening time & date we can track workbook opening time.

Create a new sheet and rename it as “Track Sheet”.

Step 1: Double click on This workbook from VBE Editor.

Step 2: Select the workbook from the object drop-down list.

Step 3: As soon as you select this option you can see new macro automatically created by itself in the name “Workbook_Open()”.

Step 4: Inside this macro, we will need to write a code to track the workbook opening date & time.

I have already written code and below is the code for you.

Code:

This will record your workbook opening times like the below one.

Recommended Articles

This has been a gu >. Below are some useful excel articles related to VBA –

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