ГлавнаяРазноеVba excel обращение к листу

Ссылка на листы по номеру индексаRefer to Sheets by Index Number. Vba excel обращение к листу


Ссылка на листы по номеру индекса

  • 06/08/2017
  • Время чтения: 2 мин
  • Соавторы

В этой статье

Индекс — это порядковый номер, назначенный в лист на основании положения его ярлычок листа (начиная с левого края) между листы того же типа.An index number is a sequential number assigned to a sheet, based on the position of its sheet tab (counting from the left) among sheets of the same type. В следующей процедуре используется свойство листы для активации первого листа в книге active.The following procedure uses the Worksheets property to activate the first worksheet in the active workbook.

Sub FirstOne() Worksheets(1).Activate End Sub

Если вы хотите работать со всеми типами листов (таблиц, диаграмм, модули и листах диалогов), используйте свойство стилей .If you want to work with all types of sheets (worksheets, charts, modules, and dialog sheets), use the Sheets property. Следующая процедура активирует лист четыре книги.The following procedure activates sheet four in the workbook.

Sub FourthOne() Sheets(4).Activate End Sub

Примечание Порядок индексов можно изменить, если перемещение, добавление и удаление стилей.Note The index order can change if you move, add, or delete sheets.

msdn.microsoft.com

VBA Excel. Как обратиться к листу/ячейке УЖЕ ОТКРЫТОГО и не сохранённого файла excel?

Память не может быть READ!!! :)

• Excel при его Открытии Вываливает в Виртуальное Пространство ОЗУ свою текущую Информацию. Такова Windows, и таков порядок её Организации.• Excel Создаёт в Temp-Каталоге Файлы Пошагового Изменения. Они нужны, чтобы с помощью Отката Команды (Ctrl+Z) можно было вернуться к предыдущим Состояниям.• Excel каждые 10 (если не ошибаюсь) минут Создаёт в Temp-Каталоге по одной Резервной Копии с текущего Состояния Открытого Документа (Фоновое Автосохранение).• Excel Создаёт в том Месте, откуда Пользователем был Открыт Файл Временный Информационный Файл. В случае Аварийного Завершения Работы (внезапное обесточивание компа). Если при Работе в Excel`е всё пройдёт успешно, и в конце Пользователь/сама Операционная Система Закроет Файл (не важно с Сохранением или без), тогда этот Файл будет Стёрт Excel`ем. Активные Файлы, как и Память (которая не может быть READ) точно также не могут быть READ!!! :)

Следовательно надо, чтобы что-то извне давало бы команду или какую-то Связь (через которую будут Выполняться какие-то Фоновые Задания, или что-то там, что надо Запустить в VBA). Значит надо загодя в сам Стандартный Шаблон умышленно Вставить Код, когда ещё не запущен Excel. Excel всегда Открывает Новый Документ из имеющегося Шаблона.

Только я не знаю наверняка, когда с помощью Excel`я Открываешь уже имеющийся Документ, будет ли он Обращаться к Шаблонам, чтобы посредством их Подстановки Эмулировать что-то нужное!!! Потому что Эмуляцию заготовленных Шаблонов не все Программы и не во всём используют. В AutoCAD`е Эмулируются только Высокоуровневые Шрифты (Системные он вообще не распознаёт). В HTTP вообще Эмулируются только несколько Высокоуровневых Шрифтов. Кстати, их последователи, Android (Операционная Система, Организованная на основе Табличной Разметки) точно также Эмулируют только несколько Высокоуровневых Шрифтов (из них лишь несколько собственных).

https://otvet.mail.ru/answer/1856273737 — о развитии компов и усилении роли SGML-образных Языков.

https://otvet.mail.ru/answer/1855823864 — о развитии Продуктов MS-Office (Excel — всего лишь одна из Программ данного Пакета, изначально привязанная к SGML-образному Языку).

23:05 21.07.2017

