May 05, 2020

The VBA Language for Script Kiddies

Written by TrustedSec

Introduction

Thanks to your super spiffy explainer on macros, I know why I should go old school and start coding in VBA, but I can’t even read it, let alone hack it. Do you have Google translate for VBA or possibly a Babel fish?

Great, I’ve convinced you that you need to start looking at macros again and learn a little VBA, and if you haven’t, re-read my previous post: Intro to Macros and VBA for Script Kiddies. Your super-slick coding skillz and rote memory of assembly instructions won’t save you when it comes to writing your own macros. For that, you do need to go BASIC—Visual Basic—for Applications, a.k.a., VBA (and no, the humor doesn’t get any better).

VBA was introduced in 1993, which makes it older than some of you. VBA is a scripting language and a subset of Visual Basic 6.0, which means it’s almost as good, but half the calories. The code actually gets compiled to an intermediate packed format, p-code, and both plaintext and compiled code are included with the document. Now, this is probably for the more advanced developers, so maybe we’ll look at this p-code again when you’re training wheels come off and you’re ready to hack with big boys and girls. In the meantime, this p-code is run on a “virtual machine” (like Java), which is hosted by the Office application in question. That means your code is good to go on all Office applications from MS-Office 97 through Office 365. VBA7 is a slightly updated version of VBA that was introduced with MS-Office 2010 and has some additional features and addresses some 64-bit issues. VBA is object-oriented, event-driven, and all the other fancy words. It can be used to create macros and even Office add-ins. VBA applications have access to a wide variety of system-level objects as well as the application and document objects. You can even reference external libraries, including DLLs, TLBs, OCXs, and the LOC…Library of Congress. Fine. VBA really is a full-featured language that gives access to all the things, but it may look a little funny and have some different ways of doing things—but that’s why you have me, your trusted guide to the VBA language for Script Kiddies.

Reading VBA

Great, now I know what VBA is. But have you tried to read this stuff? I’m just trying to parse, let alone grok or know the history. Thanks, Professor, but let’s start looking at some code!

Before we start looking at the fundamentals of the VBA language, let’s take a few minutes to just read the code. When I was first starting out, I was doing a lot of programming by StackOverflow copy-and-paste, but I found just reading the code a little confusing at first. To make sense of the code you need to understand how to read it, which for programming languages means understanding comments, indenting, labels, and why all the underscores.

First, indenting. Some languages are whitespace-sensitive, like Python. Some couldn't care less, and you can cram everything on one line if you wanted. VBA tends to the latter, but you’ll notice most code follows standard code block indenting. Again, this isn’t entirely necessary and you can fight over tabs or spaces later, but it does help to read the code.

Public Sub Hello World()
    MsgBox "Hello World" 
End Sub

Speaking of cramming everything on one line, let’s address all the underscores. Underscores allow you to continue a line of code onto the next line. There are statement terminators, like semicolons in C, so the end-of-line USUALLY ends the statement. Therefore, if you want to continue the statement onto the following line, you need to use a space followed by an underscore at the end of the line. Again, this is mostly for readability.

Public Sub Continue Prompt ()
    Dim Response 
    Response = MsgBox("Do you want to take the red pill?",_
                vbYesNo, _
                "Example MsgBox Title")
    If Response = vbYes Then
        MsgBox "Down the rabbit hole we go..."
    End If 
End Sub

Comments are used to document, document, document, just like any other programming language. They definitely help when someone is trying to figure out your code or when you forget why you did something in the first place, but remember that your plaintext source code gets included in the document. Comments are denoted with either a single quote (‘) or the keyword (REM) at the start of any line.

' This is a comment
REM This is also a comment
// This will give you an error
# So will this

Labels. Lots of languages include labels and gotos, and VBA is no exception. Some people think that labels and gotos just lead to confusing code, but those people aren’t using labels and gotos correctly. In VBA, you’ll see labels used a lot for error handling. Labels can also help create a single point for termination for functions.

If Response = vbNo Then
    GoTo Matrix
Else
    GoTo RabbitHole
End If 
Matrix:
    MsgBox "Home sweet home" 
    GoTo Termination
RabbitHole: 
    MsgBox "Here we go"
    GoTo Termination
Termination:
End

Variables

OK, now I can at least sort of read VBA, but let's start assigning things and dealing with these foos and bars.

Now that we can at least read through the VBA lines a little, so we can start looking at the variables. Every programming language has variables—they are the names we give to the memory locations that hold values that we can play with in the program. In VBA, variable names:

Must use a letter as the first character
Cannot include some special characters: period, comma, space, bang (!), @, &, $, #
Cannot exceed 255 characters
Cannot match any of the VBA keywords
Are not case-sensitive

Data Types

Variables are typically typed, but they can use the default type of “Variant”. The data types are like those that you would find in other languages.

Variant
- The default type
- It’s a special container that can hold any type
- This is often used in For Each loops
- The default value is Empty
- String
  - Two types:
    - Variable has a length up to 2 billion characters
      - The default value is ""
    - Fixed has a length up to 64k characters
      - The default value is a string filled with spaces
  - Strings are stored internally as Unicode, but auto convert when used with ANSI functions
- Integer
  - There are several integer types based on the size required
    - Byte
    - Integer (2 bytes) signed
    - Long (4 bytes) signed
    - LongLong (8 bytes) signed (only available on 64-bit platforms)
    - LongPtr signed (4 bytes on 32-bit platforms and 8 bytes on 64-bit)
      - These are often used as handles or other pointers
  - The default value is 0 for all integers
- Boolean
  - The default False
  - Booleans are internally stored as 2-byte integer
  - When converted to a number, True = -1
- Floating point
  - There are two (2) types of floating points based on the precision required
    - Single
    - Double
- Scaled
  - There are several scaled data types for specific purposes
    - Currency
      - 8 bytes
    - Decimal
      - 12 bytes
      - Can have up to 28 decimal places
  - Scaled data types are a sub-type of Variant
