Excel AddIn - how to get formula from Formula Dialog

T

Toxter

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.xlDialogFunctionWizard];
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
 
P

Peter T

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
 
T

Toxter

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 said:
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 said:
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.xlDialogFunctionWizard];
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
Click to expand...


.
Click to expand...
 
P

Peter T

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 said:
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 said:
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 said:
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.xlDialogFunctionWizard];
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
Click to expand...


.
Click to expand...
Click to expand...
 
P

Peter T

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

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 said:
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 said:
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 said:
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


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.xlDialogFunctionWizard];
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




.
Click to expand...
Click to expand...
Click to expand...
 
T

Toxter

Hi Peter,

Thank you, this solution works well.

Peter T said:
Oops, this is not right
If Len(v) Then ActiveCell.Formula = v
Click to expand...

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 said:
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 said:
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

:

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


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.xlDialogFunctionWizard];
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




.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How To Open Chart Wizard Dialog on Excel 2003 6
Copy a R1C1 formula to a user defined range results #NAME? error 2
how do I get a cell in excel to show the formula and not the word 2
How to set chart series border property with variables read from cellvalues? 2
Displaying a dataset or datatable in Excel 2
Write a function call into an Excel cell from VBA 2
Need help writing output to a file without <cr><lf> carriage contr 2
Name error trying to use add-in 2

Top