Окно VBA Excel: открывается диалоговое окно при обращении к ячейке в другом листе. Обращение к листу excel vba

Sheet happens - Макросы и программы VBA - Excel - Каталог статей

Поговорим про то, как в VBA обращаться к листам книги Excel.

Вариант 1

У глобального объекта Excel Application есть 2 коллекции листов: Sheets и Worksheets. Отличаются они тем, что вторая коллекция не включает в себя специальные листы макросов, которые поддерживала Excel 4.0 (на минуточку - 1992 год) во времена, когда VBA ещё не было в природе (появился в Excel 5.0). Тяжело найти эстетов, которые всё ещё пользуются такими листами с макросами, поэтому можно считать, что эти коллекции идентичны. Лично я использую коллекцию Sheets просто потому, что 5 букв лучше, чем 9.

Sheets(1).Cells(1,1)

Sheets(1) - это не первый лист, созданный в книге, а лист, который на панели ярлыков листов занимает КРАЙНЕЕ ЛЕВОЕ положение. То есть Sheets(2) будет указывать на второй слева лист на панели ярлыков. Если вам нужен конкретный лист, то ТАК ссылаться на него, как вы понимаете, плохая идея, ибо пользователь, изменив порядок следования листов, нарушит работоспособность вашей программы.

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

Вариант 2

Sheets("First").Cells(1,1)

Пожалуй, такое обращение к листу наиболее популярно в народе. В скобках и кавычках мы видим пользовательское имя листа. Плохо только то, что имя листа, что называется "hard coded" (жёстко запрограммировано), что является моветоном в программировании. Ибо, если у вас в модуле конструкция Sheets("First") встречается 100 раз, то при смене имени листа придётся делать поиск с заменой 100 констант в коде. Поэтому обычно делают иначе...

Вариант 3

Гораздо разумней добавить ещё один уровень абстракции и объявить константу, ссылающуюся на имя листа.

Public Const wsFirst = "First"

Sheets(wsFirst).Cells(1,1)

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

Вариант 4

У листа есть свойство Name, которое мы видим на ярлыке листа и которое можем менять (если не включена защита структуры книги, но сейчас не об этом), но ещё есть свойство CodeName, которое присваивается листу в момент создания, и которое пользователь сменить не может. В русской редакции офиса эти CodeName-ы формируются по принципу: Лист1, Лист2, Лист3 и т.д. В англоязычной версии это: Sheet1, Sheet2, Sheet3, etc.

Если вы удалите лист, то его CodeName повторно не будет использовано. Так вот - VBA разпознаёт имена совпадающие с CodeName листа, как объекты типа Worksheet.

Лист1.Cells(1,1) или [Лист1].Cells(1,1)

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

Вариант 5

Public wsData As Worksheet

Set wsData = Лист1

wsData.Cells(1,1)

Строку 1 размещаем в объявлениях модуля. Строку 2 размещаем в обработчике события Workbook_Open. И после этого в любом месте книги мы можем использовать объявленные переменные (wsData, строка 3). На мой взгляд, это самый удобный и продвинутый вариант из рассмотренных.

Вариант 6

Не забываем про свойство ActiveSheet объекта Application, указывающее на активный лист книги. Если вы работаете с текущим листом, то использовать его, я считаю, оптимально.

ActiveSheet.Cells(1,1)

Вариант 7

Ну и последнее, если вам передан в подпрограмму объект типа Range, то не забываем, что ссылку на лист возвращает его свойство Worksheet.

Это всё, что я имел сказать по поводу ссылок на листы в Excel VBA :)

perfect-excel.ru

vba - VBA: Как обратиться к правильной книге при предоставлении рабочего листа в качестве аргумента функции?

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

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

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

Вы создали лист table10 в одном из следующих элементов:

ActiveWorkbook
ThisWorkbook
WB (некоторый объект рабочей книги)

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

Использование ThisWorkbook.table10 должно давать такое же поведение, как только table10, но здесь приведены два более простых примера для вызова функции.

' A neater way to call the function: Text_To_Numbers worksheet:=ThisWorkbook.table10 ' You could also call it simply using Text_To_Numbers ThisWorkbook.table10

Если ваш лист не находится в пределах ThisWorkbook

