Excel AddIn - how to get formula from Formula Dialog
in [Programming]
|
From: Toxter on 19 Feb 2010 15:36 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
From: Peter T on 19 Feb 2010 16:04 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" <Toxter(a)discussions.microsoft.com> wrote in message news:18803047-AD2E-49F9-94A5-4F1076ED580A(a)microsoft.com... > 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 > >
From: Toxter on 20 Feb 2010 05:08 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" <Toxter(a)discussions.microsoft.com> wrote in message > news:18803047-AD2E-49F9-94A5-4F1076ED580A(a)microsoft.com... > > 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 > > > > > > > . >
From: Peter T on 20 Feb 2010 05:46 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" <Toxter(a)discussions.microsoft.com> wrote in message news:2069A71D-869D-45EE-A706-01F32BC5595E(a)microsoft.com... > 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" <Toxter(a)discussions.microsoft.com> wrote in message >> news:18803047-AD2E-49F9-94A5-4F1076ED580A(a)microsoft.com... >> > 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 >> > >> > >> >> >> . >>
From: Peter T on 20 Feb 2010 05:53
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(a)discussions> wrote in message news:%231uI7nhsKHA.3800(a)TK2MSFTNGP06.phx.gbl... > 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" <Toxter(a)discussions.microsoft.com> wrote in message > news:2069A71D-869D-45EE-A706-01F32BC5595E(a)microsoft.com... >> 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" <Toxter(a)discussions.microsoft.com> wrote in message >>> news:18803047-AD2E-49F9-94A5-4F1076ED580A(a)microsoft.com... >>> > 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 >>> > >>> > >>> >>> >>> . >>> > > |