C# Excel VBA make module names not depended on the language

Question

excelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);

This code in English version of Office creates module named: "Module1". But if office language is different "Module1" will be in another language. I need to know how this module is called in my code.

var standardModule = excelFile.VBProject.VBComponents.Item("ThisWorkbook");

The same problem is here in English version of Office "ThisWorkbook" exits, but in another language it will be called differently.

It's possible to make this code language independent?

What object type is "ThisWorkbook"? A WorkBook or a module? EDIT: also can't you access it by index? would the index number vary? — Innat3, Sep 07 '16 at 13:58
@Innat3 It's a global `Workbook` instance representing the host document containing the code of the current VBA project. — Mathieu Guindon, Sep 07 '16 at 14:01
that doesnt make any sense, the host document is already the variable "excelFile", with that piece of code I believe, you are trying to search a module with the name "ThisWorkbook" — Innat3, Sep 07 '16 at 14:05
@Innat3 every single Excel VBA project has a `ThisWorkbook` document module representing the host document, regardless of how much sense it makes. — Mathieu Guindon, Sep 07 '16 at 14:06
The second part of the question is a Possible duplicate of [How to identify ThisWorkbook module using VBA](http://stackoverflow.com/q/33485632/11683). The first part is correctly [answered below](http://stackoverflow.com/a/39372165/11683). — GSerg, Mar 09 '17 at 15:50

Mathieu Guindon · Answer 1 · 2016-09-07T14:21:06.267

excelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);

VBComponents.Add is a function, returning the object that was just added - but you're discarding that reference.

All you need to do is to hold on to that reference:

var component = excelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);

And then you can rename it at will (must be unique in the parent project though):

component.Name = "RenameMe";

...Or if you just need to know its name, it's right there for you to read:

Debug.WriteLine(component.Name);

Comintern · Accepted Answer · 2016-09-07T15:02:40.297

2

The first one is easy - VBComponents.Add returns a VBComponent. You can just inspect the .Name property:

var module = excelFile.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
Debug.WriteLine(module.Name);

The second one is a bit trickier. You'll need to loop through all of the VBComponents and test for the 2 things that are unique to the Workbook object. It will have a .Type of vbext_ct_Document and 134 properties in its .Properties collection by default:

VBComponent thisWorkbook;
foreach (var module in excelFile.VBProject.VBComponents)
{
    var test = module as VBComponent;
    if (test.Type == vbext_ComponentType.vbext_ct_Document &&
        test.Properties.Count == 134)
    {
        thisWorkbook = test;
        Debug.WriteLine(thisWorkbook.Name);
        break;
    }
}

EDIT: The Linq solution looks like this, but it's possible that you could leave dangling Interop references this way. If you want to try it, it can't hurt - but it would be the first place I'd look if Excel doesn't shut down properly:

var thisWorkbook =
    (excelFile.VBProject.VBComponents).Cast<VBComponent>()
        .First(x => x.Type == vbext_ComponentType.vbext_ct_Document &&
                    x.Properties.Count == 134);

EDIT2: As pointed out by @Mat'sMug in the comments, the property count is specific to the version - the value above is probably specific to Excel 2013. For a new workbook, the ThisWorkbook module will be the one with the highest property count. This should work on any version:

VBComponent thisWorkbook = null;
foreach (var component in excelFile.VBProject.VBComponents.Cast<VBComponent>())
{
    if (thisWorkbook == null || component.Properties.Count > thisWorkbook.Properties.Count)
    {
        thisWorkbook = component;
    }
}
Debug.WriteLine(thisWorkbook.Name);

Linq:

var thisWorkbook =
    excelFile.VBProject.VBComponents.Cast<VBComponent>()
        .Aggregate((p, x) => (p.Properties.Count > x.Properties.Count ? p : x));

edited Sep 07 '16 at 15:02

answered Sep 07 '16 at 14:24

Comintern

21,855
5
33
80

Oh, somehow I hadn't realized OP was also asking for a way to retrieve `ThisWorkbook`. Nice answer! =) – Mathieu Guindon Sep 07 '16 at 14:34
1

Is the property count 134 in all known versions of Excel? – Mathieu Guindon Sep 07 '16 at 14:35
1

@Mat'sMug - Now that you mention it, I would actually doubt it. It should be fixed per version though. 134 is the value in 2013 (worksheets have 66 BTW). It would probably be best to grab all the `VBComponent` of type `vbext_ct_Document`, group by `Properties.Count`, and take the result with the highest property count. For a new workbook, that should be a safe assumption. – Comintern Sep 07 '16 at 14:40
@WilliamCaman - Mat's Mug made a good point above - see the edit. – Comintern Sep 07 '16 at 15:03
The search is not reliable and is not needed. Given a `Workbook excelFile`, the `ThisWorkbook`'s `VBComponent` is `excelFile.VBProject.VBComponents[excelFile.CodeName]`. – GSerg Mar 09 '17 at 15:53
1

