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

[Michael Mikhail]

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

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

[Iska]

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

[okshef]

В модуль нужного листа
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]

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

[okshef]

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

[Michael Mikhail]

Но у меня одна переменная - значение ячейки 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]

просит внести какие-то изменения в настройках, »
Приведите точное сообщение.

[Michael Mikhail]

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

[okshef]

Michael Mikhail, начиная с Office 2007 книги с макросами нужно сохранять в формате "... с поддержкой макросов". В "Центре управления безопасности нужно разрешить выполнение макросов" (выбирайте на свой вкус). Теперь по коду.
Target - Определяем выделенную ячейку (http://www.firststeps.ru/vba/excel/r.php?50)
значение ячейки 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".
Дальше понятно?
Воспользовался приведённой Вами командой Cstr. Чем принципиально отличается Ваш код от моего? »
Функция Cstr преобразует переменную в текстовую. Можете попробовать без нее...
Отличие моего кода - в форматировании примечания и в некоторых командах, которые ускоряют работу, например, функции листа не пересчитываются (в коде же есть пояснения).

[Iska]

…Not Application.Intersect(Target, Range("A1:C10")) Is Nothing »
Интересная конструкция, надо запомнить.

[Michael Mikhail]

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

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

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

[okshef]

я его поместил в сам лист »
это я и понимал под "модулем листа"мне бы хотелось, »
Переделал код под вашу "хотелку". Допустим, формулы находятся в диапазоне С1:С10, там же будут и комментарии. Добавил параметры положения комментария.
Private Sub Worksheet_Calculate()
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
For Each c In Range("C1:C10")
If c.Comment Is Nothing Then c.AddComment
'--------------------------------------------
' получение значения "comtext" в ваших руках - формула в скобках любая.
'-----------------------------------------
comtext = CStr(c.Value / 2)
'-----------------------------------------
With c.Comment
.Text Text:=comtext
' чтобы коммент был виден, раскомментируйте строчку ниже
' .Visible = True
.Shape.TextFrame.AutoSize = True
' положение комментария - изменение числового значения второго параметра мне отследить не удалось
.Shape.Left = 200
.Shape.Height = 100
End With
' форматирование комментария
'--------------------------------------------
With c.Comment.Shape.TextFrame
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.AutoSize = True
.Characters.Font.Bold = True
.Characters.Font.Size = 9
End With
Next
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
периодически стало появляться сообщение »
Так и будет для книг с макросом.

[Michael Mikhail]

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

Теперь остаётся только эксперементировать с формулами в выбранном диапазоне.
Насколько я понял отсюда: http://forum.developing.ru/showthread.php/21420-%D0%92%D0%BE%D0%B7%D0%BC%D0%BE%D0%B6%D0%BD%D0%BE%D1%81%D1%82%D1%8C-%D0%BE%D1%82%D0%BC%D0%B5%D0%BD%D0%B0-%D0%B4%D0%B5%D0%B9%D1%81%D1%82%D0%B2%D0%B8%D0%B9-%D0%BF%D0%BE%D1%81%D0%BB%D0%B5-%D0%B2%D0%B2%D0%BE%D0%B4%D0%B0-%D0%B2-%D1%8F%D1%87%D0%B5%D0%B9%D0%BA%D1%83-Excel-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85 отменить действие макроса, в общем случае непросто (хотя на других форумах и приводятся различные варианты решения узких задач), но в любом случае, стандартной функцией отмены - стрелочкой влево, после выполнения макроса воспользоваться невозможно. Пробовал использовать скрипт отсюда: http://forum.ru-board.com/topic.cgi?forum=33&topic=10903&start=360, на этапе отладки пишет "Compile error. Sub or function not defined" и выделяет команду "Is_sheet_exist".

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

[okshef]

можно ли реализовать в общем виде алгоритм отката макроса. »
как создать макрос, действия которого можно было бы отменить кнопкой Ctrl+Z ? (http://www.planetaexcel.ru/forum.php?thread_id=38243)

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

P.S. Michael Mikhail, нашел, как изменить положение коммента по вертикали. В коде
' положение комментария - изменение числового значения второго параметра мне отследить не удалось
.Shape.Left = 200
.Shape.Height = 100
измените .Shape.Height = 100
на
.Shape.Top = c.Row * c.RowHeight - 5
Поэкспериментируйте с числами и при необходимости поставьте свои.

[Michael Mikhail]

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