Home » VBA

Tag: VBA

Featured: Excel VBA Unit Testing Tutorial

A Very Simple Excel VBA Unit Testing Tutorial

In this tutorial, I wanted to dispel the myth that unit tests are too advanced, complicated, and esoteric. In fact, anyone can do them—and in this tutorial I’ll show you how.

In order to successfully complete this tutorial, all you need is a good understanding of creating and using VBA Sub and Function procedures.

After you’ve completed this tutorial, you’ll be able to:

  • Create and modify VBA unit tests
  • Run tests and preview the results using the Test Explorer

To complete this tutorial, you’ll need:

  • A Windows computer with Microsoft Excel
  • Rubberduck VBA add-in

Step 1: Download & Install Rubberduck VBA

  1. Go to the Rubberduck VBA homepage at http://rubberduckvba.com/ and click on Download. This will take you to the Github repository. Scroll all the way down to the bottom of the page until you see Rubberduck.Setup.1.4.3.0.exe. Click on it to save it on your computer.
  2. After the download has finished, be sure to double-click it to start the installation. When it’s done, you’re ready to proceed to the next step.

Step 2: Locate the Rubberduck menu

  1. Launch Excel and use your favorite method to open the VBA macro editor. I use the ALT+F11 shortcut key.
  2. There will now be a new Rubberduck menu located inside the Visual Basic for Applications menu bar. In this tutorial we’ll just focus on the commands on the Rubberduck > Unit Tests sub-menu:
    In this tutorial you’ll learn about Unit Testing features of Rubberduck VBA

Step 3: Create a procedure you want to test

To start unit testing our code, we need to have something to test. Since this is an empty project and there are no code modules, we have to insert an empty module, and add a procedure to it.

When choosing a procedure to test it’s good that the procedure:

  • Is a Function and not a Sub
  • Takes one or more parameters
  • Returns a value

I’ve already prepared a function that you can just copy and paste into a new code module:

''
' Validate the user's password length is between 8-16 characters
'
' Returns:
'
' TRUE - Password matches the lenght requirement
' FALSE - Password doesn't match the length requirement

Function ValidatePasswordLength(password As String) As Boolean
 
  Dim success As Boolean: success = False
  Dim length As Long: length = Len(password)
 
  ' Validate the password is of correct length
  If (length >= 8 And length <= 16) Then
    success = True
  End If
 
  ValidatePasswordLength = success
 
End Function

The purpose of the function is to check that the length of a password string is greater or equal to 8, and less or equal to 16 characters. It takes a single string parameter, and returns a TRUE or FALSE.

Before proceeding to the next step, please verify that the module you have inserted is called Module1 and it contains the ValidatePasswordLength() function, which you pasted from the snippet above.

Step 4: Insert a Test Module

Now that we’ve created a VBA function to test, we now need to insert some tests. Tests are usually kept in a different place than the regular code modules. A module that contains one or more unit tests is called a Test Module. Here we have just one code module, and one corresponding Test Module.

To insert a new Test Module:

  1. Display the Test Explorer window by using the Rubberduck > Unit Tests > Test Explorer menu command. A new window will open:
    The Test Explorer window in Rubberduck VBA allows adding Test Modules and unit tests.
  2. Inside the Test Explorer window, click Add > Test Module. This will insert a new Test Module.

To verify a new module was inserted, you should now see two modules in Project explorer (Module1 and TestModule1):

A new Test Module shows up in Project Explorer

Here’s what your TestModule1 should look like:

The Test Module that is inserted by Rubberduck VBA is already populated with some boilerplate code.

As you can see, it already contains a bunch of code. Don’t worry if you don’t understand everything. You may ignore it for now, as we’ll come back to it later.

The only thing you need to remember is that we work with two different modules. Module1 contains the functions we want to test, and TestModule1 contains the unit testing logic. Be sure to always keep those two separate, and you’ll be fine.

Step 5: Insert a Unit Test

Now that we’ve got our TestModule1 in place, we can use it to add some tests. Usually tests are added at the very bottom of this module.

There can be any number of test cases, and they are just appended one after the other.

Let’s go ahead and add one or more unit tests. To insert a unit test:

  1. Inside Test Explorer click on the Add > Test Method button.
  2. Notice at the end of TestModule1 we have now created a test method. We know this is a test method because it’s prefixed with the @TestMethod comment, shown in green below):

Every time a test method is added, it has the @TestMethod prefix. You shouldn’t delete or modify it.

Let’s give our method a unique and descriptive name. Let’s change it from TestMethod1 to TestPasswordLengthIsTooSmall().

When we read the comments, we can see that Rubberduck recommends we use the Arrange, Act and Assert methodology for writing our test. We can delete this for now, because we won’t need it.

After renaming the test method and deleting the comments, it should look like this:

Step 6: Customize the Unit Test

In this step we’ll customize the TestPasswordLengthIsTooSmall() test method by adding some code to it. We want this method to call ValidatePasswordLength() from Step 3.

We can use many different values to ValidatePasswordLength()‘s parameters. For example:

  • “bob” –> should return FALSE
  • “ASecretPassword” –> should return TRUE
  • “” –> should return FALSE
  • “123456” –> should return FALSE
  • “IAmAVeryLongLongLongLongPassword#!{}$#.” –> should return FALSE

If we imagine running ValidatePasswordLength() with the inputs above, we should get results as indicated in the bullet points above. But let’s go ahead and verify this by writing a single unit test for the first case “bob”.

When writing unit tests, we depend a lot on functionality provided by Asserts:

Assert.IsFalse (ValidatePasswordLength("bob"))
Assert.IsTrue (ValidatePasswordLength("ASecretPassword"))
Assert.IsFalse (ValidatePasswordLength(""))
Assert.IsFalse (ValidatePasswordLength("123456"))
Assert.IsFalse (ValidatePasswordLength("IAmAVeryLongLongLongLongPassword#!{}$#."))

Assert.IsTrue and Assert.IsFalse above are methods from Rubberduck’s Assert class:

The Assert class in Rubberduck VBA provides some of the most commonly used assertions.

So let’s take the first case above and put it into our very first unit test:

To verify that the case is now available in Test Explorer, we need to click the Refresh button (this is only needed when we rename any of the test methods).

After refreshing the test methods in Test Explorer, we can see our test method is shown:

Believe it or not, that’s it! We’re done with creating our first test case.

Step 7: Run the test

Now starts the real fun: let’s run it. In order to run the test:

  • Inside Test Explore, click Run > All Tests.

This updates the Test Explorer to show us the results:

That’s it! As you can see, our unit test passes. If you’d like to add the remaining test cases, you may do so. When you’re finished adding them, you just need to refresh the Test Explorer, and re-run the tests. The Test Explorer will run all of them, and show you the summary of results for each.

Once all your tests pass, you’re good to go!

Summary

In this tutorial, I’ve shown you how to get started with Rubberduck VBA to create and run your very first unit test. Congratulate yourself on your success!

Obviously, a real-world scenario doesn’t include just one single test. There can be many unit tests organized in many test modules, depending on the complexity of your solution. The great thing about using Rubberduck VBA is that it provides a Test Explorer which shows you the details about your tests passing or failing.

If your organization needs help with implementing unit tests in your project, or require tailor-made solutions for complex scenarios, please don’t hesitate to get in touch.

featured image: vba editor missing features

Top Features Missing from VBA Editor

VBA is a great programming language for Office developers, but there’s no doubt that it’s imperfect. Whether it’s existing language features that need improving, or new ones that are missing entirely, Microsoft hasn’t been doing much for a very long time.

Visual Basic Editor (VBE) has plenty of missing features, too. For example, it has no automatic indentation and no refactoring capabilities. While some of these features are available through third-party add-ins, it would be great to have them built into the editor itself. Here’s my selection of top missing features.

#1. Automatic indentation

One of the most controversial things about VBE is that it’s code editor is very basic, whether you like it or not. I have to admit, coming back from another code editor, such as Visual Studio or Sublime, is sometimes a major inconvenience.

Wouldn’t it be great if code could be automatically re-indented?

I currently use Visual Studio for this. To re-indent messy code, I use the Edit > Advanced > Format Document command.

#2. Collapse Function and Sub procedures

Most modern code editors support collapsing functions, so as to ease the code navigation. Unfortunately, there’s no such feature in VBA Editor.

