Is the formula an array formula?

[Jason Morin]

I'd like to insert a cell's formula into a comment. My
only issue is testing to see if it's an array formula or
normal formula. As of now, the macro always says it's an
array formula. Thanks for any help you can provide.

Sub FormulaInComment()
Dim ConfirmBox As String
Dim FormulaType As String
ConfirmBox = MsgBox("Proceed?", vbYesNo)
If ConfirmBox = vbNo Then Exit Sub
With ActiveCell
If .HasFormula Then
If IsNull(.FormulaArray) Then
FormulaType = "Formula:"
Else
FormulaType = "Array Formula:"
End If
Else
MsgBox "No formula in cell!"
Exit Sub
End If
.AddComment
.Comment.Text Text:=FormulaType & Chr(10) & .Formula
.Comment.Visible = False
.Copy
.PasteSpecial (xlPasteValues)
End With
Application.CutCopyMode = False
End Sub

 

[Tom Ogilvy]

Sub FormulaInComment()
Dim ConfirmBox As String
Dim FormulaType As String
ConfirmBox = MsgBox("Proceed?", vbYesNo)
If ConfirmBox = vbNo Then Exit Sub
With ActiveCell
If .HasFormula Then
If Not .HasArray Then
FormulaType = "Formula:"
Else
FormulaType = "Array Formula:"
End If
Else
MsgBox "No formula in cell!"
Exit Sub
End If
.AddComment
.Comment.Text Text:=FormulaType & Chr(10) & .Formula
.Comment.Visible = False
.Copy
.PasteSpecial (xlPasteValues)
End With
Application.CutCopyMode = False
End Sub

 

[Don Guillett]

try this idea
Sub ifarray()
For Each c In Selection
If c.HasArray Then MsgBox c.Address
Next
End Sub

 

[Dave Peterson]

I had one of these thingies in a workbook. I thought it would be very useful to
document what happened before (and if I had to revert).

If you're thinking of doing the same thing, you may want to use .formular1c1

I inserted rows (not columns) in my data and every "retrieved" formula was now
pointing at the correct column in the wrong row.

It was not as useful as I hoped.