Темы

[Excel-2003 !!!]

Excel-2003 !!!
Причёсываю свой Personal.xls
При его открытии производится настройка разных менюшек (добавляются/удаляются меню, кнопки и подменю)
Перед тем, как добавить макросом кнопку в меню, на всякий случай (чтобы не было дублирования) хорошо бы попытаться её удалить.
Добавить то стандартную кнопку можно просто по её ID, а вот как обратиться к .Controls не по индексу или капче кнопки (они могут меняться), а тоже по ID?

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

Код (vb) Выделить Развернуть

Private Sub PopupMenuCellChange()
  On Error Resume Next
  With Application.CommandBars("Cell"):
     .Controls("Вст&авить значения").Delete
     .Controls.Add ID:=370, Before:=.Controls("Вставить").Index + 1, Temporary:=True
  End With
End SubОно, конечно, работает, но при смене наименований пунктов (например, в английской локали) работать перестанет, т.к. пункты "Вст&авить значения" и "Вставить" будут называться по-другому.
Но ведь при этом команда "Вст&авить значения" имеет вполне однозначный идентификатор ID:=370, а команда "Вставить" имеет ID:=22, которые, как я понимаю, не зависят ни от локализации, ни от версии Excel
Вот и встал вопрос: нельзя ли вместо обращения к стандартному элементу меню по его названию использовать обращение по его ID?
Что-то я не смог найти как

["Задать/Изменить"]

Доступ к УФ в Excel не удобно спрятан в меню. Да и пары функций не хватает.
Предлагаемая надстройка добавляет в меню "Ячейка", вызываемое по ПКМ, подменю "Условное форматирование" с пунктами: "Задать/Изменить", "Удалить" и "Проверить аргументы"
"Задать/Изменить" - вызов стандартного меню УФ для выделенной ячейки/диапазона
"Удалить" - удаляет все УФ в выделенной ячейке/диапазоне
"Проверить аргументы" - проверяет в выделенной ячейке/диапазоне аргументы функций УФ всех условий на наличие в них ошибок типа #ССЫЛКА! и #REF!, которые могут возникнуть, например, при удалении каких-либо ячеек, столбцов, строк. При этом в формулах листа такие ошибки видны сразу и обычно успеваешь "откатиться", а вот формулы УФ просто перестают работать и об этом обычно узнаёшь, когда откатиться уже невозможно.

P.S. Проверять формулы на результат путём копирования их в ячейки дополнительно создаваемого проверочного листа, ИМХО, слишком муторно, а метод Evaluate не хочет работать с русскими именами функций. Это разбиралось на Планете и в Миру

[Метод 1]

Метод 1.
Просматривая очень интересную книжку "Excel. Трюки (100 профессиональных приёмов)" авторов Р.Холи и Д.Холи, наткнулся на очень интересный способ защиты формул листа от кривых рук невнимательных юзеров - использование проверки данных. На основе предложенного в книге "ручного" метода слепил макрос:

Код (vb) Выделить Развернуть

