vba VBA的隐藏功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1070863/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hidden features of VBA
提问by guillermooo
Which features of the VBA language are either poorly documented, or simply not often used?
VBA 语言的哪些特性要么没有很好地记录，要么根本不经常使用？
回答by Oorang
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.
此技巧仅适用于 Access VBA、Excel，其他人则不允许。但是您可以通过在模块名称前加上下划线来使标准模块对对象浏览器隐藏。只有当您更改对象浏览器以显示隐藏的对象时，该模块才会可见。
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:
这个技巧适用于所有基于 vb6 的 VBA 版本中的枚举。您可以通过将其名称括在括号中，然后在其前面加上下划线来创建 Enum 的隐藏成员。例子：
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
In Excel-VBA you can reference cells by enclosing them in brackets, the brackets also function as an evaluate commandallowing you to evaluate formula syntax:
在 Excel-VBA 中，您可以通过将单元格括在方括号中来引用单元格，方括号还用作评估命令，允许您评估公式语法：
Public Sub Example()
[A1] = "Foo"
MsgBox [VLOOKUP(A1,A1,1,0)]
End Sub
Also you can pass around raw data without using MemCopy (RtlMoveMemory) by combining LSet with User Defined Types of the same size:
您还可以通过将 LSet 与相同大小的用户定义类型结合使用，而无需使用 MemCopy (RtlMoveMemory) 来传递原始数据：
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
Octal & Hex Literals are actually unsigned types, these will both output -32768:
八进制和十六进制文字实际上是无符号类型，它们都将输出 -32768：
Public Sub Example()
Debug.Print &H8000
Debug.Print &O100000
End Sub
As mentioned, passing a variable inside parenthesis causes it to be passed ByVal:
如前所述，在括号内传递一个变量会导致它被传递 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" is also an operator. Using it you overwrite specific portions of strings without VBA's notoriously slow string concatenation:
“Mid”也是一个运算符。使用它可以覆盖字符串的特定部分，而无需 VBA 众所周知的慢速字符串连接：
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
回答by Swanny
There is an important but almost always missed feature of the Mid() statement. That is where Mid() appears on the left hand side of an assignment as opposed to the Mid() function that appears in the right hand side or in an expression.
Mid() 语句有一个重要但几乎总是被遗漏的功能。这就是 Mid() 出现在赋值左侧的地方，而不是出现在右侧或表达式中的 Mid() 函数。
The rule is that if the if the target string is not a string literal, and this is the only reference to the target string, and the length of segment being inserted matches the length of the segment being replaced, then the string will be treated as mutable for the operation.
规则是，如果目标字符串不是字符串文字，并且这是对目标字符串的唯一引用，并且被插入的段的长度与被替换的段的长度匹配，则该字符串将被视为操作可变。
What does that mean? It means that if your building up a large report or a huge list of strings into a single string value, then exploiting this will make your string processing much faster.
这意味着什么？这意味着，如果您将大型报告或大量字符串构建为单个字符串值，那么利用这一点将使您的字符串处理速度更快。
Here is a simple class that benefits from this. It gives your VBA the same StringBuilder capability that .Net has.
这是一个从中受益的简单类。它为您的 VBA 提供了与 .Net 相同的 StringBuilder 功能。
' Class: StringBuilder
Option Explicit
Private Const initialLength As Long = 32
Private totalLength As Long ' Length of the buffer
Private curLength As Long ' Length of the string value within the buffer
Private buffer As String ' The buffer
Private Sub Class_Initialize()
' We set the buffer up to it's initial size and the string value ""
totalLength = initialLength
buffer = Space(totalLength)
curLength = 0
End Sub
Public Sub Append(Text As String)
Dim incLen As Long ' The length that the value will be increased by
Dim newLen As Long ' The length of the value after being appended
incLen = Len(Text)
newLen = curLength + incLen
' Will the new value fit in the remaining free space within the current buffer
If newLen <= totalLength Then
' Buffer has room so just insert the new value
Mid(buffer, curLength + 1, incLen) = Text
Else
' Buffer does not have enough room so
' first calculate the new buffer size by doubling until its big enough
' then build the new buffer
While totalLength < newLen
totalLength = totalLength + totalLength
Wend
buffer = Left(buffer, curLength) & Text & Space(totalLength - newLen)
End If
curLength = newLen
End Sub
Public Property Get Length() As Integer
Length = curLength
End Property
Public Property Get Text() As String
Text = Left(buffer, curLength)
End Property
Public Sub Clear()
totalLength = initialLength
buffer = Space(totalLength)
curLength = 0
End Sub
And here is an example on how to use it:
这是一个关于如何使用它的示例：
Dim i As Long
Dim sb As StringBuilder
Dim result As String
Set sb = New StringBuilder
For i = 1 to 100000
sb.Append CStr( i)
Next i
result = sb.Text
回答by mandroid
VBA itself seems to be a hidden feature. Folks I know who've used Office products for years have no idea it's even a part of the suite.
VBA 本身似乎是一个隐藏的功能。我认识的那些使用 Office 产品多年的人都不知道它甚至是套件的一部分。
I've posted this on multiple questions here, but the Object Browser is my secret weapon. If I need to ninja code something real quick, but am not familiar with the dll's, Object Browser saves my life. It makes it much easier to learn the class structures than MSDN.
我已经在此处针对多个问题发布了此内容，但对象浏览器是我的秘密武器。如果我需要快速编写一些 ninja 代码，但不熟悉 dll，对象浏览器可以挽救我的生命。它比 MSDN 更容易学习类结构。
The Locals Window is great for debugging as well. Put a pause in your code and it will show you all the variables, their names, and their current values and types within the current namespace.
Locals Window 也非常适合调试。在您的代码中暂停一下，它会向您显示当前命名空间中的所有变量、它们的名称以及它们的当前值和类型。
And who could forget our good friend Immediate Window? Not only is it great for Debug.Print standard output, but you can enter in commands into it as well. Need to know what VariableX is?
谁能忘记我们的好朋友 Immediate Window？它不仅适用于 Debug.Print 标准输出，而且您还可以在其中输入命令。需要知道什么是 VariableX？
?VariableX
Need to know what color that cell is?
需要知道那个单元格是什么颜色？
?Application.ActiveCell.Interior.Color
In fact all those windows are great tools to be productive with VBA.
事实上，所有这些窗口都是使用 VBA 提高工作效率的好工具。
回答by Dirk Vollmar
It's not a feature, but a thing I have seen wrong so many times in VBA (and VB6): Parenthesis added on method calls where it will change semantics:
这不是一个功能，而是我在 VBA（和 VB6）中多次看到错误的事情：在方法调用中添加了括号，它将改变语义：
Sub Foo()
Dim str As String
str = "Hello"
Bar (str)
Debug.Print str 'prints "Hello" because str is evaluated and a copy is passed
Bar str 'or Call Bar(str)
Debug.Print str 'prints "Hello World"
End Sub
Sub Bar(ByRef param As String)
param = param + " World"
End Sub
回答by Raj More
Hidden Features
隐藏功能
- Although it is "Basic", you can use OOP - classes and objects
- You can make API calls
- 虽然它是“基本的”，但您可以使用 OOP - 类和对象
- 您可以进行 API 调用
回答by DaveParillo
Possibly the least documented features in VBA are those you can only expose by selecting "Show Hidden Members" on the VBA Object Browser. Hidden members are those functions that are in VBA, but are unsupported. You can use them, but microsoft might eliminate them at any time. None of them has any documentation provided, but you can find some on the web. Possibly the most talked about of these hidden features provides access to pointers in VBA. For a decent writeup, check out; Not So Lightweight - Shlwapi.dll
VBA 中记录最少的功能可能是那些只能通过在 VBA 对象浏览器上选择“显示隐藏成员”来公开的功能。隐藏成员是那些在 VBA 中但不受支持的函数。您可以使用它们，但微软可能会随时消除它们。他们都没有提供任何文档，但您可以在网上找到一些。这些隐藏功能中谈论最多的可能是提供对 VBA 中指针的访问。对于体面的写作，请查看；不是那么轻量级 - Shlwapi.dll
Documented, but perhaps more obscure (in excel anyways) is using ExecuteExcel4Macro to access a hidden global namespace that belongs to the entire Excel application instance as opposed to a specific workbook.
已记录，但可能更模糊（无论如何在 excel 中）使用 ExecuteExcel4Macro 访问属于整个 Excel 应用程序实例而不是特定工作簿的隐藏全局命名空间。
回答by eksortso
Dictionaries. VBA is practically worthless without them!
字典。没有它们，VBA 几乎一文不值！
Reference the Microsoft Scripting Runtime, use Scripting.Dictionaryfor any sufficiently complicated task, and live happily ever after.
参考 Microsoft Scripting Runtime，Scripting.Dictionary用于任何足够复杂的任务，从此过上幸福的生活。
The Scripting Runtime also gives you the FileSystemObject, which also comes highly recommended.
Scripting Runtime 还为您提供了 FileSystemObject，这也是强烈推荐的。
Start here, then dig around a bit...
从这里开始，然后挖掘一下......
http://msdn.microsoft.com/en-us/library/aa164509%28office.10%29.aspx
http://msdn.microsoft.com/en-us/library/aa164509%28office.10%29.aspx
回答by guillermooo
You can implement interfaces with the Implementskeyword.
您可以使用Implements关键字实现接口。
回答by CtrlDot
Typing VBA.will bring up an intellisense listing of all the built-in functions and constants.
键入VBA.将显示所有内置函数和常量的智能感知列表。
回答by Alex K.
- Save 4 whole keystrokes by typing
debug.? xxxinstead ofdebug.print xxx. - Crash it by adding:
enum foo: me=0: end enumto the top of a module containing any other code.
- 通过键入
debug.? xxx而不是debug.print xxx. - 通过
enum foo: me=0: end enum在包含任何其他代码的模块的顶部添加：来使其崩溃。
