Show a Formula in Another Cell

[Zack Barresse]

LOL! You're good!

I just made a post to the KB with this:

=Function FTEXT(f As Range)
If f.HasFormula Then
    FTEXT = f.Formula
Else: FTEXT = f
    End If
End Function

I thought it was too bare-boned. Plus any numerical value would've come out as textual, and I didn't like that idea! But this seems to work ok!

[Mike_R]

For a less invasive appraoch, how about something like the following:

Sub ShowFormulas()
With ActiveWindow
    .NewWindow
    .DisplayFormulas = True
    .Parent.Windows.Arrange ArrangeStyle:=xlVertical
End With
End Sub

,
Mike

[Zack Barresse]

You know, I just realized that the UDF I posted was first posted by Mark007! (Sorry Mark) LOL!

You could do it with a nice UDF too:

Function ShowFormula(R As Range)
ShowFormula = R.Formula
End Function

[Ken Wright]

The other option would be to write a comment to every cell containing a formula, and then display the comment. This then gives you an easy option to switch on and off using view/comments. Gets a bit messy with lots of contiguous cells with formulas, but no more so than anything else.

Sub Formulas() 
Dim cel As Range
On Error Resume Next
Application.ScreenUpdating = False
For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
    With cel
        .AddComment
        .Comment.Visible = True
        .Comment.Text Text:=.Formula
    End With
Next cel
Application.ScreenUpdating = True
End Sub

Regards
Ken.....................

[Zack Barresse]

Nice one Ken!

Here is an addition to your code that will resize the comment box also:

Sub Formulas()
Dim cel As Range
Dim com As String
On Error Resume Next
Application.ScreenUpdating = False
For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
    With cel
        If cel.Comment.Text = "" Then
            .AddComment
            .Comment.Visible = True
            .Comment.Text Text:=.Formula
            .Comment.Shape.ScaleHeight 0.27, msoFalse, msoScaleFromTopLeft
        End If
    End With
Next cel
Application.ScreenUpdating = True
End Sub

The only thing this really won't do, is adjust if you already have a comment in a cell with a formula. Also if you have formulas in consecutive columns (on the same row) as the comment fields will most likely overlap each other.

[tommy bak]

Well, my code actually looks a lot like Zack's code, but I'll post it anyway.
For non-us users it's pracical to have the comment in their language.
This code will change all comments in selection (if any)
I think the only difference is the use of FormulaLocal and AutoSize.

Sub show_formula_in_comment()
Dim rCell As Range, OldText As String
Application.ScreenUpdating = False
On Error Resume Next
For Each rCell In Selection.SpecialCells(xlCellTypeFormulas, 23)
    With rCell
        .AddComment
        With .Comment
            .Text Text:=CStr(rCell.FormulaLocal)
            .Shape.TextFrame.AutoSize = True
            .Visible = True
        End With
    End With
Next
Application.ScreenUpdating = True
End Sub
 
'Sub to remove the comments 
Sub RemoveComments()
    Selection.ClearComments
End Sub

Tommy Bak

[Scottie P]

Hello Tommy B!

Nice to see you here

Scott (a.k.a. ViperGTS)

[tommy bak]

Thanks, Scott.
Just found this place today (thanks to shades )and it really looks nice.

regards
Tommy Bak

[Insomniac]

Just another idea on a theme.
(pinched the .HasArray & .FormulaLocal from the previous posts)
Using DataValidation Input Message via WorksheetChange Event:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim c As Range, frm As String, but As String
Application.EnableEvents = False
     On Error Resume Next
     With Target
    .SpecialCells(xlCellTypeBlanks).Validation.Delete 'remove for blanks
    .SpecialCells(xlCellTypeConstants).Validation.Delete 'remove if not a formula any more
    For Each c In .SpecialCells(xlCellTypeFormulas)
        but = vbNullString
        With c
            frm = .FormulaLocal
            If .HasArray Then frm = "{" & .FormulaLocal & "}"
            If Len(frm) > 254 Then but = " [too long]"
            frm = Left(frm, 254)
            With .Validation
                .Delete
                .Add Type:=xlValidateInputOnly
                .InputTitle = "Formula in " & c.Address & but
                .InputMessage = frm
                .ShowInput = True
            End With
        End With
    Next
End With
Application.EnableEvents = True
End Sub

Positives:
1. Automated via Change Event
2. Can drag the message anywhere on the screen & it stays there (eg. under formula bar to veiw whilst editing).
3. If (God forbid) you use the Office Assistant it will display the formula in a larger font in its balloon.
4. Will work as soon as formula is entered, with Copy & Paste, drag&drop - no need to run a macro.

Negatives:
1. Deletes any validation you may have (could be modified to at least reapply all but Input message)
2. Formulas longer than 255 characters will be chopped.
3. Excel will crash in xl97 if you Copy & Paste or Drag & Drop an array formula to more than 1 cell.

[pierrelogic]

just out of curiosity what does the "23" really stand for in the formulas above?

(i.e. Selection.SpecialCells(xlCellTypeFormulas, 23)

I understand it's a value but where is that value derived from?

Sorry for digging up an older post but doing a search does that.

[NateO]

Microsoft created it, it's a numeric constant in c-code, it's sitting on your hard drive (most likely). 23 refers to a combination all 4 value types (constants) of Special Cells -> Formulas. Those being:

xlErrors, xlLogical, xlNumbers, xlNumbers.

All of which are early bound references numeric, constant arguments. E.g., xlNumbers = 1.

23 is actually the sum of all four numeric constants:

Errors = 16
Logical = 4
Numbers = 1
Text = 2

There's only one argument to be passed. 23 is moot in this case as the argument is both optional and 23 is the value by default unless stated otherwise.

You may also want to refer to the SpecialCells Method in the VBE help file.