论坛内下拉菜单VBA程序的疑问

ZeriOne

VBA程序疑问

这个是下拉菜单的VBA程序，是在论坛里面看到的，做的效果很漂亮也很好用，于是下载下来记录一下，这是原帖：https://club.excelhome.net/thread-1607374-1-1.html

但是代码中有些疑问，想请教一下：

1、关于模块1内的第三个子程序，这里是不是有问题？

Private Sub AddControlButton(ByVal pkey$, ByVal key$, ByVal caption$, ByVal i&, ByVal n&)
    Dim myb
    Set myb = Tree(pkey).Controls.Add(Type:=msoControlButton)
    With myb    '菜单加入触发按钮
        .caption = caption    '菜单按钮名称为x
        .OnAction = "'WriteToRng " & i & "," & n & "'" '最后一级选择触发事件,完成输入
    End With
    Tree.Add key, myb
End Sub

.OnAction = "'WriteToRng " & i & "," & n & "'"

.OnAction属性指定了当用户点击此按钮时应该执行的动作，当按钮被点击时，VBA环境会执行名为`WriteToRng`的子程序，并且传递参数`i`和`n`的当前值。VBA在处理`.OnAction`属性值时将其视为宏名，因此使用了单引号来包裹整个表达式，确保VBA能够正确识别并解析包含逗号的参数列表。

但是在后续 WriteToRng 的子程序中明明只涉及了“i”和“N_col”这2个变量。如果是为了传递值，为什么是“i”和"n"？而不是“i”和“N_col”？这里是属于笔误吗？但是程序又能正常运行？

Public Sub WriteToRng(i, N_col)
    ActiveCell.EntireRow.Range("A1").Resize(1, N_col) = Sheets("数据源").Range("A" & i).Resize(1, N_col).Value
End Sub

2、关于 WriteToRng 的子程序中，是否存在多余的代码？

Public Sub WriteToRng(i, N_col)
    ActiveCell.EntireRow.Range("A1").Resize(1, N_col) = Sheets("数据源").Range("A" & i).Resize(1, N_col).Value
End Sub

.Range("A1") 、.Range("A" & i) 部分可能是多余的？因为Resize(1, N_col)已经设置了选取区域。但是这个即使加上 .Range("A1") 也是有效，因为它仍然指的是当前活动单元格所在行的 A1 单元格开始的范围，也就是说，从 A1 单元格开始，横跨第”N_col“个单元格。那为什么在"Sheet1"工作表中的[a2:d6] 区域下拉菜单能正常工作，而不是被限定在了A1:A N_col？

3、关于问题1，既然变量之间要传递，为什么程序的访问修饰符不直接都是用”Public“呢，那样理论上这些变量都可以直接调用，不是更方便？只是单纯为了更好地封装、确保代码的安全性吗？

---------------------------------------------

VBA程序还在入门，问题有点多，麻烦各位了，谢谢！

山菊花

问题1，n是前一个过程的变量，假如n=5，传递到第二个过程时，对应的参数是N_col，这时N_col=5。两个过程都有变量i，其实这两个i也是不同意义的变量名。慢慢看一下下面两个过程，理解一下参数传递中变量名的意思。



问题2，Range("A1")表示这一行的第一个单元格。如果省略它，结果也是对的。但说它多余不对，应该是严谨。另一个“Range("A" & i)”是不能省略的，它指定“数据源”表中的起始位置，可以试一下删去后的效果。

问题3，参数传递能不能用全局变量代替，要看具体情况，有时可以，有时反而麻烦或者不允许（如递归过程），自己可以去试一试。
学习程序，还是要多练习，自己有想法，去试一试，比一比。

ZeriOne

感觉这个程序对于入门来说还挺难的，很多代码都要拆开来看参数说明，有几个子程序啃不动，还在啃子程序2，有一些问题想请教一下版主大大：

第二个子程序 Sub main()中：
--------------------------------------------------------------------------------
    Dim mybar As Object, arr, brr, crr, i&, j&, key$, myb, pkey$, test
   ' 此处的 brr、crr 实际是多余的变量？声明了但未被使用
    ···
    ···
    ···


    For j = 2 To UBound(arr, 1)
        If Not Tree.exists(arr(j, 1)) Then
            If arr(j, 2) = "" Then
                AddControlButton "myCell", arr(j, 1), arr(j, 1), j, N_col
            Else
                AddControlPopup "myCell", arr(j, 1), arr(j, 1)
            End If
        End If
    Next
    '遍历第二列以后的以第一列为基准key
    'Exit Sub
    For i = 2 To UBound(arr)    '遍历数据源行
        key = arr(i, 1)    '关键字从第一列开始
        For j = 2 To N_col  '遍历2-N列
            If arr(i, j) <> "" Then    '空格跳过
                pkey = key    '父节点关键字
                key = key & "\" & arr(i, j) '本级关键字
                If arr(i, j + 1) = "" Then  '下一列为空则直接写命令按钮
                    AddControlButton pkey, key, arr(i, j), i, N_col
                Else    '有下级菜单则添加弹出节点
                    If Not Tree.exists(key) Then    '第一次菜单出现
                        AddControlPopup pkey, key, arr(i, j)
                    End If
                End If
            End If
        Next
    Next
    Set mybar = Nothing

--------------------------------------------------------------------------------

请问关于这段代码，下面这2段代码的内容、参数是指什么？

AddControlButton "myCell", arr(j, 1), arr(j, 1), j, N_col：
第一个 `myCell` 是指定CommandBar对象，第一个 `arr(j, 1)` 是按钮的内部名称ID，第二个 `arr(j, 1)` 是按钮显示的文本，`j` 是指什么？， `N_col` 是指按钮位于是菜单的第几级？

AddControlPopup "myCell", arr(j, 1), arr(j, 1)：
第一个 `myCell` 是指定CommandBar对象，第一个 `arr(j, 1)` 是按钮的内部名称ID，第二个 `arr(j, 1)` 是按钮显示的文本；


AddControlButton、AddControlPopup：
我查询搜索都说不是一个内建的函数或方法，需要确保已经定义了它，但是代码上下文都没有涉及这个函数的声明...？

ZeriOne

山菊花 发表于 2024-3-11 09:41
问题1，n是前一个过程的变量，假如n=5，传递到第二个过程时，对应的参数是N_col，这时N_col=5。两个过程 ...

版主大大，有几个问题还需要请教的，我写在了3楼，但没有正确艾特到

还望不吝赐教

ZeriOne

ZeriOne 发表于 2024-3-15 22:05
版主大大，有几个问题还需要请教的，我写在了3楼，但没有正确艾特到

还望不吝赐教

傻了，没想到这个是直接调用子程序的可能，VBA新版本中可以直接使用子程序或函数名调用，不需 `Call` 关键字，碎片时间去看程序，不一口气看完反而有点乱了

调用的子程序3、4理解上大致是下面这样？

----------------------------------------子程序3


'添加菜单命令
Private Sub AddControlButton(ByVal pkey$, ByVal key$, ByVal caption$, ByVal i&, ByVal n&)
    Dim myb
    Set myb = Tree(pkey).Controls.Add(Type:=msoControlButton)
    With myb    '菜单加入触发按钮
        .caption = caption    '菜单按钮名称为x
        .OnAction = "'WriteToRng " & i & "," & n & "'" '最后一级选择触发事件,完成输入
        'Debug.Print "'WriteToRng " & i & "," & n & "'"
    End With
    Tree.Add key, myb
End Sub
----------------------------------------

子程序 3

1. `'添加菜单命令`：注释，说明子程序的功能。
2. `Private Sub AddControlButton(ByVal pkey$, ByVal key$, ByVal caption$, ByVal i&, ByVal n&)`: 定义一个私有子程序，名为`AddControlButton`，接受五个参数。
3. `Dim myb`: 声明一个局部变量`myb`，用于存储命令按钮对象。
4. `Set myb = Tree(pkey).Controls.Add(Type:=msoControlButton)`: 创建一个命令按钮对象，并将其设置为字典`Tree`中`pkey`键所对应的菜单的子控件。
5. `.caption = caption`: 在与`With myb`相关联的命令按钮上设置`caption`属性，即按钮的标题。
6. `.OnAction = "'WriteToRng " & i & "," & n & "'"`: 设置命令按钮的`.OnAction`属性，使其在点击时运行`WriteToRng`过程，并传递参数`i`和`n`。
        1. 变量被传递到名为"WriteToRng"的子程序中;
        2. `Public Sub WriteToRng(i, N_col)`中接收的变量分别传递给了 `i` → `i` ， `n` → `N_col`
