Show Formula Contest Automatically
4

Question

Show Formula Contest Automatically

iv63 (Structural)

(OP)

8 Dec 10 14:24

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 (Mechanical) · Answer 1 · 2010-12-08 14:49:55

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

You'll need a simple user defined function.

CODE

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

electricpete (Electrical) · Answer 2 · 2010-12-08 14:56:31

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 (Mechanical) · Answer 3 · 2010-12-08 15:06:01

Oh.

It seems I didn't understand the question correctly.

jghrist (Electrical) · Answer 4 · 2010-12-08 17:21:02

Try this:

CODE

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 (Civil/Environmental) · Answer 5 · 2010-12-08 18:01:10

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:

CODE

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/

http://files.engineering.com/getfile.aspx?folder=c516e2c5-5876-456b-8b7e-89

jghrist (Electrical) · Answer 6 · 2010-12-09 11:49:12

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 (Structural) · Answer 7 · 2010-12-09 13:18:18

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 (Civil/Environmental) · Answer 8 · 2010-12-09 16:55:20

Quote:
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 (Electrical) · Answer 9 · 2010-12-09 20:10:25

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 (Electrical) · Answer 10 · 2010-12-09 23:35:03

.... 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 (Structural) · Answer 11 · 2010-12-13 15:43:01

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 (Mechanical) · Answer 12 · 2010-12-13 17:32:03

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 (Civil/Environmental) · Answer 13 · 2010-12-13 19:26:05

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 (Structural) · Answer 14 · 2010-12-14 15:46:19

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

iv63 (Structural) · Answer 15 · 2011-04-26 11:32:39

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 (Mechanical) · Answer 16 · 2011-04-26 13:22:14

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 (Mechanical) · Answer 17 · 2011-04-26 15:59:41

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

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

IDS (Civil/Environmental) · Answer 18 · 2011-04-27 08:42:03

Quote:

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.

CODE

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 (Mechanical) · Answer 19 · 2011-04-27 18:05:16

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.

IDS (Civil/Environmental) · Answer 20 · 2011-04-27 18:40:07

Quote:
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

Because:

1: Typing in the formula for each cell where it is required takes much longer than selecting a range and typing Alt-F8

2: If the purpose is auditing formulae already entered in the spreadsheet, it's a bit pointless using a procedure that requires manually entering the formulae again.

3: Copying and pasting the VBA provided is a simple operation that should be within the capabilities of anyone who uses Excel for engineering purposes.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

IDS (Civil/Environmental) · Answer 21 · 2011-04-27 20:57:39

Blog post and sample spreadsheet for download here:

http://newtonexcelbach.wordpress.com/2011/04/28/showing-numerical-values-of-a-formula/

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

iv63 (Structural) · Answer 22 · 2011-04-28 10:06:56

Doug:
Big star for you!!! My coworker told me that her previous company has the Function. I will keep "searching" ... Thank you all for your valuable contributions.
iv

IDS (Civil/Environmental) · Answer 23 · 2011-04-28 17:54:02

iv63 - if you do find a function that does this I'd be very interested to see it.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

GrahamG (Structural) · Answer 24 · 2011-04-29 14:54:27

A small modification makes the result dynamic and units can be added.

Sub ConvertFormulaToText()
    Dim intRow As Integer
    Dim intCol As Integer
    Dim intCountRow As Integer
    Dim strFormula As String
    Dim myRange As Range
    Dim strVal As String
    Dim strText As String
    Dim strAdd As String
    Dim strFormat As String

    If Selection.Columns.Count > 1 Then
        MsgBox "Too many columns selected"
        Exit Sub
    End If

    intCol = Selection.Column
    intRow = Selection.Row
    strFormat = "0.00"

    For intCountRow = 1 To Selection.Rows.Count
        Cells(intRow + intCountRow - 1, intCol).Select
        strFormula = ActiveCell.Formula
        strAdd = ActiveCell.Address(False, False)

        If Len(strFormula) = 0 Then GoTo MissRow

        For Each myRange In ActiveCell.Precedents.Cells
            With myRange
                strVal = " " & Range(.Address).Address & " "
                strText = Chr(34) & " & Text(" & strVal & "," & Chr(34) & strFormat & Chr(34) & ") &" & Chr(34)
                strFormula = Replace(strFormula, .Address, strText)
                strFormula = Replace(strFormula, .Address(RowAbsolute:=False), strText)
                strFormula = Replace(strFormula, .Address(ColumnAbsolute:=False), strText)
                strFormula = Replace(strFormula, .Address(RowAbsolute:=False, ColumnAbsolute:=False), strText)
            End With
        Next

        strFormula = Chr(34) & strFormula & Chr(34)

        'Add the result
        strFormula = strFormula & " & " & Chr(34) & "=" & Chr(34) & " & " & "TEXT(" & strAdd & "," & Chr(34) & strFormat & Chr(34) & ")"

        'Add units
        If Len(ActiveCell.Offset(0, 1).Value) Then strFormula = strFormula & " & " & Chr(34) & " " & ActiveCell.Offset(0, 1).Value & Chr(34)

        'Add some spaces to improve readability
        strFormula = Replace(strFormula, "=", " = ")
        strFormula = Replace(strFormula, "+", " + ")
        strFormula = Replace(strFormula, "-", " - ")

        'Add =
        ActiveCell.Offset(0, 2).Formula = "=" & strFormula

MissRow:

    Next intCountRow

End Sub

http://files.engineering.com/getfile.aspx?folder=45c43022-d74b-4ebd-9da6-58

iken (Mechanical) · Answer 25 · 2011-05-01 16:05:53

IDS

How does your method become easier than selecting range (as you also need to do), and hitting crtl D?

For formulae checking, you need to check all the lines in your routine, where you only need to check the first formulae. And now in excel 2010, you get a little icon showing if your formulaeas are inconsistant - very handy.

I still think on alot of items, people seem to think VBA is the way to go, when excel is more than cabable to achieve what is requried in the "front view" of excel (without stepping in the behind the scenes VBA).

I was also told some years back, that doing a simple excerise in VBA, will be alot slower (tie up your computer) than if you use excels built in functions.

electricpete (Electrical) · Answer 26 · 2011-05-01 18:08:16

iken - the original question did not ask for a strategy to design a spreadsheet... it asked for a way to display formula's with values inserted.

In some cases it is required not only to be easily created, but easily checked by a 3rd party (well documented).

Personally, I don't see a lot of value in inserting a number into the forumula... I prefer to use named ranges as mentioned 8 Dec 10 14:56 so the formula ends up looking like a formula you read in a textbook... with names.... easy to verify imo by looking at the formula which includes names. But each person and task has it's own demans and knowing how to accomplish things different ways is helpful.

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

IRstuff (Aerospace) · Answer 27 · 2011-05-01 18:49:45

This particular type of problem seems to be unique to structural engineering, and it appears that someone other than the creator of the calculation demands that equations be displayed with all values substituted, probably because it simplifies the hand checking.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize

IDS (Civil/Environmental) · Answer 28 · 2011-05-01 21:44:58

iken - you are right, if the only formula you ever use is (=2*B1+B2/3) then it is easier to use your string formula and copy it down.

Possibly iv63 sometines uses other formulas though, and only gave that as an example.

In general, I see here people going to great lengths to avoid VBA far more often than usining VBA unnecessarily. Also if speed is an issue a well written VBA routine can often be quicker than using only spreadsheet formulas.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

Come Join Us!

Posting Guidelines

Related Projects

Show Formula Contest Automatically 4