Developing with VBA for Script Kiddies

Introduction

Now that I can read these macros and code snippets on stackexchange, how do I really make use of VBA? There must be more than meets the eye. How can I transform this BASIC code into something to pwn the world? I want to develop something!

After learning the reason we should be looking at VBA again in part 1: “Intro To Macros and VBA For Script Kiddies“, we then looked at how to actually read this cryptic language in part 2: “The VBA Language for Script Kiddies“. Once we have the motivation and the know-how, we can look at expanding our mind and writing some code using the power of VBA.

We touched on it a little before, but Visual Basic for Applications was created to provide the user with a way to customize their Microsoft Office application experience. This includes everything from automating routine procedures to enhancing the underlying capabilities of the application itself. VBA can add entirely new functionality by interacting with the document, Office application, or the operating system itself.

The VBA programming language shares many of the same features as other programming languages, as we saw earlier. Now we will look at some of the key functions that you are used to having and how to debug your code. And since VBA is an event-driven, object-oriented scripting language, we will discuss how events are handled and some of the objects of interest. Finally, we will talk about extending your VBA code and utilizing some of the features of the Office application and operating system. After this, Script Kiddies, you should have the basic building blocks to develop your Dark Tower of VBA.

Key Functions

OK, I know how to make my OWN functions, but there has to be some built-in functions that I can use. Where are all my strcpy, atoi, and fread functions at? You can’t expect Batman to fight crime without his utility belt.

Like all programming languages, VBA offers a variety of built-in functions to help you accomplish your tasks. A comprehensive list of these functions is far too long for this post, but we will look at a handful of common functions. A complete list can be found in the Microsoft documentation. I know, I know, but the <a href=”https://docs.microsoft.com/en-us/office/vba/api/overview/language-reference”>VBA documentation</a> can be extremely helpful, so ignore your Script Kiddie instincts and read the instructions. There’s probably an app or at least an API for that.

Collection Manipulation

First, let’s look at some functions that will help you when it comes to dealing with arrays or collections. These functions will help you create, modify, and search through collections. These have many names in different languages, but their utility will be familiar.

  • Array(arglist) – This constructor allows you to create and initialize an array. We know how to create an array by simply putting parentheses after the name but before the type, but you can go a step further and initialize an array using this constructor.
Dim A As Varaint
A = Array(1, 2, 3)
  • Join(sourcyarray, [delimiter]) – This function merges the items in a collection to return a string of these joined items, like the join operation from Python. It takes an array of items to join and an optional delimiter to place between the joined items.
myCSV = Join(A, “,”)
  • Split(expression, [ delimiter, [ limit, [ compare ]]]) – This function splits a string expression into an array of substrings based on a delimiter, limit, and comparison. The delimiter is used to identify the character separating the substrings and defaults to the space character.
B = Split(myCSV, “,”)
  • LBound(arrayname, [ dimension ]) – This function returns the smallest available index for the given dimension of an array. Remember, VBA doesn’t always start with an index of 0.
Dim MyArray(10 To 20)
Lower = LBound(MyArray)
‘ Lower = 10
  • UBound(arrayname, [ dimension ]) – This function returns the greatest available index for the given dimension of an array.
Dim MyArray(10 To 20)
Upper = UBound(MyArray)
‘ Upperr = 20
  • Erase arraylist – This sub-procedure reinitializes the elements of fixed-size arrays and releases the memory associated with dynamic-size arrays. It is like delete in C++.
  • Filter(sourcearraymatch, [ include, [ compare ]]) – This function returns an array containing a subset of a string array based on the match, include, and compare parameters. This is similar to many other languages’ sub-string functions. The match is the sub-string you are searching for. Included is a Boolean value of whether those matches should be included or excluded, and compare is the type of comparison to perform.

Conversion

Next, let’s look at some functions that will help you convert from one data type to another. These are similar to your atoi function or hexlify functions. And, as an added bonus, most start with C for conversion. Also, remember, function calls do not require the use of parentheses except when you are utilizing the return value, which will be the case in most conversions.

  • CBool(expression), CByte(expression), CCur(expression), CDate(expression), CSng(expression), CDbl(expression), Cint(expression), CLng(expression), CLngLng(expression), CLngPtr(expression), CStr(expression), CVar(expression) – These functions explicitly force an expression into a specific, built-in data type. The expression can be numerical or any string, and the system will do its best to convert it to the function’s data type. If the value passed into the function is outside the range for a specific data type, or the system does not know how to convert the argument into the function’s data type, then an error occurs.