' Get sheet (from the workbook object you are using, WB) and pass to your Text_To_Numbers Text_To_Numbers GetSheetWithCodename("table10", WB) Function GetSheetWithCodename(ByVal worksheetCodename As String, Optional wb As Workbook) As Worksheet Dim iSheet As Long If wb Is Nothing Then Set wb = ThisWorkbook ' mimics the default behaviour For iSheet = 1 To wb.Worksheets.Count If wb.Worksheets(iSheet).CodeName = worksheetCodename Then Set GetSheetWithCodename = wb.Worksheets(iSheet) Exit Function End If Next iSheet End Function

qaru.site

VBA Excel. Ячейки (обращение, запись, чтение, очистка)

Обращение к ячейке на листе Excel из кода VBA. Запись информации в ячейку. Чтение информации из ячейки. Очистка значения ячейки. Метод ClearContents объекта Range.

Обращение к ячейке
Запись информации в ячейку

Чтение информации из ячейки
Очистка значения ячейки

Обращение к ячейке

Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.

В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:

Workbooks("Книга2.xlsm").Sheets("Лист2").Range("C5") Workbooks("Книга2.xlsm").Sheets("Лист2").Cells(5, 3)

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

Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:

ActiveCell Range("A1") Cells(1, 1)

Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере - это ячейка "A1".

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

Sheets("Лист3").Cells(2, 8)

Кроме того к ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз. Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе - зависит от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее. Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.

Запись информации в ячейку

Содержание ячейки определяется ее свойством - «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:

Cells(2, 4).Value = 15 Cells(2, 4) = 15 Range("A1") = "Этот текст записываем в ячейку" ActiveCell = 28 + 10*36

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

Чтение информации из ячейки

Считать информацию из ячейки в переменную можно также при помощи оператора присваивания «=»:

Sub Test() Dim a1 As Integer, a2 As Integer, a3 As Integer Range("A3") = 6 Cells(2, 5) = 15 a1 = Range("A3") a2 = Cells(2, 5) a3 = a1 * a2 MsgBox a3 End Sub

Точно также можно обмениваться информацией между ячейками:

Cells(2, 2) = Range("A4")

Очистка значения ячейки

Очищается ячейка от значения с помощью метода ClearContents. Кроме того, можно присвоить ячейке значение нуля или пустой строки:

Cells(10, 2).ClearContents Range("D23") = 0 ActiveCell = ""

vremya-ne-zhdet.ru

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

Vba открыть файл excel

