×
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 Automatically4

 Forum Search FAQs Links MVPs

## 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&amp;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

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.

### 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

Reducing IR and EM Issues with Automated Via Insertion
The Calibe YieldEnhancer PowerVia utility uses manufacturing requirements to perform automated insertion of DRC/LVS-clean vias. Results show significant improvements in EM/IR results, including substantial reductions in current density violations. Download Now
Run Your Ansys Fluent Simulations at Top Speed
This whitepaper reviews the results of performance testing conducted with Ansys Fluent when making use of the Intel MKL sparse LDU smoother, and it explains how the improved performance results were accomplished. Download Now
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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.