VBA的隐藏功能 [英] Hidden features of VBA

问题描述

VBA 语言的哪些功能要么文档不全，要么根本不经常使用?

Which features of the VBA language are either poorly documented, or simply not often used?

推荐答案

这个技巧只适用于 Access VBA，Excel 和其他人不允许它.但是您可以通过在模块名称前加上下划线来使标准模块对对象浏览器隐藏.只有当您更改对象浏览器以显示隐藏的对象时，该模块才会可见.

This trick only works in Access VBA, Excel and others won't allow it. But you can make a Standard Module hidden from the object browser by prefixing the Module name with an underscore. The module will then only be visible if you change the object browser to show hidden objects.

此技巧适用于所有基于 vb6 的 VBA 版本中的枚举.您可以通过将其名称括在括号中，然后在其前面加上下划线来创建 Enum 的隐藏成员.示例:

This trick works with Enums in all vb6 based version of VBA. You can create a hidden member of an Enum by encasing it's name in brackets, then prefixing it with an underscore. Example:

Public Enum MyEnum
    meDefault = 0
    meThing1 = 1
    meThing2 = 2
    meThing3 = 3
    [_Min] = meDefault 
    [_Max] = meThing3 
End Enum

Public Function IsValidOption(ByVal myOption As MyEnum) As Boolean
    If myOption >= MyEnum.[_Min] Then IsValidOption myOption <= MyEnum.[_Max]
End Function

在 Excel-VBA 中，您可以通过将单元格括在方括号中来引用单元格，方括号还用作 evaluate 命令 允许您评估公式语法:

In Excel-VBA you can reference cells by enclosing them in brackets, the brackets also function as an evaluate command allowing you to evaluate formula syntax:

Public Sub Example()
    [A1] = "Foo"
    MsgBox [VLOOKUP(A1,A1,1,0)]
End Sub

你也可以不使用 MemCopy (RtlMoveMemory) 传递原始数据，通过将 LSet 与相同大小的用户定义类型结合起来:

Also you can pass around raw data without using MemCopy (RtlMoveMemory) by combining LSet with User Defined Types of the same size:

Public Sub Example()
    Dim b() As Byte
    b = LongToByteArray(8675309)
    MsgBox b(1)
End Sub

Private Function LongToByteArray(ByVal value As Long) As Byte()
    Dim tl As TypedLong
    Dim bl As ByteLong
    tl.value = value
    LSet bl = tl
    LongToByteArray = bl.value
End Function

八进制 &十六进制文字实际上是无符号类型，它们都将输出 -32768:

Octal & Hex Literals are actually unsigned types, these will both output -32768:

Public Sub Example()
    Debug.Print &H8000
    Debug.Print &O100000
End Sub

如前所述，在括号内传递一个变量会导致它被传递 ByVal:

As mentioned, passing a variable inside parenthesis causes it to be passed ByVal:

Sub PredictTheOutput()
    Dim i&, j&, k&
    i = 10: j = i: k = i
    MySub (i)
    MySub j
    MySub k + 20
    MsgBox Join(Array(i, j, k), vbNewLine), vbQuestion, "Did You Get It Right?"
End Sub

Public Sub MySub(ByRef foo As Long)
    foo = 5
End Sub

您可以将字符串直接分配到字节数组中，反之亦然:

You can assign a string directly into a byte array and vice-versa:

Public Sub Example()
    Dim myString As String
    Dim myBytArr() As Byte
    myBytArr = "I am a string."
    myString = myBytArr
    MsgBox myString
End Sub

Mid"也是一个运算符.使用它可以覆盖字符串的特定部分，而无需 VBA 众所周知的慢速字符串连接:

"Mid" is also an operator. Using it you overwrite specific portions of strings without VBA's notoriously slow string concatenation:

Public Sub Example1()
    ''// This takes about 47% of time Example2 does:
    Dim myString As String
    myString = "I liek pie."
    Mid(myString, 5, 2) = "ke"
    Mid(myString, 11, 1) = "!"
    MsgBox myString
End Sub

Public Sub Example2()
    Dim myString As String
    myString = "I liek pie."
    myString = "I li" & "ke" & " pie" & "!"
    MsgBox myString
End Sub

这篇关于VBA的隐藏功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！