#3. Class explorer

A class explorer, such as the one found in Visual Studio, would be an awesome asset in VBE. If you’re into OOP, classes can pile up really quickly. Having a good class explorer right inside VBE would greatly enhance navigation in projects that contain many of them.

#4. Refactorings

Visual Studio saw the introduction of refactoring capabilities, a feature which power users have been after for a long time. While refactorings were always available through some third-party add-ins, it would be great to have those features built into the editor itself.

For example, it should be easy to rename a Sub or Function procedure. It should also be easy to extract a method, or to reorder parameters.

Refactoring menu in VBE, enabled by the Rubberduck VBE add-in.
Many refactorings are enabled in VBE by installing an add-in called “Rubberduck VBA”.

#5. Code metrics

A neat feature in Visual Studio is code metrics. It analyses different aspects of your code, such as number of lines of code, and length and complexity of procedures (also called cyclomatic complexity). A developer can look at these metrics and use them as a guide to improve his code.

#6. Unit testing

People comfortable with test driven development love unit tests, and I can understand why. It helps to create code that is more robust, resilient and reliable. Most modern development environments have some sort of support for unit tests, in one shape or another. Unfortunately there’s no such thing in VBA, but I wish there was!

#7. Source control

Wouldn’t it be great if you could easily integrate your project with a source control repository, such as GitHub? If you’re keen on doing this, right now you need to manually export all your code (including modules and classes), then upload to GitHub. By now, this is a very common scenario, and this should be standard in the Visual Basic Editor.

Conclusion

In this article I outlined the features I would like to see in VBA Editor: automatic indentation, collapsing Function and Sub procedures, class explorer, refactorings, code metrics, unit testing and source control.

Do you agree? Are there any features you would like to see in the VBE code editor? Share your thoughts in the comments below!

featured: vba editor syntax highlighting

How to enable syntax highlighting in VBA Editor

If you’ve worked with code editors other than VBA Editor, you probably know about syntax highlighting. It’s that feature that highlights language keywords, making the code much easier to read.

Going back from Visual Studio to VBA Editor is always a pain. Partly because there’s no syntax highlighting. Here’s how to get around this.

How to turn on Syntax Highlighting in VBA Editor

To enable syntax highlighting in VBA Editor:

  1. Click on Tools > Options.
  2. Activate the Editor Format tab.
  3. Inside Code Colors listbox, select Keyword Text and choose the text Foreground color.

This is what the final settings look like on my computer.

And, when applied, you get the following result:

Conclusion

I’ve shown you how to turn on syntax highlighting in VBA, without requiring any add-ins. Let me know what you thought about this tip by commenting below!

If you’d like to receive more tips like this one, be sure to subscribe to my newsletter and follow me on social.

Featured: Bad VBA Coding Habits

Ten Useless VBA Coding Habits That Must Be Eradicated

Coders have long banned Hungarian notation from their VBA modules. It’s meaningless when the code editor is slick enough to show information about a variable.

But now a new crop of equally meaningless, over-used coding habits have begun to litter code modules… and these need to go, too.

In my work, I frequently encounter code from other people, and from time to time I catch myself making them. Nonetheless, it’s useful to know what they are, and how they cause problems, so that you can avoid them in the future.

Note: although I used VBA as an example, these errors also apply to other programming languages, such as C# and VB.NET.

#1. Hungarian notation

If you’re wondering what is Hungarian notation, it’s an identifier naming convention in computer programming, in which the name of a variable or function indicates its type or intended use.

Let’s illustrate this with an example. In the function CalculateTotal() below, letters s, i and d are examples of Hungarian notation:

My problem with Hungarian notation is that it takes perfectly well written code, and then obfuscates it. It also makes your code less compliant with other standard coding conventions, such as those used in .NET.

If I was writing the above procedure, I’d probably use this coding convention:

If you’re interested in learning more about the best practices in coding in the Microsoft ecosystem, have a peek at Microsoft’s Naming Guidelines for .NET Framework.

#2. Long Sub and Function procedures

I also have a problem with long procedures. A procedure is supposed be short and manageable and do one thing only. If your procedure is too long, it’s probably doing many different things, invalidating its singular purpose.

To give you a good example of what I mean, try visualizing a procedure with 200 lines of code with multiple branching and conditional statements, as well as calls to other functions. It’s a giant nightmare. Instead of long procedures, I prefer using straightforward logic and flow-of-control.

What is the optimal length of a procedure? Ten lines or less is ideal. If it has more lines than that, it may be a good candidate for refactoring.

#3. Meaningless naming of variables and procedures

There’s nothing worse than vague or meaningless naming of variables. When it comes to code reviews, bad variable names are a great thing to look for. A reasonable rule for coding is to never use meaningless variable names. Here’s an example:

Meaningless names make the code harder to read, and I don’t like that.

#4. Using error handlers, just for the sake of it

It’s not necessary to use error handlers everywhere and anywhere. If you’re copy and pasting error handlers just to fill every empty procedure, or to make nice formatting, it’s pointless. A better approach would be to replace such error handlers with data validation. After you’re completely happy with that, then you may consider adding error handlers.

#5. Using comments, just for the sake of it

When you write short, self-documenting code, most of the time comments are unnecessary. Also, it’s annoying when your comment just states what’s obvious. Consider this example:

Generally, comments should describe what or why you are doing something, rather than how. Comments should be used to provide more value, not confuse you even further.

#6. Not validating data

In simple terms, validation refers to ensuring entered data is valid. Determining whether or not data is valid can sometimes be a painstaking process as there are numerous tests that need to be thought of. If you have a habit of not validating your data, you risk the robustness and reliability of your program, as run-time errors will cause problems.

One example of where data validation is used extensively is in UserForms. If you have a TextBox that only accepts zip codes, you need logic to validate and reinforce it.

You could reinforce validation at the UI level; by using input masks, for example. However, using code to validate is also a good idea.

Besides validating in UserForms, it’s also beneficial to validate in Sub and Function procedures. If you’re passing around data from one procedure to another, you need to verify that the received format matches the expected. When data is validated properly, you’ll avoid run-time errors, as well as increase data integrity.

#7. Too many parameters

When you define a Function or Sub procedure, you specify a parameter list in parentheses immediately following the procedure name. For each parameter, you specify a name and data type.

So how many parameters are too many? Here’s a comment from a thread on StackOverflow:

The ideal number of arguments for a function is zero (niladic). Next comes one (monadic), followed closely by two (dyadic). Three arguments (triadic) should be avoided where possible. More than three (polyadic) requires very special justification — and then shouldn’t be used anyway.

Let’s look at an example.

There are a few way to optimize this, and here’s one way:

#8. Using magic values

A magic value is a value that’s usually hardcoded in such a way that doesn’t require a variable. Here’s a simple example (notice the number ‘7’; it’s considered a magic value):

As your program grows, using magic values can cause problems, especially if the same value is defined in many places in your solution. Here’s how you can fix it:

#9. Declaring variables incorrectly

I see this one a lot, and it’s really annoying:

On the line where a, b, c and d are defined, only d is of type Integer. All the other ones are Variants. To avoid this error, it’s best to initialize variables on each line separately.

#10. Coding more than you have to

Coding is fun! But don’t let get carried away — a successful programmer is not measured by the number of lines of code.

If you’re writing more lines of code to express the logic than what is necessary, chances are you’re trying too hard. Don’t write more than you need to — your solutions will be easier to maintain in the future, and it won’t confuse people unnecessary.

Conclusion

The coding habits I covered are: using Hungarian notation, long Sub and Function procedures, meaningless naming of variables and procedures, using error handlers and comments erroneously, not validating data, passing too many parameters, using magic values, declaring variables incorrectly, and coding more than you have to.

Changing your coding habits overnight can be hard, but knowing where to look can be a huge boost to your productivity. I hope that by giving these tips you’ll get inspired to eradicate habits that are slowing you down.

One last thing: If you try this out, I want to hear from you! Leave me a comment letting me know if you were able to identify any bad coding habits that you’d like to improve in the future.

featured-review-unviewable+-vba-code-protection-excel-word-powerpoint-access

Protection Against VBA Hacking: Unviewable+

I recently got a call asking me about protecting intellectual property.

Their requirements were simple:

  • They wanted to prevent end-users from viewing or modifying the source code
  • They wanted to distribute their product to as many end-users without risk
  • They’re using VBA and they didn’t want to create a COM add-in in VB6 or .NET

