Vba excel activeworkbook

Объекты Excel

Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets), строки (Rows), столбцы (Columns), диапазоны ячеек (Ranges) и сама рабочая книга Excel (Workbook) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.

Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.

В Excel VBA существует особый тип объектов – коллекция. Как можно догадаться из названия, коллекция ссылается на группу (или коллекцию) объектов Excel. Например, коллекция Rows – это объект, содержащий все строки рабочего листа.

Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks, который является коллекцией всех открытых в данный момент рабочих книг. Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги. Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.

В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).

Из объекта Workbook можно получить доступ к объекту Sheets, который является коллекцией всех листов рабочей книги (рабочие листы и диаграммы), а также к объекту Worksheets, который представляет из себя коллекцию всех рабочих листов книги Excel.

Кроме этого Вы можете использовать ActiveSheet для доступа к активному в данный момент рабочему листу. Из объекта Worksheet можно получить доступ к объектам Rows и Columns, которые являются коллекцией объектов Range, ссылающихся на строки и столбцы рабочего листа. А также можно получить доступ к отдельной ячейке или к любому диапазону смежных ячеек на рабочем листе.

Доступ к диапазону, состоящему из единственной ячейки, может быть осуществлён через объект Worksheet при помощи свойства Cells, например, Worksheet.Cells(1,1).

По-другому ссылку на диапазон можно записать, указав адреса начальной и конечной ячеек. Их можно записать через двоеточие или через запятую. Например, Worksheet.Range(“A1:B10”) или Worksheet.Range(“A1”, “B10”) или Worksheet.Range(Cells(1,1), Cells(10,2)).

Обратите внимание, если в адресе Range вторая ячейка не указана (например, Worksheet.Range(“A1”) или Worksheet.Range(Cells(1,1)), то будет выбран диапазон, состоящий из единственной ячейки.

Приведённая выше таблица показывает, как выполняется доступ к объектам Excel через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:

Присваивание объекта переменной

В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set:

Активный объект

В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet, активный объект Range и так далее.

Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet, а на активный объект Range – как на Selection.

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

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

Смена активного объекта

Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы Activate или Select вот таким образом:

Методы объектов, в том числе использованные только что методы Activate или Select, далее будут рассмотрены более подробно.

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

Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других. Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name. Таким образом, чтобы присвоить переменной wbName имя активной рабочей книги, можно использовать вот такой код:

Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:

Это возможно потому, что коллекция Worksheets является свойством объекта Workbook.

Некоторые свойства объекта доступны только для чтения, то есть их значения пользователь изменять не может. В то же время существуют свойства, которым можно присваивать различные значения. Например, чтобы изменить название активного листа на “Мой рабочий лист“, достаточно присвоить это имя свойству Name активного листа, вот так:

Методы объектов

Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate, Close, Save и ещё множество других.

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

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

Чтобы передать методу аргументы, необходимо записать после вызова метода значения этих аргументов через запятую. Например, если нужно сохранить активную рабочую книгу как файл .csv с именем “Книга2”, то нужно вызвать метод SaveAs объекта Workbook и передать аргументу Filename значение Книга2, а аргументу FileFormat – значение xlCSV:

Чтобы сделать код более читаемым, при вызове метода можно использовать именованные аргументы. В этом случае сначала записывают имя аргумента, затем оператор присваивания “:=” и после него указывают значение. Таким образом, приведённый выше пример вызова метода SaveAs объекта Workbook можно записать по-другому:

В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2.

Рассмотрим несколько примеров

Пример 1

Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each. В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet.

Пример 2

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

В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial. Этот метод передаёт аргументу Paste значение xlPasteValues.

Пример 3

Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet. Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set, при помощи которого объект Range присваивается переменной Col.

Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.

Макрос сохранения листа Excel в файл

Данный макрос позволяет упростить процедуру сохранения активного листа в книге Excel в отдельный файл.

Для использования этого макроса на любом листе в книге Excel создайте кнопку, и назначьте ей макрос СохранитьЛистВФайл.

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

Сохранение производится в формате XLS (формат Excel 2003)
Если пользователь отказался от ввода имени файла (нажал клавишу ESC или кнопку «Отмена» в диалоговом окне),
то сохранения листа в файл не происходит.

PS: Кто-то может сказать, что для сохранения листа в файл в объектной модели Excel есть метод SaveAs, применимый к объекту Worksheet.

Но, как ни странно, выполнение кода ActiveSheet.SaveAs » « приводит к сохранению книги целиком, что равносильно использованию кода ActiveWorkbook.SaveAs » «

Почему этот метод сохранения работает так нелогично — лично мне не понятно (видимо, Microsoft что-то там перемудрил)

• 150834 просмотра

Комментарии

Спасибо. Разобрался. Нашёл ошибки в библиотеках.)

