Вытащить адреса из строки формул

[Alkaline]

Думаю над задачкой, которую формализовал следующим образом:


На входе - формула, содержимое какой-то ячейки (в виде ActiveCell.Formula или как переменная типа string). На выходе нужно получить массив переменных типа range, содержащих каждый из диапазонов, указанных в исходной формуле.

Попытка решить "в лоб": MyVar = CStr(ActiveCell.DirectPrecedents.Ad dress) и довольно простой разбор строки на перечисленные через запятую диапазоны.

Проблема: формула "=СУММ(A1:C3)/B2" выдаст только один диапазон (A1:C3), но не B2, т.к. B2 полностью входит в (A1:C3).

Очень не хочется переходить к посимвольному анализу строки... Буду рад даже не решению, а общим идеям, лишь бы по делу.

[Pavel55]

Точно не знаю, правильно ли я вас понял.... а так не подойдёт?

Sub Макрос1()
Dim iRange As Range
Dim iCell As Range
    Range("A1").FormulaLocal = "=СУММ(C1:C5)"
    Set iRange = Range("A1").Precedents.Cells
    For Each iCell In iRange
        MsgBox iCell.Address(0, 0)
    Next
End Sub

[Alkaline]

Э-э, спасибо, Павел, но все-таки не совсем так:

Мне нужно, чтобы для формулы "=СУММ(A1:C3)+B2" выдавалось 2 диапазона: "A1:C3" и "B2". Т.е., во-первых, не нужно всех составляющих ячеек. Во-вторых, в Вашем примере B2 указывается, но лишь как составная часть первого диапазона.

Интересно, как сам Excel обрабатывает строку формул и, в частности, раскрашивает разным цветом разные диапазоны и ссылки на них в момент редактирования формулы.

[Alkaline]

C извращениями, но кое-что придумал:

Sub TestPrecAreas()
ActiveCell.ShowPrecedents
ActiveCell.NavigateArrow True, 1
MsgBox ActiveWindow.Selection.Address
End Sub

Думаю, как получить количество Arrows... Можно, конечно, тыркаться, пока не перестанет меняться адрес.

[Alkaline]

Ну, вот, вроде, решает это! -

Sub TestPrecAreas()
ActiveCell.ShowPrecedents
rangecount = 1
initcell = ActiveCell.Address
While Not currentcell = initcell
ActiveCell.NavigateArrow True, rangecount
currentcell = ActiveWindow.Selection.Address
MsgBox ActiveWindow.Selection.Address
rangecount = rangecount + 1
Range(initcell).Activate
Wend
MsgBox ("Finished!")
ActiveCell.ShowPrecedents Remove:=True
End Sub


Только медленно будет, наверное...

[Alkaline]

Предыдущий вариант не решал для диапазонов, начинающихся с одного и тоого же адреса, один из которых полностью входил в другой!..

В общем, посимвольный анализ строки оказался не таким уж и сложным, гы! ";-D

Примерный алгоритм следующий:

Двигаясь по строке, берем один символ.
Если он - $ (а предварительно формула была принудительно приведена к абсолютной адресации ячеек, типа $A$1), то начиная с него запоминаем все символы, которые идут подряд и каждый из них совпадает с: A-Z (заглавные), 0-9, ":" (как разделитель диапазонов) или "$".

Вопрос со "случайным" $ решается так: в строке знак доллара может не относиться к адресу, только если является частью строки в кавычках, а КАВЫЧКИ ИДУТ ТОЛЬКО ПАРАМИ. Поэтому в движении по строке, если нам встретилась кавычка, мы сразу поиском находим вторую кавычку, игнорируя все, что между ними, и следуем уже с этого места.

Уф, теперь спокойно допишу, чтобы обрабатывал имена листов (это когда ! стоит перед $ ; там тоже есть свои ограничения в используемых символах, либо возникает признак одинарной кавычки), книг и файлов.

Проверьте логику, плиз!


* * *

Пример:

для ячейки с формулой

=СЦЕПИТЬ(F15;" продается в среднем за $ ";$G$15*СРЗНАЧ(G18:J18))

Результатом работы является:

Диапазон 1: $F$15
Диапазон 2: $G$15
Диапазон 3: $G$18:$J$18

[SAS888]

Здравствуйте, Alkaline.
Спасибо за совет. Интересно то, что я сейчас как раз в процессе решения аналогичной проблемы, и путь, к которому я прихожу - аналогичный. (практически такой).
С одной стороны это должно радовать (может это самое рациональное решение), а с другой стороны - все-таки я не думаю, что Excel "раскрашивает" используемые ячейки, пользуясь схожим алгоритмом.

PS. Я не смог смоделировать ситуацию, когда сочетание символов "!$" не являлось бы разделителем источника ссылки и адреса.

[Alkaline]

Когда Excel в анализирует формулу (во время ввода), он получает список всех ячеек и диапазонов, связанных с данной ячейкой. Я надеялся, что для получения этого списка есть встроенная функция, но не нашел таковой (а вдруг она есть?!). Может быть, где-то и описан встроенный алгоритм анализа введенной строки, но даже если и так, он, скорее всего, не доступен пользователям.

Относительно $: ну, а для моей задачи важно искать и отдельный $, когда влияющий диапазон - на той же странице. К тому же хотелось избежать даже случайных совпадений.