Several years ago, a potential client contacted me to work on a charting add-in. In the contract the client wanted me to sign was a clause that I would not create any chart that I created for this company and that I would never use any code that I used in creating its add-in.

Given that Excel has only a handful of chart types, I concluded that the first clause was unreasonable. The second was as bad, if not worse.

Depending on how one interpreted the code clause, would I be barred from using the Charts.Add method? Or a If statement? After all, there aren’t all that many ways to programmatically create a chart to visually represent data in a worksheet range.

A couple of years later I happened to visit the company’s website and discovered that the product was available for sale. Among the many charts were the classic BCG Matrix Model Chart and the Marimekko chart. I couldn’t help but wonder who agreed that s/he would never ever create any of the charts in the add-in. Not to mention that, strictly speaking, any Excel chart is really nothing more than one of the basic chart types, or a combination of the basic chart types, formatted appropriately. So, is this developer barred from creating a XY Scatter chart, a Bubble chart, and a Column Chart? I imagine if one went through all the charts in the add-in one would be barred from creating any chart in Excel! {grin}

Since then, I’ve worked with companies large and small including one of the world’s largest financial news provider, one of the largest, if not the largest, U.S. retail brokerage, a public utility company, several large regional health care providers and many smaller companies ranging from 10 employees to, oh, several hundred employees. And, I have not had a problem signing the contract, if any, that these companies have required.

Until a couple of weeks ago. A potential client contacted me about some work it wanted done. There were two clauses in particular that reminded me of the experience from all those years ago.

In the quotes from the contract below, I’ve replaced the company name by Cn (or Company name).

Non-Disclosure, Non-Complete and Confidentiality.
Each party acknowledges that it and its employees or agents may, in the course of this Agreement, be exposed to or acquire information which is proprietary to or confidential to the other party. Each party agrees to hold such information in strict confidence and not to disclose any such information to any third parties. Each party agrees that they will not engage in direct work with the other party’s clients.

Spelling and grammatical errors apart, I realize the intent of the last sentence was probably that we not poach the others clients. But, a literal interpretation would require something very different. If a company, say company ABC, is already a client of both Cn and myself, are we now both required to drop it as a client? {grin}

Intellectual Property.
Any writing or work of authorship, regardless of medium, created or developed by Cn or Tushar Mehta in the course of performing the Services under this Agreement and relating to any existing works owned by Cn or its clients shall not be deemed a “work for hire” and shall be owned solely and exclusively by Cn. To the extent any such work for any reason is determined not to be owned by Cn, Tushar Mehta hereby irrevocably assigns, transfers and conveys to Cn all of Tushar Mehta’s right, title, and interest in such Cn Work, including, but not limited to, all rights of patent, copyright, trade secret, know-how, and or other proprietary and associated rights in such Cn Work.

Again, I imagine the intent is very different than a literal interpretation of the clause. Cn probably wants an assurance that if it shared an existing model with me, any changes I made to it would still leave ownership with Cn. The place where I had a problem was what if I used code from my code library? Say, I drop my menu creator class into the Cn project? Or my equivalent of the now depracated Application.FileSearch? Or my version of the superset of the Range.Find method?

What happened? Well, I asked Cn if it was open to reviewing the clauses I had a problem with. A few days after my email, my contact informed me that Cn had found someone willing to sign the contract as-is.

What would you have done?

Maybe, having had a lawyer review a similar contract, you already know that my interpretation is overly paranoid?

Or, you know that the contract is unreasonable and therefore unenforceable?

Or, would you also have asked for a revised contract?

Or, just sign the contract and then ignore it when dealing with future clients? After all, how will Cn know what you do with another client?

Or, checked with a lawyer?

Or, something that I haven’t thought of myself?

Dick Kusleika shared VBE Find, a solution that intentionally had no choices and a straightforward UI. [In his post he also pointed out a few limitations with it.] Unfortunately for him, I liked it so much that I enhanced it to the point that he might now be uncomfortable with the bells and whistles. [Among the changes I shared earlier were adding RegEx support, adding a sort UI and the associated Bubble Sort code of a 2D matrix on any number of columns, and the ability to pick up the word containing the cursor.]

This post documents all the changes from the user’s perspective and from a maintenance perspective, and shares the updated file.

In the add-in dialog box, the add-in name is now VBE Helper.

The capabilities other than the VBE Find remain untouched from DK’s version.

Additional code in the OpenClose code module confirms that the add-in can access the VBE.

All the menu (commandbar) changes are now temporary.

As far as the Find capability goes…

The keyboard access is still ‘Alt t k f’. If anyone knows how to assign a custom keyboard shortcut in the VBE…

The Find button is gone. It’s no longer needed.

The Cancel button still remains. It’s a small white X on a red background in the top right corner of the form. It remains exclusively to support exiting the form with the Esc key. So, to close the form, use either the Esc key or click the form’s X button or this button.

The form is shown modeless. This means one can interact with the code with the form still active (and the reason why the Find button is not needed).