7. `Debug.Print "'WriteToRng " & i & "," & n & "'"`: （注释行）原计划用于调试输出的语句。
8. `End With`: 结束对命令按钮对象的属性设置块。
9. `Tree.Add key, myb`: 将新创建的命令按钮对象添加到全局字典`Tree`中，键为`key`。
10. `End Sub`: 结束子程序`AddControlButton`的定义。




----------------------------------------子程序4

'添加弹出菜单节点
Private Sub AddControlPopup(ByVal pkey$, ByVal key$, ByVal caption$)
    Dim myb
    Set myb = Tree(pkey).Controls.Add(Type:=msoControlPopup)
    myb.caption = caption    '菜单按钮名称
    Tree.Add key, myb '加入字典以供下级菜单索引节点用
End Sub
----------------------------------------



子程序 4

1. `'添加弹出菜单节点`：注释，说明该子程序的功能是添加一个弹出菜单节点。
2. `Private Sub AddControlPopup(ByVal pkey$, ByVal key$, ByVal caption$)`: 定义一个私有子程序，名为`AddControlPopup`，接受三个参数。
3. `Dim myb`: 声明一个局部变量`myb`，用于存储即将创建的弹出菜单对象。
4. `Set myb = Tree(pkey).Controls.Add(Type:=msoControlPopup)`: 从字典`Tree`中根据`pkey`键获取菜单对象，并在其下添加一个新的控件，类型为`msoControlPopup`（弹出菜单），并将新创建的弹出菜单对象赋值给变量`myb`。
5. `.caption = caption`: 设置与`With myb`关联的弹出菜单对象的标题属性为传递进来的`caption`变量值。
6. `Tree.Add key, myb`: 将新创建的弹出菜单对象添加到全局字典`Tree`中，键为`key`，方便后续通过键值引用该弹出菜单对象。
7. `End Sub`: 结束子程序`AddControlPopup`的定义。




为什么子程序3、4，Tree(pkey).Controls.Add 都只有`Type`这个可选参数，仅指明了要添加控件的类型，.caption 中定义了按钮名称即ID，.OnAction 则是指定当控件被触发时调用的宏名称，但是按钮的坐标和尺寸参数却没有指明？如果大小尺寸缺省，对于下拉菜单这个控件而言，可能是最小可接受尺寸，或者是根据内容自动调整的尺寸（调整文本长度去测试，确实是这样的）？但是按钮坐标这个缺省的话会自动在触发的单元格坐标中出现控件吗？如果我要指定这些参数的话该怎么编写？

山菊花

为什么子程序3、4，Tree(pkey).Controls.Add 都只有`Type`这个可选参数
没有为什么，表示其余参数默认或不重要。大家几乎都这样写，再写就是加个位置参数before。

但是按钮的坐标和尺寸参数却没有指明？
Add命令不能设置坐标和尺寸。

但是按钮坐标这个缺省的话会自动在触发的单元格坐标中出现控件吗？如果我要指定这些参数的话该怎么编写？
右键菜单，哪里点击鼠标就显示在哪里，没试过吗？

新手研究人家的程序，建议与读书相结合，循序渐进学习。
除了读书，最有效的手段就是查看帮助，有问题就按F1。
再者就是前面提的建议，多动手试试，试的结果是什么就相信什么，比问人家准确。
现在还有一个好老师，就是AI，可去试试。

ZeriOne

山菊花 发表于 2024-3-16 07:15
为什么子程序3、4，Tree(pkey).Controls.Add 都只有`Type`这个可选参数
没有为什么，表示其余参数默认或不 ...

大概理解了，谢谢版主大大解答

这段vba程序我用过2个AI做逐步解析，但有些问题还是没办法完全解答出来，最开始问的问题AI就没能给出正确的回答，书本为主AI为辅吧，埋头还搞不懂就只能向他人请假了