- Date
  - Dates contain both date and time
  - They are represented as 8-byte floating-point values
    - The integer portion stores number of days since December 30, 1899
    - The decimal is number of seconds since midnight
  - Date literals are assigned using the pound sign (#), e.g., #m/d/y h:m:s#
- Enumerations
  - Enumerations can be system or user-defined
  - Enumerations are stored internally is a Long
  - Enumerations can be sequential, assigned, composite/bitwise
- Object types
  - The date type can also be that of an object that is comprised of other data types
  - There are four (4) types of objects
    - Object
      - These are references to an object
      - They are used for late/runtime binding
    - An instantiation of a class
    - A user-defined object as defined by a Class
    - A UserForm
- User-defined
  - User-defined data types are groupings of other types
  - This is most often referred to as a structure in other languages

Declaration

Variables are declared with a name and data type, as follows:

Dim <variable_name> As <data_type>

Variables can be declared at a procedural level, module level, or global level. This is known as the scope of the variable, which is like other languages. Variables can also be Public or Private. Procedural variables are always private to that procedure. Module level variables can be private to that module or public to the world. Global level variables are always public and must be declared as such.

Dim myImplicitlyPublicVariable
Public myExplicitlyPublicVariable
Private myPrivateVariable

There are a couple tricky bits when it comes to declaring and typing variables. First, instead of using “As <data_type>”, you can use a type character as a suffix to the variable name:

% means it is an Integer, e.g., Dim iFoo%
& means it is a Long, e.g., Dim lFoo&
! means it’s a single-precision Float, e.g., Dim spFoo!
# means it’s a double-precision Float
$ means it’s a String
@ means it’s a Currency

These special characters are not actually part of the name, so they will not be used when referencing the variable after the declaration. The second gotcha is that a programmer can use “Def<Type> <Prefix>” to define a data type for all variables that start with that prefix.

DefLong dw
Dim dwFoo
' Foo is now a Long data type

When declaring a variable, you can initialize it on the same line. Usually statements are on separate lines, but initialization is one of the special cases. To initialize a variable, you use a colon (:) after the declaration, followed by the assignment.

Dim iFoo As Integer : iFoo = 42

Literals

Literals values don’t have variable names and are typically used in variable assignment, but they can be passed directly to functions and sub-procedures. Literal numbers don’t have any identifiers and are just the number itself. String literals are in double quotes, e.g., “Hello World”. Date literals are in hash tags, e.g., #3/31/2020 12:34:56#.

Constants

Variables can be declared as constant using the Const keyword. Constants must have an initialized value and can only be assigned using a literal. This variable cannot be changed during the scope of the variable. You can use the shorthand initialization when declaring a constant.

Const MYBIRTHDAY As Date = #12/1/1983#

Enumerations

Enumerations are a special data type with a specific range of values. They are declared using the Enum keyword. They can include an initial starting value and increment each value from there or assign specific values.

Public Enum DayOfWeek
    weekDayMonday = 1
    weekDayTuesday = 2
    ...
End Enum

Arrays or Collections

Variables can contain an array or collection of variables, just like in other languages. Arrays are created by adding parentheses to a variable name. Arrays are dynamically sized unless the size is specified as part of the declaration. If it is not specified as part of the declaration, then an array must be sized before use by using the ReDim keyword. You can resize an array using the ReDim keyword at any point, and if you include the Preserve keyword, then all the values will be preserved as well; otherwise, the ReDim instruction will reinitialize the array. When using ReDim or during initialization, you specify the upper-bound of the array not the size. The lower-bound is assumed to be zero unless otherwise specified. Arrays can be multi-dimensional, as in other programming languages. Finally, arrays do come with built-in functions and methods, which I will describe later.

Dim StatusCodes(1) As Integer
StatusCodes (0) = 200
StatusCodes (1) = 403
ReDim Preserve StatusCodes (3)
StatusCodes (2) = 404
StatusCodes (3) = 500

Dim Protocols() As String
ReDim Protocols(1 to 2)
' Protocols(0) = "icmp" : Error because lower-bound is 1
Protocols(1) = "tcp" 
Protocols(2) = "udp"

Operators

OMG, that took forever. I get it. VBA has variables just like other languages. At least now all those stupid Dims make sense. But let’s start doing stuff.

Now that we have variables, we can start manipulating them. But before we start building Skynet, let’s look at some simple operations. VBA has most of the basic arithmetic, comparison, logical and concatenation operators that you find in other programming languages. The arithmetic operators are +, -, *, /, % (modulus), ^ (exponentiation). The comparison operators are =, <> (not equal), >, <, >=, <=. The logical operators can be used in determining if a Boolean expression is true or false. The supported logical operators are AND, OR, NOT, XOR. Finally, you can concatenate to values using &. Strings can be concatenated using the + operator as well. So, for example:

Dim iFoo As Interger : iFoo = 0
Dim strBar As String : strBar = "FileNot"
iFoo = iFoo + 4
iFoo = iFoo * 10
' iFoo is 40 now after arithmetic
iFoo = iFoo & 4
' iFoo is 404 now after concatenation
strBar = strBar & "Found" 
'strBar is "FileNotFound" now after concatenation 
If ( iFoo > 200 AND strBar <> "OK" ) Then 
EndIf

Control Flow

Sure, simple operations are simple. Can we please start jumping around?

Now that we have basic operations, we can start looking at making some decisions and actually establishing some programming structure. Control flow is all about the programmer controlling the flow of the program. This is typically done through branching, decision instructions, loops, procedure calls, etc.

Branching

Branching uses branching instructions to jump to a location in the code. These are the basic GoTo and Label statements that most people shun, but are quite useful. They allow for error handling and allow for a single exit point from a procedure or subroutine.

GoTo <label>

Decisions

Decisions use special keyword instructions and Boolean expressions to determine which block of code to execute. VBA includes the common instructions, e.g., If, Select, as well as a couple that may be unfamiliar, such as Iif, Choose.

The If / ElseIf / Else statement:

If <Boolean Expression> Then
    <Code_If_True>
ElseIf <Boolean Expression> Then
    <Code_If_This_Condition>
Else
    <Code_For_Catch_All>
EndIf

The Select instructions jump to a block of code, or Case, based on an expression:

Select Case <expression>
    Case <expression1>
        <code_if_expression=expression1>
    Case <expression2>
        <code_if_expression=expression2>
    Case Else
        <code_for_catch_all>
End Select

The Iif instruction is like the tertiary operator in C. It is an inline if instruction, which evaluates an expression and returns a value whether the condition is true or false.

Ret = Iif(<Boolean_Expression>, <value_if_true>, <value_if_false>)

The Choose instruction makes a simple decision based on a specific index value. It’s like a really simple Select statement combined with an inline if.

Choice = Choose(<index>,<value_index1>,<value_index2>)

Finally, the Switch statement is like a nested if statement. It evaluates a list of expressions and returns the value corresponding to the first expression that evaluates to True.

Ret = Switch(<Boolean_expression1>, <value1>, _
             <Boolean_expression2>, <value2>)

Loops

Loops are a common control flow operation that let you execute the same block of code over several iterations. Usually you’re iterating over a list of objects or values and performing a set of operations for each item or value. Again, VBA has several of the looping instructions that you’re used to, e.g., For, Do, While, but there are some tricky syntax issues.

There are a couple of constants with all looping instructions. First, you can terminate a loop using the Stop or End commands. Stop stops execution and enters the break mode. End stops execution, closes open files, and clears all variables. However, you most likely just want to use Exit <loop_command> to exit the loop instead of terminating or breaking the whole program.

Also, for large loops, you should consider yielding execution so the operating system can process events. This is as simple as including a DoEvents instruction every so often in your loop, e.g., every other loop iteration.

The For instruction iterates over a set of values. You can specify the lower-bound, upper-bound, and step of the iteration. The For loop code block ends with a Next instruction, which is an indication to increment the step value and restart the loop. The For loop terminates once the upper-bound is reached or if an Exit For instruction is triggered.

For i = 0 To 9 Step 2
    If i = 5 Then
        Exit For
    End
Next

The For Each instruction allows you to iterate over a collection of objects. The For Each code block again ends with a Next instruction, indicating when to move on to the next item in the collection.

For Each Item In ArrayItems
    MsgBox( Item.Name )
Next

The While Wend loop executes a code block as long as a condition is True. The code block ends with a Wend instruction indicating to run through the code block again. In this loop, no counters are incremented, so if your conditional statement refers to a counter, you’ll need to make sure to increment it yourself.

Dim i As Interger : i =0
While i < 10
    i = i + 1
Wend

The Do While loop is similar to other languages. It loops While a specific condition is True. The code block terminates with a Loop instruction. If the While condition is at the start of the loop, then the condition is checked before initial execution. If the While condition is at the end of the loop, then the code block is run at least once before the condition is checked. Also, there is an alternative version of the Do While loop, which is the Do Until loop. Instead of checking for a specific condition to be True, it loops while the condition is True and breaks when it is False.

Dim i As Integer : i =0
Do While i < 10
    i = i + 1
    If i > 10
        Exit Do
    End If
Loop

Dim i As Integer : i =0
Do
    i = i + 1
Loop While i < 10

Dim i As integer : i =0
Do Until i > 10
    i = i + 1
Loop

Procedures

Now that we’re branching, deciding, and looping, let’s look at procedure calls. VBA has two types of procedures: sub-routines and functions. These procedures contain a group of reusable code that can be called and returned from. The difference between the two is that functions return a value or object while sub-routines do not. Anyway, any good programmer is going to make abundant use of user-defined functions as well as all the functions made available by the operating system’s libraries.

Using a procedure is done by calling the procedure name. You can do this explicitly using the Call keyword followed by the procedure name, then the parameters. You need to enclose the parameters in parentheses if you used the Call keyword, if you are assigning the return value to a variable, or if you want to override a ByRef. Procedure calls in VBA can also use named parameters to explicitly assign parameter values. These named parameters can also be used with or without parentheses depending.

Call MsgBox "Test"
vbButton = MsgBox( "Test", vbYesNow )
MsgBox "test", title = "my title"

Compiler Constants

In addition to the normal control flow of a program, you can make decisions based on compiler constants and directives. These decisions are made during compile time (or when the code is compiled by the local virtual machine running in the office application).

These decisions will include or exclude blocks of code from the final program based on compiler constants. You can declare compiler variables or constants yourself or use some defined by the VBA engine. This can be useful in differentiating your code based on architecture or the version of Office. All compiler instructions begin with the hash character, like in C.

#Const <const_name> = <value>
#If <Boolean_Expression> Then
#ElseIf <Boolean_Expression2> Then
#Else
#EndIf

Subroutines and Functions

Well, that took forever. Ifs, Fors, and all that is child’s play. I may be a Script Kiddie when it comes to VBA, but I ain’t no n00b. Although, declaring an upper-bound instead of a size for an array was a bit weird, and what’s with those Do Until and Loop While stuff? Anyway, let’s do some real coding. Let’s write some functions and start programming for realz.

Now that we have the basics down, we can start looking at functions and subroutines. Procedures provide the real power to any programming language whether it is writing your own reusable code or using code made available through the operating system and shared libraries. VBA has some key built-in functions, which we’ll hold off on discussing and instead look at creating your own user-defined functions.

First, what’s the difference between a function and a subroutine? In VBA, subroutines (or sub procedure) do NOT return a value. That’s pretty much it. Subs are declared using an optional exposure, optional Static declaration, followed by the keyword Sub, the sub procedure’s name, and an argument list enclosed in parentheses. The sub procedure concludes with an End Sub keyword. A sub procedure can be immediately exited using the keyword, Exit Sub, but best coding practices aim for one (1) point of termination.

Public Sub ExampleSub( X As Integer)
    ...
End Sub

Functions follow most of the same declaration rules as sub procedures with the exception that it must specify a return type after the argument list. The return value is set by assigning a value to the function name. A function can return multiple values if the assigned values are separated by a comma. If no value is assigned to the name, then the default value for the data type declared as the return type in the function declaration is used, e.g., 0 for Integer or “” for String.

Public Function ExampleFunction( X As Integer ) As Integer
    ...
    ExampleFunction = X + 1
End Function

Note: There is a Return keyword, but it has a special use not covered in this blog and is not to be used to return from Sub or Function.

Parameters

Parameters are declared in the function declaration. They are passed by reference by default, but they can be definitively declared as such using the ByRef keyword. Passing a variable by reference means that the value of the variable outside the procedure will be affected by the code within the procedure. This is the opposite of most programming languages, which use pass by value by default. Passing by value, which allows the procedure to access a copy of the variable without modifying the original, can be done by using the ByVal keyword in the parameter declaration.

Public Sub ExampleSub( ByVal X As Integer )
Public Sub ExampleSub( ByRef X As Integer )

VBA does allow for optional parameters by using the Optional keyword before the parameter name. All remaining parameters in the list must also be declared optional. Optional parameters can have a default value by assigning the value after the type in the parameter declaration.

Public Sub ExampleSub( X As Integer, Optional Y As Integer = 42 )

VBA allows a variable parameter array to be used to pass a variable number of arguments. This is similar to using va_args in C. The parameters are passed in as an array and can only be the last parameter in the function declaration. These are always passed ByVal and always use the Variant type (which, as you recall, can hold any data type).

Public Function ExampleFunc( X As Integer, ParamArray Y()) As Integer
    ExampleFunc = X
    For Each entry in Y
        ExampleFunc = ExampleFunc + entry
    End For
End Function
Call ExampleFunc(1, 2, 3, 4)

Invoking

Both procedure types, functions and subroutines, are invoked similarly. You can invoke a procedure by explicitly using the Call keyword followed by the procedure name, or simply using the procedure name. The Call keyword is not required, but if you use the Call keyword and the procedure requires arguments, then the arguments must be enclosed in parentheses. If you do not use the Call keyword, then you must not use the parentheses, and the comma-separated arguments can simply follow the procedure name (separated from the name by a space).

Call ExampleSub(0)
ExampleSub 0

Since Functions return a value while sub-procedures do not, function invocations can be used as part of an expression. For example, you can assign a variable to the result of a function call or use a function call as part of a Boolean expression during a decision-making operation.

When calling a procedure, arguments are passed into the procedure (using parentheses or not) according to the order in which they are declared. However, a caller can pass in the arguments using named arguments. In this case, the named argument is followed by a colon and equal sign (:=) and then the value the caller wants to assign to that argument. When using named arguments, the order of the arguments does not matter.

Call ExampleSub(X:=42)

Conclusion

That was a lot of info, but thanks for the download. I feel like I can at least look at some code and figure out what’s going on. It definitely started as Greek to me, but now it’s at least BASIC English. So, what’s next the level? Hopefully this neverending story was worth it. It may have been a bit dry in parts, but with any luck, you can now read the cryptic VBA language. You at least know why all those lines end in underscores and why there isn’t a semicolon to be found. You know which data types are available and why you always want 'Dim' sum after reading VBA code. We covered the basic operators and control flow mechanisms, including the tricky Do While…Loop and Do…Loop While. Finally, we looked at how to declare and call functions and sub-procedures. I dare say, young padawan, that you are ready to start developing your own VBA applications. So, stay tuned for Developing with VBA for Script Kiddies, and as always check your return values.

Solutions

Services

About Us