Custom Property in Excel using VBA, C#, VB.Net example

Custom Property in Excel using VBA, C#, VB.Net example

CustomProperty

Helps to create identifier with key, value format which helps developer to tag information within internal XML metadata of a Excel sheet. In this article we will see how we can create a custom property and retrieve a property. Use the Add method or the Item property of the CustomProperties collection to return a CustomProperty object.

After a CustomProperty object is returned, you can add metadata to worksheets by using the CustomProperties property of the Worksheet object with the Add method.

In this article we are using VSTO (Visual Studio Tools for office) in both flavors (C#, VB.Net) and VBA (Visual Basic for Applications) hence following prerequisites are recommended:

Prerequisites

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# or VB code style (Visual Studio Tools for Office)
  • Excel 2010 or above
  • Create a ribbon designer and put button

C# code example

private void btnCustomProperty_Click(object sender, RibbonControlEventArgs e)
{
	//Bind sheet reference
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

	//add property
	oWorksheet.CustomProperties.Add("Site_Name", "VBAOVERALL");

	//Pull custom properties from sheet
	excel.CustomProperties oCustomProperties = oWorksheet.CustomProperties;

	//Iterate each property
	foreach (excel.CustomProperty oCustomProperty in oCustomProperties)
	{
		//Read property
		Console.WriteLine("Custom Property Name-" + oCustomProperty.Name);
		Console.WriteLine("Custom Property Value-" + oCustomProperty.Value);
	}
	
}

VB.Net code example

Private Sub btnColorStop_Click(sender As Object, e As RibbonControlEventArgs) Handles btnColorStop.Click
	'Bind sheet reference
	dim oWorksheet As excel.Worksheet
	oWorksheet = Globals.ThisAddIn.Application.ActiveSheet

	'add property
	oWorksheet.CustomProperties.Add("Site_Name", "VBAOVERALL")

	'Pull custom properties from sheet
	Dim oCustomProperties As excel.CustomProperties
	oCustomProperties = oWorksheet.CustomProperties

	'Iterate each property        
	For Each oCustomProperty As excel.CustomProperty In oCustomProperties
		'Read property
		Console.WriteLine("Custom Property Name-" + oCustomProperty.Name)
		Console.WriteLine("Custom Property Value-" + oCustomProperty.Value)
	Next
End Sub

VBA code example

Public Sub CreateCustomProperty()
    'Bind sheet reference
    Dim oWorksheet As Worksheet
    Set oWorksheet = ActiveSheet

    'add property
    oWorksheet.CustomProperties.Add "Site_Name", "VBAOVERALL"

    'Pull custom properties from sheet
    Dim oCustomProperties As CustomProperties
    Set oCustomProperties = oWorksheet.CustomProperties

    'Iterate each property
    Dim oCustomProperty As CustomProperty
    For Each oCustomProperty In oCustomProperties
        'Read property
        Debug.Print "Custom Property Name-" & oCustomProperty.Name
        Debug.Print "Custom Property Value-" & oCustomProperty.Value
    Next
End Sub

Output

figure 1.0

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.