Call a Method in VSTO Addin from Visual Basic Applications

How to call a VSTO Addin Function from VBA

VSTO Addins we write might contain good number of reusable components. The following article shows how to use them from external applications. This consists of the following steps:

1. Creating a VSTO Addin with a public method

2. Expose the VSTO Method for external use

3. Consume the VSTO Method from VBA

Creation of VSTO Excel Addin

Create a new project from Visual Studio - - > Office Projects - - > Excel 2007 Addin and call the project CallVSTOExample

This will automatically create necessary references and add required directives

VSTO Addin

using System;

using System.Runtime.InteropServices;

using Excel = Microsoft.Office.Interop.Excel;

Create a new C# Class module and add the following code to it

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Runtime.InteropServices;

using Excel = Microsoft.Office.Interop.Excel;

namespace CallVSTOExample

{

[ComVisible(true)]

[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]

public interface IAddinVSTO

{

void SayHello();

}

[ComVisible(true)]

[ClassInterface(ClassInterfaceType.None)]

public class VSTOExample : IAddinVSTO

{

#region IAddinVSTO Members

public void SayHello()

{

Excel.Worksheet wks = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;

Excel.Range myRange = wks.get_Range("A1", System.Type.Missing);

myRange.Value2 = "Hello";

}

#endregion

}

}

Here we have created one interface and have also created a class that implements the interface. The class has a simple method SayHello, which inserts “Hello” in cell A1 of active worksheet

The class must be public and should expose the IDispatch interface for it to be visible to COM

InterfaceIsIDispatch indicates an interface is exposed to COM as a dispinterface, which enables late binding only.

Exposing the method for external use

Add the following code to ThisAddin class

private VSTOExample utilities;

protected override object RequestComAddInAutomationService()

{

if (utilities == null)

utilities = new VSTOExample();

return utilities;

}

RequestComAddInAutomationService method returns an object in your add-in that can be used by other Microsoft Office solutions.

Override this method to expose an object in your add-in to other Microsoft Office solutions. This includes other add-ins and document-level customizations running in the same application process, VBA code, and external automation code.

The object that you return must be public, it must be visible to COM, and it must expose the IDispatch interface. If the object you return does not meet these requirements, the Visual Studio Tools for Office runtime will throw an InvalidCastException after it calls your implementation.

Compile the code and open the application (Excel) with the addin installed.

Excel VBA Code to call VSTO Method

The VSTOAddin that was created will be loaded as a COMAddin in Excel. The following code will retrieve the addin information

Sub Execute_VSTO_Addin_Macro()

Dim oAddin As COMAddIn

Dim oCOMFuncs As Object

Set oAddin = Application.COMAddIns("CallVSTOExample")

Set oCOMFuncs = oAddin.Object

oCOMFuncs.SayHello

End Sub

The Object property returns the object represented by the specified COMAddIn object. The VSTO function SayHello is called using the COMAddin’s object as shown above

See also:

Insert Array to Excel Range using VSTO

Create Custom Task Panes for Word using VSTO

Office host applications and their support for add-ins

Copy Array Values to Excel Range using VSTO

Insert Array to Excel Range using VSTO

Many times we need to insert text to a contiguous Excel range using VSTO (like column headings). The following code does exactly that

private void InsertHeading()

{

string[] Header = { "Sno", "Prouct Code", "Product Name", "Quanity", "Price", "Total" };

Excel.Worksheet sht = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;

Excel.Range myHeader = sht.get_Range("A1", "F1");

myHeader.Value2 = Header;

myHeader.Columns.AutoFit();

}

VSTO Code Output - Autofit columns

See also:

How to use .Net Array.Sort Function in VBA

Convert Dates to Arrays using Array Function

Array Dimensioning in Visual Basic

Filtering Array Elements

Transferring array to Excel range

How to Return Multiple Values from a VBA Function

Return Multiple Values from a Visual Basic Function

A normal VBA function has a return statement that returns a value to the calling function/subroutine

