Замена зависимостей другой ссылкой на ячейку в VBA

Question

Я написал некоторый код VBA, который занимает одну ячейку и идентифицирует все ее зависимости в рабочей книге (посредством анализа NavigateArrow) и добавляет их расположение в массиве. Отсюда я хочу иметь возможность обновлять каждый зависимый элемент и изменять ссылку на исходную отдельную ячейку на другую указанную ячейку.

Особая трудность, с которой я здесь сталкиваюсь, заключается в том, что, хотя я знаю, где находится каждый зависимый элемент, ссылка на исходную ячейку может быть в начале, середине или конце формулы и может быть не закреплена, строка / столбец / оба привязаны, могут находиться на другом листе и, следовательно, иметь ссылку на лист перед ним и т. д. и т. д. Поэтому я не могу легко найти и заменить в каждой зависимой ячейке из-за этих потенциальных различий, плюс я хочу сохранить исходную привязку, сохраненную в ссылка на каждую ячейку.

Есть ли элегантное - или даже не элегантное - решение VBA для этой проблемы?

Answer 1

Я думаю, что вы ищете регулярные выражения или регулярные выражения.

Следующая картина

([A-Z0-9]*)!(\${0,1})([A-Z]{1,3})(\${0,1})([0-9]*)

будет соответствовать чему-либо вроде "Sheet1!A1", "Sheet1!$A$1", "Sheet1!$A1", "Sheet1!A$1"

Объяснение:

([A-Z0-9]*)!  =  Find anything that is before "!"
(\${0,1}) = $ or nothing
([A-Z]{1,3})  = between one and three letters
([0-9]*)  = Any number

Вы легко сможете изменить этот шаблон, чтобы он соответствовал только тому, что вы хотите. В частности, ([A-Z0-9]*)!(\${0,1})B(\${0,1})1 будет совпадать только с чем-то с B($)1 в нем... Создайте шаблон Regexp с помощью манипуляции со строками, и он должен быть хорошим.

Вам нужно будет сослаться (Инструмент> Справка) на "Регулярные выражения Microsoft VBScript 5.5"

Попробуйте следующий код, это даст вам все инструменты для достижения вашей цели

Sub ReplaceReference()
' Reference: Microsoft VBScript Regular Expressions 5.5

Dim RegEx As Object
Set RegEx = New RegExp

Dim s As String

' Here I have hardcoded the reference to the original cell for demonstration purposes
s = "Sheet1!$AB$2"


' Replacement: New sheetname, New Column, new row number
Dim NewCol As String, NewRow As String
NewCol = "C"
NewRow = "10"

Dim NewSheet As String
NewSheet = "Sheet2"


With RegEx
    .Pattern = "([A-Z0-9]*)!(\${0,1})([A-Z]{1,3})(\${0,1})([1-9]*)"
    .IgnoreCase = True
    .Global = True
End With


Debug.Print RegEx.Replace(s, NewSheet & "!" & "$2" & NewCol & "$4" & NewRow)


End Sub

Ура, Жюльен

Answer 2

Почему бы VBA не вырезать и не вставить исходную ячейку? Затем Excel отрегулирует ссылки на ячейки во всем их великолепном разнообразии.

Я создал несколько ячеек со ссылками, а затем использовал Макро-рекордер, чтобы увидеть, что может произойти в сгенерированном VBA при выборе ячейки, вырезании и копировании содержимого. Поведение, как и ожидалось, выглядит следующим образом:

Range("A1").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste

Это будет применяться к следующему листу:

A1  1
A2  =A1
A3  =$A$1
A4  =1+A1
A5  =1+A1+1

После запуска макроса все ссылки указывают на B1 ячейка назначения.