Excel это не сложно

Основные форумы => Полезные решения => Тема начата: Alex_ST от 03.06.2011, 12:19:39

Название: Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 03.06.2011, 12:19:39

Написал макрос, который при клике по гиперссылкам листа ОГЛАВЛЕНИЕ делает видимым выбранный лист и скрывает все остальные. При большом числе листов очень удобно получается не выбранные листы прятать.
Этот-то макрос работает.
Но я решил усовершенствовать - сделать ещё один макрос, который будет в книге сам создавать лист ОГЛАВЛЕНИЕ с гиперссылками на её листы и прописывать на этот лист коды обработки событий.

Народ, знатоки!
Гляньте, пожалуйста те, кто умеет работать с VBProject.VBComponents

Я в макрос Sub ОГЛАВЛЕНИЕ_КНИГИ , формирующий гиперссылки-оглавление листов, пытаюсь внести доработку: если лист с именем ОГЛАВЛЕНИЕ в книге не существует, то такой лист создаётся перед первым листом, а потом на него должен прописаться код VBA для обработки событий.

Если в примере нажать на кнопку "Создать ОГЛАВЛЕНИЕ", то, т.к. имеющийся лист у меня переименован в _ОГЛАВЛЕНИЕ, вызванный из-за этого макрос Sub Create_shContent создаст новый лист с именем ОГЛАВЛЕНИЕ и запишет на него код обработки событий.

При пошаговом проходе Sub Create_shContent , вроде бы работает. Лист создаётся, код на него записывается. Но когда запускаю его, то Ёксель "умирает", предлагая перед смертью отправить сообщение об ошибке мелко-мягким :)

Может, я неправильно обращаюсь к VBComponents(ActiveSheet.CodeName) - кодовому модулю активного листа?
Или просто пора полный ребут компу устраивать?

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Дмитрий Щербаков(The_Prist) от 03.06.2011, 14:00:51

Алексей, я чуть изменил код. Вынес в отдельные процедуры сам код(будет работать даже из надстройки, если основной код в ней).
А еще изменил в той части, где прописывались сами коды в модуль листа - советую просмотреть изменения и принять к сведению. Возможно тот факт, что Вы обращались к объекту компонента VBA именно тем способом и было причиной ошибки.
Так же я подправил, чтобы Ваш записываемый код вдруг не стал записываться перед строками деклараций(Option Explicit и прочее).

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 03.06.2011, 14:26:29

Дмитрий, спасибо.
Буду разбираться.
Но первая же попытка создать с вашим кодом новый лист ОГЛАВЛЕНИЕ привела опять к смерти Ёкселя.
Надо полностью перегружаться. Сейчас не могу. Попробую, наверное, дома.

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 03.06.2011, 15:04:46

За CountOfDeclarationLines + 1 большое спасибо. Не знал как обойти декларации.

А в Sub Go_to_Hyp ошибочка вышла : не (Target As Range), а (Target As Hyperlink)
Ну, это и понятно — привычка.

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 03.06.2011, 22:04:42

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

Поэтому я решил всё-таки прописывать код в создаваемый лист полностью без вызова подпрограмм. Но, естественно, с учётом замечаний Дмитрия.
Кстати, дома Ёксель дохнуть на макросе перестал.
А модуль Create_Content теперь можно вынести в personal.xls или в надстройку и прицепить кнопочку на панели инструментов к макросу Sub ОГЛАВЛЕНИЕ_КНИГИ

Надо бы ещё добавить сортировку листов. Её код есть в модуле, но не используется, т.к. что-то как-то хитро глючит сортировка при скрытых листах.

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 07.06.2011, 14:28:35

Тут я взялся дополировывать макрос для создания в активной книге листа "<<ОГЛАВЛЕНИЕ>>" с гиперссылками на все её листы.
При выборе гиперссылки на лист из оглавления выбранный лист и "<<ОГЛАВЛЕНИЕ>>" остаются видимыми, а все другие листы скрываются.
При активизации листа "<<ОГЛАВЛЕНИЕ>>" все остальные листы книги скрываются.
На листе "<<ОГЛАВЛЕНИЕ>>" добавлен пункт-гиперссылка "Все листы", позволяющий при его активизации сделать видимыми все листы книги.

При необходимости название создаваемого листа "<<ОГЛАВЛЕНИЕ>>" и адрес ячейки начала оглавления на нём вводятся в первых строках кода процедуры СОЗДАТЬ_ОГЛАВЛЕНИЕ

Код может функционировать самостоятельно как надстройка - создавать листы "<<ОГЛАВЛЕНИЕ>>" в любой активной книге.

Вроде, всё работает как надо.
Только почему-то приходится два раза запускать макрос СОЗДАТЬ_ОГЛАВЛЕНИЕ для создания оглавления в новой книге:
- за первый запуск создаётся лист "<<ОГЛАВЛЕНИЕ>>" и код обработки событий
- за второй - прописываются гиперссылки.

НИЧЕГО НЕ ПОНИМАЮ! Пол-дня бьюсь...

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Дмитрий Щербаков(The_Prist) от 07.06.2011, 14:39:33

Алексей. Ну ведь ячейка для создания списка какая?

Код:

 With ActiveWorkbook.Worksheets(shContent)
 Set rCell = .Range(sStartCell).Offset(iSht.Index - 1, 0)

Т.е. если листа еще нет, то у нас родительского объекта для With тоже нет. Т.к. ошибка у Вас игнорируется(On Error Resume Next), то Вы и не обнаружили столь досадное недоразумение :)
Во вложении решение.

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 07.06.2011, 15:41:56

Что-то у меня комп конкретно заглючил - при запуске и вашего и моего доработанного макроса умирает Ёксель и предлагает пожаловаться в микрософт...
Даже перезагрузка не помогла.
Я свой вариант доделал с вашими указаниями.
Если не затруднит, можете проверить на своём компе?

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Дмитрий Щербаков(The_Prist) от 07.06.2011, 16:17:50

Работает.
Но я на Планете отписал свое мнение, по поводу намеренного игнорирования подобных ошибок.

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 07.06.2011, 19:41:07

Дмитрий, а у вас на компе в вашем файле Оглавление_автомат.xls код на листе создаётся?
У меня - нет. Лист с гиперссылками создаётся, все листы кроме оглавления скрываются. Но кода на листе нет и поэтому спрятанные листы по гиперссылкам не показываются...

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Дмитрий Щербаков(The_Prist) от 07.06.2011, 19:52:06

Цитата: Alex_ST от 07.06.2011, 19:41:07

У меня все работает. Код создается и все ссылки рабочие.

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 07.06.2011, 20:25:14

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

Вот, посмотрите: я внедрил предлагаемый вами метод проверки наличия листа непосредственно в макрос.
При первом запуске макроса создаётся лист с гиперссылками, но без кода. При втором запуске на лист прописывается код... Бред какой-то. Такое впечатление, что коллекция Worksheets после Add не сразу обновляется.
Самое интересное, что если лист с работающим оглавлением удалить, а книгу сохранить, но не закрывать, то новый лист при запуске макроса создаётся уже нормально - со ссылками и с макросом.
А вот если лист удалить, книгу сохранить и закрыть, то после открытия книги макрос полностью отработает только со 2-го раза...

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Дмитрий Щербаков(The_Prist) от 07.06.2011, 20:48:51

Алексей, последний файл тоже нормально работает. Посмотрите, нет ли у Вас перехвата событий при выполеннии макроса, нет ли инициализации каких форм, переменных, процедур. Если никакие посторонние коды не вклиниваются в процесс - виноват Excel. Может стоит переустановить?

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 07.06.2011, 21:24:12

Добавил

Код:

Application.EnableEvents = False … Application.EnableEvents = True

Вроде, сначала заработало. Но раз на 3-й опять глюкнуло. Наверное, надо и в самом деле Ёксель переставить...

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 08.06.2011, 07:47:23

Сейчас попробовал свою последнюю версию на работе: Ёксель умирает при попытке прописать код в модуль созданного листа...
А дома всё в порядке. Хотя дома Офис - его близнец, поставленный с того же дистрибутива.
Буду, наверное, Ёксель переставлять...

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 08.06.2011, 16:17:13