If you want multiple values to be returned, use reference parameters. The reference parameter represents the same storage location as the argument variable and hence changes made in the function is reflected in the calling function too.

Sub ReturnMultipleValue()

Dim L As Double

Dim B As Double

L = 10

B = 6

Debug.Print L & " " & B

ChangeLengthAndBreadth L, B

Debug.Print L & " " & B

End Sub

The above sub passes the arguments to ChangeLengthAndBreadth function by reference. That is, the storage location is passed to function and the changes made inside the ChangeLengthAndBreadth function is reflected in the main method.

Function ChangeLengthAndBreadth(ByRef Length As Double, ByRef Width As Double)

Length = 1.2 * Length

Width = 1.2 * Width

End Function

Multiple Return Parameters in VBA, Multiple Return Parameters in Visual Basic, Return Multiple Values – VBA Function, VBA Function to return more than one value

Return Multiple Values

See also:

Output Parameters in C# (.NET)

How to get the return value from C# program (console application)

How to insert Symbols in Word Document using VBA

Here is a simple way to insert the commonly used symbols (Copyright, Trademark etc) using Word VBA

Sub Insert_Symbols_Example()

Selection.TypeText Text:="Copyright "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=169, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Trade mark "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=8482, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Registered Symbol "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=174, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:=" EURO"

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=8364, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Pound "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=163, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Yen "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=165, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Degrees "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=176, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Plus Minus "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=177, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Multiplication Sign "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=215, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Division Symbol "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=247, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Square Root "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=8730, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Ohm Sign "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=8486, _

Unicode:=True

Selection.TypeParagraph

Selection.TypeText Text:="Dagger "

Selection.InsertSymbol Font:="Times New Roman", CharacterNumber:=8224, _

Unicode:=True

End Sub

VBA Insert CopyRight Symbol, VBA Insert Trade mark, VBA Insert Registered Symbol, VBA Insert EURO Sign, VBA Insert Pound Sign, VBA Insert Division Symbol, VBA Insert Square Root Symbol,

See also:

Add Words to AutoCorrect Entries

How to use collections in Excel VBA

Collection of objects using Excel VBA

Collection provides a convenient way to refer to a group of objects and collections as a single object. The objects and collections in the collection don't have to be of the same data type.

In the following example we will use collections for a class (that is a collection of objects of type Vendor)

First define a class ClsVendor (Insert a Class Module to the project) and add the following item


Public VendorID As String
Public VendorName As String
Public VendorAddress As String


Public Function AddVendor(ByVal sVendorID As String, ByVal sVendorName As String, Optional ByVal sVendorAddress)

VendorID = sVendorID
VendorName = sVendorName
VendorAddress = sVendorAddress

End Function

The class has three public variables that can be initialized by AddVendor method.

Let us define a collection by

Private oColl As New Collection ' Collection to store Vendor

Now let us define a sub to create a collection of Vendors

Sub Add_Vendor_Info()

Dim oVendor As ClsVendor



Set oVendor = New ClsVendor

oVendor.AddVendor "EXV0023", "MCMASTER", "P.O. Box 94930, Cleveland, OH 44101-4930"
oColl.Add oVendor, "MCMASTER"

Set oVendor = New ClsVendor
oVendor.AddVendor "EXV0024", "SHANTI GEARS"
oColl.Add oVendor, "SHANTI GEARS"

Set oVendor = New ClsVendor
oVendor.AddVendor "EXV0025", "SKF"
oColl.Add oVendor, "SKF"

Set oVendor = New ClsVendor
oVendor.AddVendor "EXV0026", "SUNDARAM FASTNERS"
oColl.Add oVendor, "SUNDARAM FASTNERS"

Set oReqVen = oColl("SKF")
MsgBox oReqVen.VendorID & " " & oReqVen.VendorName & " " & oReqVen.VendorAddress

End Sub

Nornally, you would be reading the above information from a database or a flat file. You can also use the above example to get the information from an userform and add it to the collection

Here the Vendor objects are added to the collection using Add method. The syntax is as follows

object.Add item, key, before, after

