Hi Peter,

Thank you, this solution works well.

"Peter T" wrote:

Oops, this is not right
If Len(v) Then ActiveCell.Formula = v

try again


Sub test2()
Dim bRes As Boolean
Dim v As Variant
Dim sFmla As String

If ActiveCell.HasFormula Then
v = ActiveCell.Formula ' always a String
Else
' data type could be string, double, boolean, error etc
v = ActiveCell.Value
End If

bRes = Application.Dialogs(xlDialogFunctionWizard).Show

If bRes Then
sFmla = ActiveCell.Formula
If Len(v) Then
ActiveCell.Formula = v
Else
ActiveCell.ClearContents
End If
MsgBox sFmla
Else
MsgBox "user cancelled"
End If

End Sub

Regards,
Peter T



"Peter T" <peter_t@discussions wrote in message
...
Try this macro in VBA

Sub test2()
Dim bRes As Boolean
Dim v As Variant
Dim sFmla As String

If ActiveCell.HasFormula Then
v = ActiveCell.Formula ' always a String
Else
' data type could be string, double, boolean, error etc
v = ActiveCell.Value
End If

bRes = Application.Dialogs(xlDialogFunctionWizard).Show

If bRes Then
sFmla = ActiveCell.Formula
If Len(v) Then ActiveCell.Formula = v
MsgBox sFmla
Else
MsgBox "user cancelled"
End If

End Sub

Regards,
Peter T

"Toxter" wrote in message
...
Hi Peter, thanks for a quick reply.

Can you please elaborate what do you mean by "trap the activecell's
formula
to a variant" ?

Thank you

"Peter T" wrote:

Trap the activecell's formula to a variant
Show the dialog
Trap the boolean return value of the dialog (false cancelled, true if
user
hit enter)
If true, read the new formula from the activecell, and possibly it's
value
too
Reinstate the original formula

Regards,
Peter T


"Toxter" wrote in message
...
Hi All,

I'm working on VSTO Excel 2007 AddIn in C#.

I need to call Formula Dialog and than to fetch the formula string
from
that
dialog. Problem is that dialog doesn't offer something like
dialog.FormulaText but instead inserts formula in ActiveCell. So code
like
this:

var dlg =
this.Application.Dialogs[Microsoft.Office.Interop.Excel.XlBuiltInDialog.xlD ialogFunctionWizard];
dlg.Show(missing, missing, missing, missing, missing, missing,
missing,
missing, missing, missing, missing, missing, missing, missing,
missing,
missing, missing, missing, missing, missing, missing, missing,
missing,
missing, missing, missing, missing, missing, missing, missing);


So, this code will Show Function Dialog, and it will correctly place
generated formula to ActiveCell.

Is it possible that I do this without placing value in ActiveCell and
get
Formula string?

Thank you




.





.