Hack into Password Protected VBA Projects

October 8th, 2014 by datapig 15 comments »

A few days ago, I had to pull out one of my old tricks to hack into a password protected VBA module. The file I was working on was protected by a former (evil) employee who, for some reason, did not leave the VBA password.


I'm notoriously lazy, so there is no way I'm retyping code that's already there. I'd rather hack into the workbook.


Today, I'll walk you through the steps I use to crack VBA passwords. I learned this trick a while back from one of my Excel Boot Camp students. I can't remember who. Sorry…whoever you are. Even though I can't remember your name, I value our friendship greatly.


Step 1: Download a Hex Editor

You'll need a Hex Editor. I use one called XVI32. Why this one? Because it's the one my mysterious friend showed me. I'm not really into Hex Editors, so I couldn't tell you if it's good or bad. I just know it does the trick. Download the XVI32 Hex Editor and have it ready to go.


Step 2: Save your Workbook as XLS and MAKE A BACKUP COPY

If you're working with an XLSM file, you'll need to save it as an XLS. Be sure you make a backup of your workbook before you start. Because you're diddling the insides of the workbook, there is always a chance you could royally screw up. You'll want a backup.


Step 3: Open Your Workbook in the Hex Editor

Fire up the Hex Editor you downloaded and then (within the editor) select and open your workbook. In the XVI32 editor, my workbook looks like this:


Step 4: Find and Replace the DPB Keyword

In your Hex Editor, look for the text string DPB.


This text string apparently indicates Excel's tag for the VBA Password.


In order to confuse Excel and get around the VBA Password prompt, you just need to muddle the tag a little. You can do this by simply changing the DPB to something like DPX (replace the B with an X).


Step 5: Save Your Changes

Close the Hex Editor and be sure to save your changes


Step 6: Open your Newly Diddled Workbook

Open the workbook you just finished molesting. You will get a few messages.

First this one, which you will say Yes to:


Then you'll see this one (a few times). Don't panic, and keep clicking OK.


Step 7: Remove the VBA Protection

When Excel stops its bitching and moaning, go to the Visual Basic Editor (Alt+F11) and then select View>>Project Explorer.

Right click on the VBAProject for your workbook and select VBAProject Properties.


In the Properties dialog box, go to the Protection tab and clear any selections and text you see there.


Step 8: Save a Close your Newly Unprotected Workbook.

Save and close the workbook. At this point, you'll have full reign over the VBA in the workbook.


There you have it – another trick for your toolkit.

I could suggest that you use this trick only for good and righteous purposes, but you won't listen anyway.

5 Rarely Used VBA Tricks

October 1st, 2014 by datapig 12 comments »

Hey folks! I'm back from a long hiatus. I've been down with (of all things) the Shingles. I know…weird. Apparently, if you've had the chicken pox as a child (which I did) there is a chance that the dormant virus causing chicken pox can rear its ugly head and develop into Shingles. It's actually fairly rare in men my age to get Shingles.


So in celebration of my recent recovery from my rare Shingles outbreak, I'll share with you a few rarely used VBA tricks.


Trick 1: Using the Same Variables across All Macros in a Module

Most of us are used to declaring variables within a Macro. When we do this, those variables are good for that one Macro. But did you know that you create variables that work across all Macros within a Module? That's right! You can place your Dim declaration statements at the top of your Module. This lets you expand the scope of your variables, letting Excel know that they are good for all Macros within the Module.

Here's an example where a generic variable (Exp1) is used in both the Macros as a container for cell values.


Trick 2: Look Mom….No End If

We've all been taught that if you start an If statement, you need to have a corresponding End If. That's actually only true if your test criteria and your resulting action are on separate lines. If you place your entire If…Then statement on one line, Excel lets you get away with not entering an End If.


Trick 3: Using a Colon to Enter Case Statements on One Line

The Case statement is another conditional statement where we have been taught to enter the resulting action below the criteria evaluation. But did you know you can use the colon character to get each Case statement on one line?

In this example, you can see the resulting action of each Case is on the same line (thanks to the inclusion of the colon).


