Содержание
- Цикл «Do Until» в Visual Basic
- Примеры чтения и записи в файл
- Exiting the Loop Early
- Nested For Loops in VB.Net
- Адресация ячеек в диапазоне
- The Next Loop: Loops Through a Set of Numbers
- Примечания
- Синтаксис
- How To Use For Each Loop in VB.Net
- В каком порядке работает цикл For Each?
- VBA обратный цикл For Loop с инструкцией STEP
- Кнопка – элемент управления формы
- Функция DateDiff
- Какую задачу хотите зациклить вы?
- The Next Loop: циклы через набор чисел
- Параметры
- Примечания
Цикл «Do Until» в Visual Basic
Цикл Do Until очень похож на цикл Do While: блок кода в теле цикла выполняется раз за разом до тех пор, пока заданное условие выполняется (результат условного выражения равен True). В следующей процедуре Sub при помощи цикла Do Until извлекаются значения из всех ячеек столбца A рабочего листа до тех пор, пока в столбце не встретится пустая ячейка:
iRow = 1 Do Until IsEmpty(Cells(iRow, 1)) 'Значение текущей ячейки сохраняется в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop
В приведённом выше примере условие IsEmpty(Cells(iRow, 1)) находится в начале конструкции Do Until, следовательно цикл будет выполнен хотя бы один раз, если первая взятая ячейка не пуста.
Однако, как было показано в примерах цикла Do While, в некоторых ситуациях нужно, чтобы цикл был выполнен хотя бы один раз, не зависимо от первоначального результата условного выражения. В таком случае условное выражение нужно поместить в конце цикла, вот так:
Do ... Loop Until IsEmpty(Cells(iRow, 1))
Примеры чтения и записи в файл
Пример 1
Открытие (или создание, если он не существует) текстового файла для чтения и записи и запись в него одной строки, состоящей из двух текстовых и одного числового значений. Файл с именем myFile1.txt будет создан в той же папке, где расположен файл Excel с кодом VBA.
1 |
SubTest1() Dimff AsInteger,ws AsObject ‘Получаем свободный номер для открываемого файла ff=FreeFile ‘Открываем (или создаем) файл для чтения и записи Open ThisWorkbook.Path&»\myFile1.txt»ForOutput Asff ‘Записываем в файл одну строку Write#ff,»Дает корова молоко!»,_ «Куда идет король?»,25.35847 ‘Закрываем файл Close ff ‘Открываем файл для просмотра Setws=CreateObject(«WScript.Shell») ws.Run ThisWorkbook.Path&»\myFile1.txt» Setws=Nothing EndSub |
Строки и число можно предварительно присвоить переменным, объявленным с соответствующими типами данных, и использовать их для записи данных в файл (в строках кода с оператором Write #, как в этом и следующем примерах).
Пример 2
Открытие (или создание, если он не существует) файла без расширения для чтения и записи и запись в него трех строк: двух текстовых и одной в числовом формате. Файл с именем myFile2 будет создан в той же папке, где расположен файл Excel с кодом VBA.
Так как у файла нет расширения, Windows выведет диалоговое окно для выбора открывающей его программы. Выберите любой текстовый редактор или интернет-браузер.
1 |
SubTest2() Dimff AsInteger,ws AsObject ‘Получаем свободный номер для открываемого файла ff=FreeFile ‘Открываем (или создаем) файл для чтения и записи Open ThisWorkbook.Path&»\myFile2″ForOutput Asff ‘Записываем в файл три строки Write#ff,»Дает корова молоко!» Write#ff,»Куда идет король?» Write#ff,25.35847 ‘Закрываем файл Close ff ‘Открываем файл для просмотра Setws=CreateObject(«WScript.Shell») ws.Run ThisWorkbook.Path&»\myFile2″ Setws=Nothing EndSub |
Пример 3
Считываем строку, разделенную на отдельные элементы, из файла myFile1.txt и записываем в три переменные, по типу данных соответствующие элементам.
1 |
SubTest3() Dimff AsInteger,str1 AsString,_ str2 AsString,num1 AsSingle ‘Получаем свободный номер для открываемого файла ff=FreeFile ‘Открываем файл myFile1.txt для чтения Open ThisWorkbook.Path&»\myFile1.txt»ForInput Asff ‘Считываем строку из файла и записываем в переменные Input#ff,str1,str2,num1 Close ff ‘Смотрим, что записалось в переменные MsgBox»str1 = «&str1&vbNewLine_ &»str2 = «&str2&vbNewLine_ &»num1 = «&num1 EndSub |
Попробуйте заменить в этом примере строку сначала на строку , затем на строку , чтобы наглядно увидеть разницу между операторами Input # и Line Input #.
В следующих примерах (4 и 5) замена оператора Input # на Line Input # не приведет ни к каким изменениям, так как данные в строках файла myFile2 не разделены на элементы (поля).
Пример 4
Считываем поочередно три строки из файла myFile2 и записываем в три элемента массива, объявленного как Variant, так как в этот файл ранее были записаны две строки с текстом и одна с числом.
1 |
SubTest4() Dimff AsInteger,a(2)AsVariant,iAsByte ‘Получаем свободный номер для открываемого файла ff=FreeFile ‘Открываем файл myFile2 для чтения Open ThisWorkbook.Path&»\myFile2″ForInput Asff ‘Считываем строки из файла и записываем в элементы массива Fori=To2 Input#ff,a(i) Next Close ff ‘Смотрим, что записалось в элементы массива MsgBox»a(0) = «&a()&vbNewLine_ &»a(1) = «&a(1)&vbNewLine_ &»a(2) = «&a(2) EndSub |
Пример 5
Считываем с помощью цикла Do While… Loop все строки из файла myFile2 и записываем построчно в переменную, объявленную как String (число из третьей строки запишется как текст). Для остановки цикла при достижении конца файла используем функцию EOF.
1 |
SubTest5() Dimff AsInteger,aAsVariant,bAsString ‘Получаем свободный номер для открываемого файла ff=FreeFile ‘Открываем файл myFile2 для чтения Open ThisWorkbook.Path&»\myFile2″ForInput Asff ‘Считываем строки из файла и записываем в элементы массива DoWhileNotEOF(ff) Input#ff,a b=b&a&vbNewLine Loop Close ff ‘Смотрим, что записалось в переменную MsgBoxb EndSub |
Смотрите, как создавать и открывать текстовые файлы с помощью методов CreateTextFile и OpenTextFile. Чтение файла, запись и добавление информации с помощью объекта TextStream.
Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.
Exiting the Loop Early
Typically the loop will iterate through all items in the collection, then continue on to the next line of code below the Next line. However, we can stop the loop early with an Exit For statement.
Exit For
The following macro uses the Exit For statement to exit the loop after the first sheet that starts with the word “Report” is found an unhidden.
Sub Unhide_First_Sheet_Exit_For() 'Unhides the first sheet that contain a specific phrase 'in the sheet name, then exits the loop. Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets 'Find the sheet that starts with the word "Report" If Left(ws.Name, 6) = "Report" Then ws.Visible = xlSheetVisible 'Exit the loop after the first sheet is found Exit For End If Next ws End Sub
The ws variable retains the reference to the worksheet after the loop is exited early, and can be used again in the code below the loop.
Nested For Loops in VB.Net
The For Each loop can be nested. This will occurs when we put one For Each loop inside another For Each loop. Let us demonstrate this using an example.
Step 1) Create a new console application.
Step 2) Use the following code:
Module Module1 Sub Main() Dim nums() As Integer = {12, 23, 35} Dim names() As String = {"Guru99", "alice", "antony"} For Each n As Integer In nums For Each st As String In names Console.Write(n.ToString & st & " ") Next Next Console.ReadKey() End Sub End Module
Step 3) Click the Start button from the top bar to execute the code. You should get the following output:
Here is a screenshot of the code:
Explanation of code:
- Creating a module named Module1.
- Starting the main sub-procedure.
- Creating an array named nums with a set of integers.
- Creating an array named names with a set of names.
- Creating a variable n and using it to iterate over the items contained in the array named nums. This is the outer For Each loop.
- Creating a variable named st and using it to iterate over the items contained in the array named names. This is the inner For Each loop.
- Combining the items from the two arrays. Each item in the nums array will be combined with each item in the names array. The ToString function helps us convert the numbers read from nums array into strings. The ” ” helps us create a space after each combination. The combination has been achieved using the & (ampersand).
- End of the inner For Each loop.
- End of the outer For Each loop.
- Pause the console window waiting for the user to take action to close it.
- End of the main subprocedure.
- End of the module.
Адресация ячеек в диапазоне
К ячейкам присвоенного диапазона можно обращаться по их индексам, а также по индексам строк и столбцов, на пересечении которых они находятся.
Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:
1 | 2 | 3 | 4 | 5 |
6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 |
16 | 17 | 18 | 19 | 20 |
21 | 22 | 23 | 24 | 25 |
Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:
1 |
‘обращение по индексам строки и столбца myRange.Cells(2,4) ‘обращение по индексу ячейки myRange.Cells(9) |
Обращаться в переменной диапазона можно не только к отдельным ячейкам, но и к части диапазона (поддиапазону), присвоенного переменной, например,
обращение к первой строке присвоенного диапазона размерностью 5х5:
1 |
myRange.Range(«A1:E1») ‘или myRange.Range(Cells(1,1),Cells(1,5)) |
и обращение к первому столбцу присвоенного диапазона размерностью 5х5:
1 |
myRange.Range(«A1:A5») ‘или myRange.Range(Cells(1,1),Cells(5,1)) |
The Next Loop: Loops Through a Set of Numbers
We can also use the For Next Loop to loop through a set of numbers. This can be useful when we are looping through userform controls, arrays, or if we want to loop through a collection backwards.
The basic operation of the For Next Loop is the same as the For Each Loop. The difference is the format of the For line.
Step 1 – Declare a Variable for a Number
To loop through a set of numbers we first need to declare a variable to a whole number data type. We can use Integer or Long integer.
Dim i As Long
The variable is referred to as the Counter because it increments or counts up/down for each iteration in the loop.
A side note on Long: The Long (integer) data type holds a bigger number than Integer. It takes up more memory, but today’s computer have so much memory that it’s no longer a problem. We can use Long variables all the time. The letter L looks like the number 1 in VBA, so I’m now using i as the variable name even though I use Long as the data type. This is all a matter of personal preference and you can name the variable whatever you want.
Step 2 – Write the For Statement
Next we write the For statement. The basic construct is the keyword For, followed by the variable name (counter), then equals sign, start value To end value.
For i = 1 To 10
The start and end values can be referenced as numbers, or we can use integer/long variables in their place.
For i = iStart To iEnd
We can also use properties of objects that return a number.
For i = 1 To ActiveWorkbook.Worksheets.Count
That line of code would return the number of sheets in the active workbook. However, it is NOT looping through each worksheet. The loop is just looping through a set of numbers. We have to create a reference to a worksheet with the counter variable (i) as the index number of the Worksheets property. Step 3 shows this reference.
Step 3 – Add Code that Repeats for Each Iteration
The rest of the loop functions the same as the For Each loop. We can add lines between the For and Next lines that will run for each iteration of the loop. The counter variable can be used multiple times in these lines of code.
Worksheets(i).Visible = True
Step 4 – The Next Line Increments the Number & Loops Back
Finally, we add the Next line at the bottom.
Next i
When the macro runs it will set the variable equal to the first number in the For line. When the macro hits the Next line, it will add 1 to the value of the variable, or count up. So, i = 2 in the second iteration of the loop. It continues to loop until the last number in the loop is reached.
By default, 1 is added to the variable counter for each iteration in the loop. This is called the Step Value, and we can control the value of each step in the counter. The Step value is added to the end of the For line. The following line will add 2 to the counter for each iteration in the loop.
For i = 2 To 20 Step 2
If you wanted to shade every other row in a sheet, you might use a loop like this.
Looping Backwards
We can also use the Step Value to loop backwards by specifying a negative number.
For i = 100 To 1 Step -1
Notice that the Start Value is now the larger number and the End Value is the smaller number. The loops starts at 100 (Start Value) and subtracts 1 from the counter variable (Step -1) for each iteration in the loop until it gets to 1 (End Value).
The Step keyword is optional. If you do not specify it then VBA assumes a Step value of 1.
Looping backwards is great if you are deleting items. I will write a separate post on this, but the general idea is that when we are looping through a collection and deleting items, the size of the collection gets smaller as items are deleted. The loop will typically hit an error once it gets to the 10th item, when there are now only 9 items in the collection. Looping backwards prevents this potential error.
Примечания
Данные, считываемые с помощью Get, обычно пишутся в файл с put. Первая запись или байт в файле находятся на позиции 1, вторая запись или байт — на позиции 2 и т. д. Если вы не закроете рекнумбер, будет прочитана следующая запись или побистка после последнего утверждения Get or Put указано на последнюю функцию Seek). Необходимо добавить разделяющие запятые, например:
Для файлов, открытых в случайном режиме, применяются следующие правила:
-
Если длина считываемой информации меньше длины, указанной в пункте Len в заявлении Open, ознакомьтесь с последующими записями на границах с записью. Промежуток между окончанием одной записи и началом следующей заполняется содержимым файлового буфера. Так как количество заполняющих данных невозможно определить точно, рекомендуется использовать длину записи, совпадающую с длиной считываемых данных.
-
Если считываемая переменная является строкой переменной длины, оператор Get считывает 2-байтовый дескриптор, содержащий длину строки, а затем считывает данные, попадающие в переменную. Таким образом, длина записи, указанная инструкцией Len оператора Open, должна быть как минимум на 2 байта больше фактической длины строки.
-
Если переменная, в которую считываются данные, является (вариантом) , оператор Get считывает 2 байта, определяющие VarType типа Variant, а затем данные, попадающие в переменную. Например, при считывании данных типа Variant, принадлежащих к VarType 3, оператор Get считывает 6 байт: 2 байта, определяющие тип Variant как VarType 3 (Long), и 4 байта, содержащие данные типа . Длина записи, указанная инструкцией Len оператора Open, должна быть как минимум на 2 байта больше фактического количества байт, необходимых для хранения переменной.
Примечание
Вы можете использовать заявление Get для чтения массива Variant с диска, но вы не можете использовать Get для чтения scalar Variant, содержащего массив. Оператор Get также можно использовать для чтения объектов с диска.
-
Если переменная, в которую считываются данные, имеет тип Variant, принадлежащий к VarType 8 (String), оператор Get считывает 2 байта, определяющие VarType, 2 байта, содержащие длину строки, а затем считывает данные строки. Длина записи, указанная предложением Len оператора Open, должна быть как минимум на 4 байта больше фактической длины строки.
-
Если данные считываются в динамический массив, оператор Get считывает дескриптор, длина которого равна 2 плюс 8-кратное число измерений, то есть 2 + 8 * NumberOfDimensions. Длина записи, указанная предложением Len оператора Open, должна быть больше или равна сумме всех байтов, необходимых для чтения данных и дескриптора массива. Например, для записи представленного ниже массива на диск необходимо 118 байт.
118 bytes распределены следующим образом: 18 bytes для дескриптора (), и 100 bytes для данных ( ).
-
Если данные считываются в массив фиксированной длины, оператор Get считывает только данные. Дескриптор не считывается.
-
Если данные считываются в переменную любого другого типа (кроме строк переменной длины и типа Variant), оператор Get считывает только данные из переменной. Длина записи, указанная инструкцией Len оператора Open, должна быть больше или равна длине считываемых данных.
-
Оператор Get считывает элементы , так же как и при их отдельном считывании, но без заполняющих данных между элементами. На диске динамический массив в пользовательском типе (записанном с помощью оператора Put) предваряется дескриптором, длина которого составляет 2 плюс 8-кратное число измерений, то есть 2 + 8 * NumberOfDimensions. Длина записи, указанная с помощью инструкции Len оператора Open, должна быть больше или равна сумме всех байтов, необходимых для считывания отдельных элементов, включая массивы и их дескрипторы.
Для файлов, открытых в двоичном режиме, применяются все правила случайного режима, кроме следующих случаев:
-
Предложение Len оператора Open не влияет на считывание. Оператор Get считывает все переменные с диска непрерывно, то есть без заполняющих данных между элементами.
-
Для всех типов массивов, кроме массивов в пользовательском типе, оператор Get считывает только данные. Дескриптор не считывается.
-
Оператор Get считывает строки переменной длины, которые не являются элементами пользовательских типов, не учитывая 2-байтовый дескриптор. Число считываемых байтов равно числу символов, уже находящихся в строке. Например, приведенный ниже оператор считывает 10 байтов из 1:
Синтаксис
Имя функции
End Function
Синтаксис оператора Function содержит такие части:
Part | Описание |
---|---|
Public | Необязательное. Указывает на то, что процедура Function доступна для всех других процедур во всех . При использовании в модуле, который содержит Option Private, процедура недоступна вне . |
Private | Необязательное. Указывает на то, что процедура Function доступна только для других процедур в том модуле, в котором была объявлена. |
Friend | Необязательное. Используется только в . Указывает на то, что процедура Function видима повсюду в проекте, но невидима для контроллера экземпляра объекта. |
Static | Необязательное. Указывает на то, что локальные процедуры Function сохраняются между вызовами. Атрибут Static не влияет на переменные, которые были объявлены вне Function, даже если они используются в процедуре. |
name | Обязательный. Название Function; соответствует стандарту соглашений об именовании переменных. |
arglist | Необязательное. Список переменных, представляющих аргументы, которые передаются в процедуру Function при вызове. В качестве разделителя переменных используется запятая. |
type | Необязательное. значения, возвращаемого процедурой Function; может быть , , , , (не поддерживается в настоящее время), , (за исключением фиксированной длины), , , или любого пользователя определенного . |
Операторы | Необязательное. Любая группа операторов, которая будет выполняться в процедуре Function. |
выражение | Необязательное. Возвращаемое значение Function. |
Аргумент arglist имеет следующий синтаксис и элементы:
varname
Part | Описание |
---|---|
Необязательное | Необязательное. Указывает, что аргумент не является обязательным. При использовании все последующие аргументы в arglist также должны быть необязательными и объявляться с помощью ключевого слова Необязательный. Optional не может использоваться для каких-либо аргументов, если используется ParamArray. |
ByVal | Необязательное. Указывает, что аргумент передается . |
ByRef | Необязательное. Указывает, что аргумент передается . ByRef является значением по умолчанию в Visual Basic. |
ParamArray | Необязательное. Используется как последний аргумент в arglist, чтобы указать, что последний аргумент является массивом Optional элементов Variant. Ключевое слово ParamArray позволяет предоставлять произвольное число аргументов. Не может использоваться с аргументами ByVal, ByRef или Optional. |
varname | Обязательный. Имя переменной, представляющее аргумент; соответствует стандарту соглашений об именовании переменных. |
type | Необязательное. Тип данных аргумента, переданного процедуре; может быть Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (в настоящее время не поддерживается) Дата, Строка (переменная длина), Объект, Вариант, или определенный тип . Если параметр объявлен без ключевого слова Optional, можно также указать тип, определяемый пользователем. |
defaultvalue | Необязательное. Любая или константное выражение. Действительно только для параметров Optional. Если типом является Object, явным значением по умолчанию может быть только Nothing. |
How To Use For Each Loop in VB.Net
In the following example shows how to use For Each Loop In VB.Net
Step 1) Create a new console application
Begin by creating a new console application.
Step 2) Use the following code
Use the following code to learn For Each Loop In VB.Net
Module Module1 Sub Main() Dim myArray() As Integer = {10, 3, 12, 23, 9} Dim item As Integer For Each item In myArray Console.WriteLine(item) Next Console.ReadKey() End Sub End Module
Step 3) Click the Start button
Click on the Start button from the top bar to run the code. You should get the following result:
Here is a screenshot of the code:
Explanation of Code:
- Creating a module named Module1.
- Starting the main sub-procedure.
- Creating an array named myArray with a set of 4 integers.
- Creating an integer variable named item.
- Using the item variable to iterate over the items of the array named myArray.
- Printing the items found in the array by the above statement on the console.
- End of the For Each loop.
- Pausing the console window for a while waiting for a user to take action to close the window.
- End of the main sub-procedure.
- End of the module.
В каком порядке работает цикл For Each?
Цикл For Each Loop всегда начинается с первого элемента в коллекции и переходит к последнему элементу в том порядке, в котором они отображаются в Excel. Это основано на номере индекса предметов в коллекции. Вот несколько примеров порядка выполнения цикла для общих объектов.
- Рабочие листы. Начинается с первой вкладки листа в рабочей книге и возвращается к последней в том порядке, в котором вкладки отображаются в рабочей книге.
- Рабочие книги. Начинается с первой открытой книги и циклически повторяется в порядке открытия рабочих книг. Рабочим книгам присваивается порядковый номер по мере их открытия.
- Клетки: циклы слева направо, затем вниз. Начинается с первой ячейки в диапазоне и возвращается к следующему столбцу в той же строке, затем переходит к следующей строке.
- Таблицы и сводные таблицы. Начинается с первого объекта, созданного на листе, и циклически повторяется в порядке создания объектов. Этот же принцип действует и для других объектов, которые вы создаете на листах, таких как фигуры, диаграммы, слайсеры и т.д.
VBA обратный цикл For Loop с инструкцией STEP
Если у вас появилась необходимость перемещаться от большего значения к меньшему – вы можете использовать цикл в обратном направлении. Вот пример обратного цикла:
123456 |
Sub пример_цикла2()For счетчик = 10 to 1 Step -1 j = счетчикNext счетчик msgbox «Значение счетчика на последнем витке равно » & счетчикEnd Sub |
Последнее значение переменной счетчик будет равным 1.
Как вы могли заметить, мы можем использовать инструкцию Step nдля работы цикла как вперед, так и в обратном направлении. По умолчанию значение Stepравно 1, но оно может быть изменено, если необходимо пропускать какие-либо значения, тогда значение n будет больше одного, или перемещаться в обратном направлении, тогда n будет отрицательным.
Кнопка – элемент управления формы
Вставка кнопки на лист
- Выберите вкладку «Разработчик» и нажмите на кнопку «Вставить».
- Нажмите на значок кнопки в коллекции «Элементы управления формы».
- Кликните в любом месте на рабочем листе Excel.
- Откроется окно «Назначить макрос объекту». Нажмите «Отмена», так как макрос для этой кнопки еще не готов.
- После нажатия кнопки «Отмена», на рабочем листе появится новая кнопка из коллекции «Элементы управления формы» в режиме редактирования.
Ухватив мышкой за один из кружочков, можно изменить размер кнопки. Ухватив кнопку за границу, можно перетащить ее в другое место. Также, в режиме редактирования, можно изменить название кнопки прямо на ее поверхности.
Чтобы выйти из режима редактирования кнопки из коллекции «Элементы управления формы», кликните в любом месте на рабочем листе.
Чтобы вернуться в режим редактирования кнопки, кликните по ней правой кнопкой мыши и выберите из контекстного меню нужный пункт. Если вы хотите изменить размер или размещение кнопки перетаскиванием, кликните левой кнопкой мыши в любом месте рабочего листа. После первого клика контекстное меню закроется, а кнопка останется в режиме редактирования.
Создание процедуры для кнопки
Кнопке из коллекции «Элементы управления формы» можно назначить макрос (процедуру), размещенную в стандартном программном модуле.
Создайте или откройте файл Excel с расширением .xlsm (Книга Excel с поддержкой макросов) и перейдите в редактор VBA, нажав сочетание клавиш «Левая_клавиша_Alt+F11».
Если вы не создавали ранее в этом проекте VBA стандартный программный модуль, нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку (после значка Excel) на панели инструментов.
Ссылка на модуль появится в проводнике слева. Если модуль создан ранее, дважды кликните по его ссылке в проводнике, и он откроется справа для редактирования.
Нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.
В открывшемся окне добавления шаблона процедуры оставьте выбранным переключатель «Sub», вставьте в поле «Name» название процедуры «NovayaProtsedura» и нажмите «OK».
В стандартный программный модуль будет вставлен шаблон процедуры «NovayaProtsedura».
Вставьте внутрь шаблона процедуры следующий код:
1 |
‘Записываем в ячейку A1 число 44 Cells(1,1)=44 ‘Записываем в ячейку B1 число 56 Cells(1,2)=56 ‘Записываем в ячейку C1 формулу, которая Cells(1,3)=»=A1+B1″ |
На этом процедура (подпрограмма, макрос) для кнопки готова.
Назначение макроса кнопке
Кликните правой кнопкой мыши по кнопке на рабочем листе и в контекстном меню выберите строку «Назначить макрос…», откроется окно «Назначить макрос объекту».
Выберите в списке процедуру «NovayaProtsedura» и нажмите «OK». Кликните левой кнопкой мыши по рабочему листу, чтобы командная кнопка вышла из режима редактирования.
Теперь можете нажать созданную кнопку из коллекции «Элементы управления формы» для проверки ее работоспособности.
Функция DateDiff
DateDiff – это функция, которая возвращает количество указанных интервалов времени между двумя датами. Тип возвращаемого значения – Variant/Long.
Синтаксис
1 | DateDiff(interval,date1,date2,firstdayofweek,firstweekofyear) |
Параметры
Параметр | Описание |
---|---|
interval | Обязательный параметр. Строковое выражение из спецсимволов, представляющее интервал времени, количество которых (интервалов) требуется вычислить между двумя датами. |
date1, date2 | Обязательные параметры. Значения типа , представляющие две даты, между которыми вычисляется количество указанных интервалов. |
firstdayofweek | Необязательный параметр. Константа, задающая первый день недели. По умолчанию – воскресенье. |
firstweekofyear | Необязательный параметр. Константа, задающая первую неделю года. По умолчанию – неделя, в которую входит 1 января. |
Таблицу аргументов (значений) параметра смотрите в параграфе «Приложение 1».
Примечание к таблице аргументов: в отличие от функции , в функции спецсимвол , как и , обозначает неделю. Но расчет осуществляется по разному. Подробнее об этом на сайте разработчиков.
Параметры и определяют правила расчета количества недель между датами.
Таблицы констант из коллекций и смотрите в параграфах «Приложение 2» и «Приложение 3».
Пример
1 |
SubPrimerDateDiff() ‘Даже если между датами соседних лет разница 1 день, MsgBox DateDiff(«y»,»31.12.2020″,»01.01.2021″)’Результат: 1 год MsgBox DateDiff(«d»,»31.12.2020″,»01.01.2021″)’Результат: 1 день MsgBox DateDiff(«n»,»31.12.2020″,»01.01.2021″)’Результат: 1440 минут MsgBox»Полных лет с начала века = «&DateDiff(«y»,»2000″,Year(Now)-1) EndSub |
Какую задачу хотите зациклить вы?
Я надеюсь, что эта статья поможет вам начать работу с циклами. Не забудьте скачать бесплатный файл Excel, содержащий примеры кода.
For Next Loop VBA Macro Examples.xlsm (79.0 KB)
Циклы, безусловно, являются промежуточной техникой кодирования, которая вынуждает нас выйти за рамки MacroRecorder. К сожалению, записанный макрос не может создавать петли. Тем не менее, это навык, который вы сможете использовать снова и снова на протяжении всей своей карьеры для автоматизации простых и сложных задач. Понимание того, как использовать циклы, даст вам волшебную силу с Excel.
Пожалуйста, оставьте комментарий ниже с задачей, которую вы хотите автоматизировать с помощью цикла. Спасибо!
The Next Loop: циклы через набор чисел
Также можно использовать цикл For Next Loop для просмотра набора чисел. Это может быть полезно, когда мы перебираем элементы управления пользовательской формы, массивы или если мы хотим перебрать коллекцию в обратном направлении.
Основная операция цикла For Next такая же, как и для цикла
For Each. Разница заключается в формате строки For.
Шаг 1 — Объявление переменной для числа
Чтобы перебрать набор чисел, мы сначала должны объявить переменную для целого числа типа данных. Мы можем использовать Integer или Long integer.
Переменная называется счетчиком, потому что она увеличивается или уменьшается вверх/вниз для каждой итерации в цикле.
Дополнительное примечание к Long: тип данных Long (целое число) содержит большее число, чем Integer. Это занимает больше памяти, но для современного компьютера это не проблема. Мы можем использовать длинные переменные все время. Буква L выглядит как цифра 1 в VBA, поэтому я теперь использую i в качестве имени переменной, хотя я использую Long в качестве типа данных. Это все зависит от личных предпочтений, и вы можете назвать переменную как хотите.
Шаг 2 — Напишите строку For
Далее мы пишем строку For. Базовая конструкция — это ключевое слово For, за которым следует имя переменной (counter), затем знак равенства, начальное значение To end value.
Начальные и конечные значения могут быть указаны как числа, или мы можем использовать целочисленные / длинные переменные вместо них.
Эта строка кода возвращает количество листов в активной книге. Тем не менее, он не проходит по каждому листу. Цикл просто перебирает набор чисел. Мы должны создать ссылку на лист с переменной счетчика (i) в качестве номера индекса свойства Worksheets. Шаг 3 показывает эту ссылку.
Шаг 3 — Добавить код, который повторяется для каждой итерации
Остальная часть цикла работает так же, как цикл For Each. Мы можем добавить строки между строками For и Next, которые будут выполняться для каждой итерации цикла. Переменная counter может быть использована несколько раз в этих строках кода.
Шаг 4 — Строка Next закрывает цикл
Наконец, мы добавляем строку Next внизу.
Когда макрос запускается, он устанавливает переменную, равную первому числу в строке For. Когда макрос попадает в следующую строку, он добавляет 1 к значению переменной или увеличивает счет. Итак, я = 2 во второй итерации цикла. Он продолжает цикл, пока не будет достигнут последний номер в цикле.
По умолчанию 1 добавляется к счетчику переменных для каждой итерации в цикле. Это называется значением шага, и мы можем контролировать значение каждого шага в счетчике. Значение Step добавляется в конец строки For. Следующая строка добавит 2 к счетчику для каждой итерации в цикле.
Если вы хотите заштриховать все остальные строки на листе, вы можете использовать такой цикл.
Параметры
firstdayofweek имеет следующие параметры.
Константа | Значение | Описание |
---|---|---|
vbUseSystem | Использовать настройку API многоязыковой поддержки. | |
vbSunday | 1 | Воскресенье (по умолчанию) |
vbMonday | 2 | Понедельник |
vbTuesday | 3 | Вторник |
vbWednesday | 4 | Среда |
vbThursday | 5 | Четверг |
vbFriday | 6 | Пятница |
vbSaturday | 7 | Суббота |
firstweekofyear имеет следующие параметры.
Константа | Значение | Описание |
---|---|---|
vbUseSystem | Использовать настройку API многоязыковой поддержки. | |
vbFirstJan1 | 1 | Начать с недели, содержащей 1 января (по умолчанию). |
vbFirstFourDays | 2 | Начать с первой недели, в которой имеется по крайней мере четыре дня в данном году. |
vbFirstFullWeek | 3 | Начать с первой полной недели года. |
Примечания
Если явно не указано с помощью процедур Public, Private или Friend, процедуры Sub являются общедоступными по умолчанию.
Если не используется static, между вызовами не сохраняется значение локальных переменных.
Ключевое слово Friend может использоваться только в модулях классов. Однако доступ к процедурам Friend может осуществляться в любом модуле проекта. Процедура Friend не отображается в своего родительского класса; процедура Friend не может быть привязана позднее.
Процедуры Sub могут быть рекурсивными, то есть вызывать сами себя для выполнения определенной задачи. Однако рекурсия может привести к переполнению стека. Как правило, ключевое слово Static не используется с рекурсивными процедурами Sub.
Весь исполняемый код должен находиться в . Процедуру Sub нельзя объявлять внутри другой процедуры Sub, Function или Property.
Ключевые слова Exit Sub вызывают мгновенный выход из процедуры Sub. Выполнение программы продолжается с оператора, следующего за вызовом процедуры Sub. В процедуре Sub можно использовать сколько угодно операторов Exit Sub.
Как и процедура Function, процедура Sub является отдельной процедурой, которая может принимать аргументы, выполнять последовательность операторов и менять значения своих аргументов. Но в отличие от процедуры Function, которая возвращает значение, процедуру Sub нельзя использовать в выражениях.
Вы вызываем процедуру Sub, используя имя процедуры, а затем список аргументов. Сведения о том, как вызывать процедуры Sub, см. в заявлении Call.
Переменные, используемые в процедурах Sub, делятся на две категории: объявленные и не объявленные в явном виде внутри процедуры. Переменные, объявленные в явном виде внутри процедуры (с использованием инструкции Dim или ее аналогов) всегда являются локальными для процедуры. Переменные, которые используются, но не были явно объявлены в процедуре, также являются локальными, если они не были объявлены на более высоком уровне вне процедуры.
В процедуре могут использоваться переменные, не объявленные внутри нее в явном виде, однако если на объявлены переменные с такими же именами, может возникнуть конфликт имен. Если процедура ссылается на необъявленную переменную, имя которой совпадает с именем другой процедуры, предполагается, что она ссылается на имя из уровня модуля. Во избежание таких конфликтов рекомендуется объявлять переменные в явном виде. Также можно использовать оператор Option Explicit для принудительного объявления переменных в явном виде.
Примечание
Операторы GoSub, GoTo или Return нельзя использовать для входа в процедуру Sub и выхода из нее.

Эта тема закрыта для публикации ответов.