Спасибо. Всё запустил, но не работает. Пишет не найден проект или библиотека. Простите, не уточнил, поменял ещё саму систему (был XP стал Win7 x64).
Вот мой код:
Private Sub CommandButton2_Click()
On Error Resume Next
Const REPORTS_FOLDER = «C:Users. . . »
MkDir ThisWorkbook.Path & «» & REPORTS_FOLDER
ChDrive Left(ThisWorkbook.Path, 1): ChDir ThisWorkbook.Path & «» & REPORTS_FOLDER
FileName = [b8] & «_» & [b6] & «_» & [b4] & «_» & Format([b2], «DDMMMMYY») & «_» & [c2] & «.xlsx»
Err.Clear: Worksheets(Array(. «, «. «)).Copy: DoEvents
If Err Then Exit Sub
If ActiveWorkbook.Worksheets.Count = 2 And ActiveWorkbook.Path = «» Then
ActiveWorkbook.SaveAs FileName, xlWorkbookNormal
ActiveWorkbook.Close False
End If
End Sub

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

> найти файл с макросами в папке
> щелкнуть правой кнопкой мыши на файле — Свойства — Разблокировать — ОК
> и только после этого запускать

Перешёл с 2007 на Office 2016, перестали работать макросы. И этот. (((

Добрый день, подскажите, пожалуйста, что надо изменить в макросе, чтобы:
1. Он копировал не весь лист целеком, а только диапазон ВИДИМЫХ ячеек (A1:L50), т.к. этот диапазон только часть отфильтрованного списка.
2. Он копировал только значения, без формул ячеек.

Здравствуйте, Алексей
Да, можно такое сделать, — могу написать макрос под заказ.

Здравствуйте, Игорь.
Подскажите, пожалуйста, возможно ли такое, чтобы Лист можно было сохранять не единожды (по имени в одной ячейке) а сославшись на какой-либо диапазон ячеек. Есть потребность сделать в конкретной папке количество файлов соответствующее количеству дней в месяце(отчет на каждый день). Если в диапазоне ячеек указать даты месяца и по нажатию макрос сохранял бы, файлы с именем Даты.

Можете написать макрос под заказ? Мне надо до понедельника

Напишите ваши контакты, есть несколько задач.

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

Дело в том, что в книге порядка 20-30 листов и каждый лист необходимо сохранить в отдельные папки, соответствующие имени листа.

Дмитрий, а куда уж проще-то. вы нажимаете одну кнопку «Печать», и получаете готовый файл JPG
Зачем ещё-то упрощать.
Можно, конечно, и макрос под заказ написать
Если в формат BMP (или EMF) картинку сохранять — то макрос несложный, если в формат JPG — то макрос сложнее будет (и дороже)
Но я бы на вашем месте не стал изобретать велосипед, а оставил бы все как есть.

Сохраняю листы в JPEG формате, при помощи виртуального принтера «universal document converter» возможно ли упростить процедуру сохранения активного листа?

Здравствуйте, Игорь. Будьте любезны, взгляните на код. Основная часть была взята с другого сайта, часть с Вашего. Суть такая: на первом листе исходные данные и кнопка, на третьем — расчеты с формулами. Необходимо чтобы при нажатии кнопки создавалась папка «Двери» в текущей папке, где лежит этот файл (а если она есть, то сохранялось в нее), а имя файла бралось с листа 1 из ячеек a17 & b17. Сохраняться должен третий лист (он скрыт), формулы на нем заменить на значения. После сохранения новая книга закрывается и выводится сообщение об удачном сохранении файла с именем из ячеек a17 & b17. Сам я методом тыка пытался воплотить это, но лист не хочет сохраняться с указанным именем (используется имя по умолчанию «Книга 2. Книга 3. ). Также непонятно мне как сделать чтобы выскакивало сообщение о результате сохранения. Подскажите пожалуйста.

On Error Resume Next
Const REPORTS_FOLDER = «Двери»
‘ название подпапки, в которую по-умолчанию будет предложено сохранить файл

MkDir ThisWorkbook.Path & «» & REPORTS_FOLDER
‘ создаём папку для файла, если её ещё нет

ChDrive Left(ThisWorkbook.Path, 1): ChDir ThisWorkbook.Path & «» & REPORTS_FOLDER
‘ выбираем стартовую папку

Filename = Range(«a17») & («b17») & «.xls»
‘ вывод диалогового окна для запроса имени сохраняемого файла

If VarType(Filename) = vbBoolean Then Exit Sub
‘ если пользователь отказался от выбора имени файла — отменяем сохранение листа в файл

Dim Ar(), ArAll&(), Sh As Excel.Worksheet, n

Select Case Sheets(1).[Условие]
Case 1
Ar = Array(3)
Case Else
End Select

ReDim Preserve ArAll(0 To ThisWorkbook.Worksheets.Count — 1)
For Each Sh In ThisWorkbook.Worksheets
ArAll(n) = Sh.Index
n = n + 1
Next
ThisWorkbook.Worksheets(ArAll).Copy
Application.Volatile
Application.Calculate
Application.ScreenUpdating = False
For Each n In Ar
With ActiveWorkbook.Worksheets(n).UsedRange.Cells
.Value = .Value
End With
Next
Erase ArAll: n = 0
ReDim Preserve ArAll(0 To ThisWorkbook.Worksheets.Count — 1 — (UBound(Ar) + 1))
For Each Sh In ActiveWorkbook.Worksheets
If IsError(Application.Match(Sh.Index, Ar, 0)) Then
ArAll(n) = Sh.Index
n = n + 1
Else: If Sh.Visible = False Then Sh.Visible = True
End If
Next
ActiveWorkbook.Sheets(Ar(0)).Activate
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets(ArAll).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Dialogs(xlDialogSaveAs).Show

ActiveWorkbook.Close False
End Sub

Dim WB As Workbook
Dim ind As Integer
ind = ActiveSheet.Index
Application.DisplayAlerts = False
Set WB = Workbooks.Add
Dim li As Long
Application.DisplayAlerts = False
ind = 3
For n = ind To 2 Step -1
ThisWorkbook.Sheets(n).Copy Before:=WB.Sheets(1)
Next

pdfFilename = Application.DefaultFilePath & Application.PathSeparator & «имя файла» & Range(«D9»).Value & «.pdf»
WB.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=pdfFilename, OpenAfterPublish:=False

При этом файл создается с названием «имя файла.pdf» начисто игнорируя содержимое ячейки указанной.

Вот макрос, который сохраняет без ограничения 255-ти символов в ячейке:

11.4 Коллекция Workbooks и объект Workbook, их свойства и методы

Объект Excel.Workbook, программная работа с книгами Excel из VBA, создание и открытие книг Excel

Следующий по иерархии после Application объект в объектной модели Excel — это объект Workbook, который представляет книгу Excel. Можно сказать, что объект Workbook занимает в Excel примерно то же место, что и объект Document в Word — он нужен для получения ссылки на нужную нам книгу в наборе открытых книг Excel, а также для настройки общих свойств и выполнения общих действий со всеми листами книги. Получить этот объект можно очень просто:

• первый способ — воспользоваться коллекцией Workbooks, которая доступна через свойство Workbooks объекта Application. Впрочем, применять это свойство совершенно не обязательно — коллекция Workbooks в Excel и так постоянно доступна. Найти нужную книгу в этой коллекции можно по ее имени или номеру в коллекции:

• второй способ — использовать свойство Application.ActiveWorkbook. При помощи этого свойства мы обращаемся к активной в настоящей момент книге:

• третий способ — использовать свойство Application.ThisWorkbook. При этом мы обращаемся к той книге, которой принадлежит данный программный модуль:

На практике чаще всего нам нужно либо создать в Excel новую книгу, либо открыть существующую книгу (или другой файл в формате, который понимает Excel, например, DBF). Для этой цели используются методы Add() и Open() соответственно. Например, создать новую книгу в Excel можно так:

Dim oWbk As Workbook

Set oWbk = Workbooks.Add()

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

Открытие существующей книги выглядит так:

Dim oWbk As Workbook

Set oWbk = WorkBooks.Open(«C:mybook1.xls»)

Помимо стандартных, в коллекции Workbooks предусмотрено также три специальных метода:

• OpenDatabase() — открыть базу данных, выполнить к ней запрос (или открыть таблицу/представление напрямую), а результаты запроса поместить как импортированные внешние данные в новую автоматически созданную рабочую книгу Excel;
• OpenText() — почти то же самое, но в качестве источника здесь выступает текстовый файл. Дополнительные параметры позволяют определять его формат.
• OpenXML() — в качестве источника данных будет выступать файл в формате XML.

Как и метод InsertDatabase() в Word, эти методы следует использовать только в самых простых случаях. Рекомендуется по возможности использовать более мощные и стандартные средства объектной модели ADO.

Теперь о самых важных свойствах объекта Workbook — самой рабочей книги:

• Name, CodeName, FullName — разные имена этой книги. Самое простое имя — Name, это имя совпадает с именем файла книги. FullName — это имя файла книги вместе с полным путем к нему в операционной системе. CodeName — как эта книга будет называться в коде. CodeName можно посмотреть в окне Project Explorer или, если открыть свойства книги в окне Properties, кодовое имя книги будет представлено в строке (Name). Все три свойства доступны только для чтения, менять их можно другими способами (например, сохраняя файл под другим именем или прямо в окне Properties).

Определенное отношение к именам имеет также свойство Path (путь к файлу книги) .

• Charts, Sheets, ActiveChart, ActiveSheet, CustomViews, BuiltinDocumentProperties и CustomDocumentProperties, Windows, WebOptions возвращают одноименные коллекции соответствующих объектов. Некоторые из этих объектов будут рассматриваться ниже.
• ConflictResolution — как будут разрешаться конфликты изменения данных, если книга открыта несколькими пользователями сразу (shared workbook). Есть возможность сделать так, чтобы локальный пользователь автоматически выигрывал, автоматически проигрывал или возникало диалоговое окно с возможностью разобраться в конфликте вручную. Существует большое количество свойств, которые позволяют настроить параметры совместной работы с книгой, но по причине того, что такая работа не рекомендуется (данные для совместного доступа необходимо переносить в базу данных), рассматриваться они здесь не будут, за исключением:
  • запрещать/разрешать общий доступ к рабочей книге можно при помощи методов SaveAs() или ExclusiveAccess();
  • по умолчанию возможность совместного редактирования для книги отключена (проверить можно при помощи свойства MultiUserEditing);
  • получить список всех пользователей (а также когда они открыли файл и в каком режиме) можно при помощи свойства UserStatus.
• FileFormat — формат книги (доступен напрямую только для чтения, можно изменять при сохранении). Форматов очень много: множество версий Excel, DBF, Lotus 1-2-3, форматы TXT, CSV, XML — всего несколько десятков.
• свойство Names возвращает коллекцию всех именованных диапазонов в данной рабочей книге. Получить информацию о всех именованных диапазонах в данной книге можно, например, так:

For Each Item In ThisWorkbook.Names

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

Методов у объекта Workbook также очень много, однако значения самых употребимых — Activate(), Close(), Save(), SaveAs(), PrintOut(), Protect() и Unprotect() очевидны и действуют аналогично одноименным методам объекта Document в Word.

Vba excel activeworkbook

По материалам эхоконференции RU.EXCEL (за июль-сентябрь 1997 года) Collected by Kirienko Andrew, 2:5020/239.21@fidonet

Cодержание

Как определить последнюю запись в таблице Excel?

Q: Необходимо найти последнюю запись вэлектронной таблице. Какой функцией VB это можно было бы организовать.

A: Первое что вспомнилось: Application.SpecialCells(xlLastCell)

Как отменить выделение диапазона ячеек?

Q: Как управиться с такой болячкой:

После прекращения работы макроса диапазон остается выделенным. Как это выделение убрать?

A: Попробуй вот как: Selection.Cells(1).Select Фокус ввода попадёт после этого на первую ячейку ранее выделенного диапазона.

Как из макроса Excel программно создать таблицу Access?

Q: Подскажите, пожалуйста, как из под Excel программно создать таблицу Access

A: Вот фрагмент кода, который создаёт таблицу «BalanceShifr» базе данных MS Access:

Нint: Не забудьте выставить в Excel ссылки на объекты DAO!
[VBA] Tools/References/Available References/ [x] MicroSoft DAO. Library ‘ Function CreateTable ‘ Create temporary table «BalanceShifr» into temporary database

Public Function CreateTable(ByVal dbTemp As Database) As Boolean

Dim tdfTemр As TableDef
Dim idx As Index
Dim fld As Field

On Error GoTo errhandle

CreateTable = True
‘ CREATE TABLE «BalanceShifr»
Set tdfTemp = dbTemp.CreateTableDef(«BalanceShifr»)
Set fld = tdfTemp.CreateField(«ConditionId», dbLong)
fld.Required = True
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«Account», dbText, 4)
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«SubAcc», dbText, 4)
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«Shifr», dbLong)
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«Date», dbDate)
fld.Required = True
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«SaldoDeb», dbCurrency)
tdfTemp.Fields.Append fld
Set fld = tdfTemp.CreateField(«SaldoKr», dbCurrency)
tdfTemp.Fields.Append fld
dbTemp.TableDefs.Append tdfTemp

