[ACCEPTED]-Hidden features of VBA-hidden-features
This trick only works in Access VBA, Excel 25 and others won't allow it. But you can make 24 a Standard Module hidden from the object 23 browser by prefixing the Module name with 22 an underscore. The module will then only 21 be visible if you change the object browser 20 to show hidden objects.
This trick works 19 with Enums in all vb6 based version of VBA. You 18 can create a hidden member of an Enum by 17 encasing it's name in brackets, then prefixing 16 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
In Excel-VBA 15 you can reference cells by enclosing them 14 in brackets, the brackets also function 13 as an evaluate command allowing you to evaluate formula 12 syntax:
Public Sub Example() [A1] = "Foo" MsgBox [VLOOKUP(A1,A1,1,0)] End Sub
Also you can pass around raw data 11 without using MemCopy (RtlMoveMemory) by 10 combining LSet with User Defined Types of 9 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
Octal & Hex Literals are 8 actually unsigned types, these will both 7 output -32768:
Public Sub Example() Debug.Print &H8000 Debug.Print &O100000 End Sub
As mentioned, passing a variable 6 inside parenthesis causes it to be passed 5 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 4 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 3 also an operator. Using it you overwrite 2 specific portions of strings without VBA's 1 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
There is an important but almost always 20 missed feature of the Mid() statement. That 19 is where Mid() appears on the left hand 18 side of an assignment as opposed to the 17 Mid() function that appears in the right 16 hand side or in an expression.
The rule is 15 that if the if the target string is not 14 a string literal, and this is the only reference 13 to the target string, and the length of 12 segment being inserted matches the length 11 of the segment being replaced, then the 10 string will be treated as mutable for the 9 operation.
What does that mean? It means 8 that if your building up a large report 7 or a huge list of strings into a single 6 string value, then exploiting this will 5 make your string processing much faster.
Here 4 is a simple class that benefits from this. It 3 gives your VBA the same StringBuilder capability 2 that .Net has.
' 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 1 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
VBA itself seems to be a hidden feature. Folks 19 I know who've used Office products for years 18 have no idea it's even a part of the suite.
I've 17 posted this on multiple questions here, but 16 the Object Browser is my secret weapon. If 15 I need to ninja code something real quick, but 14 am not familiar with the dll's, Object Browser 13 saves my life. It makes it much easier 12 to learn the class structures than MSDN.
The 11 Locals Window is great for debugging as 10 well. Put a pause in your code and it will 9 show you all the variables, their names, and 8 their current values and types within the 7 current namespace.
And who could forget our 6 good friend Immediate Window? Not only 5 is it great for Debug.Print standard output, but 4 you can enter in commands into it as well. Need 3 to know what VariableX is?
Need to know what 2 color that cell is?
In fact all those windows 1 are great tools to be productive with VBA.
It's not a feature, but a thing I have seen 3 wrong so many times in VBA (and VB6): Parenthesis 2 added on method calls where it will change 1 semantics:
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
- Although it is "Basic", you can use OOP - classes and objects
- You can make API calls
Possibly the least documented features in 16 VBA are those you can only expose by selecting 15 "Show Hidden Members" on the VBA 14 Object Browser. Hidden members are those 13 functions that are in VBA, but are unsupported. You 12 can use them, but microsoft might eliminate 11 them at any time. None of them has any 10 documentation provided, but you can find 9 some on the web. Possibly the most talked 8 about of these hidden features provides 7 access to pointers in VBA. For a decent 6 writeup, check out; Not So Lightweight - Shlwapi.dll
Documented, but perhaps 5 more obscure (in excel anyways) is using 4 ExecuteExcel4Macro to access a hidden global 3 namespace that belongs to the entire Excel 2 application instance as opposed to a specific 1 workbook.
You can implement interfaces with the
Dictionaries. VBA is practically worthless 6 without them!
Reference the Microsoft Scripting 5 Runtime, use
Scripting.Dictionary for any sufficiently complicated 4 task, and live happily ever after.
The Scripting 3 Runtime also gives you the FileSystemObject, which 2 also comes highly recommended.
Start here, then 1 dig around a bit...
VBA. will bring up an intellisense listing 1 of all the built-in functions and constants.
With a little work, you can iterate over 2 custom collections like this:
' Write some text in Word first.' Sub test() Dim c As New clsMyCollection c.AddItems ActiveDocument.Characters(1), _ ActiveDocument.Characters(2), _ ActiveDocument.Characters(3), _ ActiveDocument.Characters(4) Dim el As Range For Each el In c Debug.Print el.Text Next Set c = Nothing End Sub
Your custom 1 collection code (in a class called
Option Explicit Dim m_myCollection As Collection Public Property Get NewEnum() As IUnknown ' This property allows you to enumerate ' this collection with the For...Each syntax ' Put the following line in the exported module ' file (.cls)!' 'Attribute NewEnum.VB_UserMemId = -4 Set NewEnum = m_myCollection.[_NewEnum] End Property Public Sub AddItems(ParamArray items() As Variant) Dim i As Variant On Error Resume Next For Each i In items m_myCollection.Add i Next On Error GoTo 0 End Sub Private Sub Class_Initialize() Set m_myCollection = New Collection End Sub
- Save 4 whole keystrokes by typing
debug.? xxxinstead of
- Crash it by adding:
enum foo: me=0: end enumto the top of a module containing any other code.
Support for localized versions, which (at 8 least in the previous century) supported 7 expressions using localized values. Like 6 Pravda for True and Fałszywy (not too sure, but 5 at least it did have the funny L) for False 4 in Polish... Actually the English version 3 would be able to read macros in any language, and 2 convert on the fly. Other localized versions 1 would not handle that though.
The VBE (Visual Basic Extensibility) object 21 model is a lesser known and/or under-utilized 20 feature. It lets you write VBA code to manipulate 19 VBA code, modules and projects. I once wrote 18 an Excel project that would assemble other 17 Excel projects from a group of module files.
The 16 object model also works from VBScript and 15 HTAs. I wrote an HTA at one time to help 14 me keep track of a large number of Word, Excel 13 and Access projects. Many of the projects 12 would use common code modules, and it was 11 easy for modules to "grow" in one system 10 and then need to be migrated to other systems. My 9 HTA would allow me to export all modules 8 in a project, compare them to versions in 7 a common folder and merge updated routines 6 (using BeyondCompare), then reimport the 5 updated modules.
The VBE object model works 4 slightly differently between Word, Excel 3 and Access, and unfortunately doesn't work 2 with Outlook at all, but still provides 1 a great capability for managing code.
This is because
IsDate could 3 be more precisely named
IsDateTime. And because the 2 period (
.) is treated as a time separator 1 and not a date separator. See here for a full explanation.
VBA supports bitwise operators for comparing 15 the binary digits (bits) of two values. For 14 example, the expression 4 And 7 evaluates 13 the bit values of 4 (0100) and 7 (0111) and 12 returns 4 (the bit that is on in both numbers.) Similarly 11 the expression 4 Or 8 evaluates the bit 10 values in 4 (0100) and 8 (1000) and returns 9 12 (1100), i.e. the bits where either one 8 is true.
Unfortunately, the bitwise operators 7 have the same names at the logical comparison 6 operators: And, Eqv, Imp, Not, Or, and Xor. This 5 can lead to ambiguities, and even contradictory 4 results.
As an example, open the Immediate 3 Window (Ctrl+G) and enter: ? (2 And 2 4) This returns zero, since there are no 1 bits in common between 2 (0010) and 4 (0100).
This feature exists presumably for backwards-compatibility. Or 2 to write hopelessly obfuscated spaghetti 1 code. Your pick.
More Related questions