Sheets excel vba: Использование функций листов Excel в Visual Basic

Использование функций листов Excel в Visual Basic

• 
  11/13/2018
• Чтение занимает 2 мин

В этой статье

В операторах Visual Basic можно использовать большинство функций листов Microsoft Excel.You can use most Microsoft Excel worksheet functions in your Visual Basic statements. Список функций листов, которые можно использовать, см. в статье Список функций листов, доступных для Visual Basic.For a list of the worksheet functions you can use, see List of worksheet functions available to Visual Basic.

Примечание

Некоторые функции листов не используются в Visual Basic.Some worksheet functions are not useful in Visual Basic. Например, не требуется функция Concatenate, так как в Visual Basic можно использовать оператор & для объединения нескольких текстовых значений.For example, the Concatenate function is not needed because in Visual Basic you can use the & operator to join multiple text values.

Вызов функции листа из Visual BasicCalling a worksheet function from Visual Basic

В Visual Basic функции листов Excel доступны через объект WorksheetFunction.In Visual Basic, the Excel worksheet functions are available through the WorksheetFunction object.

В следующей процедуре Sub используется функция листа Min для определения наименьшего значения в диапазоне ячеек.The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. Сначала переменная myRange объявляется как объект Range, а затем ей присваивается диапазон A1:C10 на листе Sheet1. First, the variable myRange is declared as a Range object, and then it is set to range A1:C10 on Sheet1. Другой переменной, answer, назначается результат применения функции Min к myRange.Another variable, answer, is assigned the result of applying the Min function to myRange. В конце значение answer отображается в окне сообщения.Finally, the value of answer is displayed in a message box.

Sub UseFunction() 
 Dim myRange As Range 
 Set myRange = Worksheets("Sheet1").Range("A1:C10") 
 answer = Application.WorksheetFunction.Min(myRange) 
 MsgBox answer 
End Sub

Если вы используете функцию листа, для которой требуется ссылка на диапазон в качестве аргумента, необходимо указать объект Range.If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. Например, можно использовать функцию листа Match (ПОИСКПОЗ) для поиска диапазона ячеек.For example, you can use the Match worksheet function to search a range of cells. В ячейке листа потребовалось бы ввести формулу, например =ПОИСКПОЗ (9;A1:A10;0).In a worksheet cell, you would enter a formula such as =MATCH(9,A1:A10,0). Однако в процедуре Visual Basic необходимо указать объект Range, чтобы получить такой же результат.However, in a Visual Basic procedure, you would specify a Range object to get the same result.

Sub FindFirst() 
 myVar = Application.WorksheetFunction _ 
 .Match(9, Worksheets(1).Range("A1:A10"), 0) 
 MsgBox myVar 
End Sub

Примечание

Функции Visual Basic не используют квалификатор WorksheetFunction.Visual Basic functions do not use the WorksheetFunction qualifier. Функция может иметь такое же имя, что и функция Microsoft Excel, но работать по-другому. A function may have the same name as a Microsoft Excel function and yet work differently. Например, Application.WorksheetFunction.Log и Log возвращают разные значения.For example, Application.WorksheetFunction.Log and Log will return different values.

Вставка функции листа в ячейкуInserting a worksheet function into a cell

Чтобы вставить функцию листа в ячейку, укажите функцию в качестве значения свойства Formula соответствующего объекта Range.To insert a worksheet function into a cell, you specify the function as the value of the Formula property of the corresponding Range object. В следующем примере функция листа RAND (создающая случайное число), назначается свойству Formula диапазона A1:B3 на листе Sheet1 в активной книге.In the following example, the RAND worksheet function (which generates a random number) is assigned to the Formula property of range A1:B3 on Sheet1 in the active workbook.

Sub InsertFormula() 
 Worksheets("Sheet1").Range("A1:B3").Formula = "=RAND()" 
End Sub

ПримерExample

В этом примере используется функция листа Pmt, чтобы рассчитать кредитный платеж по ипотечной ссуде.This example uses the worksheet function Pmt to calculate a home mortgage loan payment. Обратите внимание, что в этом примере используется метод InputBox вместо функции InputBox, чтобы метод мог выполнять проверку типов.Notice that this example uses the InputBox method instead of the InputBox function so that the method can perform type checking. Операторы Static приводят к тому, что Visual Basic сохраняет значения трех переменных; они отображаются как значения по умолчанию при следующем запуске программы.The Static statements cause Visual Basic to retain the values of the three variables; these are displayed as default values the next time you run the program.

Static loanAmt 
Static loanInt 
Static loanTerm 
loanAmt = Application.InputBox _ 
 (Prompt:="Loan amount (100,000 for example)", _ 
 Default:=loanAmt, Type:=1) 
loanInt = Application.InputBox _ 
 (Prompt:="Annual interest rate (8.75 for example)", _ 
 Default:=loanInt, Type:=1) 
loanTerm = Application.InputBox _ 
 (Prompt:="Term in years (30 for example)", _ 
 Default:=loanTerm, Type:=1) 
payment = Application.WorksheetFunction _ 
 .Pmt(loanInt / 1200, loanTerm * 12, loanAmt) 
MsgBox "Monthly payment is " & Format(payment, "Currency")

См. такжеSee also

Поддержка и обратная связьSupport and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи?Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Свойства и методы ActiveWorkbook в Excel подробная статья

Автор Дмитрий Якушев На чтение 19 мин. Просмотров 3.8k.

John Naisbitt

Мы тонем в информации, но жаждем знаний

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

Если вы хотите использовать VBA для открытия рабочей книги, тогда откройте «Открыть рабочую книгу»

Если вы хотите использовать VBA для создания новой рабочей книги, перейдите к разделу «Создание новой рабочей книги».

Для всех
других задач VBA Workbook, ознакомьтесь с кратким руководством ниже.

Краткое руководство по книге VBA

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

Задача	Исполнение
Доступ к открытой книге с использованием имени	Workbooks(«Пример. xlsx»)
Доступ к открытой рабочей книге (открывшейся первой)	Workbooks(1)
Доступ к открытой рабочей книге (открывшейся последней)	Workbooks(Workbooks.Count)
Доступ к активной книге	ActiveWorkbook
Доступ к книге, содержащей код VBA	ThisWorkbook
Объявите переменную книги	Dim wk As Workbook
Назначьте переменную книги	Set wk = Workbooks(«Пример.xlsx») Set wk = ThisWorkbook Set wk = Workbooks(1)
Активировать книгу	wk.Activate
Закрыть книгу без сохранения	wk.Close SaveChanges:=False
Закройте книгу и сохраните	wk.Close SaveChanges:=True
Создать новую рабочую книгу	Set wk = Workbooks. Add
Открыть рабочую книгу	Set wk =Workbooks.Open («C:\Документы\Пример.xlsx»)
Открыть книгу только для чтения	Set wk = Workbooks.Open («C:\Документы\Пример.xlsx», ReadOnly:=True)
Проверьте существование книги	If Dir(«C:\Документы\Книга1.xlsx») = «» Then MsgBox «File does not exist.» EndIf
Проверьте открыта ли книга	Смотрите раздел «Проверить открыта ли книга»
Перечислите все открытые рабочие книги	For Each wk In Application.Workbooks     Debug.Print wk.FullName Next wk
Открыть книгу с помощью диалогового окна «Файл»	Смотрите раздел «Использование диалогового окна «Файл»
Сохранить книгу	wk.Save
Сохранить копию книги	wk. SaveCopyAs «C:\Копия.xlsm»
Скопируйте книгу, если она закрыта	FileCopy «C:\file1.xlsx»,»C:\Копия.xlsx»
Сохранить как Рабочая книга	wk.SaveAs «Резервная копия.xlsx»

Начало работы с книгой VBA

Мы можем получить доступ к любой открытой книге, используя код Workbooks («Пример.xlsm»). Просто замените Пример.xlsm именем книги, которую вы хотите использовать.

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

Sub ZapisVYacheikuA1()

'Записывает 100 в ячейку A1 листа "Лист1" в МойVBA.xlsm
Workbooks("МойVBA.xlsm").Worksheets("Лист1").Range("A1") = 100

End Sub

Этот пример может показаться немного запутанным для новичка, но на самом деле он довольно прост.

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

Sub ZapisiVYacheiku()

' Записывает 100 в ячейку A1 листа "Лист1" в МойVBA.xlsm
Workbooks("МойVBA.xlsm").Worksheets("Лист1").Range("A1") = 100

' Записывает «Иван» в ячейку B1 листа «Лист1» в МойVBA.xlsm
Workbooks("МойVBA.xlsm").Worksheets("Лист1").Range("B1") = "Иван"

' Записывает 100 в ячейку A1 таблицы «Счета» в МойVBA.xlsm
Workbooks("МойVBA.xlsm").Worksheets("Счета").Range("A1") = 100

' Записывает дату в ячейку D3 рабочего листа "Лист2" в Книга.xlsx
Workbooks("Книга.xlsx").Worksheets("Лист2").Range("D3") = "1\1\2019"

End Sub

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

Взгляните на часть книги

Workbooks("Пример.xlsx")

Ключевое слово Workbooks относится к совокупности всех открытых рабочих книг. Предоставление имени книги в коллекцию дает нам доступ к этой книге. Когда у нас есть объект, мы можем использовать его для выполнения задач с книгой.

 Устранение неполадок в коллекции книг

Когда вы используете коллекцию Workbooks для доступа к книге, вы можете получить сообщение об ошибке:

Run-time Error 9: Subscript out of Range.

Это означает, что VBA не может найти книгу, которую вы
передали в качестве параметра.

Это может произойти по следующим причинам:

1. Рабочая книга в настоящее время закрыта.
2. Вы написали имя неправильно.
3. Вы создали новую рабочую книгу (например, «Книга1») и попытались получить к ней доступ, используя Workbooks («Книга1.xlsx»). Это имя не Книга1.xlsx, пока оно не будет сохранено в первый раз.
4. (Только для Excel 2007/2010) Если вы используете два экземпляра Excel, то Workbooks () относится только к рабочим книгам, открытым в текущем экземпляре Excel.
5. Вы передали число в качестве индекса, и оно больше, чем количество открытых книг, например Вы использовали
   Workbooks (3), и только две рабочие книги открыты.

Если вы не можете устранить ошибку, воспользуйтесь любой из функций в разделе Поиск всех открытых рабочих книг. Они будут печатать имена всех открытых рабочих книг в «Immediate Window » (Ctrl + G).

Примеры использования рабочей книги VBA

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

Примечание. Чтобы попробовать этот пример, создайте две открытые книги с именами Тест1.xlsx и Тест2.xlsx.

Sub SvoistvaRabocheiKnigi()

    ' Печатает количество открытых рабочих книг
    Debug.Print Workbooks.Count

    ' Печатает полное название книги
    Debug.Print Workbooks("Тест1.xlsx").FullName

    ' Отображает полное имя книги в диалоговом окне сообщения.
    MsgBox Workbooks("Тест1.xlsx").FullName

    ' Печатает количество листов в Тест2.xlsx
    Debug.Print Workbooks("Тест2.xlsx").Worksheets.Count

    ' Печатает имя активного в данный момент листа Тест2.xlsx
    Debug. Print Workbooks("Тест2.xlsx").ActiveSheet.Name

    ' Закрывает книгу под названием Тест1.xlsx
    Workbooks("Tест1.xlsx").Close

    ' Закрывает книгу Тест2.xlsx и сохраняет изменения
    Workbooks("Tест2.xlsx").Close SaveChanges:=True

End Sub

Примечание: в примерах кода я часто использую Debug.Print. Эта функция печатает значения в Immediate Window. Для просмотра этого окна выберите View-> Immediate Window из меню (сочетание клавиш Ctrl + G)

Доступ к рабочей книге VBA по индексу

Вы также можете использовать индексный номер с книгами ().
Индекс относится к порядку, в котором книга была открыта или создана.

Workbooks (1) относится к книге, которая была открыта первой. Workbooks (2) относится к рабочей книге, которая была открыта второй и так далее.

' Первая рабочая книга, которая была открыта
Debug.Print Workbooks(1).Name

' Третья рабочая тетрадь, которая была открыта
Debug. Print Workbooks(3).Name

' Последняя рабочая книга, которая была открыта
Debug.Print Workbooks(Workbooks.Count).Name

В этом примере мы использовали Workbooks.Count. Это количество рабочих книг, которые в настоящее время находятся в коллекции рабочих книг. То есть количество рабочих книг, открытых на данный момент. Таким образом, использование его в качестве индекса дает нам последнюю книгу, которая была открыта

Использование индекса не очень полезно, если вам не нужно знать порядок. По этой причине вам следует избегать его использования. Вместо этого вы должны использовать имя рабочей книги вместе с Workbooks ().

Поиск всех открытых рабочих книг

Иногда вы можете получить доступ ко всем рабочим книгам, которые открыты. Другими словами, все элементы в коллекции Workbooks ().

Вы можете сделать это, используя цикл For Each.

Sub PechatImeniFailaEach()

    ' Распечатывает полное имя файла всех открытых рабочих книг
    Dim wrk As Workbook
    For Each wrk In Workbooks
        Debug. Print wrk.FullName
    Next wrk

End Sub

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

Sub PechatImeniFailaFor()

    ' Распечатывает полное имя файла всех открытых рабочих книг
    Dim i As Long
    For i = 1 To Workbooks.Count
        Debug.Print Workbooks(i).FullName
    Next i

End Sub

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

Примечание. Оба примера читаются в порядке с первого открытого до последнего открытого. Если вы хотите читать в обратном порядке (с последнего на первое), вы можете сделать это

Sub PechatImeniFailaForKon()

    ' Распечатывает полное имя файла всех открытых рабочих книг
    ' в обратном порядке.
    Dim i As Long
    For i = Workbooks.Count To 1 Step -1
        Debug.Print Workbooks(i).FullName
    Next i

End Sub

Открыть рабочую книгу

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

Следующий код VBA открывает книгу «Книга1.xlsm» в папке «C: \ Документы»

Sub OtkritKnigu()

    ' Откройте книгу и распечатайте количество листов в ней
    Workbooks.Open ("C:\Документы\Книга1.xlsm")

    Debug.Print Workbooks("Книга1.xlsm").Worksheets.Count

    ' Закройте книгу без сохранения
    Workbooks("Книга1.xlsm").Close saveChanges:=False

End Sub

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

Sub OtkritKniguDir()

    If Dir("C:\Документы\Книга1.xlsm") = "" Then
        ' Файл не существует - сообщение пользователю
        MsgBox "Could not open the workbook. Please check it exists"
    Else
        ' открыть книгу и сделать что-нибудь с ней
        Workbooks. Open("C:\Документы\Книга1.xlsm").Open
    End If

End Sub

Проверить открыта ли книга

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

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

Function GetWorkbook(ByVal sFullFilename As String) As Workbook
    
    Dim sFilename As String
    sFilename = Dir(sFullFilename)
    
    On Error Resume Next
    Dim wk As Workbook
    Set wk = Workbooks(sFilename)
    
    If wk Is Nothing Then
        Set wk = Workbooks.Open(sFullFilename)
    End If
    
    On Error Goto 0
    Set GetWorkbook = wk
    
End Function

Вы можете использовать эту функцию так:

Sub PrimerOtkritiyaKnigi()

    Dim sFilename As String
    sFilename = "C:\Документы\Книга2. xlsx"

    Dim wk As Workbook
    Set wk = GetWorkbook(sFilename)
    
End Sub

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

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

' Функция для проверки, если книга уже открыта
Function IsWorkBookOpen(strBookName As String) As Boolean
    
    Dim oBk As Workbook
    
    On Error Resume Next
    Set oBk = Workbooks(strBookName)
    On Error GoTo 0
    
    If Not oBk Is Nothing Then
        IsWorkBookOpen = True
    End If
    
End Function

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

Sub PrimerIsp()

    Dim sFilename As String
    sFilename = "C:\temp\writedata.xlsx"

    If IsWorkBookOpen(Dir(sFilename)) = True Then
        MsgBox "File is already open. Please close file and run macro again."
        Exit Sub
    End If
    
    ' Написать в книгу здесь
    
End Sub

Если вам нужно проверить, открыта ли книга в другом экземпляре Excel, вы можете использовать атрибут ReadOnly книги. Будет установлено значение true, если оно открыто в другом экземпляре.

Закрыть книгу

Закрыть книгу в Excel VBA очень просто. Вы просто вызываете метод Close рабочей книги.

wk.Close

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

Вы можете указать, сохранять ли книгу или нет, и тогда
сообщения Excel не будут появляться.

' Не сохранять изменения
wk.Close SaveChanges:= False

' Сохранить изменения
wk.Close SaveChanges:= True

Очевидно, что вы не можете сохранить изменения в книге,
которая в данный момент открыта только для чтения.

Сохранить книгу

Мы только что видели, что вы можете сохранить книгу, когда закроете ее. Если вы хотите сохранить его на любом другом этапе, вы можете просто использовать метод Save.

wk.Save

Вы также можете использовать метод SaveAs

wk.SaveAs "C:\Резервная копия\Счета.xlsx"

Метод WorkAs SaveAs поставляется с двенадцатью параметрами, которые позволяют вам добавить пароль, установить файл только для чтения и так далее. Вы можете увидеть детали здесь.

Вы также можете использовать VBA для сохранения книги в виде копии с помощью SaveCopyAs.

Копировать книгу

Если рабочая книга открыта, вы можете использовать два метода в приведенном выше разделе для создания копии, т. е. SaveAs и SaveCopyAs.

Если вы хотите скопировать книгу, не открывая ее, вы можете использовать FileCopy, как показано в следующем примере:

Sub KopirovatKnigu()
    FileCopy "C:\Документы\Документы.xlsm", "C:\Документы\Пример копии.xlsm"
End Sub

Использование диалогового окна «Файл» для открытия рабочей книги

В предыдущем разделе показано, как открыть книгу с заданным именем. Иногда вам может понадобиться, чтобы пользователь выбрал рабочую книгу. Вы можете легко использовать Windows File Dialog.

FileDialog настраивается, и вы можете использовать его так:

1. Выберите файл.
2. Выберите папку.
3. Откройте файл.
4. «Сохранить как» файл.

Если вы просто хотите, чтобы пользователь выбрал файл, вы можете использовать функцию GetOpenFilename.

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

Public Function UserSelectWorkbook() As String

    On Error Goto ErrorHandler

    Dim sWorkbookName As String

    Dim FD As FileDialog
    Set FD = Application.FileDialog(msoFileDialogFilePicker)

    ' Откройте диалоговое окно файла
    With FD
        ' Установить заголовок диалога
        .Title = "Please Select File"

        ' Добавить фильтр
        .Filters.Add "Excel Files", "*.xls;*.xlsx;*.xlsm"

        ' Разрешить выбор только одного файла
        .AllowMultiSelect = False

        ' Диалог дисплея
        .Show

        If .SelectedItems.Count > 0 Then
            UserSelectWorkbook = .SelectedItems(1)
        Else
            MsgBox "Selecting a file has been cancelled. "
            UserSelectWorkbook = ""
        End If
    End With

    ' Убирать
    Set FD = Nothing
Done:
    Exit Function
ErrorHandler:
    MsgBox "Error: " + Err. Description
End Function

Когда вы вызываете эту функцию, вы должны проверить, отменяет ли пользователь диалог.

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

Public Sub TestViborPolz()

    Dim userBook As Workbook, sFilename As String

    ' Вызовите функцию UserSelectworkbook
    sFilename = UserSelectWorkbook()

    ' Если имя файла возвращается пустым, пользователь отменил
    If sFilename <> "" Then
        ' Откройте книгу и сделайте что-нибудь с ней
        Set userBook = Workbooks.Open(sFilename)
    End If

End Sub

Вы можете настроить диалог, изменив Title, Filters и AllowMultiSelect в функции UserSelectWorkbook.

Использование ThisWorkbook

Существует более простой способ доступа к текущей книге, чем использование Workbooks() . Вы можете использовать ключевое слово ThisWorkbook. Это относится к текущей книге, то есть к книге, содержащей код VBA.

Если наш код находится в книге, называемой МойVBA.xlsm, то ThisWorkbook и Workbooks («МойVBA.xlsm») ссылаются на одну и ту же книгу.

Использование ThisWorkbook более полезно, чем использование Workbooks (). С ThisWorkbook нам не нужно беспокоиться об имени файла. Это дает нам два преимущества:

1. Изменение имени файла не повлияет на код
2. Копирование кода в другую рабочую книгу не
   требует изменения кода

Это может показаться очень маленьким преимуществом. Реальность такова, что имена будут меняться все время. Использование ThisWorkbook означает, что ваш код будет работать нормально.

В следующем примере показаны две строки кода. Один с помощью ThisWorkbook, другой с помощью Workbooks (). Тот, который использует Workbooks, больше не будет работать, если имя МойVBA. xlsm изменится.

Sub IzmenenieImeni()

    ' Обе строки делают одно и то же.
    Debug.Print ThisWorkbook.FullName
    Debug.Print Workbooks("МойVBA.xlsm").FullName

End Sub

Использование ActiveWorkbook

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

Поначалу это может показаться полезным. Проблема в том, что
любая рабочая книга может стать активной простым щелчком мыши. Это означает,
что вы можете легко записать данные не в ту книгу.

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

Надеюсь, я дал понять, что вам следует избегать использования ActiveWorkbook, если в этом нет необходимости. Если вы должны быть очень осторожны.

Примеры доступа к книге

Мы рассмотрели все способы доступа к книге. Следующий код показывает примеры этих способов.

Sub PrimerDostupaKKnige()

    ' Это рабочая книга, которая уже открыта и называется МойVBA.xlsm.
    Debug.Print Workbooks("МойVBA.xlsm").FullName

    ' Рабочая книга, содержащая этот код
    Debug.Print ThisWorkbook.FullName

    ' Открытая рабочая книга, которая была открыта первой
    Debug.Print Workbooks(1).FullName

    ' Открытая рабочая книга, которая была открыта последней
    Debug.Print Workbooks(Workbooks.Count).FullName

    ' Рабочая книга, которая в настоящее время активна
    Debug.Print ActiveWorkbook.FullName

    ' Рабочая книга не указана - будет использована активная
    Debug.Print Worksheets("Лист1").Name

    ' Закрытая рабочая книга с именем Книга1.xlsm в папке 
      'C:\Документы
    Workbooks.Open ("C:\Документы\Книга1.xlsm")
    Debug.Print Workbooks("Книга1.xlsm").FullName
    Workbooks("Книга1.xlsm").Close

End Sub

Объявление переменной VBA Workbook

Причина объявления переменной книги состоит в том, чтобы сделать ваш код более легким для чтения и понимания. Проще увидеть преимущество на примере:

Sub OtkrKnigaPerem()

    Dim wrk As Workbook
    Set wrk = Workbooks.Open("C:\Документы\Книга1.xlsm")

    ' Распечатать количество листов в каждой книге
    Debug.Print wrk.Worksheets.Count
    Debug.Print wrk.Name

    wrk.Close

End Sub

Вы можете установить переменную книги с помощью любого из
методов доступа, которые мы видели.

Ниже показан тот же код без переменной рабочей книги.

Sub OtkrKnigaNetPerem()

   Workbooks.Open ("C:\Документы\Книга1.xlsm")

   Debug.Print Workbooks("Книга2.xlsm").Worksheets.Count
   Debug.Print Workbooks("Книга2.xlsm").Name

    Workbooks("Книга2.xlsm").Close

End Sub

В этих примерах разница несущественная. Однако, когда у вас много кода, использование переменной полезно, в частности, для рабочего листа и диапазонов, где имена имеют тенденцию быть длинными, например thisWorkbook.Worksheets («Лист1»). Range («A1»).

Вы можете назвать переменную книги как wrkRead или wrkWrite. Затем вы можете сразу увидеть,
для чего используется эта книга.

Создать новую книгу

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

 Когда
вы создаете новую книгу, вы, как правило, хотите сохранить ее. Следующий код
показывает вам, как это сделать.

Sub SozdatKnigu()

    Dim wrk As Workbook
    Set wrk = Workbooks.Add

    ' Сохранить как xlsx. Это по умолчанию.
    wrk.SaveAs "C:\Temp\Пример.xlsx"

    ' Сохранить как макрокоманду
    wrk.SaveAs "C:\Temp\Пример.xlsm", xlOpenXMLWorkbookMacroEnabled

End Sub

Когда вы создаете новую книгу, она обычно содержит три листа. Это определяется свойством Application.SheetsInNewWorkbook.

Если вы хотите, чтобы в новой книге было другое количество
листов, измените это свойство перед созданием новой книги. В следующем примере
показано, как создать новую книгу с семью листами.

Sub SozdatKniguMnStr()

    ' Сохраните значение SheetsInNewWorkbook, чтобы мы могли сбросить его позже
    Dim sheetCnt As Long
    sheetCnt = Application.SheetsInNewWorkbook

    ' Установите листы в новой книге, чтобы быть 7
    Application.SheetsInNewWorkbook = 7

    ' Рабочая книга будет создана из 7 листов
    Dim wrk As Workbook
    Set wrk = Workbooks.Add

    ' Показать количество листов
    Debug.Print "number of sheets: " & CStr(wrk.Worksheets.Count)

    ' Сброс к первоначальному значению
    Application.SheetsInNewWorkbook = sheetCnt

End Sub

With и Workbook

Ключевое слово With облегчает чтение и написание кода VBA.
Использование с означает, что вам нужно упомянуть только один раз. С
используется с объектами. Это такие элементы, как рабочие книги, рабочие
таблицы и диапазоны.

В следующем примере есть два Subs. Первый похож на код,
который мы видели до сих пор. Второй использует ключевое слово With. Вы можете
увидеть код гораздо понятнее во втором Sub. Ключевые слова End With обозначают
конец кода раздела с помощью With.

' Не используется ключевое слово With
Sub NeIspWith()

   Debug.Print Workbooks("Книга2.xlsm").Worksheets.Count
   Debug.Print Workbooks("Книга2.xlsm").Name
   Debug.Print Workbooks("Книга2.xlsm").Worksheets(1).Range("A1")
   Workbooks("Книга2.xlsm").Close

End Sub

' Использование With делает код легче читать
Sub IspWith()

    With Workbooks("Книга2.xlsm")
        Debug.Print .Worksheets.Count
        Debug.Print .Name
        Debug.Print .Worksheets(1).Range("A1")
        .Close
    End With

End Sub

Резюме

Ниже приводится краткое изложение основных моментов этой статьи.

1. Чтобы получить рабочую книгу, содержащую код, используйте ThisWorkbook.
2. Чтобы получить любую открытую книгу, используйте Workbooks («Пример.xlsx»).
3. Чтобы открыть книгу, используйте Set Wrk = Workbooks.Open («C: \ Папка\ Пример. xlsx»).
4. Разрешить пользователю выбирать файл с помощью функции UserSelectWorkbook, представленной выше.
5. Чтобы создать копию открытой книги, используйте свойство SaveAs с именем файла.
6. Чтобы создать копию рабочей книги без открытия, используйте функцию FileCopy.
7. Чтобы ваш код было легче читать и писать, используйте ключевое слово With.
8. Другой способ прояснить ваш код — использовать переменные Workbook.
9. Чтобы просмотреть все открытые рабочие книги, используйте For Every wk в Workbooks, где wk — это переменная рабочей книги.
10. Старайтесь избегать использования ActiveWorkbook и Workbooks (Index), поскольку их ссылка на рабочую книгу носит временный характер.

Вы можете увидеть краткое руководство по теме в верхней части этой статьи

 Заключение

Это был подробная статья об очень важном элементе VBA — Рабочей книги. Я надеюсь, что вы нашли ее полезной. Excel отлично справляется со многими способами выполнения подобных действий, но недостатком является то, что иногда он может привести к путанице.

Чтобы получить максимальную пользу от этой статьи, я рекомендую вам попробовать примеры. Создайте несколько книг и поиграйтесь с кодом. Внесите изменения в код и посмотрите, как эти изменения влияют на результат. Практика — лучший способ выучить VBA.

Авторизация в Excel на VBA

Авторизация в Excel на VBA: создание формы, написание программного кода, особенности реализации.

В этой статье я расскажу вам о создании формы авторизации в Excel с помощью VBA.

Что же представляет из себя авторизация в Excel? Это форма, то есть окно, с запросом ввода логина и пароля, при успешном вводе которых, пользователю будут открываться различные листы документа, в зависимости от группы доступа пользователя. Точно также, помимо открытия листов, можно будет выставить ограничения на действия в Excel: запрет форматирования ячеек, удаление строк, столбцов, использование фильтров, объектов, сценариев и так далее. Но обо всем по порядку.

Авторизация в Excel: основной алгоритм работы

Для начала, необходимо разработать алгоритм, по которому мы будем работать, и выглядеть он будет примерно так:

Итак, поехали!

Авторизация в Excel: макет документа

Наш рабочий документ будет состоять из четырех листов:

1. Лист с приветствием — единственный лист, который будет отображаться всем пользователям до авторизации
2. Лист с дашбордом (визуализированным отчетом) — графики, диаграммы/гистограммы — изначально со свойством VeryHidden*
3. Лист с данными — источник расчетов для дашборда  — изначально со свойством VeryHidden
4. Служебный/технический лист — для хранения логинов, паролей и служебной и вспомогательной информации  — также, со свойством VeryHidden

*VeryHidden— свойство листа, при котором сам лист скрыт и включить его отображение можно только через использование режима разработчика. Для выбора данного свойства, необходимо в Excel зайти в меню «Разработчик» — «Visual Basic». Далее, необходимо выбрать нужный нам лист и в его свойствах («Properties») найти свойство Visible и установить в «2 — xlSheetVeryHidden».

После этого лист станет «очень скрытым». Отменить свойство VeryHidden можно точно так же через режим разработчика, либо используя необходимый нам макрос, о котором я напишу чуть дальше.

Авторизация в Excel: группы доступа

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

1. Администраторы (Admin): доступны все листы , в том числе служебный, а также отсутствуют какие-либо ограничения.
2. Руководители и ответственные за данные (Head): доступны 2 рабочих листа + стоит пароль на изменение структуры книги.
3. Рядовые сотрудники компании (Worker): доступен только 1 рабочий лист с дашбордом + стоит пароль на изменение структуры книги.

Авторизация в Excel: разработка макета формы

С этим пунктом не должно возникнуть никаких проблем. Элементов на форме авторизации должно быть не так уж и много:

• Поле ввода для логина
• Поле ввода для пароля
• Кнопка «Авторизация» (проверка логина и пароля)
• Различные подписи на форме
• По желанию: кнопка закрытия формы, кнопка восстановления пароля, логотипы, справка и все, что душе угодно

Для тех, кто не знает, как создавать формы в VBA: нужно зайти в меню «Разработчик» — «Visual Basic», нажать правой кнопкой по нашему проекту (VBAProject Название_Файла.xlsx), затем Insert — UserForm. Создается форма с названием UserForm1, на которой мы и будем размещать все наши объекты с помощью меню «ToolBox».

Если быстро набросать элементы, которые мы перечислили, должно получиться что-то вроде этого:

После добавления элементов, поменял их стандартные названия:

«UserForm1» переименовал в «Authorization».
«TextBox1» переименовал в «TextBox_Login».
«TextBox2» переименовал в «TextBox_Pass».  

Также, в свойствах «TextBox_Pass», мы находим свойство «PasswordChar» и вводим любой символ, который мы хотим видеть вместо вводимых символов пароля — чаще всего используется символ «*».

Ничего лишнего, пока что все просто. Переходим далее.

Подготовка служебного листа

В моем примере, служебный лист будет содержать список логинов, паролей, а также соответствующую логину группу доступа. Чтобы немного «усложнить» потенциальный взлом (хотя надо понимать, что взломать Excel продвинутому пользователю не составит никакого труда), пароли на листе мы будем хранить в захешированном виде. Подробнее о хешировании можно прочитать на Википедии, но в вкратце — это преобразование данных в строку фиксированной длины, состоящей из цифр и букв с помощью определенных математических алгоритмов. О том, как мы будем хешировать пароли в Excel, я расскажу далее.

Итак, для начала, содержимое служебного листа будет выглядеть вот так:

Авторизация в Excel: особенности и написание программного кода на VBA

Данные пункты тесно взаимосвязаны между собой, поэтому я их объединил в один большой. Здесь мы будем писать программный код для всего нашего файла: как для формы, так и для некоторых событий.

Для начала, мы напишем код, который будет отображать различные листы в зависимости от роли пользователя после авторизации. Макрос user_group мы делаем приватным и вписывать его будем не в отдельный модуль, а в нашу готовую форму Authorization. Аргументом для макроса является переменная X, которая будет содержать название группы доступа в виде текстовой строки String:

Private Sub user_group(ByVal X As String)
Dim Sht As Worksheet 'создаем переменную типа Рабочий Лист

ActiveWorkbook.Unprotect "112" 'Снимаем защиту структуры книги паролем

For Each Sht In ThisWorkbook.Sheets 'перебираем все листы книги
    If (X = "Admin") Then Sht.Visible = -xlSheetVisible 'если роль Admin - каждый лист будет видимый
    If (X = "Head") And (Sht.Name <> "Settings") Then Sht.Visible = -xlSheetVisible 'если роль Head - видны все, кроме Settings
    If (X = "Worker") And (Sht. Name = "Dashboard") Then Sht.Visible = -xlSheetVisible 'если роль Worker - виден только Dashboard
Next Sht

'если роль либо Head, либо Worker - ставим защиту на структуру книги с паролем "112"
If (X = "Head") Or (X = "Worker") Then ActiveWorkbook.Protect Password:="112", Structure:=True, Windows:=False

End Sub

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

Далее, код для кнопки «Авторизация». При нажатии на данную кнопку, запускается несколько проверок:

• Проверка полей логина и пароля заполнение: если одно из них не заполнено — выводится уведомление, выполнение макроса прекращается.
• Проверка переменной Check: данная переменная хранит значение «некорректных» попыток входа. Ее необходимо объявить в основном модуле книги: Public check As Integer. Если данная переменная больше 3х — доступ к авторизации в текущей сессии блокируется.
• Поиск и проверка логина на наличие на листе «Settings». Если логин найден — введенный пароль хешируется и сравнивается с хранимым хешем на соответствующей строке логина:
  • Если все корректно — запускается макрос User_Group, который открывает доступ к листам в зависимости от группы доступа, соответствующей логину.
  • Если данные некорректные — пользователь предупреждается об этом, а также наращивается счетчик некорректных попыток авторизации.
• В оставшемся случае (все введено, но пользователь с таким логином не найден), пользователь также уведомляется.

Private Sub CommandButton1_Click()
If (TextBox_Login = "") Or (TextBox_Pass = "") Then 'предупреждение на заполнение полей логина/пароля
    MsgBox "Не введен логин или пароль!", vbInformation + vbOKOnly, "Внимание!"
    Exit Sub
End If

If (check > 3) Then 'проверка на количество паролей, введенных некорректно
    MsgBox "Вы ввели неверный пароль больше трех раз.  Доступ к файлу заблокирован!", vbCritical + vbOKOnly, "Внимание"
    Exit Sub
End If

LastRow = Sheets("Settings").Cells(Rows.Count, 1).End(xlUp).Row 'проверка списка логинов
For i = 2 To LastRow
    If TextBox_Login = Sheets("Settings").Cells(i, 1) Then 'если логин найден
        If Sheets("Settings").Cells(i, 2) = GetHash(TextBox_Pass.Value) Then 'пароль хешируется и сравнивается с хранимым хешем
            user_group Sheets("Settings").Cells(i, 3).Value 'и если все ок - запускается макрос разграничения групп доступа
            Unload Authorization
            Exit Sub
        Else 'в противном случае - уведомление о неправильном пароле
            MsgBox "Неверный пароль", vbCritical + vbOKOnly, "Внимание!"
            check = check + 1 'также наращивается счетчик неправильных паролей
            Exit Sub
        End If
    End If
Next i

'в оставшемся случае - уведомления о несуществующем логине. 
MsgBox "Пользователя с данным логином не существует.", vbInformation + vbOKOnly, "Внимание!"

End Sub

В макросе CommandButton1_Click используется функция GetHash (строка 15), которая преобразует входящие данные в хеш. Данная функция взята с сайта ExcelVba.ru, за что выражаю им огромную благодарность. Код необходимо вставить в главный модуль книги:

Function GetHash(ByVal txt$) As String
    Dim oUTF8, oMD5, abyt, i&, k&, hi&, lo&, chHi$, chLo$
    Set oUTF8 = CreateObject("System.Text.UTF8Encoding")
    Set oMD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
    abyt = oMD5.ComputeHash_2(oUTF8.GetBytes_4(txt$))
    For i = 1 To LenB(abyt)
        k = AscB(MidB(abyt, i, 1))
        lo = k Mod 16: hi = (k - lo) / 16
        If hi > 9 Then chHi = Chr(Asc("a") + hi - 10) Else chHi = Chr(Asc("0") + hi)
        If lo > 9 Then chLo = Chr(Asc("a") + lo - 10) Else chLo = Chr(Asc("0") + lo)
        GetHash = GetHash & chHi & chLo
    Next
    Set oUTF8 = Nothing: Set oMD5 = Nothing
End Function

Основной код написан, теперь переходим к более мелким.

Макрос, который запускает форму авторизации (можно установить на кнопку на главном листе «Main»):

Sub Authorization_Start()
    Authorization.Show
End Sub

Также, макрос можно использовать при событии «Open» — при открытии книги:

Private Sub Workbook_Open()
    Authorization_Start
End Sub

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

Макрос для закрытия книги.

При закрытии книги, нам нужно снять защиту со структуры книги, затем скрыть все листы (кроме листа «Main»), а затем снова вернуть защиту структуры книги с паролем «112» (можно установить на какую-либо кнопку):

Sub close_book()
Dim Sht As Worksheet
ActiveWorkbook.Unprotect "112"
For Each Sht In ThisWorkbook.Sheets
    If Sht.Name <> "Main" Then Sht.Visible = xlSheetVeryHidden
Next Sht
ActiveWorkbook.Protect Password:="112", Structure:=True, Windows:=False
End Sub

Да, данный макрос можно использовать при событии «BeforeClose» — перед закрытием книги. Однако, может возникнуть несколько проблем:

• Если пользователь сохраняется вручную до закрытия документа, закрывает документ,  а затем при автоматическом запросе о сохранении нажимает «не сохранять». В таком случае, при новом запуске документа, скрытые листы будут видны всем. Да, вероятность такого события крайне мала, но она все-таки есть.

  Private Sub Workbook_BeforeClose(Cancel As Boolean)
      close_book
  End Sub

• Если добавим принудительное сохранение после скрытия всех листов, перед окончательным закрытием файла (как на примере ниже). В таком случае, если пользователь очень сильно ошибется с данными в файле и нажмет на крестик, чтобы начать все с нуля — файл все равно сохранится и восстановить исходные данные не получится.

  Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Application.ScreenUpdating = False
      close_book
      ThisWorkbook.Save
  End Sub

Поэтому, чтобы избежать такой ситуации, я хочу предложить более радикальный, менее удобный вариант, который, однако, сведет вышеперечисленные потенциальные проблемы к минимуму: помимо выполнения макроса close_book перед каждым закрытием книги с помощью события «BeforeClose» (как на первом проблемном варианте), мы будем выполнять его и перед каждым ручным сохранением файла с помощью события BeforeSave:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    close_book
End Sub

Осталось только сделать памятку по включению макросов на главном листе документа, а также выполнить важный шаг, без которого все наши проделанные действия не имеют смысла — поставить пароль на проект VBA: Меню «Разработчик» — «Visual Basic» — «Tools» — «VBAProject — Project Properties» — Вкладка «Protection» — поставить галочку «Lock project for viewing» и ввести пароль и нажать «Ок».

Сохраняем файл в формате «.xlsb» или «.xlsm» и все, наша «Авторизация на VBA» готова. Помимо этого, можно защитить проект еще сильнее — о способах защиты написано моей отдельной статье.

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



 5.7   
Основные свойства и методы объектов семейства WorkSheets

Объект Worksheet представляет собой рабочий лист. Объект Worksheet
можно получить, используя свойства ActiveSheet или Worksheets объекта
Workbook.

Свойства
объектов семейства
WorkSheets

Методы объектов семейства
Worksheets

События объекта
Worksheet

5.8   
Объект Range

5.2.1      
Адресация ячеек в Excel

Для ссылок на ячейки в Excel используются 2
формата:

Полный адрес ячейки может содержать также имя
рабочего листа и адрес книги. После имени листа ставится знак “!”, а адрес книги
заключается в квадратные скобки. Например:

 [МояКнига.xls]Лист1!D2.

Объект Range используется для работы с
ячейками, строками, столбцами, а также их группами. Для доступа к объекту чаще
всего используются свойства Range и Cells, хотя есть и другие возможности.

Если используется свойство Range, то в качестве
аргумента указывается любая допустимая в Excel ссылка в формате A1. Если имя
листа не указывается, то используется активный лист. Например:

‘Ячейке
A5 листа Лист1 присвоить значение 5

Worksheets(«Лист1»).Range(«A5»).Value =
5

‘Ячейке
A5 текущего листа присвоить значение 5

Range(«A5»).Value = 5

Свойство Cells используется для доступа к
отдельной ячейке. В качестве аргументов указываются номер строки и столбца.
Например, так можно присвоить значение ячейке A5 первого рабочего листа:

Worksheets(1).Cells(5,1).Value =
5

Можно также использовать свойство Cells для
альтернативного указания диапазона. Например:

 Range(«A2:C3») и Range(Cells(2,1),
Cells(3,3))

определяют один и тот же диапазон.

5.2.18 
Основные свойства объекта Range

Методы объекта Range можно разделить на две
большие группы: методы, относящиеся к самому объекту, и методы, реализующие
команды. Многие из них имеют параметры, которые здесь описываются лишь частично.
Подробнее о параметрах этих методов можно прочитать, например, в справочной
системе Excel. Для изучения методов, реализующих команды, рекомендуется записать
макрос, выполняющий нужную команду, и проанализировать полученный код.

5.2.19 
Основные методы объекта Range

5.2.20 
Методы объекта Range, реализующие команды
Excel

Кроме методов, реализующих команды объект Range
имеет методы, которые используют команды Excel.

Упражнение 5.       

Свойства и методы объекта
Range
и Selection

Задание:

4.       На
рабочем листе с именем Лист1 поместите кнопку формы.

5.      
Назначьте для этой кнопки макрос с именем Кнопка1_Щелкнуть

6.       В окне
редактирования кода редактора Visual Basic запишите следующий программный код.

7.      
Прочитайте все команды программы и попытайтесь понять их
назначение и синтаксис записи. Обратите внимание на текст комментариев.

8.      
Запустите макрос на выполнение.

9.      
Проследите за тем, какие действия выполняет программа.

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

Option Explicit

Sub
Кнопка1_Щелкнуть()

‘В ячейку А1
записывается текст

Range(«A1″).Value = » Упражнение 5 »

‘Выделяется
ячейка А1

Range(«A1»).Select

With Selection

‘Получаем
адрес активной ячейки

MsgBox «Адрес
активной ячейки» & .B2″

‘Получаем
количество строк в диапазоне

MsgBox
«Количество строк в области А1:С2 =» & Range(«A1:C2») _

                           
.Rows.Count

MsgBox
«Количество строк в текущем диапазоне» & Range(«A1») _

                       
.CurrentRegion.Rows.Count

‘Очищаем
диапазон

Range(«A1:C2»).Clear

‘ Используем объект
Cells

Cells(1, 1) =
» Упражнение 5 »

End Sub

Упражнение 6.       

Задание. Пусть на
рабочем листе имеется таблица. В левой ячейке ее первой строки находится
заголовок таблицы. В следующей строке – заголовки столбцов. В остальных строках
– данные. Количество строк заранее не известно. Создайте диалоговое окно,
которое позволит отформатировать таблицу: разместит заголовок таблицы по центру
над столбцами, изменит шрифт (размер – 14, курсив, цвет – красный), заголовки
столбцов расположит в центре, изменит шрифт на полужирный. В окне также имеется
поле для ввода диапазона ячеек и кнопка, позволяющая убрать форматирование для
указанных ячеек. Вид диалогового окна и результат форматирования показан на
рис.5.13, 5.14.

Рис. 6.3. Диалоговое окно

Рис. 6.4. Результат
форматирования

Выполните следующие действия:

1.       Создайте
приведенную на рис.6.3 форму. На ней размещены элемент Надпись, две кнопки и
элемент RefEdit. Установите необходимые свойства элементов.

2.       В общей
области окна программы формы декларируйте переменные:

Dim myR
As Range

Dim
Заголовок As Range

Dim
Названия As Range

Dim c As
Integer

Dim r As
Integer

3.       Напишите
процедуры обработки события Click для кнопок. Они могут быть примерно
такими:

Private Sub
КнопкаФорматировать_Click()

       
‘присваиваем переменной myR значение (ссылка на

   
‘диапазон берется из элемента RefEdit)

    Set myR
= Range(RefEdit1.Text)

    r =
myR.Rows.Count 
‘число строк в диапазоне

    c =
myR.Columns.Count ‘число столбцов в диапазоне

    Set
Заголовок = Range(myR.Cells(1,1), myR.Cells(1,c))

    Set
Названия = Range(myR.Cells(2,1), myR.Cells(2,c))

   
Заголовок.Select

   
Selection.HorizontalAlignment = xlCenterAcrossSelection

    With
Заголовок.Font

        .Name =
«Arial Cyr»

       
.FontStyle = «полужирный курсив»

        .Size =
14

       
.ColorIndex = 3

    End
With

       
Названия.HorizontalAlignment = xlCenter

    With
Названия.Font

        .Name =
«Arial Cyr»

       
.FontStyle = «полужирный»

        .Size =
10

    End
With

End
Sub

Private
Sub КнопкаУбратьФормат_Click()

    Set myR
= Range(RefEdit1.Text)

   
myR.ClearFormats

End
Sub

4.      
Проверьте работу программы. Для вызова окна используйте кнопку,
размещенную на рабочем листе.

   Предыдущая  Следующая  В содержание темы

Border объект — Общие материалы — Общие сведения — Справочник VBA

       Этот объект имеется у любого другого объекта имеющего границу или несколько границ (сторон или граней объекта).

Пример:

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

With ActiveChart.SeriesCollection(1).Trendlines(1)

    .Type = xlLinear

    .Border.LineStyle = xlDash

End With

      У объектов Range и Style есть четыре дискретных границы — левая (left), правая (right), верхняя (top), и нижняя (bottom) — которые могут быть возвращены индивидуально или как группа. Используйте свойство Borders, чтобы возвратить коллекцию Borders, которая содержит все четыре границы и обрабатывает границы как единый модуль.

Следующий пример добавляет двойную границу к ячейке A1 на рабочем листе один.

Worksheets(1).Range(«A1»).Borders.LineStyle = xlDouble

       Используйте Borders(index), где index идентифицирует отдельную границу в объекте.

Следующий пример выбирает цвет нижней границы ячеек A1:G1.

Worksheets(«Sheet1»).Range(«A1:G1»). _

    Borders(xlEdgeBottom).Color = RGB(255, 0, 0)

Index может быть одним из следующих значений  константы XlBordersIndex:

• xlDiagonalDown=5 — Граница, работающая от верхнего левого угла до более низкой правой части каждой ячейки в диапазоне.

• xlDiagonalUp = 6 — Граница, работающая от более низкого левого угла до верхнего правого угла каждой ячейки в диапазоне.
• xlEdgeBottom = 9 — Граница внизу диапазона.
• xlEdgeLeft = 7 — Граница на левом краю диапазона.
• xlEdgeRight = 10 — Граница на правом краю диапазона.
• xlEdgeTop = 8 — Граница в верхней части диапазона.
• xlInsideHorizontal = 12 — Горизонтальные границы для всех ячеек в диапазоне кроме границ за пределами диапазона.

• xlInsideVertical = 11 — Вертикальные границы для всех ячеек в диапазоне кроме границ за пределами диапазона.

листов.Добавить метод (Excel) | Документы Microsoft

• 03.09.2019
• 2 минуты на чтение

В этой статье

Создает новый лист, диаграмму или лист макроса. Новый рабочий лист становится активным.

Синтаксис

выражение . Добавить ( до , после , Счетчик , Тип )

выражение Переменная, представляющая объект Sheets .

Параметры

Возвращаемое значение

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

Замечания

Если До и После того, как опущены, новый лист вставляется перед активным листом.

Пример

В этом примере новый лист вставляется перед последним листом в активной книге.

  ActiveWorkbook.Sheets.Add Before: = ActiveWorkbook.Worksheets (ActiveWorkbook.Worksheets.Count)
  

В этом примере новый рабочий лист вставляется после последнего рабочего листа в активной книге и записывается возвращенная ссылка на объект в локальной переменной.

  Размер листа как рабочий лист
Установить лист = ActiveWorkbook.Sheets.Add (После: = ActiveWorkbook.Worksheets (ActiveWorkbook.Worksheets.Count))
  

Примечание

В 32-разрядном Excel 2010 этот метод не может создать более 255 листов за один раз.

Поддержка и отзывы

Есть вопросы или отзывы об Office VBA или этой документации? См. Раздел Поддержка и отзывы Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.

Как дублировать лист в Excel с VBA

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

Копировать листы в Excel вручную довольно быстро и просто … если выполнить всего один или два раза. Многократное копирование нескольких листов утомительно и требует много времени. На этой странице вы найдете несколько полезных макросов для автоматизации этой задачи.

Excel VBA для копирования листа в новую книгу

Этот простейший однострочный макрос делает именно то, что предполагает его название — копирует активный лист в новую книгу.

Публичная подписка CopySheetToNewWorkbook ()
activeSheet.Копировать
Конец подписки
 

Копирование нескольких листов в Excel с помощью VBA

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

Публичная подписка CopySelectedSheets ()
ActiveWindow.SelectedSheets.Copy
Конец подписки
 

Excel VBA для копирования листа в другую книгу

В зависимости от того, куда вы хотите вставить скопированный лист, используйте один из следующих макросов.

Копия листа в начало другой книги

Этот макрос копирует активный лист перед всеми остальными листами в конечном файле, Book1 в этом примере.Чтобы скопировать в другой файл, замените «Book1.xlsx» на полное имя целевой книги.

Public Sub CopySheetToBeginningAnotherWorkbook ()
activeSheet.Copy До: = Книги ("Book1.xlsx"). Таблицы (1)
Конец подписки
 

Копия листа в конец другой книги

Этот фрагмент кода дублирует активный рабочий лист и помещает копию в конец Book1 . Опять же, не забудьте заменить «Book1.xlsx» на имя целевой книги.

Public Sub CopySheetToEndAnotherWorkbook ()
activeSheet.Copy After: = Workbooks ("Book1.xlsx"). Sheets (Workbooks ("Book1.xlsx"). Worksheets.Count)
Конец подписки
 

Примечание. Для работы макросов целевая книга должна быть сохранена на жестком диске или в сети.

Копировать лист в выбранную книгу

Чтобы иметь возможность скопировать текущий лист в любую открытую книгу, вы можете создать UserForm (с именем UserForm1 ) с элементом управления ListBox ( с именем ListBox1 ) и двумя кнопками:

Затем дважды щелкните форму и вставьте приведенный ниже код в окно кода:

Public SelectedWorkbook как строка

Частная подписка UserForm_Initialize ()
SelectedWorkbook = ""
ListBox1.Очистить
Для каждого wbk в приложении.
ListBox1.AddItem (wbk.Name)
следующий
Конец подписки

Частная подпрограмма CommandButton1_Click ()
Если ListBox1.ListIndex> -1 Тогда
SelectedWorkbook = ListBox1.List (ListBox1.ListIndex)
Конец, если
Me.Hide
Конец подписки

Частная подпрограмма CommandButton2_Click ()
SelectedWorkbook = ""
Me.Hide
Конец подписки
 

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

Копия листа в начало выбранной книги :

Public Sub CopySheetToBeginningAnotherWorkbook ()
Загрузить UserForm1
UserForm1.Show

Если (UserForm1.SelectedWorkbook <> "") То
activeSheet.Copy Before: = Workbooks (UserForm1.SelectedWorkbook) .Sheets (1)
Конец, если

Выгрузить UserForm1
Конец подписки
 

Копия листа в конец выбранной книги :

Public Sub CopySheetToEndAnotherWorkbook ()
Загрузить UserForm1
UserForm1.Шоу

Если (UserForm1.SelectedWorkbook <> "") То
        activeSheet.Copy После: = Книги (_
        UserForm1.SelectedWorkbook) .Sheets (_
        Книги (UserForm1.SelectedWorkbook). _
        Worksheets.Count)
Конец, если

Выгрузить UserForm1
Конец подписки
 

При запуске в Excel макрос покажет вам список всех открытых в данный момент книг. Вы выбираете нужный и нажимаете ОК:

Макрос Excel для копирования листа и переименования

Когда вы копируете лист в Excel, реплике присваивается имя в формате по умолчанию, например Sheet1 (2) .Следующие макросы избавят вас от необходимости менять имя по умолчанию вручную.

Этот код дублирует активный рабочий лист, называет копию «Тестовый лист» (вы можете заменить его любым другим именем) и помещает скопированный лист в конец текущей книги.

Общедоступная подписка CopySheetAndRenamePredefined ()
activeSheet.Copy После: = Листы (Sheets.Count)
При ошибке Возобновить Далее
activeSheet.Name = "Тестовый лист"
Конец подписки
 

Чтобы разрешить пользователю указать имя для скопированного листа , используйте этот код:

Публичная подписка CopySheetAndRename ()
Dim newName As String

При ошибке Возобновить Далее
newName = InputBox («Введите имя для скопированного листа»)

Если newName <> "" Тогда
activeSheet.Копировать после: = Рабочие листы (Sheets.Count)
При ошибке Возобновить Далее
activeSheet.Name = newName
Конец, если
Конец подписки
 

После запуска макрос отображает следующее поле ввода, в котором вы вводите желаемое имя и нажимаете OK:

Макрос Excel для копирования листа и переименования на основе значения ячейки

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

Самая сложная часть — сделать так, чтобы ваши пользователи всегда выбирали правильную ячейку перед запуском макроса 🙂

Общедоступная подписка CopySheetAndRenameByCell ()
Dim newName As String
При ошибке Возобновить Далее
newName = InputBox («Введите имя скопированного рабочего листа», «Копировать рабочий лист», ActiveCell.Value)

Если newName <> "" Тогда
activeSheet.Копировать после: = Рабочие листы (Sheets.Count)
При ошибке Возобновить Далее
activeSheet.Name = newName
Конец, если
Конец подписки
 

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

Публичная подписка CopySheetAndRenameByCell2 ()
Dim wks как рабочий лист
Установить wks = activeSheet
activeSheet.Копировать после: = Рабочие листы (Sheets.Count)
Если wks.Range ("A1"). Value <> "" Тогда
При ошибке Возобновить Далее
activeSheet.Name = wks.Range ("A1"). Значение
Конец, если

wks.Activate
Конец подписки
 

Макрос для копирования листа в закрытую книгу

Этот макрос копирует активный лист в конец закрытой книги. Имя другой книги не указано в коде — макрос откроет стандартное окно Windows Explorer и позволит вам выбрать любой файл назначения:

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

Общедоступная подписка CopySheetToClosedWorkbook ()
Dim fileName
Dim closedBook As Workbook
Уменьшить размер текущего листа как рабочего листа

fileName = Application.GetOpenFilename ("Файлы Excel (* .xlsx), * .xlsx")

Если fileName <> False, то
Application.ScreenUpdating = False

Установите currentSheet = Application.activeSheet
Установите closedBook = Workbooks.Open (fileName).
currentSheet.Copy После: = closedBook.Sheets (closedBook.Worksheets.Count)
закрытая книга.Закрыть (True)

Application.ScreenUpdating = True
Конец, если
Конец подписки
 

Excel VBA для копирования листа из другой книги без открытия

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

Только не забудьте сделать пару замен в коде:

• C: \ Users \ XXX \ Documents \ Target_Book.xlsx следует изменить на фактический путь и имя книги, из которой вы хотите скопировать лист.
• Sheet1 следует заменить на имя листа, который вы хотите скопировать.

Общедоступная подписка CopySheetFromClosedWorkbook ()
Тусклый источник книги как книги
Application.ScreenUpdating = False
Установите sourceBook = Workbooks.Open ("C: \ Users \ XXX \ Documents \ Target_Book.xlsx")
sourceBook.Sheets ("Sheet1"). Копировать после: = ThisWorkbook.Sheets (ThisWorkbook.Sheets.Count)
sourceBook.Close
Application.ScreenUpdating = True
Конец подписки
 

Excel VBA для многократного дублирования листа

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

Public Sub DuplicateSheetMultipleTimes ()
Dim n как целое число
При ошибке Возобновить Далее
n = InputBox («Сколько копий активного листа вы хотите сделать?»)

Если n> = 1, то
Для numtimes = от 1 до n
activeSheet.Copy После: = ActiveWorkbook.Sheets (Worksheets.Count)
следующий
Конец, если
Конец подписки
 

Откройте исходный лист, запустите макрос, укажите, сколько копий активного листа вы хотите сделать, и нажмите ОК :

Как дублировать листы в Excel с VBA

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

Как добавить макрос в книгу

Чтобы вставить код в книгу, выполните следующие действия:

1. Откройте рабочий лист, который хотите скопировать.
2. Нажмите Alt + F11, чтобы открыть редактор Visual Basic.
3. На левой панели щелкните правой кнопкой мыши ThisWorkbook , а затем щелкните Вставить > Модуль .
4. Вставьте код в окно кода.
5. Нажмите F5, чтобы запустить макрос.

Подробные пошаговые инструкции см. В разделе Как вставить код VBA в Excel.

Как запустить макрос из нашего примера книги

Кроме того, вы можете загрузить наш образец книги в Duplicate Excel Sheets и запустить код оттуда.

Образец книги содержит следующие макросы:

CopySheetToNewWorkbook — копирует текущий рабочий лист в новую книгу.

CopySelectedSheets — копирует несколько выбранных листов в новую книгу.

CopySheetToBeginningAnotherWorkbook — копирует активный лист в начало другой книги.

CopySheetToEndAnotherWorkbook — копирует активный лист в конец другого файла Excel.

CopySheetAndRename — дублирует текущий лист, переименовывает его, как указано пользователем, и помещает копию после всех других листов в текущей книге.

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

CopySheetAndRenameByCell — делает копию активного листа и переименовывает ее на основе выбранного значения ячейки.

CopySheetAndRenameByCell2 — копирует активный лист и переименовывает его на основе жестко заданного адреса ячейки.

CopySheetToClosedWorkbook — позволяет копировать лист в закрытую книгу.

CopySheetFromClosedWorkbook — позволяет копировать лист из другого файла Excel, не открывая его.

DuplicateSheetMultipleTimes — позволяет дублировать лист в Excel несколько раз.

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

1. Откройте загруженную книгу и включите содержимое, если будет предложено.
2. Откройте собственную книгу и перейдите к листу, который хотите скопировать.
3. На листе нажмите Alt + F8, выберите интересующий макрос и нажмите Выполнить .

Вот как можно продублировать лист в Excel с помощью VBA. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

Определение типов листов в VBA (Microsoft Excel)

Обратите внимание:
Эта статья написана для пользователей следующих версий Microsoft Excel: 97, 2000, 2002 и 2003.Если вы используете более позднюю версию (Excel 2007 или более позднюю), , этот совет может вам не подойти . Чтобы посмотреть версию этого совета, написанную специально для более поздних версий Excel, щелкните здесь: Определение типов листов в VBA.

Если вы пишете макросы, которые обрабатывают разные листы в книге, вам может потребоваться выяснить, какие типы листов есть в книге, прежде чем выполнять какую-либо обработку.Это может быть особенно критичным, поскольку некоторые команды VBA работают только с определенными типами листов.

Прежде чем вы сможете выяснить, какие типы листов находятся в книге, полезно узнать, как Excel внутренне хранит некоторые объекты, составляющие книгу. Excel поддерживает как коллекцию листов, так и коллекцию диаграмм. Коллекция Worksheets состоит из объектов листа, а коллекция Charts — из объектов листа диаграммы. Объекты листа диаграммы — это диаграммы, которые занимают весь рабочий лист; он не включает объекты, встроенные в рабочий лист.

Интересно, что листы и объекты листов диаграммы также являются членами коллекции Sheets. Итак, если вы хотите обработать книгу в том порядке, в котором расположены листы, проще всего сделать это, пройдя через коллекцию Sheets. Когда вы это сделаете, вы можете изучить свойство Type отдельных объектов в коллекции, чтобы определить, какой это тип объекта. Excel определяет четыре типа объектов, которые могут принадлежать коллекции Sheets:

• xlWorksheet. Это обычный рабочий лист.
• xlChart. Это диаграмма.
• xlExcel4MacroSheet. Это лист макросов, используемый в Excel 4.0.
• xlExcel4IntlMacroSheet. Это международный лист макросов, используемый в Excel 4.0.

У вас может возникнуть соблазн подумать, что достаточно взглянуть на список типов листов. Однако интересно, что Excel не всегда возвращает то, что вы ожидаете от свойства Type.Вместо этого, если вы изучите свойство Type для диаграммы, оно вернет значение, равное xlExcel4MacroSheet. Это может вызвать проблемы для любого макроса.

Таким образом, можно решить эту проблему, сравнив имя каждого элемента в коллекции Sheets с именами в коллекции Charts. Если имя есть в обеих коллекциях, можно с уверенностью предположить, что лист является диаграммой. Если его нет в обоих, то вы можете проанализировать дальше, чтобы увидеть, относится ли рабочий лист к одному из других типов. Следующий макрос SheetType следует точно такому процессу:

Sub SheetType ()
    Dim iCount как целое число
    Dim iType как целое число
    Dim sTemp As String
    Dim oChart как диаграмма
    Dim bFound as Boolean

    sTemp = ""
    Для iCount = 1 в листах.Считать
        iType = Таблицы (iCount) .Type
        sTemp = sTemp & Sheets (iCount) .Name & "is a"

        bFound = Ложь
        Для каждой диаграммы в диаграммах
            Если oChart.Name = Sheets (iCount) .Name, то
                bFound = True
            Конец, если
        Следующая oChart

        Если bFound Then
            sTemp = sTemp & «лист диаграммы».
        Еще
            Выберите Case iType
                Дело xlWorksheet
                    sTemp = sTemp & "рабочий лист."
                Дело xlChart
                    sTemp = sTemp & «лист диаграммы».
                Случай xlExcel4MacroSheet
                    sTemp = sTemp & "Лист макросов Excel 4."
                Случай xlExcel4IntlMacroSheet
                    sTemp = sTemp & "n Международный лист макросов Excel 4"
                Case Else
                    sTemp = sTemp & "Неизвестный тип листа."
            Конец Выбрать
        Конец, если
        sTemp = sTemp & vbCrLf
    Следующий iCount
    НАСТРОЙКА MsgBox
Конец подписки
 

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

ExcelTips — ваш источник экономичного обучения Microsoft Excel.
Этот совет (2538) относится к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь: Определение типов листов в VBA .

Автор Биография

Аллен Вятт

Аллен Вятт — всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей.Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше о Allen …

Изменение ориентации страницы

Ориентация страницы определяет способ печати информации на странице. Вот как вы можете управлять страницей …

Узнать больше

Найти и заменить в столбце или строке

Требуется поиск информации в таблице? Word позволяет легко ограничить поиск целым столбцом или строкой, как…

Узнать больше

Изменение меток шкалы осей

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

Узнать больше

Excel VBA Workbook и объект рабочего листа

Иерархия объектов | Коллекции | Свойства и методы

Узнайте больше о Workbook и Worksheet объект в Excel VBA .

Иерархия объектов

В Excel VBA объект может содержать другой объект, и этот объект может содержать другой объект и т. Д. Другими словами, программирование Excel VBA предполагает работу с иерархией объектов. Возможно, это звучит довольно запутанно, но мы проясним это.

Мать всех объектов — это сам Excel. Мы называем это объектом Application. Объект приложения содержит другие объекты. Например, объект Workbook (файл Excel). Это может быть любая созданная вами рабочая книга.Объект Workbook содержит другие объекты, например объект Worksheet. Объект Worksheet содержит другие объекты, такие как объект Range.

В главе «Создание макроса» показано, как запустить код, щелкнув командную кнопку. Мы использовали следующую строку кода:

Диапазон («A1»). Value = «Hello»

, но на самом деле мы имели в виду:

Application.Workbooks («create-a-macro»). Worksheets (1) .Range («A1»). Value = «Hello»

Примечание: объекты соединены точкой.К счастью, нам не нужно добавлять строку кода таким образом. Это потому, что мы поместили нашу командную кнопку в create-a-macro.xlsm на первом листе. Имейте в виду, что если вы хотите что-то изменить на разных листах, вы должны включить объект Worksheet. Читать дальше.

Коллекции

Вы могли заметить, что рабочие тетради и рабочие листы имеют множественное число. Это потому, что они коллекции. Коллекция Workbooks содержит все открытые в данный момент объекты Workbook.Коллекция Worksheets содержит все объекты Worksheet в книге.

Вы можете ссылаться на член коллекции, например, на отдельный объект Worksheet, тремя способами.

1. Использование имени рабочего листа.

Рабочие листы («Продажи»). Диапазон («A1»). Значение = «Привет»

2. Используя порядковый номер (1 — это первый рабочий лист, начиная слева).

Рабочие листы (1) .Range («A1»). Value = «Hello»

3.Использование CodeName.

Sheet1.Range («A1»). Value = «Hello»

Чтобы увидеть CodeName рабочего листа, откройте редактор Visual Basic. В Project Explorer первое имя — это CodeName. Второе имя — это имя рабочего листа (Продажи).

Примечание. CodeName остается прежним, если вы изменяете имя рабочего листа или порядок своих рабочих листов, поэтому это самый безопасный способ ссылки на рабочий лист. Нажмите «Просмотр», «Окно свойств», чтобы изменить кодовое имя рабочего листа.Есть один недостаток: вы не можете использовать CodeName, если ссылаетесь на лист в другой книге.

Свойства и методы

Теперь давайте взглянем на некоторые свойства и методы коллекции Workbooks and Worksheets. Свойства — это то, что есть в коллекции (они описывают коллекцию), в то время как методы что-то делают (они выполняют действие с коллекцией).

Поместите командную кнопку на лист и добавьте строки кода:

1.Метод Add коллекции Workbooks создает новую книгу.

Рабочие тетради. Добавить

Примечание: метод Add коллекции Worksheets создает новый рабочий лист.

2. Свойство Count коллекции Worksheets подсчитывает количество листов в книге.

MsgBox Worksheets.Count

Результат при нажатии кнопки команды на листе:

Примечание. Свойство Count коллекции Workbooks подсчитывает количество активных книг.

Найти значение в диапазоне, листе или листах с VBA

Найдите значение в диапазоне, листе или листах с помощью
VBA

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

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

Используйте «Найти» для выбора ячейки

В приведенных ниже примерах выполняется поиск в столбце A листа с именем «Sheet1» для
значение поля ввода. Измените имя листа или диапазон в коде на свое
лист / диапазон.

Совет : Вы можете заменить поле ввода на
строка или ссылка на такую ​​ячейку
FindString = «SearchWord»
или
FindString
= Листы («Лист1»). Диапазон («D1»). Значение

В этом примере будет выбрано
первая ячейка в диапазоне со значением InputBox.

 Подпрограмма Find_First ()
    Размыть FindString как строку
    Dim Rng As Range
    FindString = InputBox («Введите значение для поиска»)
    Если Trim (FindString) <> "" Тогда
        С листами ("Sheet1"). Диапазон ("A: A")
            Установить Rng = .Find (What: = FindString, _
                            После: =. Cells (.Cells.Count), _
                            LookIn: = xlValues, _
                            LookAt: = xlWhole, _
                            SearchOrder: = xlByRows, _
                            SearchDirection: = xlNext, _
                            MatchCase: = False)
            Если не Rng, то ничего
                Заявка.Goto Rng, True
            Еще
                MsgBox "Ничего не найдено"
            Конец, если
        Конец с
    Конец, если
Концевой переводник 

Если у вас более одного вхождения значения, будет выбрано
последнее появление.

 Sub Find_Last ()
    Размыть FindString как строку
    Dim Rng As Range
    FindString = InputBox («Введите значение для поиска»)
    Если Trim (FindString) <> "" Тогда
        С листами ("Sheet1"). Диапазон ("A: A")
            Установить Rng = .Find (What: = FindString, _
                            После: =.Ячейки (1), _
                            LookIn: = xlValues, _
                            LookAt: = xlWhole, _
                            SearchOrder: = xlByRows, _
                            SearchDirection: = xlPrevious, _
                            MatchCase: = False)
            Если не Rng, то ничего
                Application.Goto Rng, True
            Еще
                MsgBox "Ничего не найдено"
            Конец, если
        Конец с
    Конец, если
Концевой переводник 

Если у вас есть дата в столбце A, то в этом примере будет выбрана ячейка
с сегодняшней датой.Примечание. Если ваши даты являются формулами, возможно, вы
необходимо изменить xlFormulas на xlValues ​​в примере ниже. Если ваши даты
values ​​xlValues ​​не всегда работает с некоторыми форматами даты.

 Sub Find_Todays_Date ()
    Затенять FindString как дату
    Dim Rng As Range
    FindString = CLng (Дата)
    С листами ("Sheet1"). Диапазон ("A: A")
        Установить Rng = .Find (What: = FindString, _
                        После: =. Cells (.Cells.Count), _
                        LookIn: = xlFormulas, _
                        LookAt: = xlWhole, _
                        SearchOrder: = xlByRows, _
                        SearchDirection: = xlNext, _
                        MatchCase: = False)
        Если не Rng, то ничего
            Заявка.Goto Rng, True
        Еще
            MsgBox "Ничего не найдено"
        Конец, если
    Конец с
Концевой переводник 

Отметить ячейки с одинаковым значением в столбце A столбца B

В этом примере выполняется поиск в Таблицах («Лист1») в столбце A для каждой ячейки с
«ron» и используйте «Смещение», чтобы отметить ячейку в столбце справа.
Примечание : вы можете добавить больше значений в массив MyArr .

 Sub Mark_cells_in_column ()
    Dim FirstAddress As String
    Dim MyArr как вариант
    Dim Rng As Range
    Тусклый я пока

    С приложением
        .ScreenUpdating = False
        .EnableEvents = Ложь
    Конец с

    'Поиск значения или значений в диапазоне
    'Вы также можете использовать больше значений, таких как этот Array ("ron", "dave")
    MyArr = массив ("рон")

    'Поиск в столбце или диапазоне
    С листами ("Sheet1"). Диапазон ("A: A")

        'очистите ячейки в столбце справа
        .Offset (0, 1) .ClearContents

        Для I = LBound (MyArr) To UBound (MyArr)

            'Если вы хотите найти часть rng.value, используйте xlPart
            'если вы используете LookIn: = xlValues, он также будет работать с
            'ячейка формулы, которая вычисляется как "ron"

            Установите Rng =.Найдите (What: = MyArr (I), _
                            После: =. Cells (.Cells.Count), _
                            LookIn: = xlFormulas, _
                            LookAt: = xlWhole, _
                            SearchOrder: = xlByRows, _
                            SearchDirection: = xlNext, _
                            MatchCase: = False)

            Если не Rng, то ничего
                FirstAddress = Rng.Address
                Делать
                    'отметьте ячейку в столбце справа, если "Рон" найден
                    Rng.Смещение (0, 1) .Value = "X"
                    Установить Rng = .FindNext (Rng)
                Цикл пока не звонит - это ничего и Rng.Address <> FirstAddress
            Конец, если
        Далее я
    Конец с

    С приложением
        .ScreenUpdating = Истина
        .EnableEvents = Истина
    Конец с
Концевой переводник 

Цвет ячеек с одинаковым значением в диапазоне, листе или всех листах

В этом примере окрашиваются все ячейки в диапазоне
Листы («Лист1»). Диапазон («B1: D100») с «ron».См. Комментарии в коде
если вы хотите использовать все ячейки на листе. Я использую цветовой индекс в этом
пример, чтобы присвоить всем ячейкам с «ron» цвет 3 (обычно это красный)

См. этот сайт для всех 56 номеров индекса
http://dmcritchie.mvps.org/excel/colors.htm

Совет : Для изменения цвета шрифта см. Примерные строки
под макросами.

 Sub Color_cells_In_Range_Or_Sheet ()
    Dim FirstAddress As String
    Dim MySearch как вариант
    Dim myColor как вариант
    Dim Rng As Range
    Тусклый я пока

    'Заполните поисковый индекс Значение и цвет
    MySearch = Массив ("рон")
    myColor = Array ("3")

    'Вы также можете использовать больше значений в массиве
    'MySearch = Array ("рон", "желе", "юдифь")
    'myColor = Array ("3", "6", "10")


    'Заполните диапазон поиска для всего листа
    'вы можете использовать Таблицы ("Лист1").Клетки
    С листами ("Лист1"). Диапазон ("B1: D100")

        'Измените цвет заливки на "без заливки" во всех ячейках
        .Interior.ColorIndex = xlColorIndexNone

        Для I = LBound (MySearch) в UBound (MySearch)

            'Если вы хотите найти часть rng.value, используйте xlPart
            'если вы используете LookIn: = xlValues, он также будет работать с
            'ячейка формулы, которая вычисляет MySearch (I)
            Установите Rng = .Find (What: = MySearch (I), _
                            После: =. Cells (.Cells.Счетчик), _
                            LookIn: = xlFormulas, _
                            LookAt: = xlWhole, _
                            SearchOrder: = xlByRows, _
                            SearchDirection: = xlNext, _
                            MatchCase: = False)

            Если не Rng, то ничего
                FirstAddress = Rng.Address
                Делать
                    Rng.Interior.ColorIndex = myColor (I)
                    Установить Rng = .FindNext (Rng)
                Цикл пока не Rng ничего и Rng.Адрес <> FirstAddress
            Конец, если
        Далее я
    Конец с
Концевой переводник 

Пример для всех листов в книге

 Sub Color_cells_In_All_Sheets ()
    Dim FirstAddress As String
    Dim MySearch как вариант
    Dim myColor как вариант
    Dim Rng As Range
    Тусклый я пока
    Dim sh As Рабочий лист

    'Заполните поисковый индекс Значение и цвет
    MySearch = Массив ("рон")
    myColor = Array ("3")

    'Вы также можете использовать больше значений в массиве
    'MySearch = Array ("рон", "желе", "юдифь")
    'myColor = Array ("3", "6", "10")

    Для каждого sh в ActiveWorkbook.Рабочие листы

        'Заполните диапазон поиска для диапазона на каждом листе
        'вы также можете использовать sh.Range ("B1: D100")
        С ш.Cells

            'Измените цвет заливки на "без заливки" во всех ячейках
            .Interior.ColorIndex = xlColorIndexNone

            Для I = LBound (MySearch) в UBound (MySearch)

                'Если вы хотите найти часть rng.value, используйте xlPart
                'если вы используете LookIn: = xlValues, он также будет работать с
                'ячейка формулы, которая вычисляет MySearch (I)
                Установите Rng =.Найти (Что: = MySearch (I), _
                                После: =. Cells (.Cells.Count), _
                                LookIn: = xlFormulas, _
                                LookAt: = xlWhole, _
                                SearchOrder: = xlByRows, _
                                SearchDirection: = xlNext, _
                                MatchCase: = False)

                Если не Rng, то ничего
                    FirstAddress = Rng.Address
                    Делать
                        Rng.Interior.ColorIndex = myColor (I)
                        Установить Rng = .FindNext (Rng)
                    Цикл пока не звонит - это ничего и Rng.Address <> FirstAddress
                Конец, если
            Далее я
        Конец с
    Следующий ш
Концевой переводник 

Измените цвет шрифта вместо цвета интерьера

Замените:

‘Измените цвет заливки на «без заливки» во всех ячейках
.Interior.ColorIndex = xlColorIndexNone

С

‘ Измените шрифт в столбце к АКПП
.Font.ColorIndex = 0

и замените:

Rng.Interior.ColorIndex = myColor (I)
с
Rng.Font.ColorIndex = myColor (I)

Скопируйте ячейки на другой лист с помощью Find

В приведенном ниже примере будут скопированы все ячейки с адресом электронной почты в диапазоне
Листы («Лист1»). Диапазон («A1: E100») до нового листа в вашей книге.
Примечание : я использую xlPart в коде вместо xlWhole, чтобы найти
каждая ячейка с символом @.

 Sub Copy_To_Another_Sheet_1 ()
    Dim FirstAddress As String
    Dim MyArr как вариант
    Dim Rng As Range
    Dim Rcount As Long
    Тусклый я пока
    Dim NewSh как рабочий лист

    С приложением
        .ScreenUpdating = False
        .EnableEvents = Ложь
    Конец с

    'Введите значение поиска
    MyArr = массив ("@")

    'Вы также можете использовать больше значений в массиве
    'myArr = Array ("@", "www")

    'Добавьте новый лист в свою книгу, чтобы скопировать в
    'Вы также можете использовать существующий лист, как этот
    'Установить NewSh = Sheets ("Sheet2")
    Установите NewSh = Worksheets.Add

    С листами ("Лист1"). Диапазон ("A1: Z100")

        Rcount = 0

        Для I = LBound (MyArr) To UBound (MyArr)

            'Если вы используете LookIn: = xlValues, он также будет работать с
            'ячейка формулы, значение которой равно "@"
            'Примечание: в этом примере я использую xlPart, а не xlWhole
            Установите Rng =.Найдите (What: = MyArr (I), _
                            После: =. Cells (.Cells.Count), _
                            LookIn: = xlFormulas, _
                            LookAt: = xlPart, _
                            SearchOrder: = xlByRows, _
                            SearchDirection: = xlNext, _
                            MatchCase: = False)
            Если не Rng, то ничего
                FirstAddress = Rng.Address
                Делать
                    Rcount = Rcount + 1

                    Rng.Copy NewSh.Диапазон ("A" и Rcount)

                    'Используйте это, если вы хотите скопировать только значение
                    'NewSh.Range ("A" & Rcount) .Value = Rng.Value

                    Установить Rng = .FindNext (Rng)
                Цикл пока не звонит - это ничего и Rng.Address <> FirstAddress
            Конец, если
        Далее я
    Конец с

    С приложением
        .ScreenUpdating = Истина
        .EnableEvents = Истина
    Конец с
Концевой переводник 

Дополнительная информация

Если вы хотите заменить только значения на своем листе, вы можете использовать
Заменить вручную (Ctrl + h) или использовать Заменить в VBA.Приведенный ниже код заменяет ron
для Дэйва на всем листе. Измените xlPart на xlWhole, если хотите
заменить ячейки только ron.

 ActiveSheet.Cells.Replace What: = "ron", Замена: = "dave", LookAt: = xlPart, _
                              SearchOrder: = xlByRows, MatchCase: = False, _
                              SearchFormat: = False, ReplaceFormat: = False 

См. Также сайт Чипа Пирсона по теме
http://www.cpearson.com/excel/FindAll.aspx

Как объединить файлы и таблицы Excel

Иногда нужные данные Microsoft Excel разбиваются на несколько листов или даже файлов. Может быть намного удобнее хранить всю эту информацию в одном документе.

В крайнем случае, можно скопировать и вставить различные требуемые ячейки, поместив их все на один лист.Однако, в зависимости от того, с каким объемом данных вы работаете, это может занять много времени и усилий.

Вместо этого подумайте о более умных способах решения той же задачи.Эти методы могут просто позволить вам пропустить часть работы, когда дело доходит до объединения листов или файлов в Excel.

Как объединить листы в Excel

Excel позволяет легко объединить более одного листа в новую книгу:

1. Откройте листы, которые хотите объединить.
2. Щелкните Главная > Формат > Переместить или скопировать лист .
3. Используйте раскрывающееся меню, чтобы выбрать (новая книга) .
4. Нажмите ОК .

Объединить таблицы Excel в один файл

Самый простой способ объединить листы в Excel — использовать команду «Переместить» или «Копировать лист ».Этот метод имеет свои ограничения, но он быстрый и простой.

Сначала откройте листы, которые вы хотите объединить в одну книгу.Оттуда:

1. Направляйтесь в Дом
2. Нажмите Формат
3. Выберите Переместить или скопировать лист

Вы должны увидеть элементы управления, указывающие, куда переместить выбранные листы и порядок этих листов.

В раскрывающемся списке выберите (новая книга) .Это будет основная электронная таблица, куда вы отправите все свои отдельные листы. Вы можете использовать поле До листа , чтобы указать порядок расположения листов.

Повторите этот процесс с остальными листами, которые хотите объединить.Затем сохраните новый составной документ.

Чтобы разделить большую электронную таблицу Excel на отдельные файлы, ознакомьтесь с нашим руководством.

Объединить данные Excel на один лист

Иногда вам может потребоваться взять несколько наборов данных и представить их на одном листе.Это довольно легко сделать в Excel, если вы заранее позаботитесь о том, чтобы ваши данные были правильно отформатированы.

Для правильной работы этого процесса есть два важных условия.Во-первых, объединяемые листы должны иметь одинаковый макет, с одинаковыми заголовками и типами данных. Во-вторых, не может быть пустых строк или столбцов.

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

На этом новом листе перейдите на вкладку Data и щелкните Consolidate .В раскрывающемся списке выберите Сумма , а затем используйте кнопку в поле Ссылка , чтобы получить доступ к своей электронной таблице, чтобы вы могли выбрать нужные данные.

Чтобы добавить следующий набор данных, щелкните Добавить и затем таким же образом выберите данные.Сделайте это для всех наборов данных, которые вы хотите объединить. Вы даже можете рисовать из других книг, используя кнопку Обзор , которая соответствует Выбрать в версии Excel для Mac.

Установите флажок Создать ссылки на исходные данные , если вы собираетесь продолжать обновлять данные на других листах и ​​хотите, чтобы этот лист отражал это.Вы также можете выбрать, какие метки переносятся, с помощью флажков Использовать метки в , как показано выше.

Наконец, нажмите ОК .

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

Объедините файлы Excel с VBA

Если вы хотите объединить листы из нескольких книг в одно мгновение, лучший способ — написать простой макрос VBA.Это будет особенно удобно, если вы будете выполнять эту задачу регулярно.

Во-первых, убедитесь, что все файлы, которые вы хотите объединить, находятся в одной папке на вашем компьютере.Затем создайте новую таблицу Excel, которая объединит их всех.

Перейдите на вкладку Developer и выберите Visual Basic .Щелкните Вставка> Модуль .

Скопируйте и вставьте следующий код, взятый из руководства ExtendOffice:

  Sub GetSheets () 
 Path = "C: \ [ПУТЬ К ФАЙЛАМ]" 
 Имя файла = Dir (Path & "*.xls ") 
 Выполнить, пока имя файла <>" "
 Workbooks.Open Filename: = Path & Filename, ReadOnly: = True 
 For Every Sheet In ActiveWorkbook.Sheets 
 Sheet.Copy After: = ThisWorkbook.Sheets (1) 
 Next Sheet 
 Workbooks (Filename) .Close 
 Filename = Dir () 
 Loop 
 End Sub  

Не забудьте изменить путь к месту хранения файлов на вашем компьютере.

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

Будьте осторожны перед объединением данных Excel

Объединение листов и файлов в Excel может быть довольно сложным и беспорядочным.Это проливает свет на один из самых важных уроков о Microsoft Excel: всегда хорошо планировать заранее.

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

Excel отлично подходит для создания документов, к которым вы можете обращаться и использовать в течение длительного периода времени, но решения, принятые на раннем этапе, могут вызвать или предотвратить проблемы позже.Чтобы получить дополнительную помощь по Excel, ознакомьтесь с тем, как объединить два столбца Excel, как зафиксировать или заблокировать данные Excel, а также в нашем руководстве для начинающих по Microsoft Excel.

Как управлять Центром обновления Windows в Windows 10

Центр обновления Windows постоянно меняется в Windows 10.Прошли те времена, когда пользователи могли блокировать исправления безопасности и обновления драйверов. Корпорация Майкрософт упростила и автоматизирует процесс обновления Windows, как ожидается …

Об авторе

Sandy Writtenhouse
(Опубликовано 462 статей)

Имея степень бакалавра информационных технологий, Сэнди много лет проработала в ИТ-индустрии в качестве менеджера проекта, руководителя отдела и руководителя PMO.Затем она решила следовать своей мечте и теперь постоянно пишет о технологиях.

Ещё от Sandy Writtenhouse

Подпишитесь на нашу рассылку новостей

Подпишитесь на нашу рассылку технических советов, обзоров, бесплатных электронных книг и эксклюзивных предложений!

Еще один шаг…!

Подтвердите свой адрес электронной почты в письме, которое мы вам только что отправили.