Slang's Anti-Singularity
  • Slanghome
  • Slangblog
  • Slangpuzzles
    • CurrentPuzzles
    • ScholarPuzzle
    • PastPuzzles

But All I Want Is For It To Go Away

2/28/2013

0 Comments

 
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):
Original
-->(a)-->This is my text to be removed.
Marked up
-->[(a)-->This is my text to be removed.]
 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. 
What it should be
-->(a)-->This is my text to be removed.
What is happening (notice the extra space between the tab and the opening parenthesis)
--> (a)-->This is my text to be removed.
 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? 
 Note that this behavior is the same for trailing parentheses. Also, in addition to tabs, this behavior occurs if the abutting character is an em-space: Asc(8194)
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.
0 Comments

VBA Code Wrangling

7/25/2012

0 Comments

 
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 "-".
Picture
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.
Picture
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. 
Picture
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! 
codeoutput.dotm
File Size: 62 kb
File Type: dotm
Download File

0 Comments

VB6 migration... Now?

6/26/2012

1 Comment

 
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.
1 Comment

VBA, ClickOnce Applications and Parameters - oh my!

5/18/2012

0 Comments

 
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.

Const cAlphaFile As String = "C:\alphacall.txt"
Const cAlphaApp As String = "C:\ProgramData\Microsoft\Windows\Start Menu\LCB Applications\AlphaWave.appref-ms"
'What we are writing is the parameter string to be read later. In this case, it's a single line
My.Computer.FileSystem.WriteAllText(cAlphaFile, "Sisyphus", True)
Try
Process.Start(cAlphaApp)
Catch ex As Exception
MsgBox("Your application not found error message here.", MsgBoxStyle.Information)
End Try

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.

Public strParamaters as string
Dim cAlphaCall as string = "C:\alphacall.txt"
Try
strParameters = My.Computer.FileSystem.ReadAllText( cAlphaCall )
Kill( cAlphaCall )
Catch ex As Exception
End Try
' Identify the version in the app
If Deployment.Application.ApplicationDeployment.IsNetworkDeployed Then
cAppVersion = "v. " & Deployment.Application.ApplicationDeployment.CurrentDeployment.CurrentVersion.ToString
Else
cAppVersion = "Development version"
End If
theForm.lblVersion.Text = cAppVersion
Application.Run(theForm)

Calling a ClickOnce Application from VBA
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:

Module Module1
Sub Main()
Main(Environment.GetCommandLineArgs())
End Sub