Trick 4: Using the IIF Function

Microsoft Access users will be familiar with the IIF function. The IIF function is one of the main functions used in Microsoft Access to apply If..Then..Else conditional checks. This is function works in the same way as the standard If..Then..Else statement. You can leverage it in Excel VBA to do things like the example below. In this example, the IIF statement evaluates the value in cell D3 and returns either "Greater Than 50" if or "Not Greater Than 50" depending on the value.


So what is the difference between the IIF function and standard If..Then..Else statements? The difference is that where the standard If..Then..Else statement stops evaluating when condition is True, the IIF function continues to evaluate all arguments even if any of the conditions are true. For instance, in this example, if the value in D3 is greater than 50, the IIF function will continue to evaluate the nested IIF function to see if the value is less than 50.

It's Not the easiest thing in the world to read, but the IIF funciton can help reduce the amount of syntax needed.


Trick 5: Using the Mid Statement as a Replacement Operator

Most of us know that the Replace function in VBA helps you replace specific text with some other text. But did you know that VBA has a Mid Statement designed to replace text at a specific position with some other text? The syntax for VBA's Mid Statement is this:

Mid([Text], [Start Position], [Number of Characters to Replace], [Replacement Text])

In this example, we are replacing the three characters starting at position 5 of the TestString with "fox"


So why would you use this instead of the Replace function? Well remember that the Replace function requires the exact text you are replacing. In some scenarios, you may not be able to specify the exact text. For instance take a look at this example. Here, we are need to replace the 5th character in the string variables with the letter Z. Using the Mid statement allows us to perform a replace based on character position instead of specifying specific characters.


As useful as this Mid statement may sound, there is a strange drawback you should be aware of. The Mid statement will not return more characters than you feed it. For example, note that in the code below, when trying to replace the four characters starting at position 5 ("York") with "Mexico", the Mid Statement truncates my replacement text to only return the exact number of characters that was in the original text.


Well there you have it – a list of rarely used VBA tricks.

I don't use these very often. Some of these are so specific in their utility that they turn out to be irrelevant for most of my work. Others, I simply forget to use.


Feel free to comment about the rare VBA trick you use.

Or you can comment to tell us about your own gross disease (like the Shingles).

Enable Double-Click and Right-Click with the Essbase Add-in

August 27th, 2014 by datapig 2 comments »

Last week, Dick Kusleika and I conducted some training for the good folks at TD Ameritrade. While training, some of the folks found that Excel wasn't responding when double-clicking or right-clicking the mouse. As most of us know, there are plenty of shortcuts and productivity gains to be leveraged in Excel with the mouse. But these folks weren't able to do any of those mouse actions.


It turns out the issue was the Essbase Add-in. Essbase is the tool most finance departments use to easily port financial data into Excel. By default, Essbase highjacks the standard double-click and right-click for its own purposes. Essbase does this to give their users mouse-driven ways to perform some of the actions that are already in the Essbase menu.


If you're using the Essbase Add-in, you can reclaim the double-click and right-click mouse actions by going to the Essbase Options dialog box, then clicking on the Global tab. There, you will see the Mouse Actions section.


Uncheck the Enable Secondary Button option to reclaim Right-Click.

Uncheck the Enable Double-Clicking option to reclaim Double-Click.


At this point, all normal mouse actions will return to Excel.

The Huey Louie Dewey Error Message Needs an Update

August 15th, 2014 by datapig 1 comment »

Every so often, when working in Access, I get the ever delightful Huey Louie Dewey error message.

Never heard of it? Well, when you try to specify query criteria with commas and no operators or quotes, you get this error message:

The expression you entered contains invalid syntax, or you need to enclose your text data in quotes. You may have entered an invalid comma or omitted quotation marks. For example, if the Default Value property of a text field is ''Huey, Louie, and Dewey,'' it must be enclosed in quotes if you mean it as a literal text string. This avoids the confusion with the expression ''Huey Louie'' And ''Dewey''.


When I saw this error message today, I got to thinking » Read more: The Huey Louie Dewey Error Message Needs an Update

Understanding Weighted Averages

August 12th, 2014 by datapig 5 comments »

One of the first warnings an aspiring analyst hears is, "never take an average of an average". The reason for this warning may not be intuitive, but it's important to understand why it's generally a bad thing to average a bunch of averages. In today's post, I'll attempt to explain the problems with using simple averages and how Weighted Averages can help you avoid inaccurate conclusions about your data.


Where Straight Averages can Go Wrong

Imagine you manage two sales reps (Jim and Tim). These two sales reps have been working for you for three months. Over the last three months, you've collected data on their win rates vs. sales calls. After three months of selling » Read more: Understanding Weighted Averages

Dashboard Tools Add-In Update

August 5th, 2014 by datapig 2 comments »

I've been pretty busy with client work, so no new blog post today. I did, however, want to share some updates I've made to my Dashboard Tools Add-In.


If you don't know yet, the Dashboard Tools Add-in is a utility that helps you: enhance your dashboards with stylized graphics, find viable color schemes, and create dashboard mock ups.


For this update, I added two new tools: a Color Capture tool and a Customize Percentages tool.


The Color Capture Tool

The new Color Capture tool is based on the eye dropper tool developed by » Read more: Dashboard Tools Add-In Update

Quantifying Subjective Text with an Excel-Based Sentiment Analysis Tool

July 24th, 2014 by datapig 8 comments »

Sentiment Analysis is a growing field in the world of BI and Data Science. With social media serving up millions of data points by way of comments, tweets, and status updates, it's becoming increasingly important to be able to take all that subjective text and turn it into quantitative data that can be integrated with other metrics. That is essentially what Sentiment Analysis is - a term that describes the exercise of quantifying subjective text by analyzing certain keywords deemed to be either positively or negatively charged.


How Sentiment Analysis Works

In its simplest form, a Sentiment Analysis algorithm can be nothing more than determining if a text string has more positive keywords than negative keywords. Text with more positive words is classified as positive content, while » Read more: Quantifying Subjective Text with an Excel-Based Sentiment Analysis Tool

Generating Normally Distributed Random Numbers – Sample Data that Makes Sense

July 16th, 2014 by datapig 9 comments »

Like many of you, I'm often asked to develop mockups (working demos of dashboards and reports).

In my zeal to make my mockup as realistic as possible, I spend a good bit of time generating sample data.


For example, if I'm mocking up an HR dashboard, I'll dedicate some brain power to generating salary data. Now in order to make salary data realistic, there has to be a believable mix of high salary and low salary records. I can't just use the RAND and RANDBETWEEN functions Excel provides. These functions generate random data in a "Uniform Distribution". That is to say, every number has an equal chance of occurring with an equal frequency.

. » Read more: Generating Normally Distributed Random Numbers – Sample Data that Makes Sense

Lazy Summer Update

July 9th, 2014 by datapig 4 comments »

Hey there….remember me? I've been having a lazy summer; cooking my obscene body in the hot South Carolina sun. I'll be back with regular posts next week. I just wanted to get this update out.


New Book in August

Dick Kusleika and I wrote an Excel Formulas book. This book is for anyone who wants to jump head first into formulas. Here, we offer step-by-step instructions for creating and using 101 of the most commonly requested formulas. We include tips, tricks and detailed explanations along with actual working formulas. This is targeted at beginning to intermediate Excel users. Feel free to click on the book to see more details.


. » Read more: Lazy Summer Update

8 VBE Tips Every Programmer Should Know

June 19th, 2014 by datapig 13 comments »

I've covered these time-saving VBE (Visual Basic Editor) tricks across several posts over the years. I thought it would be helpful to get them all into a single post.


Whether you're a fresh-faced analyst new to programming, or a jaded veteran living on sunflower seeds and Mountain Dew, these tips will make programming with Excel and Access so much easier.


Tip 1: Block Comments

Are you still commenting code one line at a time?
» Read more: 8 VBE Tips Every Programmer Should Know