В разделе Другие языки и технологии на вопрос VBA Excel. Как обратиться к листу/ячейке УЖЕ ОТКРЫТОГО и не сохранённого файла excel? заданный автором Михаил Капустин лучший ответ это Workbooks("file name").Worksheets(...илиSet newbook = Workbooks.Item("file name")

Ответ от 22 ответа[гуру]

Привет! Вот подборка тем с ответами на Ваш вопрос: VBA Excel. Как обратиться к листу/ячейке УЖЕ ОТКРЫТОГО и не сохранённого файла excel?

Ответ от Европейский[гуру]Возможно решением будет передавать данные ИЗ открытого файла. "Внутри открытого файла" пишем макрос, который и передает нужные данные.

Ответ от Просуществовать[гуру]Ваша ошибка в том, что Open открываете уже открытый файл, и он закрывается перед открытием, очищая блоки управления. Правильно в программе активировать (Activate) книгу, если с ней много работы. Затем активировать лист и ячейку, но часто их достаточно выбрать (Select).Application.Workbooks("имя_файла").ActivateActiveWorkbook.Worksheets("имя_листа").Range("A5").SelectMsgBox SelectionДля книги в коллекции книг Workbooks можно указывать порядковый номер файла, автоматически присвоенный при открытии. Порядок можно определить в списке окон Windows (в Excel) или глядя по-еврейски (справа-налево) на ярлыки открытых Excel-файлов на Панели задач. Но этим номером в автоматической программе пользоваться не рекомендуется, так как можно перед запуском вручную закрыть или открыть какие-то файлы и порядок изменится, да и количество. Свежеоткрытый файл меет порядковый номер 1, то есть попадает в голову коллекции. Значит этот способ пригоден только для фиксированного числа открытых файлов.Правильно вместо номера задавать ИМЯ открытого файла (и даже заранее отслеживать ситуацию, открыт ли он).Лист надо также выбирать или активировать по его ИМЕНИ на вкладке в рабочей книге. Но можно и по порядковому номеру вкладки. Дело в том, что при создании НОВОГО листа он открывается по-еврейски в левой вкладке книги и автоматически получает номер 1 в коллекции листов книги. Tо есть свежий лист это всегда Worksheets(1). Но его затем можно передвинуть среди вкладок и он этого номера не потеряет, но вы можете запутаться (как и с файлами)! Правильный порядковый номер листа (вместе с именем в скобках) можно увидеть в среде VBA в списке проекта.Пользуясь именами, вы не ошибётесь, но для этого произвольные имена нужно уметь запоминать в программе, чтобы потом обращаться к ним в процессе выполнения.

Ответ от Єаимя Салихова[гуру]Память не может быть READ!!! 🙂• Excel при его Открытии Вываливает в Виртуальное Пространство ОЗУ свою текущую Информацию. Такова Windows, и таков порядок её Организации.• Excel Создаёт в Temp-Каталоге Файлы Пошагового Изменения. Они нужны, чтобы с помощью Отката Команды (Ctrl+Z) можно было вернуться к предыдущим Состояниям.• Excel каждые 10 (если не ошибаюсь) минут Создаёт в Temp-Каталоге по одной Резервной Копии с текущего Состояния Открытого Документа (Фоновое Автосохранение).• Excel Создаёт в том Месте, откуда Пользователем был Открыт Файл Временный Информационный Файл. В случае Аварийного Завершения Работы (внезапное обесточивание компа). Если при Работе в Excel`е всё пройдёт успешно, и в конце Пользователь/сама Операционная Система Закроет Файл (не важно с Сохранением или без), тогда этот Файл будет Стёрт Excel`ем. Активные Файлы, как и Память (которая не может быть READ) точно также не могут быть READ!!! 🙂Следовательно надо, чтобы что-то извне давало бы команду или какую-то Связь (через которую будут Выполняться какие-то Фоновые Задания, или что-то там, что надо Запустить в VBA). Значит надо загодя в сам Стандартный Шаблон умышленно Вставить Код, когда ещё не запущен Excel. Excel всегда Открывает Новый Документ из имеющегося Шаблона.Только я не знаю наверняка, когда с помощью Excel`я Открываешь уже имеющийся Документ, будет ли он Обращаться к Шаблонам, чтобы посредством их Подстановки Эмулировать что-то нужное!!! Потому что Эмуляцию заготовленных Шаблонов не все Программы и не во всём используют. В AutoCAD`е Эмулируются только Высокоуровневые Шрифты (Системные он вообще не распознаёт). В HTTP вообще Эмулируются только несколько Высокоуровневых Шрифтов. Кстати, их последователи, Android (Операционная Система, Организованная на основе Табличной Разметки) точно также Эмулируют только несколько Высокоуровневых Шрифтов (из них лишь несколько собственных).— о развитии компов и усилении роли SGML-образных Языков.— о развитии Продуктов MS-Office (Excel — всего лишь одна из Программ данного Пакета, изначально привязанная к SGML-образному Языку).23:05 21.07.2017PS. Насколько я понимаю, на Активные Файлы в Windows можно влиять через Реестр, Прописав Доступные Действия для Активных Файлов. Расширять возможности Excel`я, наверное, придётся прямо отсюда из «Базы Данных» Windows. Т. е. где-то здесь должна быть лазейка?! ?\_(°·о) _/?23:12 21.07.2017

Ответ от 2 ответа[гуру]

Привет! Вот еще темы с нужными ответами:

Ответить на вопрос:

22oa.ru

Как обратиться к листу Excel по его имени объекта VBA в другой книге? MS Excel онлайн

У меня есть две книги Excel:

Source.xlsx
Tool.xlsm

Source.xlsx содержит Source.xlsx лист с именем объекта VBA shtTests :

Предположим, что в Tool.xlsm меня есть переменная, содержащая ссылку на Source.xlsx хранящуюся в Source.xlsx :

Dim wkbSource as Workbook Set wkbSource = GetSourceWorkbook() ' Some function that gives a reference to the workbook

Основной вопрос: как я могу ссылаться на shtTests в Tool.xlsm , используя shtTests VBA shtTests ?

Или сформулировать вопрос как код … предположим, что у вас есть этот фрагмент кода:

Dim wkbSourceShtTests as Worksheet Set wkbSourceShtTests = GetShtTestsFromWkbSources(wkbSources)

Вопрос: Что GetShtTestsFromWkbSources выглядеть GetShtTestsFromWkbSources ?

Примечание. Я не хочу ссылаться на нее по имени Excel, как это было бы с использованием wkbSources.Worksheets("Test Cloning") потому что люди могут однажды изменить свое имя Excel.

Solutions Collecting From Web of "Как обратиться к листу Excel по его имени объекта VBA в другой книге?"

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

Sub example() Dim wkbSource As Workbook Set wkbSource = GetSourceWorkbook() Dim wkbSourceShtTests As Worksheet Set wkbSourceShtTests = GetShtTestsFromWkbSources(wkbSource, "shtTests") End Sub Function GetShtTestsFromWkbSources(wkbk As Workbook, codename As String) As Worksheet For Each sht In wkbk.Sheets If sht.codename = codename Then Set GetShtTestsFromWkbSources = sht Next End Function

Это то, что вы пытаетесь?

Sub Sample() Dim wbThis As Workbook, wbThat As Workbook Dim wsThat As Worksheet Dim wsCodeName As String Set wbThis = ThisWorkbook Set wbThat = Workbooks("Book4") '<~~ Change this to relevant workbook wsCodeName = "ShtSheets" Set wsThat = wbThat.Worksheets(CStr(wbThat.VBProject.VBComponents(wsCodeName).Properties(7))) Debug.Print wsThat.Name End Sub

Примечание . Для этого вам необходимо включить доступ к проектам Visual Basic

В меню « File Excel нажмите « Options|Trust Center|Trust Center Settings|Macro Settings Установите флажок «Доверять доступ к объектной модели проекта VBA».

excel.bilee.com

vba - Обращение к диапазону ячейки на другом листе

Существует три разных способа ссылки на разные листы. Ниже было украдено: http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

Имя вкладки Таблицы

Если вы когда-либо записали макрос в Excel, который ссылается на конкретный листок в книге, вы будете знать, что код будет продолжать работать только в том случае, если имена (имена) листа остаются неизменными. Например, код типа; Листы ("Бюджет"). Выбор больше не будет работать, если Бюджетный лист будет переименован. Это связано с тем, что макрорекордер генерирует такой код на основе имени вкладки "Таблицы", имени, которое мы видим в Excel. Если вам становится лучше, многие кодеры VBA также используют имена вкладки "Лист" более чем двумя способами, поскольку они не знают лучшего.

Индекс

Номер индекса листов определяется его положением в рабочей книге. На самом левом листе всегда будет номер индекса 1, следующий справа будет 2 и так далее. Excel VBA позволяет нам указать любой лист, используя его индексный номер, но, к сожалению, этот метод не используется Excel при записи макроса. Он использует имя вкладки "Таблицы"; Таблицы ("Бюджет"). Выберите Если этот лист был третьим слева, мы могли бы использовать: Листы (3). Выберите. Это часто лучший вариант, чем использование имени вкладки листа, но все еще имеет потенциальные проблемы. Под этим я подразумеваю, что положение листов в книге может меняться, если мы добавляем, удаляем или перемещаем листы.

Название кода

Это метод, используемый опытными кодировщиками VBA. Каждому листу в рабочей книге присваивается уникальное CodeName, которое не изменяется даже при перемещении этого листа, переименовании или других листах. Каждый лист CodeName можно увидеть только в редакторе Visual Basic (Инструменты> Макро> Редактор Visual Basic Alt + F11), а затем отобразите Project Explorer (View> Project Ctl + R Project Explorer)

В приведенном выше скриншоте CodeName для листа с названием вкладки Budget является Sheet3. Листы CodeName всегда являются именем не внутри круглой скобки при просмотре в Project Explorer. Мы можем ссылаться на этот лист с кодом VBA в рабочей книге, используя: Sheet3.Select, как указано в листе ("Бюджет"). Выберите или Таблицы (3). Выберите

Если ваша книга уже заполнена кодом VBA, записанным или написанным, который не использует CodeName, вы можете изменить его на уровне Project (весь код во всех модулях в книге), перейдя в Edit> Replace в то время как в VBE (Visual Основной редактор). Один оттянуть назад

Единственный раз, когда вы не можете использовать листы CodeName, - это когда вы ссылаетесь на лист, который находится в другой книге, на ту, которая находится в коде.

С приведенной выше информацией ваш код теперь становится следующим:

Sub COPYVALUES() Sheets("Base_Copy").Range("F15:AK46").Copy ' OR Sheets(2).Range("F15:AK46").Select ' OR Sheet2.Range("F15:AK46").Select Sheets("Final_Copy").Range("F15:AK46").Paste Application.CutCopyMode = False End Sub

Теперь на стороне примечание: вам не нужно использовать copy/paste для перемещения данных...

Попробуйте следующую строку кода:

Sub COPYVALUES() Sheets("Final_Copy").Range("F15:AK46").Value = Sheets("Base_Copy").Range("F15:AK46").Value End Sub

qaru.site

vba - Окно VBA Excel: открывается диалоговое окно при обращении к ячейке в другом листе

Я ломаю голову над этим, и я надеюсь, что кто-то может помочь. У меня есть процедура, которая добавляет новый рабочий лист в книгу Excel и добавляет основную информацию этого рабочего листа в обзор на другом листе (той же книге). Все работает отлично, как должно, но, к сожалению, за одним исключением. Существует одна ячейка, которая должна иметь значение ячейки во вновь созданном рабочем листе. Я использовал эту строку для этого:

c.Offset(0, 27).Value = "=" & Left(AccName.Value, 20) & "!N16"

Здесь "Left (AccName.value, 20)" равно имени рабочего листа. К сожалению, здесь код открывает диалоговое окно, в котором я могу открыть файл. Я понятия не имею, почему и, следовательно, не знаю, как я могу это исправить. Кто-нибудь здесь знает?

Редактировать: Здесь весь суб:

Sub FillBestandsübersicht() Dim c As Range Dim i As Integer i = 3 'Find next empty row Set c = Sheets("Bestandsübersicht").Range("A3") Do Until c.Value = "" Set c = c.Offset(1, 0) i = i + 1 Loop 'Fill Bestandsübersicht c.Value = AccName.Value c.Offset(0, 1).Value = ProgRef.Value c.Offset(0, 2).Value = QuoteNr.Value c.Offset(0, 3).Value = PolicyNr.Value If LdrY.Value = True Or LocY.Value = False Then c.Offset(0, 4).Value = "n.a." c.Offset(0, 5).Value = ddUnderwriters.Value c.Offset(0, 6).Value = IncDate.Value c.Offset(0, 7).Value = ExpDate.Value If LdrY.Value = True Then c.Offset(0, 8).Value = "Lead" Else c.Offset(0, 8).Value = "Follow" End If c.Offset(0, 10).Value = PMNPL.Value If LdrY.Value = True And LocY.Value = True Then c.Offset(0, 11).Value = AmountLoc.Value Else c.Offset(0, 11).Value = 0 End If If CoiY.Value = True Then c.Offset(0, 12).Value = AmountCOI.Value Else c.Offset(0, 12).Value = 0 End If c.Offset(0, 14).Value = "n" c.Offset(0, 15).Value = "n" If DocY.Value = False Then c.Offset(0, 16).Value = "x" Else c.Offset(0, 16).Value = "n" If LdrY.Value = False Or LocY.Value = False Or CoiY.Value = False Then _ c.Offset(0, 17).Value = "x" Else c.Offset(0, 17).Value = "n" If FacY.Value = False Then c.Offset(0, 18).Value = "x" Else c.Offset(0, 18).Value = "n" If LdrY.Value = True Or LocY.Value = False Then c.Offset(0, 19).Value = "x" Else c.Offset(0, 19).Value = "n" If LdrY.Value = False Or LocY.Value = False Then c.Offset(0, 20).Value = "x" Else c.Offset(0, 20).Value = "n" c.Offset(0, 21).Value = "n" c.Offset(0, 26).Value = Left(AccName.Value, 20) c.Offset(0, 27).Value= "=" & Left(AccName.Value, 20) & "!N16" 'Sort Bestandsübersicht Range("A3:AB10000").Sort key1:=Range("A3:A10000"), order1:=xlAscending, Header:=xlNo 'AutoFit rows Sheets("Bestandsübersicht").Rows("3:" & i).EntireRow.autofit End Sub

qaru.site