Private Sub Main(ByVal args() As String)
If UBound(args) = 0 Then
MsgBox("This application is designed to be launched from VBA Code with an argument of a path to a clickonce application as follows:" & vbCrLf & vbCrLf & _
" RetVal = ShellExecute(0, ""open"", chr(34) & & chr(34), Chr(34) & & Chr(34), """", 0)" & vbCrLf & vbCrLf & _
"Double clicking it without passing an argument of the clickonce app path will do nothing other than get you this message box.")
Exit Sub

End If
Try
Process.Start(args(1))

Catch ex As Exception
MsgBox("The ClickOnce application that you are trying to launch (" & args(1).ToString & ") was not found in the expected location." & vbCrLf & _
"Please ensure it has been installed locally, and the shortcut properly located in the LCB Applications folder.", MsgBoxStyle.Information)

End Try
End Sub
End Module

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:

Private Declare Function ShellExecute Lib "Shell32.dll" Alias _
"ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub VBACallToLaunchClickOnceApp()
Dim strLauncherPath as string
Dim strAppPath as string
Dim RetValue as Long
strLauncherPath = ""'this is the path to the clickOnceLauncher Application
strAppPath = ""'this is the path to the appref.ms shortcut you want to start up
RetVal = ShellExecute(0, ""open"", chr(34) & strLauncherPath & chr(34), Chr(34) & strAppPath & Chr(34), """", 0)
End Sub

That's it. I hope someone else can find the results of my investigations useful. Have a great weekend.
0 Comments

Ribbon Wrestling and Wrangling

3/28/2012

0 Comments

 
I've been in my new digs for about a week or so, and my first and foremost task has been to migrate an "application" that I (mostly) wrote in MS Word 1997-2003 to work in a Windows 7/Office 2010 environment. On the whole, the migration is a smooth seamless process when it comes to processes in the code itself. VBA is VBA regardless of whether the base is 2003 or 2010. The issues start to arise when the time came to create the custom interfaces.

Back in the 2003 days, the way to get the specific functions in front of the user was through either:
  1. customizing the standard menu;
  2. customizing the standard toolbar(s);
  3. creating a new menu;
  4. creating a new toolbar;
As it turned out, I - and the others who worked the project over the years - did all four.

After migrating the templates and code forward to 2010, all the customizations we did still were there, but packaged in a god-awful "Add-Ins" tab (what was formerly known as a menu) at the end of the list of standard tabs. Yuck. Nobody in their right mind would think that an appropriate solution to put before the end user. I certainly couldn't do it in good conscience. So, I started poking around the web, looking for methods, instructions and tools that would help me to:
  • Eliminate the "Add-In" tab and get back to meaningful named tabs;
  • Get back to context sensitive tabs and functions based on document type (template) where only the one custom tab would be visible.
I must say, there really isn't a lot to choose from. And to make it worse, what is good to work with and useful doesn't play natively with 2010, and what is barely functional is what's needed to create 2010 ribbons. That is, unless you want to delve into the file structure itself, which I don't particularly want to.

Microsoft publishes an incredibly lame tool with the glib and catchy name of "Custom UI Editor for Microsoft Office" (hereafter to be known as CUIEMO) that is, well, functional, I guess. I picked it up here. It is needed downstream in my convoluted process. But for creating the ribbon from scratch, ugh. I really wouldn't like it.

The other tool I found is extremely useful, and intuitive. It's drawback is that it doesn't create native 2010 XML; it creates customUI.xml files rather than customUI14.xml files. The cool tool is an AddIn for Excel. Yes, Excel. To customize Word ribbons, I am using Excel. If nothing else, one must learn to be flexible when dealing with Microsoft products!

The AddIn is called the RibbonX Visual Designer, developed by Andy Pope, and is available from his website. Below are screen shots between the two tools. I'll let you choose which you think is more useful for designing your custom ribbon. 
Picture
Andy Pope's RibbonX Visual Designer
Picture
Custom UI Editor for Microsoft Office
Since one is useful for designing the ribbon and the other is necessary for creating the actual files necessary, I use the two in conjunction. Here's how.
  1. Open the file (.docm/.dotm) that needs a ribbon in CUIEMO.
  2. From the Insert Menu, select "Insert Office 2010 Custom UI Part."
  3. From the Insert Menu, select "Sample XML | Custom Tab." You should see some XML inserted. We'll be replacing that later with what you really want.
  4. If you want to add icons, select "Insert | Icons..." and add any images you want to be a part of the custom ribbon. Make note of the names, you'll need them later.
  5. Save and close.
  6. Open the .docm/.dotm in the RibbonX Visual Designer Interface.
  7. Customize to your heart's content. If you have decided to use any of the custom images you added to the file above, enter the name in the image property without the extension. If you want to use standard iconography, use the imageMso property.
  8. Click on the CustomUI XML tab.
  9. Select everything between the <ribbon> and </ribbon> tags and Copy.
  10. Save and close.
  11. Open CUIEMO again.
  12. Open the document.
  13. Select the text between the <ribbon> and </ribbon> tags and Paste.
  14. Click the Validate XML button to confirm nothing bad happened.
  15. Save and close. 

Your new ribbon is done! If you need to edit it again later, all you need to do is from Step 6 and beyond. Oh, all your callbacks are also automatically generated, so all you need to do is copy and paste those into your VBA code too. Of course, if you are comfortable simply writing XML code, you can either do it within the CUIEMO environment, but it really sucks with jumping around and flicker. Or you can use your favorite XML editor and copy/paste that too.

 Have at it. I hope this saves someone else the difficulty I had in getting to a functional, custom ribbon. 
0 Comments

    RSS Feed

    Author

    Just a guy out exploring the world. Former world-class never-was endurance runner.

    ​Hit me up, and we'll catch a beer or coffee in your town.


    Follow @slang4201

    Archives

    August 2022
    July 2022
    June 2022
    May 2022
    April 2022
    March 2022
    February 2022
    January 2022
    December 2021
    November 2021
    October 2021
    September 2021
    August 2021
    May 2021
    October 2020
    September 2020
    December 2019
    November 2019
    October 2019
    July 2019
    October 2018
    September 2018
    May 2018
    April 2018
    November 2017
    October 2017
    July 2017
    June 2017
    June 2015
    August 2014
    January 2014
    October 2013
    September 2013
    June 2013
    May 2013
    April 2013
    March 2013
    February 2013
    January 2013
    December 2012
    November 2012
    October 2012
    September 2012
    August 2012
    July 2012
    June 2012
    May 2012
    April 2012
    March 2012
    February 2012
    January 2012
    December 2011
    November 2011
    October 2011
    August 2011
    March 2011
    October 2010
    July 2010
    January 2009
    December 2008
    October 2008

    Categories

    All
    2011
    Alfama
    Alternate Energy
    Android
    Angela Sullivan
    Animals
    Antiques
    Apple
    Ash Canyon
    Astronomy
    AT&T
    Australia 2022
    Bailout
    Battery
    Bicycling
    Biometrics
    Books
    Brisbane
    Cairns
    Canary Islands
    Carrier Iq
    Carson City
    Cascais
    Centennial
    C Hill
    C-Hill
    Christmas
    Climate
    Clothing
    Coding
    Colorado
    Columbus
    Cramps
    Curiosity
    Dad
    Dardanelles Lake
    Dell
    Dick's Lake
    Dilbert
    Diving
    Eagle Lake
    Earworms
    Eating Problems
    Eclipse
    Economy
    Ecuador
    Education
    Eldorado Canyon
    El Valle
    Energy
    Errors
    Espionage
    Europe 2019
    Evi
    Fallon
    Family
    Fontanillis Lake
    Food
    Gamboa
    Garmin
    Geocaching
    Goals
    Google
    Google Earth
    Grouse Lake
    Hiking
    Inov8
    Investing
    Ipad
    Iphone
    Iron Mountain
    Lanzarote
    Legislatures
    Lisbon
    Mac
    Market
    Market Drop
    Mars
    Mctarnahan
    Medicine
    Microsoft Word
    Motivation
    Mountain Biking
    Moving Minutes
    Music
    Nevada Day
    Nfc
    Ohio
    Olympics
    Openoffice
    Opportunity
    Panama 2018
    Panama 2022
    Paper Airplane
    People
    Playa Coronado
    Politics
    Portugal
    Prison Hill
    Privacy
    Puts
    Puzzles
    Quito
    Race
    Rant
    Reno
    Retrospective
    Roosevelt
    Running
    Running Dynamics
    Saddest Cities
    Safe & Sober
    Science
    Scuba
    Shoes
    Shopping
    Sicily
    Sierra
    Sierra Canyon
    Sintra
    Slangsploration
    Snl
    Soccer
    Software
    Spasms
    Spirit
    Sullivan Canyon
    Svn
    Tahoe
    Tahoe Rim Trail
    Taormina
    Taxes
    Technology
    Transit
    Travel
    Trees
    Vba
    Velma Lakes
    Venus
    Verizon
    Violin
    Watches
    Weather
    Wolframalpha
    Words
    Wrestling
    Writing
    Xkcd
    Yawbe
    Yoga

This is ALL MINE, I tell you! copyright 2010-2022