Excel VBA: Adding custom Button to the Toolbar or Ribbon

Custom buttons are added to the toolbars or ribbons when a Microsoft Excel sheet is opened, and removed when the sheet is closed. To be notified when a given Excel sheet is loaded we need to listen to the Workbook_Open event of the ThisWorkbook object found in the VBA Project explorer.
Please notice that the following code works best with Excel versions prior to Office 2007. If used in newer versions, from Excel 2007, the button and commandbar will be added to the "Add-Ins" tab. It is only possible to add buttons to the main ribbon using dynamic XML when using VBA. Se below for more information.

' ConstantsPrivate Const COMMANDBAR_NAME As String = "Custom Toolbar"Private Const BUTTON_CAPTION As String = "My Button"' OpenPrivate Sub Workbook_Open()
    ' Variables
    Dim objCommandBar As CommandBar
    Dim objButton As CommandBarButton

    ' Try to get the Commandbar (if it exists)
    On Error Resume Next
    Set objCommandBar = Me.CommandBars(COMMANDBAR_NAME)
    On Error GoTo 0
    
    ' Was the commandbar available?
    If (objCommandBar Is Nothing) Then
        ' Create the commandbar
        On Error Resume Next
        Set objCommandBar = Application.CommandBars.Add(Name:=COMMANDBAR_NAME, Position:=msoBarTop, Temporary:=True)
        On Error GoTo 0
        
        ' Valid commandbar?
        If (Not objCommandBar Is Nothing) Then
            ' Add the buttons to the command bar
            With objCommandBar
                ' Add button
                Set objButton = objCommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
                
                 ' Set the button properties
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = BUTTON_CAPTION
                    .FaceId = 258
                    .TooltipText = "Do Something"
                    .OnAction = "'" & ThisWorkbook.Name & "'!OnDoSomething"
                End With
                
                ' Show the command bar
                .Visible = True
            End With
        End If
    End IfEnd Sub' Before ClosePrivate Sub Workbook_BeforeClose(Cancel As Boolean)
    
    On Error Resume Next
    ' Try to remove the iTrade command bar
    Call Application.CommandBars(COMMANDBAR_NAME).Delete
    
    ' Restore error handling
    On Error GoTo 0
End Sub

The custom event called OnDoSomething must be defined in a global Module. It doesn't work to define the method in the Workbook class. Insert a new Module and add the following code:

Option Explicit

Public Sub OnDoSomething()
    MsgBox "Hello World!"End Sub

You can change the button icon by specifying another FaceId value. To get a list of all available FaceIds on you computer, download and and install the FaceID Browser:

• FaceID Browser

Excel 2007 and later
To dynamically add buttons to the Ribbon you must use a combination of XML and VBA. For more information, please visit:

• How to: Use VBA to Add a Custom Command to the Ribbon 

Resources
Other usefull pages:

• Excel VBA: Adding items to the right-click menu

Excel VBA: String Format

The functions string.Format or StringBuilder.AppendFormat are two very usefull functions for formatting strings and increasing the readability of your .NET code. The Format function in VBA unfortunately works in a quite different way than the string.Format function in .NET. As far as I know there is no built-in function in VBA to acomplish the exact same as string.Format. In VBA the functionality can be achieved the following way:

' Format string using the .NET way
Public Function StringFormat(ByVal strValue As String, ParamArray arrParames() As Variant) As String
    Dim i As Integer

    ' Replace parameters  
    For i = LBound(arrParames()) To UBound(arrParames())
        strValue = Replace(strValue, "{" & CStr(i) & "}", CStr(arrParames(i)))
    Next
   
    ' Get the value    StringFormat = strValue
End Function

For more information please visit

• Using .NET String.Format in Excel

Excel VBA: Send E-mail from Excel

There are several ways to send e-mail from Excel using Microsoft Outlook.

It is possible to use the built-in function ActiveWorkbook.SendMail. However, it only allows simple e-mails to be created, and there is no way to add attachments.

In this example we are going to use the COM library of Microsoft Outlook to get more control. Attachments are not covered in this example.

We start by checking if an instance of Outlook is already running. To be independent of the installed version of Outlook we use the GetObject to try to get an existing instance. Another option would have been to reference the Outlook library directly, but then we are required to update the reference every time a new version of Microsoft Office is installed. If no instance is running, we start Outlook by calling CreateObject. Please remember to close down Outlook when you are finished.  Otherwise you will get multiple instance of the same application without the user knowing it.

We get a reference to the current users inbox by calling getDefaultFolder(6).The value 6 is a constant referring to the Inbox. If you used a reference to the COM library, you would have used the enum value olFolderInbox.

' Send E-mail

' strTo - Recipients. List of e-mails (separated by ';')

Public Sub SendEmail(ByVal strTo As String)

    Dim objOutlookApp As Object

    Dim objEmail As Object

    Dim objMapi As Object

    Dim objInboxFolder As Object

    ' Valid e-mails?

    If (strTo = "") Then

        ' Error

        MsgBox "No e-mails have been set!", vbExclamation, "No e-mails"

       

        ' Finished

        Exit Sub

    End If

   

    ' Attach to outlook

    On Error Resume Next

    Set objOutlookApp = GetObject(, "Outlook.Application")

    On Error GoTo 0

   

    ' Is Outlook running?

    If (objOutlookApp Is Nothing) Then

        ' Create new instance of outlook

        Set objOutlookApp = CreateObject("Outlook.Application")

       

        ' Get the MAPI namespace (e-mails)

        Set objMapi = objOutlookApp.GetNamespace("MAPI")

       

        ' Get the inbox folder

        Set objInboxFolder = objMapi.getDefaultFolder(6)

       

        ' Display the inbox folder (make outlook visible)

        Call objInboxFolder.Display

    End If

   

    ' Create the new e-mail

    Set objEmail = objOutlookApp.CreateItem(0)

   

    ' Set the properties of the new email

    With objEmail

        ' Set the recipients

        .To = strTo

       

        ' Show the message

        Call .Display

       

        ' Resolve all recipients (Same as pressing the "Check Names" button)

        Call .Recipients.ResolveAll

    End With

    ' Free memory

    Set objEmail = Nothing

    Set objOutlookApp = Nothing

End Sub