‘ CREATE INDEX «BalanceShifr»
Set tdfTemp = dbTemp.TableDefs(«BalanceShifr»)
Set idx = tdfTemp.CreateIndex(«ForeignKey»)
Set fld = idx.CreateField(«ConditionId»)
idx.Fields.Append fld
tdfTemp.Indexes.Append idx
Exit Function

errHandle:
MsgBox «Table creating error!», vbExclamation, «Error»
CreateTable = False
End Function

Удаление листов в зависимости от даты

Q: Как удалить рабочие листы листов в зависимости от даты?

A: Вот код функции на Excel VBA, который решает данную проблему:

‘ Function DelSheetByDate
‘ Удаляет рабочий лист sSheetName в активной рабочей книге,
‘ если дата dDelDate уже наступила
‘ В случае успеха возвращает True, иначе — False

Public Function DelSheetByDate(sSheetName As String, _
dDelDate As Date) As Boolean
On Error GoTo errHandle

DelSheetByDate = False
‘ Проверка даты
If dDelDate

Подавление «горячих» клавиш.

Q:Как подавить доступ по «горячим» клавишам, имеется ввиду предопределенные в Excel клавиши типа Ctrl-O и т.д.?

A:Вот малюсенький исходник на Excel VB, который решает такую проблему. 🙂

