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
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
You'll need a simple user defined function.
CODE
MyFormula = Adrs.Formula
End Function
RE: Show Formula Contest Automatically
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
It seems I didn't understand the question correctly.
RE: Show Formula Contest Automatically
CODE
'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
RE: Show Formula Contest Automatically
CODE
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
GetFormula = "(" & Right(GetFormula, (Len(GetFormula) - 1)) & ")"
to my code before End Function
RE: Show Formula Contest Automatically
Thank you both for your time.
iv
RE: Show Formula Contest Automatically
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
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
Also note discussion of excel's formula auditing feature in the referenced thread.
=====================================
(2B)+(2B)' ?
RE: Show Formula Contest Automatically
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
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
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
Regards,
iv
RE: Show Formula Contest Automatically
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
h
RE: Show Formula Contest Automatically
(=2*B1+B2/3).
="2 x "&B1&" + "&B2&" / 3"
RE: Show Formula Contest Automatically
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
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
RE: Show Formula Contest Automatically
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
http:/
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Show Formula Contest Automatically
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
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Show Formula Contest Automatically
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
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
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
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Show Formula Contest Automatically
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/