The Add method syntax has the following object qualifier and named arguments:
object - Required. An object expression that evaluates to an object in the Applies To list.

item - Required. An expression of any type that specifies the member to add to the collection.

key - Optional. A unique string expression that specifies a key string that can be used,
instead of a positional index, to access a member of the collection.

before - Optional. An expression that specifies a relative position in the collection. The member to be added is placed in the collection before the member identified by the before argument. If a numeric expression, before must be a number from 1 to the value of the collection's Count property. If a string expression, before must correspond to the key specified when the member being referred to was added to the collection. You can specify a before position or an after position, but not both.

after - Optional. An expression that specifies a relative position in the collection. The member to be added is placed in the collection after the member identified by the after argument. If numeric, after must be a number from 1 to the value of the collection's Count property. If a string, after must correspond to the key specified when the member referred to was added to the collection. You can specify a before position or an after position, but not both.

Here we have added the item and the key

oColl.Add oVendor, "SUNDARAM FASTNERS"

The above adds "SUNDARAM FASTNERS" as a key to the corresponding Vendor. This Key will be useful to retrieve the information from the collection (as shown later)

Once a collection is created, its members can be retrieved using the Item method. The entire collection can be iterated using the For Each...Next statement

The Item method can be used with numeric or string arguments. If you are specifying a string argument, it should match the ‘Key’ of the member

Retrieving a specific member of a Collection object by position

Set oReqVen = oColl(1)
MsgBox oReqVen.VendorID & vbCrLf & oReqVen.VendorName & vbCrLf & oReqVen.VendorAddress

Collection data retrieved using position
Retrieving a specific member of a Collection object by key

Set oReqVen = oColl("SKF")
MsgBox oReqVen.VendorID & vbCrLf & oReqVen.VendorName & vbCrLf & oReqVen.VendorAddress

Collection data retrieved using Key (EXcel VBA)

To delete a member from a collection using VBA use the remove method

oColl.Remove ("SKF")

or

oColl.Remove (3)

Collection after removing a member

Convert Decimal to Percentage using VBA Format Function

Convert Decimal to Percentage using VBA FormatPercent Function

Here is a simple example to convert a decimal to percentage using VBA function

Sub Convert_Decimal2Percentage()


Dim dblSuccess As Double
Dim sSuccess As String

dblSuccess = 0.456345

sSuccess = FormatPercent(dblSuccess, 2)

sSuccess = Format(Expression:=dblSuccess, Format:="Percent")


End Sub

How to Read and Write Configuration Files using VBA

How to Read and Write INI files using VBA

Ini files were extensively used for storing configuration path. Of late this has been replaced with XML. Here is a small snippet for using

Here is a sample INI File

[MOSS_Documents]
AdminDocPath=d:\MOSS\AdminDocs\
ArchiveDocPath="d:\MOSS\DocsArchive\"


Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long

Declare Function WritePrivateProfileString Lib "kernel32" _
Alias "WritePrivateProfileStringA" _
(ByVal sSectionName As String, _
ByVal sKeyName As String, _
ByVal sString As String, _
ByVal sFileName As String) As Long

Private Function GetSectionEntry(ByVal strSectionName As String, ByVal strEntry As String, ByVal strIniPath As String) As String

'===================================================================================
'Purpose : Retrieve data from Sample.ini
'Assumptions:
'Effects :
'Inputs : Section Name and Entry Name in the KitBOM ini
'Returns : Value of the Entry in the section
'===================================================================================

Dim X As Long
Dim sSection As String, sEntry As String, sDefault As String
Dim sRetBuf As String, iLenBuf As Integer, sFileName As String
Dim sValue As String

On Error GoTo ErrGetSectionentry
sSection = strSectionName
sEntry = strEntry
sDefault = ""
sRetBuf = Strings.String$(256, 0) '256 null characters
iLenBuf = Len(sRetBuf$)
sFileName = strIniPath
X = GetPrivateProfileString(sSection, sEntry, _
"", sRetBuf, iLenBuf, sFileName)
sValue = Strings.Trim(Strings.Left$(sRetBuf, X))