Dim myExpression As String := “3.14”
Dim myDouble As Double := CDbl(myExpression)
  • Int(number), Fix(number) – These functions return the integer portion of a number. The argument to these functions is a double-precision float or any valid numeric expression. Negative numbers are the difference between the two functions. Int() returns the first negative integer less than or equal to the argument, while Fix() returns the first negative integer greater than or equal to the argument.
Dim MyNumber As Integer
MyNumber = Int(3.14) ‘ Returns 3
MyNumber = Fix(3.14) ‘ Returns 3
MyNumber = Int(-3.14) ‘ Returns -4
MyNumber = Fix(-3.14) ‘ Returns -3
  • Hex(number), Hex$(number), Oct(number), Oct$( number) – These functions return a hexadecimal or octal representation of a number. The function names that end with $ explicitly return a String data type, while the standard name returns a Variant. If the number is not a whole number, it is rounded to the nearest whole number.
Dim myHex As Variant := Hex(9.9) ‘ Returns A
Dim myHexString As String := Hex$(9.9) ‘ Returns “A”
  • Str(number), Str$(number) – These functions return the decimal representation of a number. Again, the version ending in $ explicitly returns a String data type, while the standard returns a Variant. If the number is negative, the string will start with a “-“, but if the number is positive, it will start with a space “ “.
  • Val(string) – This function returns the numbers contained within a string as a numeric value. It joins the numbers and stops reading the string when it encounters the first character it cannot convert (it skips whitespace and understands &H for hex).
Dim myValue As Integer := Val(“ 1 23 4”) ‘ Returns 1234
Dim myValue As Integer := Val(“12:34pm”) ‘ Returns 12

Date and Time

