Using the Smart Tags Object Model in Excel [Office 2003 SDK Documentation]

You can use the Microsoft Excel smart tags object model in the same way as the Microsoft Word object model, but there are additional methods to take advantage of. The sample Excel macro (in the file "Metric Conversion Demo.xls") demonstrates some of the same things as the Word macro described above, but also demonstrates the ability to fire actions programmatically.

The statement below loops through all of the smart tags in the active worksheet using the Count property.

For i = 1 To Application.ActiveSheet.SmartTags.Count

Then each smart tag is checked if it is of the type looked for.

If (Application.ActiveSheet.SmartTags(i).Name = _
    METRICURI) Then

If it is, then the units are checked to see whether they are of the right type as well. If they are, then a variable is set to indicate whether or not to turn on bold. At the same time, the range object associated with the smart tag is cached. The reason this object is cached is that executing the action to convert the smart tag to English units will remove the smart tag from the collection, and in the process will remove the range object associated with that particular smart tag.

If (Application.ActiveSheet.SmartTags(i).Properties _
    ("unit") = "meters") Then
    turnOnBold = 1
    ' Cache the range object since the smart tag may go away.
    Set r = Application.ActiveSheet.SmartTags(i).Range
Else
    turnOnBold = 0
End If

The statement below is used to fire the action whose verb name is "convertToEnglishUnits" (as returned by the ISmartTagAction_VerbNameFromID function).

Application.ActiveSheet.SmartTags(i).SmartTagActions _
    ("convertToEnglishUnits").Execute

Finally, Excel's object model is used for changing the font style to set bold on the Range where the smart tag was, prior to being converted to English units.

If (turnOnBold = 1) Then
     r.Font.Bold = 1
End If