Is there a way to set temporary flags on Excel workbook objects?

I have a C# helper class (ExcelMacroHelper) that assists with injecting VBA macro code into open Excel workbooks and running the resulting macros. I just realized that the following steps would break my code:

1. C# application injects macro code into the active workbook, which causes the ExcelMacroHelper to mark its state as ready to run macros

2. The user switches to a different workbook in Excel.

3. The C# application attempts to run a macro. The开发者_运维技巧 ExcelMacroHelper will think it is ready to run macros, but the VBA code was injected into a different workbook so the call will fail.

To fix this, I think I need some way of setting a temporary property on the Workbook object indicating that my macro code has been injected into it, or at least a way of maintaining a list of Workbooks that have been handled. Any ideas?

---

You can use Names to hold values (as well as range references etc)

In pseudo code

if not (name already exists) then
    Set nm = workbook.Names.add("Injected")
    nm.Value = False
    nm.Visable = False
end if

if nm.value = False
    //Inject Code
    nm.value = true
endif

Note: simplest way to test if name exists is to try to access it and handle the error if it doesn't

---

You could use the Tag property of the Workbook.

When you inject the code, you could do something like this:

public class ExcelMacroHelper
{
   public static void Inject(Workbook workbook)
   {
       if ((workbook.Tag as String != "Injected"))
       {
          //Inject the code

           workbook.Tag = "Injected";
       }
   }
}

---

I ended up using custom document properties. Something like this:

private bool needToInjectMacroCode() {
    // Get custom document property with name tagPropertyName

    object properties, property, propertyValue;

    properties = excel.ActiveWorkbook.GetType().InvokeMember(
        "CustomDocumentProperties",
        BindingFlags.Default | BindingFlags.GetProperty,
        null, excel.ActiveWorkbook, null);

    try {
        property = properties.GetType().InvokeMember(
            "Item",
            BindingFlags.Default | BindingFlags.GetProperty,
            null, properties, new object[] { tagPropertyName });
    } catch (TargetInvocationException) {
        return true;
    }

    propertyValue = property.GetType().InvokeMember(
        "Value",
        BindingFlags.Default | BindingFlags.GetProperty,
        null, property, null);

    return (tagString != (propertyValue as string));
}

// ...

private void setMacroCodeInjected() {
    // Set custom property with name tagPropertyName to value tagString

    object properties = excel.ActiveWorkbook.GetType().InvokeMember(
        "CustomDocumentProperties",
        BindingFlags.Default | BindingFlags.GetProperty,
        null, excel.ActiveWorkbook, null);

    try {
        properties.GetType().InvokeMember(
            "Add",
            BindingFlags.Default | BindingFlags.InvokeMethod,
            null, properties, new object[] {
                tagPropertyName, false,
                Office.MsoDocProperties.msoPropertyTypeString,
                tagString
            });
    } catch (TargetInvocationException) {
        object property = properties.GetType().InvokeMember(
            "Item",
            BindingFlags.Default | BindingFlags.GetProperty,
            null, properties, new object[] { tagPropertyName });

        property.GetType().InvokeMember(
            "Value",
            BindingFlags.Default | BindingFlags.SetProperty,
            null, property, new object[] { tagString });
    }
}