How to create a VB macro in Excel from Automation...
 

I'm working on an Visual C++ application which uses Automation for
Excel. I have a problem though. My application creates a new toolbar
in Excel, but i am unable to figure how to assign some functions to
the buttons from the Toolbar.
The CommandBarButton has the OnAction property but this runs a Macro
in Excel. Giving this thing, i came with the ideea to keep a hidden
sheet in Excel, modify the data in a cell and receive a message from
Excel and then run my function. I can do that by creating a macro with
Automation( but i don't know if it's possible) and assign the macro to
the CommandBarButton's OnAction.
Can anyone tell me if this is possible and how to do it? Or maybe you
have a better ideea on how to assing my own functions with the
toolbar's buttons.

Here's the code that i use to create the toolbar:

Office::CommandBarControlsPtr pControls;
m_pCellToolbar = m_pCommandBars-Add(_variant_t ( (LPCTSTR) "My
toolbar"),
_variant_t( (short)Office::msoBarFloating));
pControls = m_pCellToolbar-GetControls();
m_pComboCtrl = pControls-Add( _variant_t(
(short)Office::msoControlComboBox),
vtMissing, vtMissing, vtMissing, vtMissing);
m_pButPropertiesCtrl = pControls-Add( _variant_t(
(short)Office::msoControlButton),
vtMissing, vtMissing, vtMissing, vtMissing);
m_pButRemoveCtrl = pControls-Add( _variant_t(
(short)Office::msoControlButton),
vtMissing, vtMissing, vtMissing, vtMissing);
m_pButCopyCtrl = pControls-Add( _variant_t(
(short)Office::msoControlButton),
vtMissing, vtMissing, vtMissing, vtMissing);
m_pButPasteCtrl = pControls-Add( _variant_t(
(short)Office::msoControlButton),
vtMissing, vtMissing, vtMissing, vtMissing);
m_pCellToolbar-Visible = true;
m_pComboCtrl-Visible = true;
m_pButCopyCtrl-Visible = true;
m_pButPasteCtrl-Visible = true;
m_pButPropertiesCtrl-Visible = true;
m_pButRemoveCtrl-Visible = true;
m_pButCopyCtrl-PutCaption( (LPCTSTR) "Copy");
m_pButPasteCtrl-PutCaption( (LPCTSTR) "Paste");
m_pButPropertiesCtrl-PutCaption( (LPCTSTR) "Properties");
m_pButRemoveCtrl-PutCaption( (LPCTSTR) "Remove");

How to create a VB macro in Excel from Automation...
 

I cannot help you with coding in C++ but this is how I'd add a standard
module and sum code to the active workbook:

Sub a()
Dim LinePtr As Integer
With ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct _StdModule)
LinePtr = .CodeModule.CountOfLines + 1
.CodeModule.InsertLines LinePtr, "Sub Test"
LinePtr = LinePtr + 1
.CodeModule.InsertLines LinePtr, "Sheet1.range(""A1"").Value = 1"
LinePtr = LinePtr + 1
.CodeModule.InsertLines LinePtr, "End Sub"
End With
End Sub


--
Jim Rech
Excel MVP
"Cristian Godoci" wrote in message
m...
| I'm working on an Visual C++ application which uses Automation for
| Excel. I have a problem though. My application creates a new toolbar
| in Excel, but i am unable to figure how to assign some functions to
| the buttons from the Toolbar.
| The CommandBarButton has the OnAction property but this runs a Macro
| in Excel. Giving this thing, i came with the ideea to keep a hidden
| sheet in Excel, modify the data in a cell and receive a message from
| Excel and then run my function. I can do that by creating a macro with
| Automation( but i don't know if it's possible) and assign the macro to
| the CommandBarButton's OnAction.
| Can anyone tell me if this is possible and how to do it? Or maybe you
| have a better ideea on how to assing my own functions with the
| toolbar's buttons.
|
| Here's the code that i use to create the toolbar:
|
| Office::CommandBarControlsPtr pControls;
| m_pCellToolbar = m_pCommandBars-Add(_variant_t ( (LPCTSTR) "My
| toolbar"),
| _variant_t( (short)Office::msoBarFloating));
| pControls = m_pCellToolbar-GetControls();
| m_pComboCtrl = pControls-Add( _variant_t(
| (short)Office::msoControlComboBox),
| vtMissing, vtMissing, vtMissing, vtMissing);
| m_pButPropertiesCtrl = pControls-Add( _variant_t(
| (short)Office::msoControlButton),
| vtMissing, vtMissing, vtMissing, vtMissing);
| m_pButRemoveCtrl = pControls-Add( _variant_t(
| (short)Office::msoControlButton),
| vtMissing, vtMissing, vtMissing, vtMissing);
| m_pButCopyCtrl = pControls-Add( _variant_t(
| (short)Office::msoControlButton),
| vtMissing, vtMissing, vtMissing, vtMissing);
| m_pButPasteCtrl = pControls-Add( _variant_t(
| (short)Office::msoControlButton),
| vtMissing, vtMissing, vtMissing, vtMissing);
| m_pCellToolbar-Visible = true;
| m_pComboCtrl-Visible = true;
| m_pButCopyCtrl-Visible = true;
| m_pButPasteCtrl-Visible = true;
| m_pButPropertiesCtrl-Visible = true;
| m_pButRemoveCtrl-Visible = true;
| m_pButCopyCtrl-PutCaption( (LPCTSTR) "Copy");
| m_pButPasteCtrl-PutCaption( (LPCTSTR) "Paste");
| m_pButPropertiesCtrl-PutCaption( (LPCTSTR) "Properties");
| m_pButRemoveCtrl-PutCaption( (LPCTSTR) "Remove");