When the form first shows, it will automatically pick up the current selection (if it does not span a line boundary) or, if there is no selection, the word containing the cursor.

If the Find field contains a token, it will be selected.

Click on any row of the results shown and the code will highlight the search item on that line of code.

The form includes support for finding by ‘whole word’, ‘match case’, and ‘pattern matching’. These are as in the default Find and use the same search mechanism. I find the ‘Pattern Matching’ somewhat less than useful since it doesn’t support the entire Regular Expression feature set.

So, there is the ‘Match with Regular Expression’ option. This uses new code added by me.

If there is an error in the search pattern (either for ‘pattern matching’ or ‘match with regular expression’ the error will be shown in red between the 2 rows of checkboxes.

The result is now sortable on any of 3 columns (module name, procedure name, if any, and line number). The priority of multiple sort columns is left-to-right in the UI. When the last checkbox is unchecked the results are left untouched (i.e., they do not return to the original sequence).

When the userform, i.e., dialog box, is closed, additional code ensures that the user is in the window where s/he was last.

If one changes the active project while the dialog box is still open a subsequent search will search the project that was the active project when the dialog box was first shown. Not the nicest of features but we’ll leave that for version 3.

What else would I like to add? Well, a custom shortcut key combo. But, I don’t know how to do that. And, if I change the code while the dialog box is still open, then, I’d like the results updated, at least when I click in the dialog box again. Unfortunately, I don’t know how to do that either since there is no userform event that triggers when one clicks in a code pane and then clicks back in the dialog box (the Activate event doesn’t work). Nor is there a VBE event to detect a code change.

The updated file.

Tushar Mehta
www.tushar-mehta.com

In addition to being able to sort the results of Dick Kusleika’s VBE Find I wanted the code to automagically fill in the search field. If there is a selection, the code should use that selection as long as it doesn’t span more than one line. If there is no selection, then the add-in should use the word or token currently containing the cursor. As an example, if the cursor is between the ‘s’ and the ‘u’ or between the ‘u’ and the ‘b’ of ‘Sub’ in the line Private Sub UserForm_Activate(), the add-in should prefill the search field with the word Sub. Similarly, if it is anywhere between the ‘U’ and the final ‘e’ of ‘UserForm_Activate’ it should prefill the search field with ‘UserForm_Activate’ Finally, if it was in a non-word token — i.e, between the e and the ( or between the ( and the ), it should prefill the search field with the complete non-word token, () in this case.

The code below does that. And, yes, the code can be used with any scenario with a text string and a ‘current position’ indicator. What the code does is the following. It splits the total string at the current position. Then, it picks up the string from the left of the token to the current position and, separately, the string from the current position to the end of the token. Finally, it puts the two pieces together.

The code reuses some of the Regular Expression functions developed to include regexp search patterns.

An example of how to use the WholeToken() function is the enhancement to the Userform_Activate function. The GetSelection method returns the line and column of the start and the end of the current selection. If there is none, the start and end values are the same. So, depending on those values, the add-in uses either the selection or calls the WholeToken function to get the word (or token) containing the cursor.

In Adding Functionality – 2a I laid out the UI changes to support sorting of the results of Dick Kusleika’s VBE Find.

In this post, I develop the actual sort routine.

To recap, the task is to sort a 2D matrix on any number of columns. The sort columns are listed in a 1D array, with each entry in this array identifying one sort column. So, the sort routine’s signature is

The routine uses what is called the Bubble Sort. It is fast for “small” matrices, and will be more than adequate for our needs.

The Bubble Sort essentially goes through the matrix row by row. For each row, it compares that row with every other row between itself and the end of the matrix. If necessary, it swaps the two rows. Consequently, once we are done with a particular row, we never need to reexamine it again. And, that’s why it’s called a Bubble Sort. The results “bubble” to their correct spot.

That’s it. At its core, that’s the Bubble Sort. If we had a single key to sort on, we’d use the test DataArr(I,key-column) > DataArr (J, key-column). However, in this case, we have to cater to possibly multiple keys in the SortCols array. So, we write a slightly more complex comparison routine — and in the process also convert a number provided as a string to a numeric value.

To process multiple keys, we start with the ‘outermost’ key. If we can decide on the relative positions of the 2 records, we don’t have to check the next key. However, if the 2 key values are equal, we check the next key.

The algorithm I eventually implemented uses an ‘index array’ to track the final sequence of the rows in the data matrix. Use of an index array means that each swap involves just 1 element rather than every column in the data matrix.

Here’s where one would see a benefit. Suppose we have 3 records with the keys 33,22, and 11, respectively. Then, the final, and desired, sequence would be the records with keys 11,22, and 33.

With a Bubble Sort we would get the following swaps: (33,22,11) -> (22,33,11) -> (22,11,33) -> (11,22,33). So, we have to swap the rows 4 times, with each swap requiring an exchange of all the columns in the 2 rows, one at a time.

Alternatively, we could build an array with the record numbers in it, i.e., (1,2,3). Now, we change only the contents of this array leaving the original array alone. So, the four swaps would yield the index array (1,2,3) -> (2,1,3) -> (2,3,1) -> (3,2,1). This gives us the sequence of the final output, i.e., row 3, row 2, and finally row 1. So, we would have 4 swaps with the index array and 3 sequence moves for the data matrix.

Dick Kusleika’s VBE Find is useful enough that it justifies the resources need to enhance it.

In addition to adding Regular Expression searching (Adding functionality – I) a sort capability made sense since since the results include code from multiple code modules and procedures within them. The original code showed 4 elements for each match: module name, procedure name, if any, line number, and the code itself. Reasonable columns for sorting seem to be the 1st three (module, procedure, and line). For the sake of simplicity, if the user selects multiple sort criteria, the sequence will be left-to-right. So, if all three sort criteria are specified, the sort will be first by module name, then for each module by procedure name, and finally within each procedure by line. On the other hand, for a module-and-line sort the result will be first by module name and within each module by line number.

The final decision before changing the UI was how the sort would work. Rather than a custom solution for this problem, this seemed to be a good opportunity to implement a general search algorithm for a 2D matrix with one-or-more columns specified as the sort columns. For this specific task, the 2D matrix meant it could capture the contents of the lbxFound field in their entirety. The sort columns would be specified in a 1D array. So, to sort on just the line number, this array would have 1 entry in it, the value 2 (based on a starting value of 0). To sort by module name, procedure name, and line number, the array would have three entries: 0, 1, and 2.

With these decisions out of the way, it was time to change the UI. The three sort checkboxes line up with the first three columns of the results.

The UI related code changes were not a lot: One procedure to coordinate (coordinate not actually perform) the sort, event procedures for each of the three checkboxes to call this common routine, and a change to the tbxFind_Change routine to also call this common procedure.

The common procedure configured the 1D array for the multi-column sort (as explained above) and passed off the sort responsibility to an yet unwritten routine called doSort.

The three event procedures simply call the checkForSort routine, as does the addition to the tbxFind_Change routine.

Add an empty doSort procedure and the existing code would continue to function. It would not do any sorting but the existing functionality would remain available.

Inspired by Dick Kusleika’s “Inflexible Find” I decided to add some flexibility to it. In a comment to his original post (http://www.dailydoseofexcel.com/archives/2011/02/10/vbe-find/#comment-58902), I indicated that the userform was now shown modeless. This meant that one could interactively search for a token and examine the associated code.

Another improvement I wanted to make was a ‘find whole word’ capability. And, as always, I wanted to do it with minimal impact on the existing code, which, in turn, meant minimal testing. Luckily DK’s coding approach works well for me.

For obvious reasons, I decided to use a Regular Expression object. It requires a reference to ‘Microsoft VBScript Regular Expressions’ library (in the VBE, Tools | References…)

I added a checkbox named FindWholeWord to the userform.

Next, I added a function in the userform’s code module to check if a string token exists inside another string.

Then, I had to make only minimal changes to the code in the tbxFind_Change routine, replacing

with

and

with

Commenting out the original code leaves an easy fallback to functioning code just in case there’s a problem with the new code.

Finally, I added an event procedure so that a change to FindWholeWord would trigger the search.

While I did test the changes, I was fairly confident I didn’t have to.

I also laid the groundwork to add ‘case matching’ through a trivial extension to the userform: add a checkbox named, say, MatchCase, and replace the ‘True’ in above call to RegExpFind with Not me.MatchCase.value

A client wanted to visualize a table of data with a dashboard where each chart represented specific information in the table.

I had two choices: 1) Develop a client-specific solution and charge the client the entire development cost, or 2) develop a more general solution, market it to a broader customer base and charge the client only for the integration of the software with their existing add-in.

I opted for the latter approach and the result is the TM PivotTable Dashboard add-in.

This add-in creates a dashboard from the contents of a PivotTable. Each chart in the dashboard shows the last of the row fields and the data field. Elements of the chart are customizable. The example below shows a PivotTable reporting on the number of annual scans of different types performed by each technologist at a radiology department with two locations (the 2011 data are projected scan volume). The PivotTable has 4 row fields (Year, Location, Modality, and Technologist) and 1 data field (Sum of Scan). The dashboard consists of 1 chart for each combination of Year, Location, and Modality with each chart showing the number of scans by technologist.

For more, visit http://www.tushar-mehta.com/excel/software/pivottable/

What might interest developers is how the UI allows one to specify a ‘template’ chart. Because the dialog box is shown modeless, one can select a chart and then click the appropriate button in the dialog box. This identifies the chart on which the add-in should base the dashboard charts.

On many occasions there is a need to lay out controls horizontally in a userform so that they are equally spaced and, as a group, centered within the userform. An example is the group of three buttons (OK, Cancel, and Help) in the userform below.

The worksheet below calculates the left position of each of the controls. Key assumptions: 1) each control is the same width; 2) the space between two controls is half the width of the control; 3) the layout is rational and logical (i.e., the worksheet has no GIGO protection); and, 4) there are no more than 6 controls.