microsoft excel
I'm trying to make two cells retain background formula after user input and has a circular formula.
A1 = Lbs
A2 = Kgs
Formula
A1 = A2*2.2
A2 = A1/2.2
I know about the thread below but I cannot get this to work for circular formulas.
I have enabled use iterative calculation in preference.
Excel – how to either have direct data input or cell value from formula
You can't do this with Evaluate Formula because this isn't the purpose of the function. That's why it is called evaluate, it is for evaluating the formulas. What you want is some kind of unpacking. This is a bit special need so it isn't implemented as a tool in Excel, but there are solutions if you create some Visual Basic functions/macros.
Create a VBA code module (macro) as you can see in this tutorial.
Module in Insert.Function CellFormula(Target As Range) As String
CellFormula = Target.Formula
End Function
Then enter the following to a cell: =CellFormula(A1)
This will tell the formula of the cell. The only problem with this code is that it only works for one level. If you want to unpack the contained cells formulas too, then you need a more complex code with recursion.
It was a long journey but I created a VBA macro for you that implements this function. I don't state that this code will work for every formula, but it will work in most/some of them. Also, I don't state that this code will generate formulas that is equivalent with the originally entered code or will give the same result as the original.
Option Explicit
Function isChar(char As String) As Boolean
Select Case char
Case "A" To "Z"
isChar = True
Case Else
isChar = False
End Select
End Function
Function isNumber(char As String, isZero As Boolean) As Boolean
Select Case char
Case "0"
If isZero = True Then
isNumber = True
Else
isNumber = False
End If
Case "1" To "9"
isNumber = True
Case Else
isNumber = False
End Select
End Function
Function CellFormulaExpand(formula As String) As String
Dim result As String
Dim previousResult As String
Dim cell As Range
Dim stringArray() As String
Dim arraySize As Integer
Dim n As Integer
Dim trimmer As String
Dim c As Integer 'character number
Dim chr As String 'current character
Dim tempcell As String 'suspected cell's temporaly result
Dim state As Integer 'state machine's state:
Dim stringSize As Integer
result = formula
previousResult = result
state = 0
stringSize = 0
For c = 0 To Len(formula) Step 1
chr = Mid(formula, c + 1, 1)
Select Case state
Case 0
If isChar(chr) Then
state = 1
tempcell = tempcell & chr
ElseIf chr = "$" Then
state = 5
tempcell = tempcell & chr
Else
state = 0
tempcell = ""
End If
Case 1
If isNumber(chr, False) Then
state = 4
tempcell = tempcell & chr
ElseIf isChar(chr) Then
state = 2
tempcell = tempcell & chr
ElseIf chr = "$" Then
state = 6
tempcell = tempcell & chr
Else
state = 0
tempcell = ""
End If
Case 2
If isNumber(chr, False) Then
state = 4
tempcell = tempcell + chr
ElseIf isChar(chr) Then
state = 3
tempcell = tempcell + chr
ElseIf chr = "$" Then
state = 6
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case 3
If isNumber(chr, False) Then
state = 4
tempcell = tempcell + chr
ElseIf chr = "$" Then
state = 6
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case 4
If isNumber(chr, True) Then
state = 4
tempcell = tempcell + chr
Else
state = 0
stringSize = stringSize + 1
ReDim Preserve stringArray(stringSize - 1)
stringArray(stringSize - 1) = tempcell
tempcell = ""
End If
Case 5
If isChar(chr) Then
state = 1
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case 6
If isNumber(chr, False) Then
state = 4
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case Else
state = 0
tempcell = ""
End Select
Next c
If stringSize = 0 Then
CellFormulaExpand = result
Else
arraySize = UBound(stringArray)
For n = 0 To arraySize Step 1
Set cell = Range(stringArray(n))
If Mid(cell.formula, 1, 1) = "=" Then
trimmer = Mid(cell.formula, 2, Len(cell.formula) - 1)
If trimmer <> "" Then
result = Replace(result, stringArray(n), trimmer)
End If
End If
Next
If previousResult <> result Then
result = CellFormulaExpand(result)
End If
End If
CellFormulaExpand = result
End Function
Function CellFormula(rng As Range) As String
CellFormula = CellFormulaExpand(rng.formula)
End Function
To make it work, just create a macro (as I described it in the beginning of the answer) and copy-paste the code. After this, you can use it with =CellFormula(A1) where A1 can be any kind of 1x1 cell.
I created some examples so you can see it in action. In this case, I demonstrate the use with strings. You can see it works perfectly. The only little bug is that somewhy the algorithm changes the semicolons to commas. After you replace them (as I did in this example), you get the correct output.
Here, you can see how it works with numbers. Now, we face the first problem that the algorithm doesn't care about the mathematical operation sequence, that's why the red number is 6 when it should be 10. If we put the sensitive operations (like addition and subtraction) into parenthesis, then the given formula entered back will give the same output as you can see in the green number in the bottom that says 10.
This algorithm is not perfect. I only tried to implement the most common uses, so it can be improved by adding more features that handle other cases like ranges.
As you can see in this example, I used SUM() with a range as a parameter. Since the algorithm decrypts the cells content from top to down, it starts with the replacement of the SUM() parameters than later with anything else. Therefore, the : stays in its place while around it everything is replaced, so new cells are replaced near to it, who will change the meaning of it. Thus the output will be wrong. So in this case, you can only use this macro to study the original formula.
You can use a worksheet change function in vba. Something like
Private Sub Worksheet_Change(ByVal target As Range)
If Intersect(target, Range("e7:e8")) Is Nothing Then Exit sub
Application.enableevents = False
ActiveSheet.range("A4").formula="=if(e7="""",e8*765,e7*960)"
Application.enableevents = True
End Sub
Anytime the sheet changes it checks if the cell changed was e7 or e8. If it was it puts the formula
=if(e7="",e8*765,e7*960)
in cell A4. If you then put a value in cell A4 it will stay until you again change e7 or e8. You may need to adjust to get it to do exactly what you want but the idea is there.
This would go in the worksheet object under microsoft excel objects in vba.
EDIT:
To fit in with comments.
New code, this one will only do something if the cell has a value added or changed, it won't run if you delete a value.
Then it checks if e7 was edited. If it was e8 is deleted. If it wasn't we know e8 was edited so e7 gets deleted. Formula stays the same.
Private Sub Worksheet_Change(ByVal target As Range)
If Intersect(target, Range("e7:e8")) Is Nothing Or target.Value = "" Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Range("A4").Formula = "=if(e7="""",e8*765,e7*960)"
If Intersect(target, Range("e7")) Is Nothing Then
ActiveSheet.Range("e7").ClearContents
Else
ActiveSheet.Range("e8").ClearContents
End If
Application.EnableEvents = True
End Sub
Best Answer
Excel does not work like this.
If both cells have formulas then there is nowhere to get an input value from. That's why this will not work with circular references.
A cell can either have a formula or a value. If you enter a value into a cell that has a formula, the formula will be overwritten. What you are trying to achieve will require at least 3 cells: an entry cell for the value, a cell for the unit and a third cell for the conversion result. You may even want to add a fourth cell for the unit of the result. Consider this screenshot:
The Value is entered in B2, the unit of the value is specified in C2. The conversion is calculated in B3
The unit of the result is calculated in C3 with
=IF(C2="Kgs","Lbs","Kgs"), but that bit is optional.Edit: You can use VBA to convert the value and place it in the other cell. This can be done without writing a formula into the cell, though. If you already use VBA, you might as well do the calculation in VBA instead of using VBA to write a formula into a cell. Here is the VBA for that: