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

[Cristian Godoci]

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");

 

[Jim Rech]

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
| 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");