@GSerg - In this specific case, sure. But the presence of a `CodeName` property on the *Excel* object doesn't make the search unreliable. AFAIK examining the properties is the only way to determine whether the underlying document module is a Workbook or a Worksheet when given an arbitrary `VBComponent`. If you have a better method, the RD devs would love to know it. – Comintern Mar 09 '17 at 16:11
@Comintern I don't quite understand. Both `Workbook` and `Worksheet` have a `CodeName` and the lookup works for them just as well. On contrary, the Excel object (which is `Excel.Application` I assume?) does not have a `CodeName`, but it is not associated with a code module either. – GSerg Mar 09 '17 at 16:17
@GSerg see [how do I know that "ThisWorkbook" is a "Workbook"?](http://stackoverflow.com/q/37078992/1188513) – Mathieu Guindon Mar 09 '17 at 16:20
@GSerg - How do you get the `Excel.Workbook` object from an arbitrary `VBComponent`? Using the `CodeName` property only works if you have access to the Excel object. That's why I say that using the `CodeName` works fine *in this case* - but it has the underlying assumption that you hold the reference to the Workbook or Worksheet. – Comintern Mar 09 '17 at 16:21
I will look into the reverse lookup, but I still don't understand why it's an issue to have to have a reference to Excel file. In the code in your answer, you have `excelFile` which is a `Workbook`, and you use it everywhere. – GSerg Mar 09 '17 at 16:24
@GSerg - It's *not* an issue if you have access to the `excelFile`. That fact that the solution above gets the `VBComponents` collection from the Workbook is only coincidental to the OP's code. The test as to whether the `VBComponent` is a Workbook is intentionally meant to be a more general solution. – Comintern Mar 09 '17 at 16:30
1

@Mat'sMug Okay, I think understand better. Then again, maybe I do not - because if you can't make assumptions on existence and structure of the host application, the how can you look up anything? Your absolute best is getting to the `VBProject` that contains the module (which is `component.Collection.Parent`), anything beyond that *requires* knowing that there must be a "Workbook" that must have the VBProject as its property. And still, this means this answer is much more confusing and less reliable *for the OP* than `VBComponents[excelFile.CodeName]`. – GSerg Mar 09 '17 at 16:51
@GSerg - I agreed that *for the OP*, using the Excel object directly was easier about an hour ago. You keep coming back to the "less reliable" assertion though. `VBComponent.Properties.Count` per module type can be considered a constant for any given version of Excel - it's defined in the TypeLibrary and isn't effected by user code. Is there an example where sorting by property count *wouldn't* be reliable? – Comintern Mar 09 '17 at 17:06
@Comintern I don't have an example, but I can't take the `For a new workbook, the ThisWorkbook module will be the one with the highest property count` for granted either. It just doesn't sound like it is based on something other than experiment. It may be true today, it might change with a next version. It sounds even less convincing when I try to apply it to a host-agnostic situation. – GSerg Mar 09 '17 at 17:20
@GSerg - By that logic you can't take for granted that the next version of Excel won't remove the `CodeName` property. The property count *can't* contain anything outside of the TypeLib, because that's not really the way that COM works. Anything that isn't a declared property of the document class would have to be on an extended interface. One of the rules for a dispatch interface is "The member and parameter DISPIDs must remain constant for the lifetime of the object." - it ***has to*** be bound to the defined interface in the TypeLib. – Comintern Mar 09 '17 at 17:31
@Comintern It's nothing like removing properties from an interface. `Properties` is a collection that can be filled with any number of elements. Apparently you are saying that the `Properties` collection is always filled in a way that mirrors the typelib-declared properties of the parent, and their count always matches. I did not know that, but if that is the case, still in the next Excel version a Worksheet may contain more properties than a Workbook - because a bunch of properties can be added to the sheet with none removed from the workbook. – GSerg Mar 09 '17 at 17:39
@GSerg - Right, and the next version of Excel could remove `CodeName`. I think the likelihood of *either* of those things happening is approaching zero. ;-) – Comintern Mar 09 '17 at 17:42
If you only have the component reference, then `TypeOf vbcomp.Properties("Parent").Object Is Application` will tell you if it is a Workbook, but how do you distinguish between a Chart sheet and a Worksheet? The Property count, in my testing, is more reliable *and* faster. – ThunderFrame Mar 09 '17 at 19:51

score -1 · Answer 3 · answered Sep 07 '16 at 14:03

Since you just added that module in your code, it should be on the last index position, thus it should be accesible by doing:

VBComponent ModuleIJustAdded = excelFile.VBProject.VBComponents.Item(excelFile.VBProject.VBComponents.Count - 1);

Since you are unsure about the module's name, I suggest you try to play around the index.

C# Excel VBA make module names not depended on the language

3 Answers3