Sub Formula_Protect_with_CellValidation()
       ActiveWindow.RangeSelection.SpecialCells(xlCellTypeFormulas).Select
       With Selection.Validation
           .Delete
           .Add Type:=xlValidateCustom, Formula1:="="""""
           .ErrorTitle = "ОШИБКА!"
           .ErrorMessage = "В ячейке формула!" & vbCrLf & "Ввод данных запрещён!"
           .ShowError = True
       End With
End Sub
Защищает ячейки с формулами, расположенные на активной странице, от случайного ввода данных с клавиатуры.
Не защищает от копи-паста (т.к. тогда и условия проверки ячейки тоже заменяются).
К стати, Select начале кода можно и убрать, но тогда будет не видно, что макрос отработал и защитил ячейки.
И к тому же от Select есть ещё один побочный плюс: если ячейки с формулами окажутся после отработки макроса выделенными, то с них будет легко и снять проверку при необходимости что-то подправить в формулах.

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

Осваиваю XML-настройку Ribbon'а "под себя"
Нужны картинки для своих кнопок.
Чтобы вставлять существующие в Офисе картинки кнопок в XML-текст сделал файл, который при его открытии размещает на вкладке "разработчик" новую группу, а в ней - галереи картинок.
При нажатии на интересующую картинку на экран выводится форма с 3-мя видами картинки (16х16, 24х24, 32х32) и двумя кнопками: "Закрыть" и "Копировать".
Кнопка "Копировать" помещает имя картинки в готовом для вставки к код виде(типа imageMso="HappyFace" )в буфер обмена.
Делал для начала для 2007-го. Там картинок чуть больше 1800 штук.
Первые "грабли", на которые наткнулся, это то, что в галерее оказывается не может содержаться более 1000 объектов (иначе XML-схема становится не валидной и не отрабатывается).
Ну, и фиг с ним. Слишком большие галереи смотреть всё равно не удобно. Сделал три галереи по примерно 600 картинок. Всё отлично заработало.

А когда взялся за аналогичную работу для 2010-го, то наткнулся на вторые "грабли" (и намного хуже): встроенных картинок в 2010-м больше 7000.
Попробовал сделать 8 галерей каждая немного меньше 1000 картинок.
Валидацию-то файл прошёл, а вот отобразилось всего 5 галерей с 3994 картинок в сумме (5-я оказалась совсем коротенькая). Остальные же 3 галереи и оставшиеся элементы 5-й не отобразились вообще.
Вот и вопрос, а кто ограничивает общее количество элементов: вкладка, группа или бокс, в котором размещены галереи?
И если я создам новую вкладку и размещу галереи в её группах, то на вкладке тоже будет такой же предел или всё-таки получится разместить 7000 элементов в галереях на одной, а не делать две вкладки (уж очень не хочется) ?

Что-то я не могу найти, где в профиле пользователя можно посмотреть за что ему менялась репутация?
У меня в профиле есть кнопочки "Кто изменил мою карму" и "Кому я изменил карму", но при нажатии любой из них я получаю "отлуп" типа: "Ошибка! Извините - у Вас нет права просмотра лога кармы."
И вообще: карма - это репутация или что-то другое?
Да и про других пользователей интересно знать, за что им плюсуют или минусуют "репу"? Почти на всех форумах это можно сделать, а здесь почему-то нет   (или я просто не нашёл?)
А началось-то всё с того, что я увидел у себя откуда-то появившийся минус в репе, но я не помню, за что и когда его заработал... (или просто это склероз? )

[стандартных]

Из-за того, что мелко-мягкие решили, что они лучше пользователей знают, какие группы и кнопки должны быть на стандартных вкладках Ribbon-интерфейса Excel-2010, удалить или переместить в другое место эти элементы невозможно.
Возможно только создавать новые вкладки и группы и добавлять группы и кнопки для вызова команд. А вот удалить часть не нужных кнопок стандартных функций из стандартной вкладки или даже переставить их - нет. Можно только переименовать или целиком сделать невидимой всю стандартную вкладку.
Пришла в голову мысль, а нельзя ли извратиться так:
1. Макросом создать пользовательские настраиваемые вкладки - копии ВСЕХ стандартных вкладок с нередактируемыми элементами. Копиям вкладок можно присвоить названия с суффиксом '(апостроф) - и имя другое, и ширина символа маленькая.
2. Тем же макросом скрыть (раз уж нельзя совсем удалить) стандартные вкладки.
3. Как полноправный юзер в ручную отредактировать на ленте новые вкладки (убрать не нужное, переставить элементы, добавить кнопки для вызова своих макросов, ...)

Похоже, что должно получиться. Как опытные юзверги Ёкселя-2007/10 на это смотрят?
К сожалению, с  программированием объекта риббон у меня совсем никакого опыта нет, т.к. хорошо знаю только VBA Excel-2003.
Вот потому и обращаюсь к вам с просьбой: Люди добрые, макрописцы хитроумные! Поможите, кто чем может... Подскажите, как макрос состряпать с цикликом по стандартным вкладкам, да чтобы ещё и свои вкладки с такими же командами сделал.

[ChartArea]

Мне необходимо сделать на листе прямоугольник с изменяемыми пользователем размерами.
При этом текущие размеры в миллиметрах необходимо показывать внутри прямоугольника.
В общем должен получиться приближенный аналог прямоугольника с проставленными размерами как в Visio.
Прямоугольник должен свободно перемещаться по экрану мышкой в обычном режиме (не в режиме конструктора форм!). Дополнительные кнопки на листе крайне нежелательны, а на панелях управления и пунктах меню - вообще не допустимы.
Т.е. остаётся одно: использовать вызов процедур по событиям объекта.
Т.к. из графических объектов листа обработку событий поддерживает только ДИАГРАММА, то вместо прямоугольника решил её и использовать.
Вроде, практически всё заработало. НО!
Я плохо умею работать с диаграммами, поэтому встал ряд проблем:
После изменения размеров диаграммы они должны отображаться внутри поля.
Не знаю, в какой из параметров диаграммы их вывести...?
Хоть ChartArea и имеет все параметры для форматирования выводимого на неё текста, но сам текст вывести некуда - нет у ChartArea такого параметра.

Пришлось выводить в ChartTitle, но у него нет параметров Height и Width, а есть только Top и Left поэтому не могу сделать высоту и ширину во всё поле ChartArea - остаются пустые поля справа и внизу.
Да ещё и ChartTitle.AutoScaleFont как-то странно работает - не после каждого изменения размера подстраивает текст.

При этом размеры нужны в миллиметрах! Как пересчитать - не знаю ...

Ну, и там ещё пара вопросиков есть чисто по теории в комментариях... 

[Фильтр-Текстбокс АВТОМАТ]

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

На листе Фильтр-Текстбокс АВТОМАТ - автофильтр по тексту, вводимому в текстбоксы.
Фильтрация происходит "на лету" по мере ввода символов в текстбокс.
Столбцы для фильтрации могут определяться как автоматически по положениям левых верхних углов текстбоксов, так и задаваться пользователем, поэтому вмешательство в код для изменения столбцов фильтрации либо не требуется, либо минимально. Можно задать столбцы фильтрации, отличные от положения текстбоксов. Для изменения столбцов фильтрации достаточно либо изменить положение текстбоксов в "Режиме констркутора", либо задать номер столбца в аргументе программы обработки.
Для каждого текстбокса опционально возможно его автоматическое позиционирование относительно ячейки и подгонка его ширины-высоты, а также замена "пробела" на "звёздочку".

На листе Фильтр-Ячейка АВТОМАТ - автофильтр по тексту, вводимому в ячейки.
Фильтрация происходит после выхода из ячейки.
Фильтровать или нет в столбце при изменении значения ячейки определяется тем, есть в ней специальная метка - примечание с заданным в коде текстом-признаком ФИЛЬТР-ячейка
Поэтому вмешиваться в код для того, чтобы добавить или убрать ячейки, в которые вводятся критерии фильтрации, нет необходимости.
P.S. Текст-признак при желании можно изменить во второй строке кода.
P.P.S. Текст-признак чувствителен к регистру!

На листе Фильтр-Ячейка - автофильтр по тексту, вводимому в ячейки. Фильтрация происходит после выхода из ячейки.
Ячейки, включающие фильтр, задаются непосредственно в коде. Столбцы, в которых производится фильтрация, совпадают со столбцами ячеек.

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

[KukLP]

Многие, а особенно - участники форумов, знают о баге работы буфера обмена в VISTA и Win-7: если скопировать в буфер обмена текст, содержащий символы кириллицы, не переключив перед копированием раскладку клавиатуры в RUS, то после вставки скопированного текста в ответ форума или "блокнот" вместо кириллических букв будут "кракозябры" типа:
Äîïîëíèòåëüíûå ñâåäåíèÿ ñì. â çàìåòêàõ î âûïóñêå

Для того, чтобы прочесть "сглюченный" таким образом текст, разработана функция пользователя:

Код Выделить Развернуть

Function ПОЧИНИТЬ_КИРИЛЛИЦУ(ГЛЮК$)
   '---------------------------------------------------------------------------------------
   ' Procedure    : ПОЧИНИТЬ_КИРИЛЛИЦУ
   ' Author       : The_Prist & Alex_ST
   ' DateTime     : 29.04.11, 09:52
   ' Topic_HEADER : Как прочесть "кракозябры", получаемые после копирования-вставки кириллицы из Висты?
   ' Topic_URL    : http://www.planetaexcel.ru/forum.php?thread_id=26894
   ' Purpose      : правит глюки типа Ýòà äîë  и другие "кракозябры" после вставки кириллицы из Vista
   ' Notes        :
   '---------------------------------------------------------------------------------------
   Dim Arr, i%, sTxt$, sSymb$
   'ГЛЮК = Replace(ГЛЮК, "&#", ";&#"): ГЛЮК = Replace(ГЛЮК, ";;", ";"):Arr = Split(ГЛЮК, ";")
   Arr = Split(Replace(Replace(ГЛЮК, "&#", ";&#"), ";;", ";"), ";")
   If UBound(Arr) > LBound(Arr) Then
      For i = LBound(Arr) To UBound(Arr)
         If Left(Arr(i), 2) = "&#" And IsNumeric(Mid(Arr(i), 3)) Then
            Arr(i) = CInt(Replace(Arr(i), "&#", ""))
            Arr(i) = Chr(IIf(Arr(i) > 256, Arr(i) - 848, Arr(i)))
         End If
      Next
      sTxt = Join(Arr, "")
   Else
      For i = 1 To Len(ГЛЮК)
         sSymb = Mid(ГЛЮК, i, 1)
         If AscW(sSymb) > 255 Then
            sTxt = sTxt & sSymb
         Else
            sTxt = sTxt & Chr(AscW(sSymb))
         End If
      Next i
   End If
   ПОЧИНИТЬ_КИРИЛЛИЦУ = sTxt
End Function

А для того, чтобы такие глюки не возникали при вашей работе, KukLP где-то нарыл следующий метод:
1. В папке system32 удаляем файл c_1252.nls
2. Там же делаем копию файла c_1251.nls и переименовываем её в c_1252.nls
3. Перезагружаемся. Проверяем. БАЛДЕЕМ!
Главная проблема - суметь удалить имеющийся файл c_1252.nls
Виста, маниакальная сволочь >( , не даёт это сделать даже администратору. А стать Full Admin я не сумел.
Зато по совету того же KukLP я попробовал удалить файл с помощью программы Unlocker - получилось!

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

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

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

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

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

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

Иногда в выкладываемых примерах хотелось бы для наглядности сделать фигуру (кнопочку или картинку), при клике на которую открывается VBE.
Где-то в Форуме на Планете я видел инструкцию, как приписать к фигуре такую ссылку...
Я даже сделал у себя в паре примеров личной копилки такие кнопки для пробы.
Там как-то хитро нужно было создавать гиперссылку. Но как - я забыл и нигде не записал.
А на Планете что-то никак не могу найти, где видел инструкцию?
Дмитрий, не подскажите?

[The_Prist]

Ситуация, наверное, типичная...
Я юзаю два компа: один на работе (под ХРюшей), другой дома (Виста). На обоих компах Excel-2003.
Большую часть макросов в Personal.xls и доработок панелей управления (наделал много своих кнопочек для вызова макросов, своих менюшек со своими кнопочками, своих кнопочек в стандартных менюшках...) делаю на работе  
Хочется и дома иметь такой же настроенный "под себя" Excel, как и на работе.
Но к сожалению просто тупо подложить домашнему Ёкселю файл Excel11.xlb вместе с папочкой XLSTART нельзя, т.к. не совпадают пути к макросам, лежащим в Personal.xls
А сидеть и просто в режиме настройки перепрописывать макросы, прикреплённые к каждой кнопочке жутко лень.
The_Prist на Планете в топике "Перенос макросов на другой комп с изменением путей доступа к ним" предлагал делать это макросом, который пройдётся циклом по всем кнопочкам менюшек и исправит у них путь, прописанный в OnAction:

Код Выделить Развернуть

Sub Replace_Personal()   ' перенос макросов и настройки видимых панелей на другой компьютер
   Const NewPath$ = "'C:\Users\MyNewUserName\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLS'!" ' если переносить на Vista
   'Const NewPath$ = "'C:\Documents and Settings\MyNewUserName\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS'!" ' если переносить на XP
   Dim cmdBar As Object, iBtn As Object, sPath$
   For Each cmdBar In Application.CommandBars
       If cmdBar.Visible Then
           For Each iBtn In cmdBar.Controls
               On Error Resume Next
               sPath = iBtn.OnAction
               If Len(sPath) > 0 Then
                   If InStr(1, sPath, "'!", 1) > 0 Then iBtn.OnAction = NewPath & Mid(sPath, InStr(1, sPath, "!", 1) + 1)
               End If
           Next iBtn
       End If
   Next cmdBar
End Sub

Пробовал. Работает, но не полностью : переписывает пути только у тех кнопочек, которые видны на экране, а у тех кнопочек, которые не видны - нет.
Попробовал убрать

Код Выделить Развернуть

If cmdBar.Visible Then ... - почему-то не помогло  
Всё равно не правятся пути у кнопочек, спрятанных в недрах выпадающих менюшек...
У кого-нибудь есть идеи, как "подпилить"?

[The_Prist]

Лежал у меня "в заначке" подпиленный под свои нужды макрос Дмитрия (The_Prist) Ведение журнала сделанных в книге изменений.
В общем-то работал, но не применялся, т.к. было очень не удобно из-за того, что пользователю нельзя отменять свои действия (и это естественно, т.к.работу макроса отменить весьма затруднительно и далеко не всегда возможно).
А тут меня на форуме "Профессиональные приемы работы в Microsoft Excel" в топике Макрос по регистрации изменений Andrey Lenets навёл на мысль о другой возможности записи журнала изменений: его же Ёксель может САМ вести если файл открыть в общем доступе!
Да, согласен: общий доступ "коцает" многие из полезных фенечек Ёкселя...
НО! При использовании встроенного журнала изменений все допустимые в общем доступе изменения оказывается можно отменять.
В общем, посмотрите, что я наваял на скорую руку.
Только учтите:
1. Изменения фиксируются в журнале только после сохранения книги.
2. Создаваемый лист "Журнал" автоматически удаляется при сохранении файла или при отмене общего доступа.
Поэтому если он ещё будет нужен, то его надо сначала куда-то скопировать (там есть макрос, к которому не прицеплена кнопка), а потом уже сохраняться.