PS. Насколько я понимаю, на Активные Файлы в Windows можно влиять через Реестр, Прописав Доступные Действия для Активных Файлов. Расширять возможности Excel`я, наверное, придётся прямо отсюда из «Базы Данных» Windows. Т. е. где-то здесь должна быть лазейка?! ¯\_(°·о) _/¯

23:12 21.07.2017

otvet.mail.ru

Ссылка на листы по имени

  • 06/08/2017
  • Время чтения: 2 мин
  • Соавторы

В этой статье

Листы можно определить по имени с помощью свойств таблицы и диаграммы .You can identify sheets by name using the Worksheets and Charts properties. Следующие операторы активировать различные листы в активной рабочей книги.The following statements activate various sheets in the active workbook.

Worksheets("Sheet1").Activate Charts("Chart1").Activate DialogSheets("Dialog1").Activate

Свойство стилей для возврата электронной таблицы, диаграммы, модуль или диалогового окна листа.You can use the Sheets property to return a worksheet, chart, module, or dialog sheet. Коллекции листов содержит все эти виды стилей.The Sheets collection contains all of these kinds of sheets. Следующий пример активирует лист, с именем «Chart1» в активной рабочей книги.The following example activates the sheet named "Chart1" in the active workbook.

Sub ActivateChart() Sheets("Chart1").Activate End Sub

Примечание Внедренные на листе диаграммы являются участниками семейства ChartObjects , тогда как диаграммы, существующие на собственные листы принадлежит к коллекции диаграмм .Note Charts embedded in a worksheet are members of the ChartObjects collection, whereas charts that exist on their own sheets belong to the Charts collection.

msdn.microsoft.com

обращение к ячейкам диапазону ячеек, vba excel

11.6 Объект Range, его свойства и методы

Объект Excel.Range, программная работа с ячейками и диапазонами (наборами) ячеек средствами VBA, создание объекта Excel.Range, свойства, методы и события объекта Excel.Range

Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист. Если в Word вы могли для ввода данных использовать как объект Range, так и объект Selection, то в Excel все сводится к объекту Range:если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;если вам нужно просто что-то сделать с группой ячеек, первое ваше действие опять-таки получить объект Range, представляющий эту группу ячеек.

В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:самый простой и очевидный способ воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего). Например, получить ссылку на объект Range, представляющий ячейку A1, можно так:

Dim oRange As Range

Set oRange = Worksheets("Лист1").Range("A1")

А на диапазон ячеек с A1 по D10 так:

Dim oRange As Range

Set oRange = Worksheets("Лист1").Range("A1:D10")

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

Set oRange1 = Worksheets("Лист1").Range("C1")

Set oRange2 = oRange1.Range("B1")

oRange2.Value = 20

пропишет значение 20 не в ячейку B1, как можно было понять из кода, а в ячейку D1 (то есть B1 по отношению к виртуальному листу, начинающемуся с C1).второй способ воспользоваться свойством Cells. Возможностей у этого свойства меньше мы можем вернуть диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т.п.). Например, для получения ссылки на ячейку D1 можно использовать код вида:

Dim oRange As Range

Set oRange = Worksheets("Лист1").Cells(1, 4)

Чтобы получить диапазон, состоящий из нескольких ячеек, удобно применять свойства Range и Cells вместе:

Dim oRange

Set oRange = Range(Cells(1, 1), Cells(5, 3))третий способ воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый. Эти свойства будут рассмотрены ниже.

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

oRange.Value = "Мое значение"

Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много (и для комфортной работы в Excel их нужно знать). Ниже представлены некоторые самые употребимые свойства:Address позволяет вернуть адрес текущего диапазона, например, для предыдущего примера вернется $A$1:$C$5. Этому свойству можно передать много параметров для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal то же самое, но с поправкой на особенности локализованных версий Excel.

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

sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") 2))

А номер строки так :

sRowNumber = Mid(oRange.Address, (InStr(2, oRange.Address, "$") + 1))

На первый взгляд кажется сложным, но на самом деле все очень просто для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он у нас всегда первый символ) и вторым, а для номера строки бы берем все, что у нас находится после второго знака доллара. Найти этот второй знак доллара можно при помощи встроенной функции InStr(), а взять нужное количество символов начиная с какого либо проще всего при помощи встроенной функции Mid().AllowEdit это свойство, доступное только для чтения, позволяет определить, сможет ли пользователь править данную ячейку (набор ячеек) на защищенном листе. Используется для проверок.Areas свойство исключительно важное. Дело в том, что, как уже говорилось, объект Range может состоять из несмежных наборов ячеек. Многие методы применительно к таким диапазонам ведут себя совершенно непредсказуемо или просто возвращают ошибки. Свойство Areas позволяет разбить подобные нестандартные диапазоны на набор стандартных. Созданные таким образом объекты Range будут помещены в коллекцию Areas. Это свойство можно использовать и для проверки "нестандартности" диапазона:

If Selection.Areas.Count > 1 Then

Debug.Print "Диапазон с несмежными областями"

End IfBorders возможность получить ссылку на коллекцию Borders, при помощи которой можно управлять рамками для нашего диапазона.Cells это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона:

Dim oRange, oRange2 As Range

Set oRange = Range(Cells(2, 2), Cells(5, 3))

Set oRange2 = oRange.Cells(1, 1) 'Вместо A1 получаем ссылку на B2

Debug.Print oRange2.Address 'Так оно и есть

Точно такие же особенности у свойств Row и Rows, Column и Columns.Characters это простое с виду свойство позволяет решить непростую задачу: как изменить (текст или формат) части текста в ячейке, не затрагивая остальные данные. Например, чтобы ввести текст в ячейку A1 и изменить цвет первой буквы, можно воспользоваться кодом

Dim oRange As Range

Set oRange = Range("A1")

oRange.Value = "Мой текст"

oRange.Characters(1, 1).Font.Color = vbRed

Если же вам просто нужно изменить значение, то лучше воспользоваться свойством Value как в третьей строке примера.Count возвращает количество ячеек в диапазоне. Может использоваться для проверок.CurrentRegion очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (когда сколько будет этих данных, нам изначально неизвестно). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом

Worksheets("Лист1").Activate

ActiveCell.CurrentRegion.SelectDependents позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать кодWorksheets("Лист1").ActivateActiveCell.Dependents.Select

Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использовать свойства DirectDependents и DirectPrecedents.End еще одно часто используемое свойство. Оно позволяет получить объект Range, представляющий последнюю ячейку исходного диапазона. В какой стороне будет считаться последняя ячейка, можно определить при помощи передаваемого параметра.Errors свойство, которое через коллекцию Errors позволяет получить доступ к объектам Error, представляющим обнаруженные ошибки в диапазоне.Font как и в Word, это свойство позволяет получить доступ к объекту Font, при помощи которого можно настроить особенности оформления текста в ячейке (цвет, шрифт, размер букв и т.п.)FormatConditions возможность создать собственный объект, представляющий вариант оформления ячеек, который затем можно применять к разным ячейкам и диапазонам.Formula одно из самых важных свойств объекта Range. Доступно и на чтение, и на запись. Если используется на чтение, то возвращает текст формулы, прописанной в ячейку (а не вычисленное значение), если используется на запись, то позволяет записать формулу в ячейку. Если применить это свойство для диапазона, в который входит несколько ячеек, то формула будет прописана по все ячейки диапазона. Пример применения этого свойства может выглядеть так:

Worksheets("Лист1").Range("A3").Formula = "=$A$1+$A$2"Свойство FormulaLocal, как и AddressLocal, позволяет внести поправки на особенности нумерации ячеек в локализованной версии Excel (для русских версий Excel в нем нет необходимости).FormulaHidden возможность спрятать формулы в диапазоне от пользователя. Работает только на защищенных листах.HasFormula проверить диапазон на наличие вычисляемых значений (формул).Hidden спрятать диапазон. Будет работать только в случае, если диапазон включает в себя хотя бы одну строку или столбец целиком, в противном случае вернется ошибка.Interior еще одно свойство, связанное с форматированием. В основном позволяет покрасить ячейки диапазона.Item позволяет получить еще один объект Range, который определяется путем смещения исходного диапазона.Locked это свойство позволяет заблокировать ячейки диапазона при защите листа.Name возможность получить ссылку на специальный объект именованного диапазона Name. На графическом экране с его возможностями можно познакомиться при помощи меню Вставка -> Имя. Он позволяет обращаться к диапазонам и формулам по именам и несколько напоминает по функциональности объект закладки в Word.Next перейти на следующую ячейку. Если лист не защищен, то следующей ячейкой будет считаться ячейка справа, если защищен то следующая незаблокированная ячейка.NumberFormat возможность установить один из предопределенных форматов для чисел. Соответствует возможностям вкладки Число в меню Формат -> Ячейки на графическом экране.Offset это свойство позволяет получить новый объект Range с определенным смещением от исходного. Например, чтобы получить ячейку со смещением на три ячейки вверх от сходной и три ячейки влево, можно использовать код

Worksheets("Лист1").Activate

ActiveCell.Offset(rowOffset:=-3, columnOffset:=-3).Activateсвойство Orientation позволяет сориентировать текст в ячейках. Указывается угол наклона в градусах. Например, чтобы расположить текст по диагонали, можно использовать код

oRange.Orientation = -45PageBreak это свойство обычно используется для программной вставки разрывов страницы. Его применение может выглядеть так:

Worksheets("Лист1").Rows(50).PageBreak = xlPageBreakManualвсе свойства, которые начинаются на Pivot+, относятся к работе с объектом PivotTable (сводная таблица). Особенности работы с ней будут рассмотрены ниже.QueryTable это очень важное свойство позволяет получить ссылку на объект QueryTable полученные с внешнего источника данные. Это свойство для объекта Range позволяет получить ссылку на объект QueryTable, которые находится в данном диапазоне. Подробнее про объект QueryTable будет рассказано ниже.Range это свойство, как уже говорилось выше, позволяет создать новый диапазон на основе уже существующего. Необходимо помнить про особенности нумерации ячеек в этом случае.Resize возможность изменить текущий диапазон. Например, увеличение его на один столбец вниз и одну строку вправо может выглядеть так:

oRange.Resize(oRange.Rows.Count + 1, oRange.Columns.Count + 1).SelectShrinkToFit это свойство позволяет автоматически настроить размер текста в диапазоне таким образом, чтобы текст умещался в ширину столбца.Style это свойство позволяет вернуть объект Style, представляющий стиль для указанного диапазона. На графическом экране то, что позволяет сделать объект Style, можно сделать через меню Формат -> Стиль.Text возможность получить значение первой ячейки диапазона в виде значения типа String. Для объекта Range это свойство доступно только для чтения.Validation это свойство позволяет вернуть объект Validation, при помощи которого можно настроить проверку вводимых в диапазон данных.Value наиболее часто используемое свойство объекта Range. Позволяет получить или назначить значение (числовое, текстовое или какое-либо другое) ячейкам диапазона. Точно для той же цели используется свойство Value2, единственное отличие это свойство не поддерживает типы данных Currency и Date.WrapText возможность включить/отключить перевод текста на следующую строку в ячейках диапазона.

Теперь о методах объекта Range:Activate() выделяет текущий диапазон и устанавливает курсор ввода на его первую ячейку.AddComment() возможность добавить комментарий к ячейке. Ячейка будет помечена красным уголком, а текст комментария будет показан в виде всплывающей подсказки. Этот метод можно вызвать только для диапазона, состоящего из одной ячейки. То же самое на графическом экране можно сделать при помощи меню Вставка -> Примечание.AutoFill() возможность использовать автозаполнение для диапазона (например, если первые две ячейки будут заполнены как 1 и 2, то дальше в автоматическом режиме будет продолжено: 3, 4, 5 и т.п.)AutoFit() автоматически поменять ширину всех столбцов и высоту всех строк в диапазоне, чтобы туда уместился текст ячеек. Можно применять только к тем диапазонам, которые состоят из набора столбцов (полностью) или набора ячеек (также полностью), иначе будет ошибка.AutoFormat() возможности использовать один из стилей автоформатирования (то, что на графическом экране доступно через меню Формат -> Автоформат).BorderAround() возможность поместить диапазон в рамку с выбранными вами параметрами.методы Clear+ позволяют очистить содержимое диапазона от значений, форматирования, комментарий и т.п.Consolidate() возможность слить данные нескольких диапазонов (в том числе на разных листах) в один диапазон, используя при этом выбранную вами агрегатную функцию.Copy() возможность скопировать диапазон в другое место. Если место назначения не указано, он копируется в буфер обмена. Аналогично работает метод Cut(), при котором данные исходного диапазона вырезаются.CopyFromRecordset() очень удобный метод, который позволяет вставить данные из объекта ADO Recordset на лист Excel, начиная с верхнего левого угла указанного диапазона. DataSeries() метод, который может сэкономить множество времени и избежать возни с функциями даты и времени. Этот метод позволяет увеличить вами значения даты в диапазоне на указанный вами временной интервал. Например, если у вас в диапазоне стоит первое января, то при помощи этого метода можно сгенерировать первое число любого другого месяца.Delete() удаляет данные текущего диапазона. В качестве необязательно параметра можно определить, с какой стороны будут сдвигаться ячейки на место удаленных.Dirty() пометить ячейки диапазона как "грязные". Такие ячейки будут пересчитаны при следующем же пересчете. Обычно используется, когда Word сам не может догадаться, что их нужно пересчитать. Пересчитать ячейки диапазона можно и принудительно при помощи метода Calculate().методы Fill+ (FillDown(), FillUp(), FillLeft(), FillRight()) позволяют размножить одно и то же значение по ячейкам диапазона в указанном вами направлении.метод Find() позволяет произвести поиск по ячейкам диапазона и вернуть новый объект Range, который представляет первую ячейку, в котором было найдено нужное значение. У этого метода есть множество необязательных параметров, которые позволяют определить направление поиска, чувствительность к регистру, искать все значение ячейки или часть и т.п. Методы FindNext() и FindPrevious() позволяют продолжить поиск, начатый методом Find(), в разных направлениях.метод GoalSeek() позволяет применить автоподбор значений для функции Excel программным способом. На графическом экране то же самое можно сделать при помощи меню Сервис -> Подбор параметра.метод Insert() позволяет вставить ячейки в диапазон, сдвинув остальные (вы можете выбрать вправо или вниз).метод Justify() позволяет равномерно распределить текст по диапазону. Если в данный диапазон он не помещается, он будет распространен на соседние ячейки (с перезаписью их значений).метод Merge() позволяет слить все ячейки диапазона в одну. При этом останется только одно значение верхней левой ячейки. Разбить обратно такую слитую ячейку на несколько обычных можно при помощи метода UnMerge().Parse() позволяет разбить одну ячейку на несколько по указанному вами шаблону (например, чтобы отделить код города от номера телефона).PasteSpecial() операция, дополняющая Copy() и Cut(). Она позволяет вставить то, что лежит в буфере обмена, с указанием специальных параметров вставки (вставлять с добавлением к существующим данным, с умножением, вычитанием, делением и т.п.)PrintOut() и PrintPreview() позволяют вывести диапазон на печать или открыть режим просмотра перед печатью..Replace() метод, дополняющий метод Find(). Позволяет проводить поиск и замену значений в диапазоне.Select() возможность выделить указанный диапазон. Объекта Selection в Excel нет вместо него есть возможность получить объект Range, представляющий выделенную область.Show() экран будет проскроллирован таким образом, чтобы показать указанный диапазон.ShowDependents() показать стрелки для ячеек, которые зависят от указанного диапазона (только первый уровень зависимости) или эти стрелки убрать. Обратный метод ShowPrecedents().ShowErrors() показать источник ошибки для указанной ячейки.Sort() возможность произвести сортировку ячеек в диапазоне. Можно использовать большое количество необязательных параметров для настройки сортировки. SortSpecial() с учетом особенностей азиатских языков.Speak() удивительный метод, который позволяет зачитывать вслух содержимое диапазона (можно определить, в каком направлении и будут ли зачитываться формулы). К сожалению, в локализованной версии Excel не работает.SpecialCells() очень удобный метод, который позволяет вернуть объект Range, включающий в себя все ячейки определенного типа (пустые, с ошибками, с комментариями, последние, с константами, с формулами, с определенным форматированием) и с определенным значением. Например, чтобы вернуть объект Range, состоящий из всех пустых ячеек диапазона, можно использовать код

Set oRange2 = oRange.SpecialCells(xlCellTypeBlanks)

oRange 2. Select 'проверяем, так ли это метод SubTotal() позволяет посчитать итоговое значение для диапазона (можно выбрать агрегатную функцию и множество других параметров).метод Table() позволяет создать таблицу на основе передаваемого столбца, строки и функции, которую нужно использовать для вычисления ячеек таблицы. Пример из документации по этому методу позволяет автоматически сгенерировать таблицу умножения.TextToColumns() сложный метод, который позволяет разбить столбцы в диапазоне на несколько столбцов в соответствии с определенным алгоритмом. Принимает множество необязательных параметров.

denisdd.livejournal.com