title: Excel VBA: Compare formulas / formula structure of two cells
body: <p>I am trying to write a code where I test whether the formula / formula structure in two cells are comparable. The purpose of the code is to check if the formula in one cell has been copied from e.g. the cell to the left or the cell above. Hence, I need to test if the structure of the formula is the same, if absolute cell references are the same and relative cell references change like they would when you copy a cell. </p>
  
  <p>Example:<br>
  Let's say Cell B5 in Sheet2 contains the formula "=Sheet1!<strong><em>B3</em></strong>+$A$1/10". I would then like to check if cell C5 contains the formula "=Sheet1!<strong><em>C3</em></strong>+$A$1/10" and get true/false as the result. </p>
  
  <p>I know it's customary to post the code you are working on here, but I am not sure if it's beneficial or just confusing in this case. I have tried three approaches this far:</p>
  
  <ol>
  <li><p>Trace direct precedents of the original cell (let's say B5 from the example above), offset them and replace them in the formula extracted from B5 and check if the formula is equal to the formula in C5. <em>Does not work as trace precedents can't handle off-sheet references.</em> </p></li>
  <li><p>Get all cell references in a formula by using RegExp. Offset these references and replace them in the formula extracted from B5 and test if this formula is equal to C5. This approach will probably work if I am able to finish it, but it'll be an incredibly large and complicated sub with a lot of text handling and a lot of work for something that I suspect there is an easier approach to.</p></li>
  <li><p>Use a "help" sheet where I copy cell B5 to C5 and check if the formula is equal to the original formula in cell C5. It'll work, but takes waaaaay to much time if I want to check a large range/sheet. </p></li>
  </ol>
  
  <p>Anyone have any suggestions?        </p>