Рекурсивно извлекать значения из формулы в VBA

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

mysheet = "Sheet1" Sheets(mysheet).Range("A1").Formula = "=+B1" Sheets(mysheet).Range("B1").Formula = "=+C1-C2" Sheets(mysheet).Range("C1").Value = "value:val1" Sheets(mysheet).Range("C2").Formula = "=+D1-D2" Sheets(mysheet).Range("D1").Value = "value:val2" Sheets(mysheet).Range("D2").Value = "value:val3" 

Можете ли вы дать мне подсказку (или, лучше, код кода) о том, какая функция (рекурсивная, может быть?) Может вернуть строку (назовем ее my_formula) так, чтобы команда

  MsgBox(my_formula) 

вернет следующее:

  A1 = +value:val1 - value:val2 + value:val3 

? пожалуйста, помогите мне

спасибо

ОБНОВЛЕНИЕ ПОСЛЕ МЕНЬШЕГО ЧЕРЕЗ ЧАСЫ

Привет всем и большое спасибо за ваши ответы. Я хотел бы указать, что значения в ячейках являются фактически строками + У меня есть только простые операции (+ и -) + Все зависимости находятся на одном листе + У меня нет диапазонов, таких как $ X $ 1. Тем не менее, я знаю о методах / свойствах, опубликованных некоторыми из вас (прецедент, NavigateArrow …), но я попросил идею о том, как наилучшим образом использовать этот метод / свойства без головных болей.

Еще раз спасибо

Этот код будет работать для вашего примера – но YMMV для того, как он будет масштабироваться до более сложных формул в реальной жизни. Тем не менее, это поможет вам начать работу, а затем – с тем, как переусердствовать над Precedents коллекции Range . Я прокомментировал код, но самый простой способ понять это – перейти в режим отладки с помощью F8.

 Option Explicit Sub Test() Dim ws As Worksheet Dim rng As Range Dim strOutput As String 'your test case from the original question Set ws = ThisWorkbook.Sheets("Sheet1") With ws .Range("A1").Formula = "=+B1" .Range("B1").Formula = "=+C1-C2" .Range("C1").Value = "10" .Range("C2").Formula = "=+D1-D2" .Range("D1").Value = "20" .Range("D2").Value = "30" End With 'the cell you want to start from Set rng = Sheet1.Range("A1") 'get the output from calling the recursive function 'note we pass the original formula in to kick off the function strOutput = rng.Address(0, 0) & GetFullFormula(rng, rng.Formula) 'show user MsgBox strOutput End Sub Function GetFullFormula(rng As Range, strFormula As String) As String Dim rngPrecedents As Range Dim rngPrecedent As Range Dim strPrecedentAddress As String Dim strPrecedentFormula As String If rng.HasFormula Then Set rngPrecedents = rng.Precedents For Each rngPrecedent In rngPrecedents 'get the precedent cell address to check if in current formula (without $) strPrecedentAddress = rngPrecedent.Address(0, 0) 'Debug.Print strFormula 'substiute into formula if matching a range address If rngPrecedent.HasFormula Then If InStr(1, strFormula, strPrecedentAddress, vbBinaryCompare) Then 'strip = from formula and put in brackets to preserve ordering strPrecedentFormula = "(" & Mid(rngPrecedent.Formula, 2, Len(rngPrecedent.Formula) - 1) & ")" 'replace our formula with precedent formula strFormula = Replace(strFormula, strPrecedentAddress, strPrecedentFormula) End If 'carry on with recursion - passes formula back into function for expansion GetFullFormula rngPrecedent, strFormula Else 'just a value If InStr(1, strFormula, strPrecedentAddress, vbBinaryCompare) Then 'replace the address with the value strFormula = Replace(strFormula, strPrecedentAddress, rngPrecedent.Value) End If End If Next rngPrecedent End If GetFullFormula = strFormula End Function 

Вывод:

 =+(+10-(+20-30)) 

Будет проблема с этим кодом, если вы используете ссылки на ячейки, такие как $A$1 потому что функция ожидает ссылки на явные ячейки. Код может быть расширен для обработки этих случаев и именованных диапазонов, но это всего лишь базовый пример.

  • Рекурсивный поиск в excel
  • Рекурсивно искать файлы для информации, содержащейся в ячейке excel и обратном пути
  • Перечислите все модули DOORS в папке рекурсивно через DXL и напишите в Excel Sheet
  • Внедрение кода FETET в код VBA Excel
  • Формула для расчета следующей даты, исключая список дат
  • Рекурсивная функция в Excel VBA с использованием GoTo, сочетания символов из массива
  • Interesting Posts

    VBA: DateAdd и EoMonth работают нормально в середине цикла

    Ошибка времени выполнения VBA Excel VBA При получении данных из других листов

    Загрузка триггерного файла с кнопки надстройки надстройки – OfficeJS

    генерируя диапазон из других ячеек excel vba

    Excel Минимальное значение, зависящее от сгруппированных данных

    Получение идентификаторов на основе нескольких строковых критериев

    Нужна ячейка для возврата с гиперссылкой

    Вставка скопированного олеобъекта на новый лист в том же месте

    Макро / VBA: Наборы данных имеют разные размеры, как я могу гарантировать, что макрос работает только на ячейках с данными в них?

    vba Excel: параметры в под. Что такое изменение?

    Как заменить кавычки в Excel формулой SUBSTITUTE?

    Хотите цветной диапазон A2: P

    Как рассчитать сумму с учетом условий?

    Как конвертировать XLS в CSV-файлы с помощью python?

    Применить 'On error GoTo' только для определенного диапазона

    Давайте будем гением компьютера.