×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Show Formula Contest Automatically
4

Show Formula Contest Automatically

Show Formula Contest Automatically

(OP)
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

RE: Show Formula Contest Automatically

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
 

RE: Show Formula Contest Automatically

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)'  ?

RE: Show Formula Contest Automatically

Oh.

It seems I didn't understand the question correctly.

 

RE: Show Formula Contest Automatically

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
 

RE: Show Formula Contest Automatically

3
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/
 

RE: Show Formula Contest Automatically

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
 

RE: Show Formula Contest Automatically

(OP)
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
 

RE: Show Formula Contest Automatically

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/
 

RE: Show Formula Contest Automatically

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)'  ?

RE: Show Formula Contest Automatically

.... 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)'  ?

RE: Show Formula Contest Automatically

(OP)
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

RE: Show Formula Contest Automatically

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.

RE: Show Formula Contest Automatically

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/
 

RE: Show Formula Contest Automatically

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

RE: Show Formula Contest Automatically

(OP)
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

RE: Show Formula Contest Automatically

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

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

RE: Show Formula Contest Automatically

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/
 

RE: Show Formula Contest Automatically

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.

RE: Show Formula Contest Automatically

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/
 

RE: Show Formula Contest Automatically

(OP)
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

RE: Show Formula Contest Automatically

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/
 

RE: Show Formula Contest Automatically

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

RE: Show Formula Contest Automatically

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.
 

RE: Show Formula Contest Automatically

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)'  ?

RE: Show Formula Contest Automatically

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/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

The Low-Code Digital Transformation Guide
Change the way you develop apps and, in turn, change the way your business operates and engages with customers, leading to new channels of revenue. This ebook is the culmination of 14 years of experience with 4,000 customers that have all transformed their business through low-code development. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close