vba - How to display calculations, values and variables in Excel? -

for didactic purposes perform , display calculations in excel. display calculations use following vba worksheet-function:

function displayformula(range_rng range) string    application.volatile     if range_rng.hasarray         displayformula = "<-- " & " {" & range_rng.formulaarray & "}"        else         displayformula = "<-- " & " " & range_rng.formulaarray    end if end function 

this works, however, i'm stuck implementation of 2 modifications:

1. i display actual values called in range_rng.
2. i display variables instead of ranges. variables assigned in separate cell, next cell called (see graphic below).

column "c" shows (desired) output formats displayformula(b3):

you can try brute force approach.
can't optimized, can satisfy 2 conditions above.

function displayformula2(r range, optional o variant) string     dim a, b, z, x, y, w     dim f string, tf string     dim c range     dim integer      if ismissing(o) o = 0      = array("+", "-", "/", "*", "%", "&", "^", "=", _         "<", ">", "<=", ">=", "<>", "(", ")")      f = r.formulaarray: tf = f     each b in         application.worksheetfunction             tf = .substitute(tf, b, "|")         end     next      z = vba.split(tf, "|")      each w in z         debug.print w         on error resume next         set c = range(w)         on error goto 0         if not c nothing             if isarray(x)                 redim preserve x(ubound(x) + 1): x(ubound(x)) = w                 redim preserve y(ubound(y) + 1): y(ubound(y)) = c.offset(0, o).value2             else                 x = array(w)                 y = array(c.offset(0, o).value2)             end if         end if         set c = nothing     next      if isarray(x)         = lbound(x) ubound(x)             application.worksheetfunction                 f = .substitute(f, x(i), y(i))             end         next     end if     displayformula2 = iif(r.hasarray, "<-- {" & f & "}", "<-- " & f) end function 

by way, don't think need use .volatile removed it.
recalculate long set calculation mode automatic.

actual formula in c3:c5:
c3: =displayformula(b3)
c4: =displayformula2(b4)
c5: =displayformula2(b5,-1)