Формула в примечание Excel - Компьютерный форум OSzone.net

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)

- - Формула в примечание Excel (http://forum.oszone.net/showthread.php?t=247393)

Michael Mikhail

21-11-2012 17:11 2029739

Формула в примечание Excel

Здравствуйте,

Можно ли получить результат вычисления формулы в примечании к ячейке?
Конкретизирую, - нужно разделить значение ячейки A1 на "N" (N - целое число) и результат вычисления отобразить в примечании к этой же ячейке. Синтаксис оператора, в частности для деления, есть здесь: http://msdn.microsoft.com/ru-ru/library/25bswc76.aspx
На некоторых форумах предлагается осуществить вычисление в отдельной ячейке с последующим переносом результата в примечание, но если VBA сам осуществляет вычисления, зачем прибегать к помощи вспомогательной ячейки?

Iska	21-11-2012 18:11 2029789

Откуда будет браться значение N?

okshef

21-11-2012 21:15 2029884

В модуль нужного листа

Код:

Private Sub Worksheet_Change(ByVal Target As Range)

With Application

    .EnableEvents = False

    .ScreenUpdating = False

End With

    If Not IsEmpty(Target.Value) Then

        With Target

                If Target.Comment Is Nothing Then .AddComment

' получение значения "comtext" в ваших руках - формула в скобках любая.

'-----------------------------------------

            comtext = CStr(.Value / 2)

'-----------------------------------------

            .Comment.Text Text:=comtext

' чтобы коммент был виден, раскомментируйте строчку ниже

'            .Comment.Visible = True

        End With



' форматирование комментария

'--------------------------------------------

        With Target.Comment.Shape.TextFrame

            .HorizontalAlignment = xlCenter

            .VerticalAlignment = xlCenter

            .ReadingOrder = xlContext

            .AutoSize = True

            .Characters.Font.FontStyle = "полужирный"

            .Characters.Font.Size = 9

        End With

'--------------------------------------------

    End If

With Application

    .EnableEvents = True

    .ScreenUpdating = True

End With

End Sub

Формулу для comtext придумайте сами.

Michael Mikhail

22-11-2012 12:15 2030218

Что вводить вместо Target и Value?

okshef

22-11-2012 12:16 2030222

Ничего - это переменные

Michael Mikhail

22-11-2012 14:25 2030308

Но у меня одна переменная - значение ячейки A1 (либо диапазона ячеек, либо любой ячейки листа). Дело в том, что при компиляции выскакивает ошибка, пытался вводить нужную ячейку в формате "A1" вместо "Target.Value", - прошло, но вот дальше, где "Target.Comment" - выдаёт ошибку.

Реализовал вот так (пока только для одной ячейки A1; N=10):

Sub Name()
Worksheets(1).Range("A1").Comment.Text Text:=CStr(Range("A1") / 10)
End Sub

Воспользовался приведённой Вами командой Cstr. Чем принципиально отличается Ваш код от моего?

Теперь возник вопрос: при сохранениии документа (с макросом), просит внести какие-то изменения в настройках, кто знает, где?

Iska	22-11-2012 15:11 2030360

Цитата:

Цитата Michael Mikhail

просит внести какие-то изменения в настройках, »

Приведите точное сообщение.

Michael Mikhail

22-11-2012 16:16 2030426

Теперь уже и не помню (закрыл без сохранения); видимо при следующем входе надо макросы подключать в настройках ("Параметры Excel"→"Центр управления безопасностью"→"Параметры центра управления безопасностью"→"Параметры макросов").

okshef

22-11-2012 18:19 2030551

Michael Mikhail, начиная с Office 2007 книги с макросами нужно сохранять в формате "... с поддержкой макросов". В "Центре управления безопасности нужно разрешить выполнение макросов" (выбирайте на свой вкус). Теперь по коду.
Target - Определяем выделенную ячейку

Цитата:

Цитата Michael Mikhail

значение ячейки A1 (либо диапазона ячеек, либо любой ячейки листа) »

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

Код:

If Not IsEmpty(Target.Value) And Not Application.Intersect(Target, Range("A1:C10")) Is Nothing Then

и меняйте значение диапазона в функции Application.Intersect(Target, Range("A1:C10"))
В данном примере комментарии будут добавляться, если будут меняться значения в ячейках диапазона "A1:C10".
Дальше понятно?

Цитата:

Цитата Michael Mikhail

Воспользовался приведённой Вами командой Cstr. Чем принципиально отличается Ваш код от моего? »

Функция Cstr преобразует переменную в текстовую. Можете попробовать без нее...
Отличие моего кода - в форматировании примечания и в некоторых командах, которые ускоряют работу, например, функции листа не пересчитываются (в коде же есть пояснения).

Iska	22-11-2012 19:43 2030641

Цитата:

Цитата okshef

Код:

…Not Application.Intersect(Target, Range("A1:C10")) Is Nothing

Интересная конструкция, надо запомнить.

Michael Mikhail

23-11-2012 13:29 2031155

Спасибо, но почему то функция отмены последнего действия (действий), - стрелочка влево (Ctrl+Z), перестала работать: что ж я теперь, предыдущее значение ячейки не могу вернуть (на случай некорректного ввода текущего)? Кое-что (http://www.excel-vba.ru/chto-umeet-e...tviya-makrosa/) почитал, теперь задумался, - насколько востребованным может быть использование макросов.
Кроме того, код заработал только после того, как я его поместил в сам лист (через: Кнопка "Разработчик", кнопка "Visual Basic," "Лист 1", "View code"), а не в модуль листа (в котором он отказывался работать). При этом код работает только в незанятых (пустых) ячейках, а мне бы хотелось, чтобы примечание появлялось к ячейке, значение которой вычисляется по формулам Excel, из данных других ячеек.

Можно ли как-то задавать позицию комментария на листе (отличную от установленной по умолчанию)?

И ещё, - периодически стало появляться сообщение: "Предупреждение о конфиденциальной информации: документ содержит макросы, элементы управления ActiveX, данные пакета расширения XML или веб-компоненты. Они могут включать личные сведения, которые нельзя удалить с помощью инспектора документов". Если нажать "Отмена", появляется следующее сообщение: "Возникла непредвиденная ошибка. В этом сеансе работы с Excel автовосстановление отключено".

okshef

23-11-2012 22:46 2031485

Цитата:

Цитата Michael Mikhail

я его поместил в сам лист »

это я и понимал под "модулем листа"

Цитата:

Цитата Michael Mikhail

мне бы хотелось, »

Переделал код под вашу "хотелку". Допустим, формулы находятся в диапазоне С1:С10, там же будут и комментарии. Добавил параметры положения комментария.

Цитата:

Цитата Michael Mikhail

периодически стало появляться сообщение »

Так и будет для книг с макросом.

Michael Mikhail

24-11-2012 13:44 2031752

Спасибо, всё работает!

Теперь остаётся только эксперементировать с формулами в выбранном диапазоне.
Насколько я понял отсюда: http://forum.developing.ru/showthrea...BD%D1%8B%D1%85 отменить действие макроса, в общем случае непросто (хотя на других форумах и приводятся различные варианты решения узких задач), но в любом случае, стандартной функцией отмены - стрелочкой влево, после выполнения макроса воспользоваться невозможно. Пробовал использовать скрипт отсюда: http://forum.ru-board.com/topic.cgi?...0903&start=360, на этапе отладки пишет "Compile error. Sub or function not defined" и выделяет команду "Is_sheet_exist".

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

okshef

24-11-2012 15:01 2031816

Цитата:

Цитата Michael Mikhail

можно ли реализовать в общем виде алгоритм отката макроса. »

как создать макрос, действия которого можно было бы отменить кнопкой Ctrl+Z ?

Но это совершенно другая тема. Удачи!

P.S. Michael Mikhail, нашел, как изменить положение коммента по вертикали. В коде

Код:

' положение комментария - изменение числового значения второго параметра мне отследить не удалось

                .Shape.Left = 200

                .Shape.Height = 100

измените .Shape.Height = 100
на

Код:

.Shape.Top = c.Row * c.RowHeight - 5

Поэкспериментируйте с числами и при необходимости поставьте свои.

Michael Mikhail

26-11-2012 10:13 2032970

Спасибо ещё раз - буду экспериментировать.

Время: 04:54.