The Recommendations

To do this kind of thing, I suggested that they use the following:

The wonderful thing? Some of these tools are really easy to use and affordable, so there’s very little work you have to do to protect your product.

With an increasing number of new unlocker tools available on the market, how do you keep your source code secure?

Meet the New Kid On The Block

Unviewable+ is a relatively new product, launched after a successful IndieGoGo campaign. It’s a VBA protector tool that works with Excel, Word and PowerPoint files.

Compared to other products, it offers several new methods of protection, which are explained below.

Protection Types

Unviewable+ provides three types of protection:

  • Hidden modules – Standard modules will be hidden (classes and userforms will still be visible). The VBA project won’t be locked, so any module type can be added and saved. Procedures can be called from hidden modules, but VBA code cannot be read from them, even when using the VBA Extensibility library.
  • Unviewable password protected VBA project – Unviewable password protected VBA projects are less secure than the equivalent locked projects, as recovery information has to be maintained within the file.
  • Unviewable locked VBA project – Permanent protection. Locked VBA projects cannot be made visible by the application.

PROTECTION Levels

Unviewable+ provides four levels of protection:

  • VBADiff Compatible – VbaDiff is a must-have source control and code differencing application. This least secure level is being offered to allow developers to work with VbaDiff and unviewable VBA projects.
  • Simple – Similar protection as in the previous level, but VBA projects cannot be read by the VbaDiff application.
  • Medium Strength – Ideal setting for beta VBA applications or projects with poor error-handling. Also a good option, if the Ultimate protection restrictions conflict with your setup (see below).
  • Medium Strength – Permanent protection. Locked VBA projects cannot be made visible by the application.
  • Ultimate. The most secure unviewable setting which: disables the Debug functionality, prevents public macros from being visible in the list of macros, as well as prevents running of macros via assigned shortcut keys.

The Advantages

The Unviewable+ application meets the following requirements:

  • Unviewable+ maintains the normal file extension of your workbooks, presentations, templates and add-ins.
  • Unviewable+ protected files will not trigger antivirus software to block opening your workbook, presentation, template or add-in.
  • VBA code can still be read by malware analysis scanners and antivirus tools.
  • VBA projects still behave like macro workbooks (in regards to security warnings and trusted locations).
  • The application does not travel with workbooks, presentations, templates or addins.
  • The application is not intrusive. No DLLs are loaded on target computers. The protection scheme doesn’t depend on executable code.
  • Compatible with both 32 and 64 bit versions of Office.
  • Has the ability to run from the command-line, which can be useful in automated builds.
  • Available in several languages: Danish, German, Spanish, French, Portuguese, Chinese and English.

Disadvantages

  • The disadvantage is that it may be easier to hack, as compared to compiled DLLs.

The Four Steps to Using Unviewable+ to Protect Your Code

In this example I’ll use a HelloWorld.xlsm workbook and protect it with Unviewable+.

Step One: Launch Unviewable+

When you start Unviewable+, you ‘ll see a ribbon with a few basic options:

unviewable plus step 1

Step Two: Open Your File

Now I’ll just browse for my macro-enabled file. I am presented with a choice that says “VBA Project is NOT clean. Do you want to clean it?”. Confirm with Yes to proceed.

The cleaning process is not a part of VBA security. If you’re interested in background information about what happens when you clean a file, here’s a link that explains it in more details.

unviewable plus step 2

STEP THREE: SAVE THE FILE

Once you reach this step, you’ll see a lot of different options. Choose the method of protection that’s most suited to your needs.

unviewable plus step 3

Once you’re ready to proceed, click on “Protect VBA Project”.

Next, a warning will appear:

unviewable plus step 5

Choose Yes to proceed to the next step.

Step Four: Save the File

The last step is pretty simple. You are presented with a summary of results as shown here:

unviewable plus step 4

Once you’ve done that, you’re good to go — you can send your file to the end-users.

Summary

Unviewable+ lets you protect your VBA project in four easy steps.

The Unviewable+ protection features are just one of the reasons I strongly recommend Unviewable+. If you want more information, you can read my other product reviews, or give me a call for specific advice.