Страницы: 1
RSS
VBA - запись в ячейку 2 результата вычисления формулой 1
 
Здравствуйте.
Задачка: в ячейку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.
Изменено: ArtemonX - 19.02.2013 20:19:50
 
Вот "за обеденным бутербродом" добавил комментарии и подправил имена переменных (по фэншую :) )
Код
' 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  

и про Parent  не понятно.
 
Будет понятно, если или книжку прочитать или хотя бы справку по методам. В именах содержатся ссылки - 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), присваивается значение из ОТКУДА

Ну, блин, и написАл! Не уверен, что стало понятнее, но я старался  :D
С уважением, Алексей (ИМХО: 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 , то могут возникнуть ошибки, которые будут проигнорированы и макрос отработает не верно.
Изменено: Alex_ST - 19.02.2013 20:24:43
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
Добрый вечер, коллеги.
Мало пока понимаю в макросах, но передо мной возникла задача хранения формул в одном экземпляре для уменьшения объема файла.

Т.е. имеется массив ("умная таблица"), где половина полей (колонок) - результаты вычислений - массив занимает всю оперативную память и не дает работать с собой. Поэтому для уменьшения объема файла хочу хранить формулы в единственном экземпляре (на отдельном листе), и применять эти формулы по запуску макроса - нажатию кнопки.
Внимание вопрос:
Правильно ли я понимаю, что все выше описанное в теме решает и мою задачу в т.ч., но с небольшими доработками? Или посоветуете создать отдельную тему для обсуждения?

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

Немного подробностей: в столбце не все ячейки имеют одинаковые формулы, поэтому хочу создать не формулу для всего столбца, а наборы формул для строк определенного вида - вид буду прописывать в отдельной колонке в массиве.
Изменено: it.k.bondarev - 16.08.2017 18:08:28
Страницы: 1
Наверх