vba 使用定义目录中的子目录名称列表填充用户表单组合框

Populate a userform Combobox with a list of subdirectory names in a defined directory

提问by NewSpeaker

I apologize if this question was answered previously on this board. My searches didn't turn up what I'm looking for. I am a VBA novice and would like to know if there is a way to populate a userform combobox with the names of all subdirectories contained within a predefined directory (I need the list to be updated every time the userform is launched). I've seen some code that does this on other websites but they were for earlier versions of Excel and I could not get them to work. I am using Excel 2007. I appreciate any help you may be able to provide.

如果这个问题之前在这个板上得到了回答，我深表歉意。我的搜索没有找到我要找的东西。我是 VBA 新手，想知道是否有办法用预定义目录中包含的所有子目录的名称填充用户表单组合框（每次启动用户表单时，我都需要更新列表）。我在其他网站上看到过一些代码，但它们适用于早期版本的 Excel，我无法让它们工作。我正在使用 Excel 2007。感谢您提供的任何帮助。

回答by Tomalak

Option Explicit

Private Sub UserForm_Initialize()
  Dim name

  For Each name In ListDirectory(Path:="C:\", AttrInclude:=vbDirectory, AttrExclude:=vbSystem Or vbHidden)
    Me.ComboBox1.AddItem name
  Next name
End Sub

Function ListDirectory(Path As String, AttrInclude As VbFileAttribute, Optional AttrExclude As VbFileAttribute = False) As Collection
  Dim Filename As String
  Dim Attribs As VbFileAttribute

  Set ListDirectory = New Collection

  ' first call to Dir() initializes the list
  Filename = Dir(Path, AttrInclude)

  While Filename <> ""
    Attribs = GetAttr(Path & Filename)
    ' to be added, a file must have the right set of attributes
    If Attribs And AttrInclude And Not (Attribs And AttrExclude) Then
      ListDirectory.Add Filename, Path & Filename
    End If
    ' fetch next filename
    Filename = Dir
  Wend
End Function

A few notes, since you said you had little experience with VBA.

一些注意事项，因为您说您对 VBA 几乎没有经验。

• Always have Option Explicitin effect. No excuses.
• Dir()is used in VB to list files.
• Collections are a lot more convenient than arrays in VBA.
• There are named parameters available in function calls (name:=value). You don't have to use them, but they help to make sense of long argument lists. Argument order is irrelevant if you use named parameters. You cannot mix named and unnamed parameters, though.
• You can have optional arguments with default values.
• Note that assigning to the function name (ListDirectoryin this case) sets the result of a function. You can therefore use the function name directly as a variable inside that function.
• Set AttrIncludeto -1if you want to return all types of files. Conveniently, -1is the numerical value of True., i.e. ListDirectory("C:\", True).
• Set AttrExcludeto 0if you want to exclude no files. Conveniently, 0is the numerical value of False., i.e. ListDirectory("C:\", True, False), which also is the default.
• All logical operators in VB 6.0 are bit-wise, hence you can check whether a file is a directory by using If Attribs And VbDirectory Then ...
• You can combine multiple bit values with Or, e.g. vbSystem Or vbHidden.
• Consequently, you can filter directories with a simple bit-wise logic check.
• Use the Object Browser (hit F2) to inspect available Functions, Types and Constants, for example the constants in the VbFileAttributeenum.

• 始终Option Explicit有效。没有理由。
• Dir()在 VB 中用于列出文件。
• 集合比 VBA 中的数组方便得多。
• 函数调用中有可用的命名参数 ( name:=value)。您不必使用它们，但它们有助于理解长参数列表。如果使用命名参数，则参数顺序无关紧要。但是，您不能混合命名和未命名参数。
• 您可以使用带有默认值的可选参数。
• 请注意，分配给函数名称（ListDirectory在本例中）会设置函数的结果。因此，您可以直接将函数名称用作该函数内的变量。
• 如果要返回所有类型的文件，请设置AttrInclude为-1。方便的-1是，是 . 的数值True，即ListDirectory("C:\", True)。
• 如果您不想排除任何文件，请设置AttrExclude为0。方便的0是 . 的数值False，即ListDirectory("C:\", True, False)，这也是默认值。
• VB 6.0 中的所有逻辑运算符都是按位的，因此您可以使用以下命令检查文件是否为目录 If Attribs And VbDirectory Then ...
• 您可以将多个位值与 组合在一起Or，例如vbSystem Or vbHidden。
• 因此，您可以通过简单的逐位逻辑检查来过滤目录。
• 使用对象浏览器（按 F2）检查可用的函数、类型和常量，例如VbFileAttribute枚举中的常量。