Public Sub Auto_Open()
‘ Overrride standard accelerators
With Application
.OnKey «^o», «Dummy»
.OnKey «^s», «NewAction»
.OnKey «^р», «» ‘ Kill hotkey !
End With
End Sub

‘ ——
Public Sub Dummy()
MsgBox «This hotkey redefined!»
End Sub

‘ ——
Public Sub NewAction()
SendKeys «^n» ‘ Press + for create new file
‘ instead of + !
End Sub

Hint: Отлажено в MS Excel ’97 !

Подсказки к Toolbar

Q: Как сделать к «само нарисованным» кнопочкам на Toolbar’е подсказки? (Ну, те, что после 2-х секунд молчания мышки появляются)

A: Сделать можно вот как: (Пример реализации на Excel’97 VBA )

‘ Cоздаем тулбар
Рublic Sub InitToolBar()
Dim cmdbarSM As CommandBar
Dim ctlNewBtn As CommandBarButton

Set cmdbarSM = CommandBars.Add(Name:=»MyToolBar»,
Position:=msoBarFloating, _
temporary:=True)
With cmdbarSM
‘ 1) Добавляем кнопку
Set ctlNewBtn = .Controls.Add(Type:=msoControlButton)
With ctlNewBtn
. FaceId = 26
.OnAction = «OnButton1_Click»
.TooltipText = «My tooltip message!»
End With
‘ 2) Добавляем ещё кнопку
Set ctlNewBtn = .Controls.Add(Type:=msoControlButton)
With ctlNewBtn
.FaceId = 44
.OnAction = «OnButton2_Click»
.TooltipText = «Another tooltip message!»
End With
.Visible = True
End With
End Sub