В общем, как ни бился, даже полностью Офис с другого дистрибутива переставил, а Ёксель как помирал на "исправленном" примере, так и помирает.

Тогда взял и сделал тупо - взял свой пример от 07.06.2011, 15:28:35 и сделал два раза подряд вызов процедуры:

Код:

Sub duplet()
   СОЗДАТЬ_ОГЛАВЛЕНИЕ :СОЗДАТЬ_ОГЛАВЛЕНИЕ
End Sub

ПОМЕР ЁКСЕЛЬ ОПЯТЬ! А ведь при ручном повторном вызове всё работало (см. мой пост 07.06.2011, 15:15 )
Тупо ввёл задержку в 1 секунду:

Код:

Sub duplet()
   СОЗДАТЬ_ОГЛАВЛЕНИЕ
   Application.OnTime Now + TimeValue("00:00:01"), "СОЗДАТЬ_ОГЛАВЛЕНИЕ"
End Sub

ЗАРАБОТАЛО!

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 08.06.2011, 20:15:56

Вот посмотрите, пожалуйста последний исправленный вариант: я просто добавил перед записью кода на созданный лист задержку в 1 секунду.
Дома всё теперь работает. Завтра попробую на работе. А если заработает, то перенесу модуль в Персонал (в надстройках я плаваю, к сожалению).

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Дмитрий Щербаков(The_Prist) от 08.06.2011, 20:38:54

Алексей, а если попробовать убрать задержку, но добавить:

Код:

Dim lCntDeclrLines As Long
    sCodeName = ActiveSheet.CodeName
    sCode = sProc1 & vbLf & vbLf & sProc2
    Set oSh = ActiveWorkbook.VBProject.VBComponents(sCodeName)
    lCntDeclrLines = oSh.CodeModule.CountOfDeclarationLines + 1
    oSh.CodeModule.InsertLines lCntDeclrLines, sCode

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 08.06.2011, 21:07:05

Дмитрий, к сожалению дома на 100% проверить не могу - здесь и так почти всегда работает.
Но, честно говоря, вряд ли глюк идёт из-за того, что параметр CountOfDeclarationLines мы указываем прямо в инструкции добавления линий кода процедур. Ведь в этих линиях деклараций нет, поэтому и CountOfDeclarationLines из-за их добавления "на лету" не меняется.
Но, как говорил Лаврентий Павлович: "Попытка - не пытка". Дома сейчас сработало. А на работе - будем завтра посмотреть.

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 08.06.2011, 21:16:00

Нет, не работает. Но не совсем:
- после открытия файла "от кнопки" создаётся лист с гиперссылками, но код на него прописывается с вероятностью ~50%
- после открытия файла и запуска макроса из VBE лист с гиперссылками тоже создаётся, но код на него прописывается практически всегда

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Дмитрий Щербаков(The_Prist) от 08.06.2011, 21:20:24

Цитата: Alex_ST от 08.06.2011, 21:07:05

вряд ли глюк идёт из-за того, что параметр CountOfDeclarationLines мы указываем прямо в инструкции добавления линий кода процедур. Ведь в этих линиях деклараций нет, поэтому и CountOfDeclarationLines из-за их добавления "на лету" не меняется.

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

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 08.06.2011, 21:36:30

Надо будет на CitForum поковыряться в Меню Visual Basic (http://citforum.ru/programming/vbhelp/index.shtml)
там очень много всего разного, но есть и про команды для работы с VBProject. В том числе и про CountOfDeclarationLines (http://citforum.ru/programming/vbhelp/vbhelp_08.shtml#_421)
Но к сожалению это не учебник, а справочник.
Нет ли у вас ссылки на русскоязычный учебник по работе с VBProject? (или стандартный ответ - Уокенбах?)

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 09.06.2011, 09:13:15

На работе Ёксель при запуске макроса гибнет...
При этом именно при выполнении oSh.CodeModule.InsertLines
Пытался сделать тупо: сначала удалить весь код с листа:

Код:

   Set oSh = ActiveWorkbook.VBProject.VBComponents(sCodeName)
   Debug.Print "CountOfLines = " & oSh.CodeModule.CountOfLines
   lCountOfLines = oSh.CodeModule.CountOfLines
   oSh.CodeModule.DeleteLines 1, lCountOfLines
   Debug.Print "CountOfLines = " & oSh.CodeModule.CountOfLines

Это сработало. А когда на следующей строке (проходил в построчном режиме) встретилось

Код:

   oSh.CodeModule.InsertLines 1, sCode

тут он и помер >:(

Порылся в и-нете. Оказывается, CodeModule.InsertLines у многих глючит - не всегда прописывает код.
На одном буржуйском форуме (http://www.mrexcel.com/forum/showthread.php?t=42454) предлагают вставить перед этим в код после создания страницы DoEvents. Попробовал. Не помогло.
Вспомнил, что надо ещё подключить ссылку на Microsoft Visual Basic for Applications Extensibility 5.3. Подключил (после переустановки Офиса старая ссылка слетела). Не помогло.

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Дмитрий Щербаков(The_Prist) от 09.06.2011, 09:20:55

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

Код:

Dim objVBPrj as object, oSh as object
Dim lCntDeclrLines As Long
sCodeName = ActiveSheet.CodeName
sCode = sProc1 & vbLf & vbLf & sProc2
Set objVBPrj = ActiveWorkbook.VBProject
Set oSh = objVBPrj.VBComponents(sCodeName)
lCntDeclrLines = oSh.CodeModule.CountOfDeclarationLines + 1
oSh.CodeModule.InsertLines lCntDeclrLines, sCode

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 09.06.2011, 11:49:17

Пошёл ещё дальше - начал определять переменные прямо "от печки" (подсмотрел на Programming The VBA Editor (http://www.cpearson.com/excel/vbe.aspx) ):

Код:

Dim VBProj As VBIDE.VBProject
   Dim VBComp As VBIDE.VBComponent
   Dim CodeMod As VBIDE.CodeModule
   Dim sCodeName$, lFirstProcLine&
   sCodeName = ActiveSheet.CodeName
   Set VBProj = Application.ActiveWorkbook.VBProject
   Set VBComp = VBProj.VBComponents(sCodeName)
   Set CodeMod = VBComp.CodeModule
   lFirstProcLine = CodeMod.CountOfDeclarationLines + 1
   CodeMod.InsertLines lFirstProcLine, sCode

Всё равно вылетает, зараза, на строке с InsertLines

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 09.06.2011, 14:40:22

От безысходности попытался с нуля постепенно, проверяя каждый шаг, создать файл.
Выяснил, что "собака-то порылась" вовсе не в InsertLines , а в попытке обращения к созданному листу уже после прописывания на него кода обработки событий.
Посмотрите, пожалуйста.
В приложенном примере после нажатия на кнопочку СОЗДАТЬ_ОГЛАВЛЕНИЕ лист оглавления и код на нём создаются. А следующие шаги - перемещение этого листа на первое место и операции с его ячейками (стирание диапазона, прописка в ячейки гиперссылок) заремарены.
Если ремарку с чего-нибудь внутри блока With oWbk.Sheets(shName) … End With снять (ну, хотя бы с .Move Before:=oWbk.Sheets(1) ) , то при выполнении макроса Ёксель вылетит.

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: Alex_ST от 10.06.2011, 14:56:41

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

Ссылки на сильно скрытые листы теперь не создаются (сделано специально, но не трудно и убрать). Вместо таких листов - пропуск в оглавлении
В данном примере сильно спрятан Лист3

Если у кого-то Excel всё-таки будет "умирать" при запуске макроса (это иногда бывает на некоторых релизах Офиса), то надо снять ремарку с указанной строки кода. Тогда полностью функционирующее оглавление будет создаваться за два прохода макроса.

Название: Re:Показать только лист, выбранный в оглавлении, остальные скрыть.
Отправлено: solmir от 16.06.2011, 20:23:08

На самом деле, на 2 разных машинах абсолютно разные настройки, сам недавно убедился... The_Prist можно ли тебе код высыть? Я думал это платно... :'(