|
Excel AddIn - how to get formula from Formula Dialog
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 |
Excel AddIn - how to get formula from Formula Dialog
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 |
Excel AddIn - how to get formula from Formula Dialog
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 . |
Excel AddIn - how to get formula from Formula Dialog
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 . |
Excel AddIn - how to get formula from Formula Dialog
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 . |
Excel AddIn - how to get formula from Formula Dialog
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 . . |
| All times are GMT +1. The time now is 10:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com