Show Formula Contest Automatically 4

[iv63]

Variables a and b are defined in cells B1 & B2. Cell B3 has a formula 2a+b/3 (=2*B1+B2/3). I want in cell C3 contest of this formula (2*1+2/3) not (=2*B1+B2/3). This can be done manually like this ="(2*"&TEXT(B1,0)&"+"&TEXT(B2,0)&"/3)"
I would appreciate if you already have Macro or VBA that does this automatically and you do not mind sharing it.
Regards,
iv

 

[MintJulep]

I don't think there is a native function that does this.

You'll need a simple user defined function.

Public Function MyFormula(Adrs As Range)
MyFormula = Adrs.Formula
End Function

 

[electricpete]

I cannot offer the direct solution you requested.

However an alternative that may improve the readability is to clearly label each of the input cells with a variable name (like a and b… or more descriptive names like Length and Width). Then use the formula that MintJulep provided above to get a display like “=2*a+b” or “=2*Lengtha+Width”

Note 1 – I find it handy to put the variable name directly to the left of the cell and create the name using Insert/Names/Create/Left column… that way you can’t possibly forget what you called it… the name remains there as a reminder.

Note 2 – variable names beginning with r and c can be problematic.


=====================================
(2B)+(2B)' ?

 

[MintJulep]

Oh.

It seems I didn't understand the question correctly.

 

[jghrist]

Try this:

Function GetFormula(Cell As Range) As String
   'Application.Volatile = True
   If VarType(Cell) = 8 And Not Cell.HasFormula Then
    GetFormula = "'" & Cell.Formula
   Else
    GetFormula = Cell.Formula
   End If
   If Cell.HasArray Then _
     GetFormula = "{" & Cell.Formula & "}"
End Function

Then put the formula =GetFormula(B3) in cell C3

 

[IDS]

The function below will return the function with values substituted for variables if ReturnType = 0, or the evaluated function if ReturnType is anything else, or if it is omitted, see attached screen shot:

Function Eval(Func As String, ParamA As Variant, ValueA As Variant, Optional ReturnType As Long = 1) As Variant
    Dim Eform As String, i As Long

    ' Evaluate a function (Func), replacing the parameters listed in the
    ' range "ParamA" with the corresponding values in "ValueA"

    'Func is a single cell or text string containing the function to be evaluated
    'ParamA and ValueA are single column ranges containing the same number of
    'cells
    
    ' ReturnType = 0 to return the function with values substituted for variables
    ' ReturnType = 1 (default) to return evaluated function

    ParamA = ParamA.Value2
    ValueA = Valuea.Value2
    
    Eform = Func
    For i = 1 To UBound(ParamA, 1) - LBound(ParamA, 1) + 1
        Eform = Replace(Eform, ParamA(i, 1), ValueA(i, 1))
    Next i
    If ReturnType = 0 Then
    Eval = Eform
    Else
    Eval = Evaluate(Eform)
    End If
End Function

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[jghrist]

If you want the result to be surrounded in parentheses instead of having an "=" sign, then add

GetFormula = "(" & Right(GetFormula, (Len(GetFormula) - 1)) & ")"

to my code before End Function

 

[iv63]

jghrist: your function gives the formula (=2*B1+B2/3) but I want formula’s contest (values of cells B1 & B2 eg. 2*1+2/3). Doug’s function works well but it is “too complicated”.
Thank you both for your time.
iv

 

[IDS]

Doug's function works well but it is "too complicated".Thank you both for your time.

What do you mean? 14 lines of code is too complicated?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

Maybe it is the structure of the input that is obectionable (requires names adjacent to values)?

I believe the request at the following thread was similar to the request in this thread. It is not a simple task. Many solutions discussed:

http://www.tek-tips.com/viewthread.cfm?qid=1591952

=====================================
(2B)+(2B)' ?

 

[electricpete]

.... Although I do think using named Ranges as Doug did and leaving the name there will be useful to help document the logic. In that case Doug's code should work very well with miniscule effort.

Also note discussion of excel's formula auditing feature in the referenced thread.

=====================================
(2B)+(2B)' ?

 

[iv63]

electricpete:
You are right about doug's "too complicated" function - it requires names adjacent to values.

macropod's Sub from the referenced thread worked "like a charm". I prefer Function instead of Sub. Is it hard to modify this code? Thank you all for your time.

Sub GetRefs()
Dim MyRange As Range, strFormula As String, strVal As String
With ActiveCell
strFormula = .Formula
For Each MyRange In .Precedents.Cells
With MyRange
strVal = """" & Range(.Address).Value & """"
strFormula = Replace(strFormula, .Address, strVal)
strFormula = Replace(strFormula, .Address(RowAbsolute:=False), strVal)
strFormula = Replace(strFormula, .Address(ColumnAbsolute:=False), strVal)
strFormula = Replace(strFormula, .Address(RowAbsolute:=False, ColumnAbsolute:=False), strVal)
End With
Next
MsgBox .Formula & vbCr & strFormula
End With
End Sub

 

[MintJulep]

Not so easy to convert to a function.

It seems that .precedents used in a function returns the precedents of the cell that the function is in, and that there is no way to point it towards the precedents of some other cell.

 

[IDS]

I think there is a bit of confusion because my function and macropod's sub do two different things.

My function takes a text string (e.g. =F*L^3/3), substitutes values for the function parameters using the values in the specified range, and either returns the value of the function (if ReturnType = 1 or is omitted) or a text string with values in place of letters (if ReturnType = 0). Note that it does not use range names. You can use the same function string with any number of different sets of parameter values, which you can't do using range names.

Macropod's sub takes an active cell formula and substitutes values for the cell addresses, returning a text string. Re-reading the original post I think that is probably what was wanted.

Regarding converting the sub to a function, I agree with MintJulep, there doesn't seem to be any way of getting the cell precedents from a function.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[iv63]

Does .precedents have to be used? Is there an alternative for .precedents?
Regards,
iv

 

[iv63]

I am still looking for solution of using Function instead of Sub in this Sub:

Sub GetRefs()
Dim MyRange As Range, strFormula As String, strVal As String
With ActiveCell
strFormula = .Formula
For Each MyRange In .Precedents.Cells
With MyRange
strVal = """" & Range(.Address).Value & """"
strFormula = Replace(strFormula, .Address, strVal)
strFormula = Replace(strFormula, .Address(RowAbsolute:=False), strVal)
strFormula = Replace(strFormula, .Address(ColumnAbsolute:=False), strVal)
strFormula = Replace(strFormula, .Address(RowAbsolute:=False, ColumnAbsolute:=False), strVal)
End With
Next
MsgBox .Formula & vbCr & strFormula
End With
End Sub

Thank you,
iv

 

[cowski]

I have not tried it, but did PHV's (of tek-tips fame) method work for you?

http://www.tek-tips.com/viewthread.cfm?qid=1646522&page=1

 

[iken]

You could use the concenate function (or "&" as follows)
(=2*B1+B2/3).

="2 x "&B1&" + "&B2&" / 3"

 

[IDS]

I have not tried it, but did PHV's (of tek-tips fame) method work for you?

http://www.tek-tips.com/viewthread.cfm?qid=1646522&page=1

Unless I'm doing something wrong, the PHV solution doesn't work because the .Precedents method does not work inside a function.

I haven't been able to get a function to work, but the sub below does allow a whole column of formulas to be converted in a single operation.

Select the range of formulas (must be a single column) and optionally a separate range for the output (must be a single column of the same length). Then press alt-F8 and run the sub.

If only the formulas were selected the output will be written to the adjacent column (overwriting anything that might be there!). If a second column was selected then the output will be written there.

Sub GetRefs()
    Dim MyRange As Range, strFormula As String, strVal As String, FormCell As Range
    Dim NumRows As Long, FormA() As String, i As Long, Outrange As Range
    
    NumRows = Selection.Rows.Count
    ReDim FormA(1 To NumRows, 1 To 1)
    i = 1
    For Each FormCell In Selection.Areas(1).Cells
        With FormCell
            strFormula = .Formula
            For Each MyRange In .Precedents.Cells
                With MyRange
                    strVal = " " & Range(.Address).Value & " "
                    strFormula = Replace(strFormula, .Address, strVal)
                    strFormula = Replace(strFormula, .Address(RowAbsolute:=False), strVal)
                    strFormula = Replace(strFormula, .Address(ColumnAbsolute:=False), strVal)
                    strFormula = Replace(strFormula, .Address(RowAbsolute:=False, ColumnAbsolute:=False), strVal)
                End With
            Next

            strVal = "' " & .Formula & "; " & strFormula
        End With
        FormA(i, 1) = strVal
        i = i + 1
    Next
    
    With Selection
        If .Areas.Count > 1 Then
            Set Outrange = .Areas(2)
        Else
            Set Outrange = Selection.Offset(0, 1)
        End If
    End With
    Outrange.Value = FormA

End Sub

I'll post a spreadsheet with some examples on my blog tomorrow (time permitting!).


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[iken]

Why go into VBA to the extent as shown above. The simple formulae of ="2 x "&B1&" + "&B2&" / 3" works a treat, is very simple and easy to understand.