OK, Microsoft, I get it. You want to be helpful in making all my documents format correctly. However, sometimes you go too far. Case in point: automatically inserting a space before or after parentheses and brackets. A behavior that didn't exist in the previous version of Word we used for document drafting (2003), but does now (Word 2010).

In my little niche-world, opening and closing brackets are used in tandem  to denote a range of text that is being marked to be eventually removed. After something has been marked for delete it looks like this in the document (with the --> denoting a tab):

 At times, when a set of characters are "bracketed" by the user (all through VBA code, by the way, by selecting text and pressing Delete), the user may have erroneously done it. HORRORS! There is an undelete function also in VBA where the user presses the appropriate keystroke (Ctrl-Delete) and the brackets and all formatting disappear, leaving the text pristine as is shown below. 

 The brackets are typically handled quite easily by a one-liner command (after the bracket is identified and set to the range:
     'Set range to the bracket first
         BracketRange.Delete

However, if the situation is as shown above, Word doesn't allow the tab and opening parenthesis to co-exist next to each other; it automatically inserts a space. Manually selecting the bracket and pressing the delete key generates the same result: a space. If you don't select the bracket, but rather place the insertion point immediately preceding the character and press Delete, it will perform as expected, and make the bracket go away. Why, then, does having it selected prior to pressing the delete cause a space? 

I was able to address this by changing my one line of code to:
     'Set range to the bracket first
        BracketRange.Text = ""

Which effectively does the same thing. My irritation lies in why I had to make the code change in the first place.

In the environment I am working, we use MS Word - customized to within a hairsbreadth of its life. Across multiple code templates that are loaded when Word starts, document/code templates that are available only when a particular document type is open, and even docm files that are documents that contain code, there is close to 100K lines of VBA code. That's a lot of code to manage. 

As part of the management process, changes and/or updates are necessary. That's pretty much what we do, most of the time, and add new functionality when users dream up something new. All good. The issues I was running into was the time spent looking for a particular function or module that I could leverage as is, or repurpose with limited modifications. 

The search ability built into the Visual Basic Environment is limited to open code documents and templates. And, unlike OpenOffice code, Word's code is not simple text files that can be searched via really handy tools such as GREP. It looked like I had to roll my own. No problem. VBA contains a self-referential method to examine the code itself. What I did was open all the code templates/documents, extract the contents of all the modules' subs and functions, and built a single searchable Word document, color coded and styled to identify template, subs, functions and comments.

After the initial code development and ensuring it all worked, I put it all in a portable docm, slapped an interface on the front and created a few bells and whistles to make using it easier. Now, all I need to do is open the codeoutput.docm file, and double click the start button on the document to launch the options dialog, click the appropriate generate button, and get a fresh cup of coffee while it grinds through all my templates and code documents.

There are a few options that I can select as shown in the image below. You can identify folders of code templates to include, and individual code templates/files to exclude. Both of those processes are point and click using the "+" and "-" buttons. To add, click the "=" and use the folder/file picker to make your selection. To remove, select an item in the list and click "-".

Clicking the "Sub & Fcn List" button generates a three-column table list of all your subs and functions, and includes the first line past the name of the sub or function and saves the file as "Sub_Fcns<yyyymmdd>.docx" in the location where you've indicated. Why the first line past the name, you ask? I includedi t, since I usually put a comment here that indicates the purpose of the sub/function. All public subs/functions are highlighted. Snip below.

Clicking the "All Text" button generates the whole enchilada. It creates and saves the file as "Code<yyyymmdd>.docx" in the location you've indicated in the form. What I did after that is convert the document to a chm file, so I didn't have to open the document for searching if I was knee deep in Word code. Launch the .chm, and keep it open for reference.  

Each module is styled as Heading1 and each sub or function is styled as Heading2. That way, you can collapse the document to an outline and speed around if you want, and, if you convert it to a .chm file, the styles can become TOC entries. What it looks like is shown below. 

Click the "Save Settings" button to save the folders to include, files to exclude and file types for the next time you need it. 

If you want to download the docm that does it and modify it for your own purposes, click below and have at it. I'm sure many can improve it. The code has a digital signature verified by Verisign. I'm legit! 

VB6. Haven't heard that one in a while, have you? I seem to be about 8 years out of date. But if an application works, why change it, right?

One would think that embedding a spreadsheet into a Visual Studio 2010 (VS2010), VB-flavored form would be a fairly straightforward and simple process. One would even go as far as to say, "Gee, those Microsoft folks have Excel running around loose in the wild, maybe they'll even have a built-in component!"

One would be wrong on both counts.

Some background: years ago, I created a front-end application that would emulate the look of the timesheets we use for tallying the time we do for tasks. It linked up to the SQL back end, so the folks in Accounting could simply download the data into csv files for import into their magic buckets to spit out paychecks. For the most part, it all went swimmingly, with various levels of access to others' timesheets and two-level electronic approval, though the Accounting wonks never wanted to use the import function; apparently they preferred re-entering all the data from printed sheets. Whatever.

Fast forward 10 years. I've left and returned. The same VB6-based application is still in use, with nearly no changes. Now, for some inexplicable reason, that application is conflicting with the data access components we have in our extensive Word 2010 customization. Looking at the data access, I ensured all the Word stuff was using the latest ADO dlls (6.0) from the existing mishmash of 2.0, 2.5, 2.6, 2.7 and 2.8. Still, the timesheet app wouldn't work. Interestingly, re-installing the timesheet causes factory-access errors in our Word customization. Since the Word aspect of the division is much more important than the once-every-two-weeks timesheet, a fix for the timesheet was needed. I had two weeks until the pay period ended.

No problem. I would get the source code, poke around and find what needs updating. Anyone got a copy of VB6 running around? *crickets* As it turns out, there is not a functional copy of the combination of source code and VB6 application to make modification. Swell. Forced re-write time. 

In trying to emulate the existing look and feel of the application (mustn't upset the users), I needed to embed a spreadsheet in a form. What a headache. To do so, one needs to download and INSTALL the office web components executable on every computer that will be running the app. Sorry, there will no simple dll and regsvr32.exe action for you! Manual process on every computer. I don't get it. 

I tell you, if I need to do anything else on this timesheet, and/or if the replacement application (anyone hear of Tyler??) is delayed any longer or sucks more than anticipated, I'll scrap the VS2010 application altogether, and just customize the hell out of a spreadsheet and Excel instance. It'll likely be easier.

I'm a big fan of the KISS principle: "Keep It Simple Slang." I tried following the instructions for passing arguments to a clickonce application, and though I am not stupid, they seemed, I don't know, complex. I went for a much simpler route that works well in my environment.

Let me back up and give the situation. I have a simple clickonce application call AlphaWave that allows users to select from a couple of drop down lists, and enter some information into a text box and maybe add an image to provide feedback to me. Pretty simple stuff. What I wanted to do is to pre-populate one of the dropdowns based on the application that was calling the AlphaWave app so the user wouldn't have to. Again, pretty simple stuff. Or so I thought. Searching through the morass of blogs, MSDN articles, and other assorted goop, I found a couple of good articles that discuss it. The discussion was in-depth and complex. But I am lazy; I don't want to work that hard. So I bypassed all the passing parameter stuff in the usual way. What I did, is I create a text file with the parameters in it before calling AlphaWave through a Process.Start method in my Visual Studio 2010 VB application. In this particular case, the call was on a button click event. The WriteAllText line is what creates a text file and populates it with a string (the second parameter). In this particular case, the string "Sisyphus" indicating the application that is calling AlphaWave.

The Process.Start line fires up the AlphaWave application itself, so from the perspective of the calling application, the job is done. A parameter has been "passed" by writing to the text file, and the AlphaWave application started.
In AlphaWave, I needed to set the startup object to be Sub Main and have that look for the text file with the parameters in it before displaying the form itself. If the parameter text file exists, I read its contents as the equivalent of a passed parameter, kill the file, then continue processing the AlphaWave form startup. If the text file doesn't exist, the code bypasses it all and I just don't have the parameters loaded into strParameters for launching AlphaWave. Simple and fast enough.


The additional code dealing with the cAppVersion is how I display the version number of the app. If it's deployed as a ClickOnce, it reads the data from the Deployment object. If it isn't, I am working in my own dev environment, and I display that.

The second problem I had to solve was calling the app from inside VBA code. AlphaWave's raison d'être is to serve as a feedback process and – in addition to stand-alone applications – there is a lot of custom code in MS Word for which I need feedback.

The "Process.Start" method is not available in VBA, and the Shell command only works against .com, .bat or .exe files. A conundrum in that it appears there isn't a way to launch a clickOnce application from within a MS Office application. Why? A ClickOnce application is a completely different animal with an extension of appref.ms. Another search across the web pretty much resulted in nothing.

After thinking a bit, I cleverly (I think) came up with a clickOnce Application Launcher... application. It is a console app, taking as a passed argument the path to the clickOnce application I want to start. Since it can easily take in an argument, AND it can also launch appref.ms files, the problem was solved. Below is the ENTIRE contents of the clickLauncher application:

Here is the VBA code that is used to call the app launcher to instantiate a ClickOnce app. You will need a reference to the ShellExecute function at the top of your module: