vba excel记录查询_记录您的Excel VBA过程

vba excel记录查询

In a complicated Excel file, you might end up with several code modules, and it's easy to lose track of what's connected to what. Here's how you can document your Excel VBA procedures.

在一个复杂的Excel文件中，您可能最终会得到几个代码模块，并且很容易丢失与什么内容相关的跟踪。 这是记录文档VBA过程的方法。

Recently, Professor Lee Townsend, from the University of Hartford, sent me the code that she uses to document a workbook's code. In her email to me, she warned that, "The code is not very polished…I am a professor and optical physicist by profession, not a programmer. I program Excel for fun and necessity."

最近，哈特福德大学的Lee Townsend教授向我发送了她用来记录工作簿代码的代码。 在给我的电子邮件中，她警告说：“代码不是很优美……我是专业的教授和光学物理学家，而不是程序员。我出于乐趣和必要性而对Excel进行编程。”

I tested the code in one of my files, and it worked very well, showing a list of all procedures, plus a list of which procedures are called by others. Maybe you'll have suggestions for improving the code, but I didn't experience any problems with it.

我在我的一个文件中测试了代码，它运行良好，显示了所有过程的列表，以及其他过程调用的列表。 也许您会提出改进代码的建议，但是我没有遇到任何问题。

建立代码 (Building the Code)

On her web page, Professor Townsend described why she created this code, and how it works:

汤森教授在其网页上描述了创建此代码的原因以及其工作方式：

• "In the process creating a somewhat complicated Excel VBA workbook I found I needed a code that would give me a procedure flow chart, i.e. what procedure calls or references what procedure."
  “在创建一个稍微复杂的Excel VBA工作簿的过程中，我发现我需要一个代码，该代码可以为我提供过程流程图，即什么过程调用或引用什么过程。”

To write the code, Professor Townsend says she relied heavily on Chip Pearson's "Programming The VBA Editor" – a great source of Excel information.

汤森教授说，要编写代码，她非常依赖Chip Pearson的“ Programming the VBA Editor ”(Excel信息的重要来源)。

代码如何工作 (How the Code Works)

The code runs in Excel 2010, and on the Mac, in Excel 2011. However, the code does not handle user-defined classes, because she doesn't use those.

该代码可在Excel 2010和Mac上的Excel 2011中运行。但是，该代码无法处理用户定义的类，因为她未使用这些类。

When the code runs, it creates a sheet named MacroFlowChart, if one doesn't already exist. It lists the workbook's procedures and shapes, and the lists go across to column Z, so scroll across to see all the information.

代码运行时，它将创建一个名为MacroFlowChart的工作表(如果尚不存在)。 它列出了工作簿的过程和形状，并且列表进入了Z列，因此请滚动查看所有信息。

There are buttons at the left of the sheet, which let you sort and update the sheet.

表格左侧有按钮，可让您对表格进行排序和更新。

在工作簿中使用代码 (Use the Code in Your Workbooks)

If you're adding the code to your own workbook, read the instructions in the code comments, starting at line 39 in the genProcedureFlowChart module.

如果要将代码添加到自己的工作簿中，请阅读代码注释中的说明，从genProcedureFlowChart模块的第39行开始。

The instructions tell you how to:

这些说明告诉您如何：

1. set an reference to the VBA Extensibility library
   设置对VBA可扩展性库的引用
2. enable programmatic access to the VBA Project (not required on a Mac)
   启用对VBA项目的编程访问(在Mac上不是必需的)

Then, go to the View tab on the Ribbon, and click Macros.

然后，转到功能区上的“视图”选项卡，然后单击“宏”。

Select the genProcedureFlowChart_Main macro, and run it, to create the MacroFlowChart sheet.

选择genProcedureFlowChart_Main宏，然后运行它以创建MacroFlowChart表。

Click OK when the confirmation message appears.

出现确认消息时，单击“确定”。

After the MacroFlowChart sheet has been created, you can click the button there, to update the lists.

创建MacroFlowChart工作表后，您可以单击此处的按钮以更新列表。

下载样本文件 (Download the Sample File)

Professor Townsend generously offered to share her code, so I've put a sample file on the Contextures website. Go to the Excel Sample Files page, and in the UserForms, VBA, Add-Ins section, look for "UF0018 - Excel VBA Code Documenter".

汤森教授慷慨地提出要分享她的代码，因此我在Contextures网站上放置了一个示例文件。 转到“ Excel示例文件”页面 ，然后在“ 用户窗体，VBA，加载项”部分中查找“ UF0018-Excel VBA Code Documenter”。

The sample file is in Excel 2007/2010 format, and is zipped. It contains macros, to be sure to enable macros if you want to test the file. There are instructions on the intro page, and comments in the code.

该示例文件为Excel 2007/2010格式，并已压缩。 它包含宏，如果要测试文件，请确保启用宏。 简介页上有说明，代码中有注释。

You can download another sample file from Professor Townsend's web page, in the Excel section. If you have any comments or suggestions, she would like to hear from you.

您可以从Townsend教授的网页的Excel部分下载另一个示例文件。 如果您有任何意见或建议，她希望收到您的来信。

翻译自: https://contexturesblog.com/archives/2012/07/10/document-your-excel-vba-procedures/

vba excel记录查询