Hint: На VBA для Excel’95 это делается несколько иначе!

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

Q: Как в макросе узнать и использовать текущее положение курсора (не мышиного, естественно)?

A: Очень просто! 🙂
ActiveCell.Row и ActiveCell.Column — покажут координаты активной ячейки.

Подсчет комментариев на рабочем листе

Q: Как узнать есть ли хоть один Notes (комментарий) в рабочем листе, кроме как перебором по всем ячейкам? . Без этого не работает:

A: В Excel’97 эта проблема может быть решена вот как:

‘ Function IsCommentsPresent
‘ Возвращает TRUE, если на активном рабочем листе имеется хотя бы
‘ одна ячейка с комментарием, иначе возвращает FALSE
‘
Public Function IsCommentsPresent() As Boolean
IsCommentsPresent = ( ActiveSheet.Comments.Count <> 0 )
End Function

Подсказки к Toolbar (Excel’95)

Q: Как сделать свой собственный Toolbar с tooltip’ами на кнопках в Excel’95?

A: Вот фрагмент кода для Excel’95, который создаёт toolbar с одной кнопкой с пользовательским tooltiр’ом. Нажатие кнопки приводит к выполнению макроса NothingToDo() .

‘
‘ This example creates a new toolbar, adds the Camera button
‘ (button index number 228) to it, and then displays the new toolbar.
‘
Public Sub CreateMyToolBar()
Dim myNewToolbar As Toolbar
On Error GoTo errHandle:

