Здравствуйте. Задачка: в ячейку2 (лист2) нужно записать число получившееся в результате вычисления формулой в ячейке1 (лист1) (но не формулу, как у меня получилось). В связи с этим поискал на форуме и нашел в архиве похожий вопрос: http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=16687 пост №17 содержит пример, в котором я хочу разобраться , что бы применить к своему случаю. Растолкуйте пожалуйста значения (смысл) команд по-порядку в макросе:
' ZVI:2010-07-25 http://www.planetaexcel.ru/forum.php?thread_id=17730 ' Значение ячеек с именами "ОТКУДА*" будут автоматически скопированы в ячейки с именами "КУДА*" ' Например: ' Имя ячейки B3: "Откуда1" ' Имя ячейки B4: "Куда1" ' При пересчете формул значение из B3 будет скопировано в B4 Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Dim Rng As Range, N As Name, s As String Application.EnableEvents = False ' откюч. обработку событий On Error Resume Next ' вкл. игнорирование ошибок выполнения For Each N In Names s = UCase(N.Name) ' переводит значение строковой переменной на все заглавные буквы If s Like "ОТКУДА*" Then Set Rng = Range(N.RefersTo) If Rng.Parent Is Sh Then Range(Names("КУДА" & Mid(s, 7)).RefersTo) = Rng.Value End If End If Application.EnableEvents = True ' включ. обработку событий Next End Sub Хочу не только решить задачку, но и понять этот макрос конкретно. например подчеркнутое - это имя процедуры , а SheetCalculate- это событие или что? и что должно передаваться на вход процедуры в качестве Sh.
Вот "за обеденным бутербродом" добавил комментарии и подправил имена переменных (по фэншую )
Код
' ZVI:2010-07-25 http://www.planetaexcel.ru/forum.php?thread_id=17730
' Значение ячеек с именами "ОТКУДА*" будут автоматически скопированы в ячейки с именами "КУДА*"
' Например:
' Имя ячейки B3: "Откуда1"
' Имя ячейки B4: "Куда1"
' При пересчете формул значение из B3 будет скопировано в B4
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) ' обработчик событий "пересчёт листа" (Sh - лист, на котором произошло событие)
Dim rRng As Range, oName As Name, sNAME As String
Application.EnableEvents = False ' отключаем возникновение ЛЮБЫХ событий чтобы не возникало пересчёта формул при изменениях, вносимых макросом на лист
On Error Resume Next ' игнорируем ошибки (они могут возникнуть при отсутствии в книге требуемых имён)
For Each oName In Names ' цикл по элементам (объектам) коллекции Names данной книги
sNAME = UCase(oName.Name) ' имя объекта oName ЗАГЛАВНЫМИ буквами (сравнение оператором Like чувствительно к регистру)
If sNAME Like "ОТКУДА*" Then ' если имя начинается с ОТКУДА (* - любая последовательность символов)
' т.к. используются только однозначные номера ОТКУДА-КУДА, то корректнее было бы записать:
'If sNAME Like "ОТКУДА?" Then' если имя начинается с ОТКУДА и имеет ещё 1 символ(? - любой одиночный символ)
Set rRng = Range(oName.RefersTo) ' диапазон (ячейка), имеющий имя, начинающееся с ОТКУДА
If rRng.Parent Is Sh Then ' если имя, начинающееся с ОТКУДА, находится на том же листе, где был пересчёт листа
Range(Names("КУДА" & Mid(sNAME, 7)).RefersTo) = rRng.Value ' скопировать в ячейку с именем "КУДА" & Mid(sNAME, 7) - седьмой символ имени диапазона ОТКУДА (его номер)
End If
End If
Application.EnableEvents = True ' включаем возникновение событий
Next
End Sub
С уважением, Алексей(ИМХО: Excel-2003 - THE BEST!!!)
Спасибо. Вот не пойму зачем в этих конструкциях применяется RefersTo Set rRng = Range(oName.RefersTo) Range(Names("КУДА" & Mid(sNAME, 7)).RefersTo) = rRng.Value
Будет понятно, если или книжку прочитать или хотя бы справку по методам. В именах содержатся ссылки - RefersTo, которые и определяют диапазон имени. Parent - даже Google переведет, что это родитель. В программировании это родительский объект(т.е. тот, к которому принадлежит объект, из которого сам метод вызван).
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Для диапазона (Range) "родитель" (Parent) - лист (Sheet), для листа - книга (Workbook), для книги - приложение (Application) При возникновении события изменения любого листа возникает событие Workbook_SheetCalculate, которое в качестве аргумента получает ссылку на лист, на котором это событие произошло (Sh). Процедура обработки события перебирает все имена (oName) в коллекции Names Если имя очередного элемента коллекции совпадает с шаблоном "ОТКУДА*", то: 1. Переменной rRange присваивается ссылка на диапазон (ячейку), которому назначено это имя: Set rRng = Range(oName.RefersTo) (это, к стати, вполне можно было бы заменить на Set rRng = oName.RefersToRange. Посмотрите Справку. Там вполне понятно и просто написано про свойства имён RefersTo и RefersToRange.) 2. Проверяется "родитель" диапазона rRng Если диапазон rRng (и имя, начинающееся с ОТКУДА, соответственно тоже), находится на том же листе, где был пересчёт листа, то из имени берётся СЕДЬМОЙ символ (ОТКУДА - 6 символов, 7-й - номер этого ОТКУДА) и диапазону (ячейке), имеющему имя, состоящее из "КУДА" и этого седьмого символа (Range(Names("КУДА" & Mid(sNAME, 7)).RefersTo), присваивается значение из ОТКУДА
Ну, блин, и написАл! Не уверен, что стало понятнее, но я старался
С уважением, Алексей(ИМХО: Excel-2003 - THE BEST!!!)
Спасибо, очень доходчиво. Я спросил про RefersTo потому, что у меня без его применения получилось решить задачку (упрощенно выглядит так) Worksheets("Лист2").Range("ячейка2") = Worksheets("Лист1").Range("ячейка1") и так Worksheets("Лист2").Range(Names("ячейка2").RefersTo) = Worksheets("Лист1").Range(Names("ячейка1").RefersTo) Результат одинаковый. В чем разница по-существу не пойму.
Действительно, Range("RngSh1").Value чаще всего вернёт то же значение, что и ThisWorkbook.Names("RngSh1").RefersToRange.Value , НО прямое обращение к диапазону по его имени (первый вариант) иногда вызывает ошибку если именованный диапазон находится в другом модуле (на другом листе) по сравнению с модулем, где выполняется код. Эти ошибки ("Object Variable not set", кажется) при обращении к диапазону по имени возникают "непонятно когда" и поэтому чтобы её наверняка избежать часто используется более сложное, но и 100%-надёжное обращение через коллекцию Names. В нашем случае это тем более актуально, т.к. ошибки в коде игнорируются и о том, что они возникают, мы даже и не узнаем, пока не обнаружим, что на некоторых листах макрос не переносит данные.
С уважением, Алексей(ИМХО: Excel-2003 - THE BEST!!!)
Отличная подсказка, The_Prist, спасибо. Мне это тоже пригодится.
Цитата
Alex_ST пишет: ... ошибки в коде игнорируются и о том, что они возникают, мы даже и не узнаем, пока не обнаружим, что на некоторых листах макрос не переносит данные.
В моем случае лучше узнать о сбое в работе программы через ошибку , чем быть в неведении и допустить неправильные расчеты. В общем спасибо всем. Всё понял.
Как раз наоборот, если обращаться к именованным диапазонам как к элементам коллекции Names, то ненужных ошибок не возникнет и макрос будет работать наверняка. А если использовать более простое обращение типа Range("MyRangeName").Value или ещё проще [MyRangeName].Value , то могут возникнуть ошибки, которые будут проигнорированы и макрос отработает не верно.
Добрый вечер, коллеги. Мало пока понимаю в макросах, но передо мной возникла задача хранения формул в одном экземпляре для уменьшения объема файла.
Т.е. имеется массив ("умная таблица"), где половина полей (колонок) - результаты вычислений - массив занимает всю оперативную память и не дает работать с собой. Поэтому для уменьшения объема файла хочу хранить формулы в единственном экземпляре (на отдельном листе), и применять эти формулы по запуску макроса - нажатию кнопки. Внимание вопрос: Правильно ли я понимаю, что все выше описанное в теме решает и мою задачу в т.ч., но с небольшими доработками? Или посоветуете создать отдельную тему для обсуждения?
Если мой вопрос не является аналогом или логическим продолжением темы, прошу не продолжать обсуждение, а послать меня в нужном направлении.
Немного подробностей: в столбце не все ячейки имеют одинаковые формулы, поэтому хочу создать не формулу для всего столбца, а наборы формул для строк определенного вида - вид буду прописывать в отдельной колонке в массиве.