The next set of common functions we will examine will relate to dates and time. As you recall, Date is its own specific data type and uses an 8-byte, floating-point number to represent the date and time. The date is represented by the number to the left of the decimal while the time is represented by the value to the right of the decimal. You probably won’t see a negative number because these are dates before December 30, 1899. One last thing to remember is that date literals are enclosed within number signs (#), e.g., #14 Mar 2015#.

  • Now() – This function returns a variant representing the current date and time according to the system. Since this function does not require any arguments, you may see it invoked without parentheses.
Dim myDate As Date := Now
  • Date(), Date$(), Time, Time$() – These functions return a variant containing the current date or time, as specified. Again, the versions ending with a dollar sign will return a String representation, while the standard version returns a Variant type. And again, these functions do not require any arguments—you may see them invoked without parentheses.
  • Year(date), Month(date), Weekday(date), Day(date), Hour(date), Minute(date), Second(date) – These functions return an integer Variant containing the number representation of the specific part of the date time.
Dim myDate, myYear, myHour
myDate = #3/14/2015 9:26:53 AM#
myYear = Year(myDate) ‘Returns 2015
myHour = Hour(myDate) ‘Returns 9
  • Timer() – The timer function returns a Single precision float representing the number of seconds that have elapsed since midnight. In Windows, this Single precision float also includes the fractional seconds since midnight, while on Mac it is rounded to the nearest whole second.

FileSystem

There are a number of built-in functions that let you access the filesystem. These functions help when navigating the directories and accessing the files. Most of these functions should be familiar, as they are similar to other programming languages and command-line interfaces.

  • ChDir path, ChDrive drive – These functions are used to navigate the filesystem and change the current directory or drive to the specified value. The arguments are string representations of the desired directory and drive.
  • CurDir – This function returns a Variant String representing the current path.
  • Dir [ (pattern, [ attributes ] ) ] – This returns a String that represents the name of a file or directory matching the specified pattern (and optional attributes). The Dir function supports wild cards as part of the pathname. If there are multiple matches to the search pattern, then Dir returns the first filename that matches the pattern and additional matches are returned with each subsequent call to Dir without any arguments. When there are no more matches, Dir will return an empty string (“”).
Dim myListing As String
myListing = Dir “” ‘ Returns the first listing in the directory
Do
      MsgBox myListing
Loop While myListing <> “”
  • MkDir path, RmDir path – These functions will create or delete a directory. An error will occur if you try to remove a directory containing files.
  • EOF (filenumber) – This returns an Integer containing the Boolean value True when the end of a file has been reached.
  • LOF(filenumber)This returns a Long representing the size in bytes of an opened file.
  • FileDateTime(pathname) – This function returns the date and time when a file was created or last modified.
  • FileLen(pathname) – This returns a Long specifying the length of a file in bytes.
  • Open pathname For mode As #filenumber – This function will open a file for input and output operations. If the file does not exist, then it is created based on the mode. The required file number is used for subsequent file operations, as with other programming language file functions.
  • Close [ filenumberlist ] – This function closes the specified files that were opened using the Open function. The function can take a list of file numbers to close.
  • Write #filenumber, output – This is used to write data to a file as specified by the file number.
  • Input #filenumber, varlist – This is used to read data from an opened file and assigns the data to variables.
Dim MyString, MyNumber
Open "TESTFILE" For Input As #1     ‘ Open file for input
Do While Not EOF(1)                 ‘ Loop until end of file
    Input #1, MyString, MyNumber    ‘ Read data into two variables
    Debug.Print MyString, MyNumber  ‘ Print to the Immediate window
Loop
Close #1                            ‘ Close file.
  • FreeFile – This function returns an Integer representing the next available file number for use by Open statements.
Dim myFileNumber
myFileNumber = FreeFile
Open “Test.txt” For Output As #myFileNumber
Write #myFileNumber, “Test output”
Close #myFileNumber
  • FileCopy sourcedestination – This function copies a file from source to destination. It will error if you try to copy a file that is currently open.
  • Kill pathname – This function deletes a file from disk. The Kill function supports wildcards.

String

As we learned last time, VBA supports both variable and fixed-length strings. The strings are stored internally as Unicode but will convert behind the scenes when used with ANSI functions. In general, functions that end with a B indicate that a function is performing operations on a Binary or ANSI string, while W functions are manipulating a Unicode string. And as always, functions that end with a dollar sign ($) return an explicitly typed String data type instead of the default Variant data type. Here are some of the common string functions that you are most likely going to use:

  • Asc(string) – This function returns an Integer representing the ASCII character code for the first letter in the string argument.
  • Chr(charcode) – This function returns a String containing the character specified by the character code argument.
  • Filter, Join, Split – These functions were touched on earlier under collection manipulation, but they allow you to search for, join, and split strings into substrings.
  • Format(Expression, [ Format ]) – Format returns a Variant, or String with $, containing an expression formatted according to the format expression.
Dim MyStr
MyStr = Format( #15:14:15#, “hh:mm am/pm” )     ‘ Returns “3:14 pm”
MyStr = Format( 3141.5926, “##,##0.00” )        ‘ Returns “3,141.59”
  • InStr([ start ], string1string2, [ compare ]) – This function searches a string for a substring and returns the position of the first occurrence of that substring.
  • Left(string, length), Right(string, length), Mid(stringstart, [ length ]) – These functions return a substring with the specified number of characters starting from either the left, right, or middle of the string.
  • Len(string|varname) – Returns a Long corresponding to the number of characters in a string. This function can be used with a variable name to return the number of bytes required to store that variable.
  • LCase(string), UCase(string) – These functions change the case of a string.
  • LTrim(string), RTrim(string), Trim(string) – These functions trim whitespace from the left, right, or both ends of a string
  • Replace(expressionfindreplace, [ start, [ count, [ compare ]]]) – This function returns a string in which a substring has been replaced with another substring, similar to find and replace.
  • StrComp(string1string2, [ compare ]) – The function compares two (2) strings and returns the result: 0 if they are equal, -1 if string1 is less than string2, and 1 if it is greater.
  • StrConv(stringconversion, [ LCID ]) – This converts a string using the specified conversion. This can covert a string to upper, lower, or proper case. It can convert a string to wide (2-byte) characters or narrow (1-byte). It can also convert from Unicode or to Unicode. Check the VBA reference for the conversion specifiers.
  • StrReverse(expression) – This reverses the character order of a string.
  • String(number, character), Space(number) – This creates a string with a repeating character or space.

Interaction

VBA is part of a user-driven, event-based environment, so in addition to the standard functions, VBA offers many functions that help you interact with the user and/or application. Some of these functions are similar to those found in other programming languages, and some are unique to VBA.

  • AppActivate title – This function will activate an application window. This changes the focus to the named application but does not affect whether it is minimized or maximized.
  • Shell(pathname, [ windowstyle ]) – This function will run an executable program and return the program’s task ID if successful (or 0 if not). The optional windowstyle argument can hide a window or force it to start minimized.
Dim RetVal
RetVal = Shell("C:\WINDOWS\CALC.EXE", 0)  ' Start a hidden Calculator
  • CallByName (objectprocnamecalltype, [args()] ) – This will execute a method of an object dynamically at runtime by using the function’s name.
  • Environ( { envstring | number } ) – This function will return a string associated with the operating system environment variable.
  • GetSetting(appnamesectionkey, [ default ]), DeleteSetting appnamesection, [ key ], SaveSetting appnamesectionkeysetting – These functions are used to get, save, and delete settings from the application’s entry in the Windows registry.
  • InputBox(prompt, [ title ], [ default ], [ xpos ], [ ypos ], [ helpfilecontext ]) – This function displays an input dialog box to the user and returns a String containing the contents of the text box.
  • MsgBox (prompt, [ buttons, ] [ title, ] [ helpfilecontext ]) – This function displays a message dialog box to the user and waits for the user to press a button. The return value is an Integer indicating which button the user pressed.
  • SendKeys string, [ wait ] – This function sends one (1) or more keystrokes to the active window as if they were typed at the keyboard.

Objects

Great! I got my utility belt of functions, but what about my batarang and batgrapple, and enough of the Batman analogies, but there have to be some handy-dandy objects for me to play with. All languages have objects, right?

Visual Basic for Applications is indeed an object-orient programming language. And for those n00b Script Kiddies, an object is defined in a class, which contains a collection of variables (known as properties) and functions (known as methods). Once you create an instance of this class, you have an object. You manipulate this object using those methods and properties through an object.method or object.property notation.

VBA contains several built-in object types that are useful and similar to those found in other languages. It also has a few that are unique to the Office environment of VBA. In fact, the Office application in which the VBA script is running contains a hierarchy of objects and is the primary way that you will access data and accomplish your tasks.

Standard Objects

The standard objects are those contained within the language itself and are not necessarily part of the Office application. These are the objects that you will be most familiar with. We have tangentially discussed some of them already or at least some functions that work with these objects.

  • Collection – The Collection object is just that—a collection of related and ordered items. The members of a collection do not need to share the same data type. As we have already seen, collections are manipulated using the Add, Remove, and Item methods, and they can be used in control loops, like For Each … Next.
  • Debug – The Debug object is obviously used in debugging code. It has two (2) methods: Print and Assert. The Assert and Print statements can be used with the Debug class itself and do not need to be referenced via a Debug object.
    • Debug.Print [ outputlist ] – This method sends information to the immediate window.
    • Debug.Assert booleanexpression – This method tests if a condition is true. If the condition is False, the code execution breaks and triggers the debugger.
Private Sub Test( errno as Integer )
      Debug.Assert (errno<>0)
      Debug.Print “error success, continuing”
End Sub

Note: The Stop keyword is not part of the Debug object, however, the stop statement will suspend execution and trigger a breakpoint in the code.

  • Dictionary – This object is similar to a Perl associative array. It can contain any type of data that are stored in an array and can be accessed using a unique key.
Dim d
Set d = CreateObject(“Scripting.Dictionary”)
d.Add “AL”, “Alabama”   ‘AL is the unique key
d.Add “AK”, “Alaska”
  • Drive – The Drive object provides access to the properties of a disk drive or network share.
  • Err – The Err object is used to provide information about run-time errors. It is used extensively in exception handling. The properties of the Err objects are set by the programmer or operating system when the Err is raised. The default property of the Err object is the Number. The Err object has two (2) primary methods: Raise and Clear.
    • Err.Raise numbersourcedescriptionhelpfilehelpcontext – This method generates a run-time error. When an Err has been raised, it triggers the exception handler, which can be set up using the On Error keyword. This On Error statement establishes where to GoTo to handle the error, e.g., GoTo ExceptionOccurred, and where to return to after handling the error, e.g., Resume Next.
    • Err.Clear – This method clears the properties of the Err object after the error has been handled.
Private Function Divide( x As Integer, y As Integer ) As Integer
      Dim z As Integer 
On Error GoTo ExceptionHandler
      If y = 0 Then
            Err.Raise 5
      Else
            z = x / y
      End If
      ExceptionHandler:
            Debug.Print “errno: “ & Str(Err.Number)
            Debug.Print “Divide by Zero”
            Err.Clear
            z = -1
      SetAnswer:
      Divide = z
End Function
  • File – The file object represents a file on disk and its properties contain information about that file. Some of the key properties include: Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive, Name, ParentFolder, Path, ShortName, ShortPath, Size, and Type. In addition to these properties, the File object has a few key methods: Copy, Delete, Move, and OpenAsTextStream.
  • FileSystemObject – The FileSystemObject provides access to the computer’s filesystem. It contains properties, including Drives, Name, Path, Size, and Type. It also has numerous methods, including CopyFile, CopyFolder, CreateFolder, CreateTextFile, DeleteFile, DeleteFolder, FileExists, FolderExists, GetAbsolutePathName, GetFileName, MoveFile, and WriteLine.
Sub CreateAfile
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set test = fs.CreateTextFile("c:\testfile.txt", True)
    test.WriteLine("This is a test.")
    test.Close
End Sub
  • Folder – The folder object provides access to all of the key properties of a folder in the file system. In addition to some properties for that folder, like Attributes, DateCreated, DateLastModified, Name, IsRootFolder, ParentFoder, Size, etc., it also has two (2) collections: Files and SubFolders. These are collections of File and Folder objects, respectively, and contain the objects within that folder. Besides the properties, the Folder object has a few methods of note, including Copy, Delete, Move, and AddFolders (which can add a new folder to the SubFolders collection).
  • TextStream – This object helps handle text files. It provides sequential access to a file for reading or writing. TextStream objects are created or opened typically with the CreateTextFile method of a FileSystemObject. The TextStream object has a couple properties of note, including AtEndOfLine, AtEndOfStream, Column, and Line. In addition to these properties, it has several I/O methods: Read, ReadAll, ReadLine, Write, WriteLine, and Close.
  • UserForm – A UserForm object represents a window or dialog box as part of the user interface. A UserForm has properties that determine the appearance of the window, including position, size, and color. Additionally, UserForms can be manipulated using the methods and can even respond to events, as we will see later.

Application-Specific Objects

Now that we’ve looked at some of the more general objects, we can examine some of the Office-specific objects. Every Office host application has an application object, e.g., Word, Excel, etc. The application object consists of the application settings and options as properties of the object and the methods to access the various objects contained within the document hierarchy. For instance, the Microsoft Word application object contains references to all the objects within the application, like Add-ins, Dialogs, Documents, Templates, and the VBE (Visual Basic Editor) itself. The Microsoft Excel application object similarly contains a collection of Add-in objects, Dialog objects, and the VBE, as well as the Workbooks and Worksheets (instead of Documents).

On a side note, VBA uses the concept of Active objects to refer to the object with focus, e.g., ActiveDocument, ActiveWindow, ActiveCell, ActiveSheet, etc. This allows all subsequent properties and methods to be utilized without repeatedly having to select the object from the collection of objects. You can explicitly reference an object as well using the keyword statement With <object> … End With. Inside the With code block, you can skip naming the object explicitly and just access the methods and properties using a period (.).

With MyLabel
 .Height = 2000
 .Width = 2000
 .Caption = "This is MyLabel"
End With

Finally, the application object supports many methods, but one to take note of may be the Run method.

  • expression.Run (MacroArg1Arg2, …) – This method runs a macro of calls a function explicitly by name.

Events

Now we have covered some key functions and objects, but wait, isn’t there more? I thought there was something about VBA being event-driven. What are these events, and how can I attend?

It’s true, VBA is an event-driven programming language, but you don’t need an invitation to attend; you just need an event-handler. Events are triggered when a user interacts with the application. This interaction can trigger a specific event itself or trigger a chain-reaction that eventually triggers the event you are interested in. Triggering these events gives you a chance to respond programmatically if you are “listening” for them. For instance, when an object becomes active, i.e., the user clicks on a window, the object’s Activate() event handler is called. This event can be triggered by user interaction but is also triggered if the object’s Show() method is called in code.

VBA comes with pre-defined event handlers for each object and, unlike other programming languages, you cannot create custom event handlers. You enable the event handlers (or expose them) when declaring a variable using the WithEvents keyword. This type of declaration can only be done with objects that support events like class modules, UserForms, and Documents, but not your own custom classes, since standard code modules do not receive events.

Dim WithEvents myWorksheet As Worksheet
‘ Now can listen for Worksheet_Activate events

Some of these events fire automatically and generally start with Auto, e.g., Auto_Open() and Auto_Close(). There is a separate event for the Document_Open, which actually fires before other autorun/autofire events.

Extending VBA

Great. We got functions. We got objects. We got events. Is there anything else I need to know before I start planning to take over the world and make Clippy do my bidding?

Now that you have a basic understanding of the language, we can look at actually developing a VBA project. There are several types of VBA projects that allow you to interact with the Office applications themselves or just the documents in which the macro resides. We should also look at extending your VBA programs. We can take advantage of external libraries along with runtime and dynamic code execution.

Project Types

We can create several different types of projects with VBA, including Applications, Add-ins, Macro collections, and Excel User Defined Functions. The various types of projects are all created using the VBE, but they have different features and purposes.

  • Applications – These are full-featured applications that take advantage of the objects and events made available to Office applications.
  • Add-ins – These projects do not have documents of their own, but instead are part of the Office application and work with the current, ActiveDocument or ActiveWorkbook, etc. The add-ins are saved to a special location to be loaded with the Office application.
  • Macro collections – These projects also do not contain document-specific code and instead can be referenced by any document. This is normally accomplished by saving these macro collections as part of a template, perhaps even the Normal template for Word, or the Personal Workbook template for Excel. These collections make reference to the current document using the ActiveDocument.
  • Excel user-defined functions – These projects allow you to create more powerful Excel functions. The code must be part of the code module and are usually part of an add-in. The VBA code can only change the value of the cell that it is called from.

Ribbon Customization

The Office Fluent User Interface, more commonly known as the Ribbon, was introduced with Office 2007. The Ribbon itself is a collection of settings written in RibbonX (a form of XML). The Ribbon’s XML customizations are stored in an XML file that is part of the Office documents. The newer Office documents that end with an ‘x’ are actually just a ZIP archive of several underlying files and directories, one of which is the customUI.xml. There may be an additional customUI14.xml for Office 2010 and later, specifically. The ribbon can be customized through the user interface using the Office Custom UI Editor Tool for Office, but it can also be customized by modifying the XML directly. The customUI.xml can contain special buttons to display on the Ribbon itself. These customizations can also include actions to preform when a button is pressed using the onAction property. This onAction can reference a VBA function or sub procedure within the document.

External Libraries and Functions

We have already discussed many key functions built into the VBA language that you will want to use, but VBA can also use functions and code from other shared libraries like DLLs, OCXs, and TLBs. You can add a library reference through the VBE (Tools->References). This gives you access to the classes and functions contained within that library. These objects and functions can now be referenced using the object-oriented notation. Adding an additional library through the editor allows for early-binding, which takes place at compile-time. This early binding gives you type checking, syntax checking, IntelliSense, etc. For instance, a common library to add is the Microsoft Scripting Runtime (scrrun.dll). After adding the Scripting library, the classes and their properties and methods are now available.

Dim FSO As Scripting.FileSystemObject
Set FSO = New FileSystemObject ‘ Use Set when instantiating a class
If FSO.FolderExists(“C:\temp”) Then
      MsgBox “Temp exists”
End If
Set FSO = Nothing ‘ Deletes/cleans up the object

Besides adding the reference to the library in the VBE and using early-binding, you can use late binding, which establishes the reference at runtime. This does not allow for some of the helpful editing features, but as a Script Kiddie you might see some benefits to not including your library references at compile-time. When using this late binding, you must declare the external functions and their prototype. When declaring external functions in Office 2010 and later, you need to use the PtrSafe keyword, which means you are taking pointer types into account. If you want, you can use the compiler directive for VBA7 to have different function declarations based on the version of VBA (and therefore Office). The external function declaration must include the function name that you will refer to it as in your VBA code, the library name where the function is located, the alias name that is called by in the DLL (usually ends in an A or W), the arguments (which are usually ByVal), and the return value date type. It may be beneficial to create wrapper functions that set up the arguments and provide some error checking.

Private Declare Function RegOpenKeyEx _
  Lib "advapi32.dll" _
  Alias "RegOpenKeyExA" ( _
  ByVal hKey As Long, _
  ByVal lpSubKey As String, _
  ByVal ulOptions As Long, _
  ByVal samDesired As Long, _
  phkResult As Long _
  ) As Long

Conclusion

Awesome. I think I’m finally getting it. I got all my functions, objects, and events. I can even call any Windows library API. This VBA stuff is legit. It has gone from ‘ya BASIC’ to ‘all your base are belong to us.’ I can’t wait to get out there and cause some mayhem.

We have delved into the depths of the VBA language and come out the other side. You should now have a much better understanding of the object-oriented, event-driven nature of the VBA language. This blog built on the knowledge of the VBA language we gained in the previous post. We covered some of the key functions that you will want to use, the standard and application-specific objects, the basics of events, and then wrapped up with the possibilities of creating and expanding your project. You should now be able to create your own impressive old-school macros, and old-school is the best school. Don’t forget, “With great power comes great responsibility.” And whether you use this power for good or join the dark side, always remember to check your return values.

  • Browse by Category

  • Clear Form