If sValue <> "" Then
GetSectionEntry = sValue
Else
GetSectionEntry = vbNullChar
End If

ErrGetSectionentry:
If Err <> 0 Then
Err.Clear
Resume Next
End If

End Function

Here is the sub to drive the above function

Sub Store_And_Retrieve_Values()

Dim sIniPath
Dim sBuffer
Dim ret

sIniPath = "d:\temp\VBA_MOSS.ini"
sBuffer = GetSectionEntry("MOSS_Documents", "AdminDocPath", sIniPath)

' Write Data to Ini File
ret = WritePrivateProfileString("MOSS_Documents", "AdminDocPath", "d:\MOSSNew\AdminDocs\", sIniPath)
End Sub

The following will be the output INI file after running the macro

[MOSS_Documents]
AdminDocPath=d:\MOSSNew\AdminDocs\
ArchiveDocPath="d:\MOSS\DocsArchive\"

Keywords: Write INI files using VBA, Read files using VBA Read Configuration Files using VBA, Write Configuration Files using VBA, Excel VBA Ini files, VBA GetPrivateProfileString Function, VBA WritePrivateProfileString function, Read and Write Ini files using Excel VBA, Update an Section in Ini file using VBA, Overwrite a string in INI file using VBA, Replace a string in INI file using VBA

Programmatically add macro to Excel Workbook using VBA

Insert Code Module to Workbook using Excel VBA

There are numerous instances where code/macro needs to be added to the workbook on the fly. The following code snippet adds the code from the module to the specified workbook

Function Insert_Button(ByRef oWB As Workbook)

Dim oVBP As VBProject ' VB Project Object

Dim oVBC As VBComponent ' VB Component Object

On Error GoTo Err_VBP

Set oVBP = oWB.VBProject

Set oVBC = oVBP.VBComponents.Add(vbext_ct_StdModule)

oVBC.CodeModule.AddFromFile "c:\VBADUD\Templates\MSample.bas"

oWB.Application.Run "'" & oWB.name & "'!SayHello"

oWB.Save

' -------------------

' Destroy Objects

' -------------------

Finally:

If Not oVBP Is Nothing Then Set oVBP = Nothing

' -------------------

' Error Clearer

' -------------------

Err_VBP:

If Err < > 0 Then

Err.Clear

GoTo Finally

End If

End Function

After adding the macro to the Excel workbook programmatically, the code fires the Macro – “SayHello” from the added bas module

The code uses VBProject. Ensure that the Microsoft Visual Basic for Applications Extensibility reference is loaded

Excel VBA CodeModule, Excel VBA AddFromFile, Excel VBA add Code Modules, Excel VBA VBComponents

How to add macro to workbook using VBA, Programmatically add macros to workbook, Add code from BAS module to workbook using VBA, Excel VBA add code to workbook,

Microsoft Visual Basic for Applications Extensibility reference Dialog

See also:

Add Macro to Workbook programatically using Excel VBA

Extract Procedure Names from all Modules - VBA

Insert Procedure to a Module Using VBComponents

Delete Module on the Fly using VBA

Insert User Form on the Fly

Insert Class Module on the Fly

VBA check program modes

How to check if the program is in debug mode using VBA

Here is a simple way to check if the program is in debug mode using VBA

Sub Check_VBE()

Dim oVBe As VBProject

Set oVBe = ThisWorkbook.VBProject

If oVBe.Mode = vbext_vm_Run Then

MsgBox "Executing.."

MsgBox oVBe.VBE.MainWindow.Visible

End If

End Sub

The code uses VBProject. Ensure that the Microsoft Visual Basic for Applications Extensibility reference is loaded

Microsoft Visual Basic for Applications Extensibility reference

Excel VBA TimeStamp – Milliseconds using Excel VBA

How to Get Time in Milliseconds using Excel VBA

The following function uses Timer function to get the milliseconds and append it to the current time

Public Function TimeInMS() As String

TimeInMS = Strings.Format(Now, "dd-MMM-yyyy HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)

End Function

Timer function returns a Single representing the number of seconds elapsed since midnight.

Another method is to use API Functions as shown below

Private Type SYSTEMTIME

wYear As Integer

wMonth As Integer

wDayOfWeek As Integer

wDay As Integer

wHour As Integer

wMinute As Integer

wSecond As Integer

wMilliseconds As Integer

End Type

Private Declare Sub GetSystemTime Lib "kernel32" _

(lpSystemTime As SYSTEMTIME)

Public Function TimeToMillisecond() As String

Dim tSystem As SYSTEMTIME

Dim sRet

On Error Resume Next

GetSystemTime tSystem

sRet = Hour(Now) & ":" & Minute(Now) & ":" & Second(Now) & _

":" & tSystem.wMilliseconds

TimeToMillisecond = sRet

End Function

Millisecond timer using VBA, How to get milliseconds in VBA Now() function, VBA Now() function, VBA Timer function , Excel VBA Timer, VBA Milliseconds

See also:

Sleep Function in Excel VBA

How to get number of Weekdays/Weekends in a given month using (C#/VB.NET)

Calculating Time taken for an operation using VB.NET (in Milliseconds)

Calculate Working days (Excluding Holdiays) using Excel Function / VBA

Automatically Event Repeat in Excel VBA (OnTime Method)

Compare Files by Date

Display Time Dynamically in Form using Time/Now Function

Excel VBA uninstall Excel Addins

Programmatically uninstall Excel Addins using VBA

Sub UnInstall_Addins_From_EXcel_AddinsList()

Dim oXLAddin As AddIn

For Each oXLAddin In Application.AddIns

Debug.Print oXLAddin.FullName

If oXLAddin.Installed = True Then

oXLAddin.Installed = False

End If

Next oXLAddin

End Sub

See also:

Call a Method in VSTO Addin from Visual Basic Applications

Programmatically Delete Word Addins from the Addins List using VBA

UnInstall Word Addins using VBA

Programmatically Add Modules/Addins to Word using VBA

How to use .Net Array.Sort Function in VBA

Word VBA add command buttons through code

Add CommandButton to Word Document using VBA (through AddOLEControl)

Here is one of the ways to add a command button on a Word document using Word VBA

Sub Macro_Add_Button()

Dim oCtl

Dim oCmd

Set oCtl = ActiveDocument.InlineShapes.AddOLEControl(ClassType:="Forms.CommandButton.1")

Set oCmd = oCtl.OLEFormat.Object

oCmd.Caption = "Click Me..."

End Sub

Manipulate ActiveX TextBoxes in a Word Document using VBA

Change Content of Embedded Textboxes in Word using VBA

Word Document might contain text boxes embedded in it as Inlineshapes. In that case, it can be manipulated using VBA as follows:

Sub Document_TextBoxes()

Dim oCtl As InlineShape

Dim oTB

For Each oCtl In ActiveDocument.InlineShapes

If oCtl.OLEFormat.ProgID = "Forms.TextBox.1" Then

Set oTB = oCtl.OLEFormat.Object

oTB.Text = "Sample Text"

End If

Next

End Sub

Embedded Text Box using Word VBA

Automatically Tag Underlined Words using Word VBA

Tag Formatted Text in Word using VBA

Here is a Word VBA snippet for searching text in a particular format, for example, underlined text and Tag them

Sub Tag_Under_Line()

Selection.ClearFormatting

Selection.HomeKey wdStory, wdMove

Selection.Find.Font.Underline = wdUnderlineSingle

Selection.Find.Execute ""

Do Until Selection.Find.Found = False

Selection.Font.Underline = wdUnderlineNone

Selection.InsertBefore "<>"

Selection.InsertAfter "< /UL >"

Selection.MoveRight

Selection.Find.Execute ""

Loop

End Sub

Word Document with Formatting (Underline)

(Tagged Word Document after Macro Execution)

Programmatically Delete Word Addins from the Addins List using VBA

Delete Word Addins using Word VBA

At times there will be quite a no of Addins that you would have tested in Word and they will line-up in the Addins List or you would have added no of versions of the same addin and it still is showing on the list

The following code will delete all uninstalled word addins from the Addins List

Private Sub Delete_UnInstalled_WordAddins()

Dim oAddin As AddIn

On Error GoTo Err_Addin

For Each oAddin In AddIns

If oAddin.Installed = False Then

oAddin.Delete

End If

Next oAddin

Finally:

If Not oAddin Is Nothing Then Set oAddin = Nothing

Err_Addin:

If Err < > 0 Then

Err.Clear

GoTo Finally

End If

End Sub

Uninstalled Word Addin

Word Addins Removed using VBA

UnInstall Word Addins using VBA

Here is a simple method to uninstall a Word Addin (.dot file) using Word VBA

Private Sub UnInstalled_AllWordAddins()

Dim oAddin As AddIn

On Error GoTo Err_Addin

For Each oAddin In AddIns

If oAddin.Installed Then

msg = oAddin.Name

oAddin.Installed = False

End If

Next oAddin

Finally:

If Not oAddin Is Nothing Then Set oAddin = Nothing

Err_Addin:

If Err < > 0 Then

Err.Clear

GoTo Finally

End If

End Sub

Installed Word Addin

Word Addin List after Macro Execution. Addin is uninstalled (not removed)

Programmatically Add Modules/Addins to Word using VBA

Install Word Addins using VBA

Here is a simple method to add a Word Addin (.dot file) using Word VBA

Private Sub Add_Word_Addin()

Dim oAddin As AddIn

On Error GoTo Err_Addin

' Add the Word Addin and Install It

Set oAddin = Application.AddIns.Add("c:\ShasurData\vbadud_Business_Template1.0.dot", True)

Finally:

If Not oAddin Is Nothing Then Set oAddin = Nothing

Err_Addin:

If Err < > 0 Then

Err.Clear

GoTo Finally

End If

End Sub

Word Addins Dialog Before Macro Execution

Word Addins Dialog After Macro Execution

Add Macro to Workbook programatically using Excel VBA

How to Add Macro to Workbook using Excel VBA

Here is a simple procedure to add a macro to the current workbook using VBComponents

Sub Add_Macro_To_ThisWorkbook()

Dim VBP As VBProject
Dim VBM As VBComponent
Dim VBModule As CodeModule
Dim VBProc As VBComponent

Set VBP = ThisWorkbook.VBProject

Set VBModule = VBP.VBComponents.Item("ThisWorkbook").CodeModule

VBModule.AddFromString ("Sub Sample_Macro" & vbCrLf & "ret = msgbox (""Hello VBADUD"") " & vbCrLf & "End Sub")

End Sub

Add Controls Popup Menu using Powerpoint VBA

Powerpoint Application - Create Popup menu using VBA

The following code is a simple one to create popup menu in Powerpoint using VBA

Sub Add_Ctrl_To_Popup(ByVal sControlName As String, ByVal sMacroName As String)

On Error GoTo DisplayErr

' ---------------------------------------------------------------'
Written By Shasur for http://vbadud.blogspot.com
'---------------------------------------------------------------
Dim ctlCB As CommandBarDim ctlCommand As CommandBarControl

Set ctlCB = Application.CommandBars("Shapes")

If ctlCB Is Nothing Then Exit Sub

Set ctlCommand = ctlCB.Controls.Add

ctlCommand.Caption = sControlName

ctlCommand.OnAction = sMacroName

DisplayErr:

If Err <> 0 Then

MsgBox Err.Description

Err.Clear
Resume Next

End If
End Sub

Following sub will call the function

Sub Drive_Add_Ctrl_To_Poppup()
Add_Ctrl_To_Poppup "NewMenu", "Test"
End Sub

The above function will add an item to popup menu using VBA and assign a macro to it

See also:

Creating Menu’s and CommandBars in Powerpoint using VBA

Disable Cut & Copy from Popup menu (Excel VBA/Word VBA)

enable popup menu

Disable Right Click using VBA

Add Control To PopupMenu