Set myNewToolbar = Toolbars.Add(Name:=»My New Toolbar»)
With myNewToolbar
.ToolbarButtons.Add Button:=228, StatusBar:=»Statusbar help string»
.Visible = True
With .ToolbarButtons(1)
.OnAction = «NothingToDo»
.Name = «My custom tooltiр text!»
End With
End With
Exit Sub
errНandle:
MsgBox «Error number » & Err & «: » & Error(Err)
End Sub

‘
‘ Toolbar button on action code
‘
Рublic Sub NothingToDo()
MsgBox «Nothing to do!», vbInformation, «Macro running»
End Sub

Нint: В Excel’97 этот код тоже работает!

Запуск Excel с поиском ячейки

Q: Как запустить Excel, чтобы оказаться на ячейке содержимое которой известно заранее?

A:Вот как я решил бы твою задачу:

‘ Sub GotoFixedCell:
‘ Делает активной ячейку, содержащую значение vVariant на
‘ рабочем листе sSheetName в активной рабочей книге.
‘
‘ Note: Содержимое ячеек интерпретируется как ‘значение’!
‘
Public Sub GotoFixedCell(vValue As Variant, sSheetName As String)
Dim c As Range, cStart As Range, cForFind As Range
Dim i As Integer

On Error GoTo errhandle:

Set cForFind = Worksheets(sSheetName).Cells ‘ Диапазон поиска
With cForFind
Set c = .Find(What:=vValue, After:=ActiveCell, LookIn:=xlValues, _
LookAt:= xlРart, SearchOrder:=xlByRows,_
SearchDirection:=xlNext, MatchCase:=False)
Set cStart = c
While Not c Is Nothing
Set c = .FindNext(c)
If c.Address = cStart.Address Then
c.Select
Exit Sub
End If
Wend
End With
Exit Sub
errНandle:
MsgBox Err.Descriрtion, vbExclamation, «Error #» & Err.Number
End Sub

Нint: Достаточно выполнить этот код из макроса Auto_Oрen()!

Нint: Протестировано и отлажено в Excel’97.

ThisWorkBook или ActiveWorkBook?

Q: На листе модулей открытой рабочей книги присутствует процедура, которая копирует некий лист из другой (не активной) рабочей книги. В этом листе в некоторых ячейках находятся определенные пользователем формулы. Процедура работает без проблем.
Из workbook, содержащей эту процедуру, я делаю надстройку (.xla) и подключаю ее к Excel 95. При вызове вышеописанной процедуры она выдает сообщение:
Run time error 424 object required
Kак можно избежать это сообщение?

