获取具有说明的Excel宏名称 [英] Get Excel macros names with descriptions

问题描述

我已经找到了使用自动化从Excel获取宏名的许多方法（示例）。它们大体上是一样的，也不能1）检索宏描述（每个记录的宏可以使用Excel UI进行描述）或2）过滤出普通的VBA函数（有Excel记录的宏，还有一些funcs /写自己）。看起来好像Excel在源代码中保留描述作为注释，也在某些私人的地方。如果删除代码注释，Excel的描述仍然可见。

I have found many ways for getting macro names from Excel using automation (example). They are mostly the same and neither can 1) retrieve macro descriptions (each recorded macro can be decorated with a description using Excel UI) or 2) filter out ordinary VBA functions (there are Excel recorded macros and there are funcs/macros that you an write yourself). It seems as if Excel is keeping descriptions as comments in the source code but also in some private place too. If one deletes code comments the description is still visible to Excel.

我需要实现至少2），如果可能的话1）。我很欣赏一个C＃或VBA解决方案，但是真的有什么会做的。

I need to achieve at least 2) and if possible 1) too. I'd appreciate a C# or VBA solution, but really anything will do.

推荐答案

我遇到同样的问题，帮助你的网站，你可以通过csharp读取Excel VBA宏和函数。我做了一些改变，并提出了下面的解决方案。我收到一个列表，可用的宏及其首次创建时所获得的描述。我使用正则表达式来解析描述。可能会有一些更好的解决方案，但至少它可以用于我的目的。

I encountered the same problem and solved it by help of a website where you could read Excel VBA macros and functions through csharp. I made some changes and came up with the solution seen below. I receive a list with the available macros and the description they got when they first where created. I use regex to parse the description. Might be some better solution for this, but at least it works for my purpose.

    public List<Tuple<string,string>> GetAllMacrosInExcelFile(string fileName) {

        List<Tuple<string,string>> listOfMacros = new List<Tuple<string,string>>();

        var excel = new Excel.Application();
        var workbook = excel.Workbooks.Open(fileName, false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, false, false, Type.Missing, false, true, Type.Missing);

        var project = workbook.VBProject;
        var projectName = project.Name;
        var procedureType = Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc;

        foreach (var component in project.VBComponents) {
            VBA.VBComponent vbComponent = component as VBA.VBComponent;
            if (vbComponent != null) {
                string componentName = vbComponent.Name;
                var componentCode = vbComponent.CodeModule;
                int componentCodeLines = componentCode.CountOfLines;

                int line = 1;
                while (line < componentCodeLines) {
                    string procedureName = componentCode.get_ProcOfLine(line, out procedureType);
                    if (procedureName != string.Empty) {
                        int procedureLines = componentCode.get_ProcCountLines(procedureName, procedureType);
                        int procedureStartLine = componentCode.get_ProcStartLine(procedureName, procedureType);
                        var allCodeLines = componentCode.get_Lines(procedureStartLine, procedureLines);

                        Regex regex = new Regex("Macro\r\n' (.*?)\r\n'\r\n\r\n'");
                        var v = regex.Match(allCodeLines);
                        string comments = v.Groups[1].ToString();

                        if (comments.IsEmpty()) { comments = "No comment is written for this Macro"; }

                        line += procedureLines - 1;
                        listOfMacros.Add(procedureName.Tuple(comments));
                    } 
                    line++;
                }
            }
        }
        excel.Quit();
        return listOfMacros;
    }

这篇关于获取具有说明的Excel宏名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！