A:Вот что я тебе посоветую:
Посмотри ещё разок код модулей рабочей книги и исправь все ссылки вида ActiveWorkbook.WorkSheets(«.. на ссылки вида ThisWorkBook.WorkSheets(«..

Дело в том, что когда выполняется код надстройки активной книгой в Excel’е является _не_ сама надстройка! Конструкция ThisWorkbook позволяет сослаться на книгу, в которой в настоящий момент выполняется код Excel VBA.

Нint: Это общий принцип создание надстроек Excel!

Как задать имя листу, который будет вставлен?

Q:Хочy через Excel VBA задать имя листу, который будет вставлен. Но у команды Sheets.Add нет такого параметра ! Как бороться?

A: Очень просто.
‘
‘ Sub CreateSheet
‘ Вставляет активную рабочую книгу в рабочий лист с именем sSName.
‘ Note: Если параметр bVisible имеет значение False, этот лист становится скрытым.
‘
Рublic Sub CreateSheet(sSName As String, bVisible As Boolean)
Dim wsNewSheet As WorkSheet

On Error GoTo errНandle

Set wsNewSheet = ActiveWorkBook.Worksheets.Add
With wsNewSheet
.Name = sSName
.Visible = bVisible
End With
Exit Sub
errНandle:
MsgBox Err.Descriрtion, vbExclamation, «Error #» & Err.Number
End Sub

Как проверить существует ли лист?

Q: А как проверить существует ли лист?

A: Я бы поступил вот как:

‘ Function IsWorkSheetExist
‘ Проверяет, имеется ли в активной рабочей книге лист с именем sSName.
‘ В случае успеха возвращает True, иначе — False
‘
Рublic Function IsWorkSheetExist(sSName As String) As Boolean
Dim c As Object

On Error GoTo errНandle:
Set c = sheets(sName)
‘ Альтернативный вариант :
Worksheets(sSName).Cells(1, 1) = Worksheets(sSName).Cells(1, 1)
IsWorkSheetExist = True
Exit Function
errНandle:
IsWorkSheetExist = False
End Function

Нint: Отлажено и протестировано в Excel’97.

Как обратиться к ячейке по ее имени?

Q: Как обратиться к ячейки по ее имени? Т.е. есть Лист1 и в нем ячейки с именем Дебет и Кредит. Хочy подсчитать Дебет-Кредит средствами Excel VBA. Попробовал Range(Дебет)-Range(Кредит), ругается, что не описаны переменные.

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

‘ Function ValueOfNamedCell
‘ Возвращает значение ячейки с именем sCellName. в активной рабочей книге.
‘ Note: Если ячейка с именем sCellName не существует — функцией возвращается
‘ значение Emрty.
‘
Рublic Function ValueOfNamedCell(sCellName As String) As Variant
On Error GoTo errНandle
ValueOfNamedCell = ActiveWorkbook.Names(sCellName).RefersToRange.Value
Exit Function
errНandle:
ValueOfNamedCell = Emрty
End Function

Нint: Отлажено и протестировано в Excel’97.

Можно ли из программы на Visual Basic создать рабочую книгу Excel?

Q: Можно ли из программы на Visual Basic создать рабочую книгу Excel?

Пример того, как из Visual Basic’a через OLE запустить Excel, и создать рабочую книгу.

‘ CreateXlBook
‘ Вызывает MS Excel, создает рабочую книгу с именем sWbName с одним
‘ единственным рабочим листом. Рабочая книга будет сохранена в каталоге
‘ sDirName. В случае успеха возвращает True, в противном случае — False.
‘
Public Function CreateXlBook(sWbName As String, sDirName) As Boolean

‘ MS Excel hidden instance
Dim objXLApp As Object
Dim objWbNewBook As Object

Set objXLApp = CreateObject(«Excel.Application»)
If objXLApp Is Nothing Then Exit Function

‘ В новой рабочей книге создавать только один рабочий лист
objXLApp.SheetsInNewWorkbook = 1

Set objWbNewBook = objXLApp.Workbooks.Add
If objWbNewBook Is Nothing Then Exit Function

‘ Сохраняем книгу
If vbNullString = Dir(sDirName, vbDirectory) Then Exit Function

objWbNewBook.SaveAs (sDirName + «» + sWbName + «.xls»)
CreateXlBook = True

‘ Освобождение памяти
Set objWbNewBook = Nothing
objXLApp.Quit
Set objXLApp = Nothing
CreateXlBook = True

Hint: Tested and approved with MS Visual Basic 4.0 Enterprise Edition

Select и Activate — зачем нужны и нужны ли?

Все начинающие изучать VBA сталкиваются с тем, что записанные через макрорекордер коды пестрят методами Select и Activate.
Если не знакомы с работой макрорекордера — Что такое макрос и где его искать?
Это значительно ухудшает читабельность кода и, как ни странно — быстродействие. Но есть недостатки и куда более критичные. Если код выполняется достаточно долго и он постоянно что-то выделяет — пользователь может заскучать и забыться и начнет тыкать мышкой по листам и ячейкам, выделяя не то, что выделил ранее код. Что повлечет ошибки логики. Т.е. код может и выполнится, но совершенно не так, как ожидалось. Поэтому избавляться от Select и Activate необходимо везде, где это возможно.

Для начала рассмотрим два кода, выполняющие одни те же действия — запись в ячейку А3 листа Лист2 слова «Привет». При этом сам код запускается с Лист1 и после выполнения код Лист1 должен остаться активным. Чтобы сделать эти действия вручную потребуется сначала перейти на Лист2, выделить ячейку А3, записать в неё слово «Привет» и вернуться на Лист1. Поэтому запись макрорекордером этих действий приведет к такому коду:

Sub Макрос1() Sheets(«Лист2»).Select ‘выделяем Лист2 Range(«A3»).Select ‘выделяем ячейку А3 ActiveCell.FormulaR1C1 = «Привет» ‘записываем слово Привет Range(«A4»).Select ‘после нажатия Enter автоматически выделяется ячейка А4 Sheets(«Лист1»).Select ‘возвращаемся на Лист1 End Sub

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

Sub Макрос1() Sheets(«Лист2»).Range(«A3»).FormulaR1C1 = «Привет» End Sub

Как видно, вместо 5-ти строк кода получилась одна строка. Которая выполняет ту же задачу, что и код из 5-ти строк.
Прежде чем понять как правильно избавляться от лишнего давайте разберемся зачем же тогда VBA записывает эти Select и Activate? Как ни странно, но здесь все очень просто. VBA просто не знает, что Вы будете делать после того, как выделили Лист2. И когда Вы переходите на Лист2 — VBA записывает именно переход(его активацию, выделение). Когда выделяете ячейку — так же именно это действие записывает VBA. Захотите ли Вы затем выделить еще что-то, или закрасить ячейку, или записать в неё формулу/значение — VBA не знает. Поэтому в дальнейшем VBA работает именно с выделенным объектом Selection на активном листе.
Но при написании кода вручную или при правке записанного рекордером мы уже вольны в выборе и знаем, чего хотели добиться и какие действия нам точно не нужны.
Итак, чтобы записать в ячейку слово «Привет» рекордер предложит нам такой код:

Sub Макрос1() Range(«A3»).Select ‘выделяем ячейку А3 ActiveCell.FormulaR1C1 = «Привет» ‘записываем слово Привет Range(«A4»).Select ‘после нажатия Enter автоматически выделяется ячейка А4 End Sub

однако выделять ячейку( Range(«A3»).Select ) совершенно необязательно. Значит один Select уже лишний. После этого идет обращение к активной ячейке — ActiveCell . .FormulaR1C1 = «Привет» означает запись значения «Привет» в эту ячейку.
Пусть не смущает FormulaR1C1 — VBA всегда так указывает запись и значения и формулы. Т.к. перед словом «Привет» нет знака равно — то это значение.
Т.к. ActiveCell является обращением к выделенной ячейке, а выделили мы до этого А3, значит их можно просто «сократить»:

Sub Макрос1() Range(«A3»).FormulaR1C1 = «Привет» Range(«A4»).Select ‘после нажатия Enter автоматически выделяется ячейка А4 End Sub

Теперь у нас код получился короче и понятнее. Однако остался один Select: Range(«A4»).Select . Если нет необходимости выделять ячейку А4 после записи в А3 значения, то надо просто удалить эту строку и после выполнения кода активной будет та ячейка, которая была выделена до выполнения(т.е. выделенная ячейка просто не изменится). Таким образом мы с трех строк сократим код до 1-ой:

Sub Макрос1() Range(«A3»).FormulaR1C1 = «Привет» End Sub

Теперь несложно догадаться, что с листами все в точности так же. Sheets(«Лист2»).Select — Select хоть и не нужен, но и ActiveSheet после него нет. Здесь необходимо знать некоторую иерархию в Excel. Сначала идет сам Excel — Application, потом книга — Workbook. В книгу входят рабочие листы(Worksheets), а уже в листах — ячейки и диапазоны — Range и Cells(Application ->Workbook ->Worksheet ->Range). Если перед Range или Cells не указывать явно лист: Range(«A3»).FormulaR1C1 = «Привет» , то значение будет записано на активный лист. Подробнее можно прочесть в статье: Как обратиться к диапазону из VBA

Маленький нюанс: если сокращаем обращение к объектам, то Select-ов быть не должно вообще. Иначе есть шанс получить ошибку «Subscript out of range»:

буквально это означает, что указанный индекс вне досягаемости. А появляется эта ошибка потому, что нельзя выделить ячейку НЕактивного листа или лист НЕактивной книги. Легко эту ошибку получить например в таком коде:

Sub Макрос2() Windows(«Книга3»).Activate ‘здесь появится ошибка, т.к. пытаемся выделить лист в Книга2 ‘а на данный момент активной является Книга3 Windows(«Книга2»).Sheets(«Лист3»).Select End Sub

Ошибка обязательно появится, т.к. сначала мы активировали кодом книгу «Книга3», а потом пытаемся активировать лист НЕактивной на этот момент книги «Книга2». А это сделать невозможно без активации той книги, в которой активируемый лист. Т.е. активация должна происходить именно последовательно: Книга ->Лист ->Ячейка. И никак иначе, если мы хотим активировать именно конкретную ячейку конкретного листа в конкретной книге.
И пример с ячейками:

Sub Макрос2() Sheets(«Лист3»).Select ‘здесь появится ошибка, т.к. пытаемся выделить ячейку на листе «Лист1» ‘а на данный момент активным является Лист3 Sheets(«Лист1»).Range(«C7»).Select End Sub

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

Еще небольшой пример оптимизации:

Sub Макрос2() Windows(«Книга3»).Activate Sheets(«Лист3»).Select Range(«C7»).Select ActiveCell.FormulaR1C1 = «Привет» Range(«C7»).Select Selection.Font.Bold = True With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub

Этот код записывает в ячейку С7 Лист3 книги «Книга3» слово «Привет», потом делает жирным шрифт и назначает желтый цвет заливке. Убираем активацию книги, листа и ячейки, заменив их прямым обращением: