Category: Coding

Counting Occurrences of One String in Another Using VBA

5/14/2013

I needed to determine the number of times one particular string occurred as part of a larger string. Specifically, I needed to determine how many tabs were at the start of a line of text in order to convert to a properly nested indent (i.e., for each tab, I needed to indent .5").

Poking around, I knew I could parse out the string and iterate through a counter for each time I found the string. Cumbersome, but doable. I'm lazy, and didn't want to type that many lines of code, so I thought about it a little more and thought the built in Split() function could be leveraged to do what I need.

Indeed, that was the case and the resulting code is shown below:

Public Function CountCharacter(ByVal theString As String, ByVal strSearchChar As String) As Integer
Dim iCount As Integer
Dim myarray() As String
     myarray = Split(theString, strSearchChar)
     iCount = UBound(myarray)
     CountCharacter = iCount
End Function

The code does exactly what I hoped, is case-sensitive, and can be used to count the number of occurrences of any text element in any other text element. In my specific case, to search for the number of tabs at the start of a line, I used a range object to identify the text and passed the vbTab constant as the search character:

MsgBox "The number of tabs in the line is " & CountCharacters(myRange.Text, vbTab)

Of course, a selection object's text attribute can be passed as well, or any string variables if you've already done some processing on a piece of text from a document.

The end result is a fast, clean and small amount of code. That, I liked. If you are interested, the code that converted the tabs to indents was equally small (altRange is the string from the start of the line to the first non-tab character, and wholeRange is the paragraph where altRange is found):

i = CountCharacter(altRange.Text, vbTab)
altRange.Delete
wholeRange.ParagraphFormat.LeftIndent = InchesToPoints(0.5 * i)

0 Comments

Binding a keystroke to a button action in VB.Net

12/20/2012

0 Comments

That sounds... ugly? Simple? Why bother?

I understand the use of the ampersand ("&") combined with a button's UseMnemonic property will enable the keystroke combination of Alt+ whatever letter trails the "&" in the button's text property (e.g., if ButtonAdd.Text = "&Add New..." pressing Alt+A will trigger). But what to do if your users want a keystroke combination that doesn't include Alt? Or a keystroke combination that doesn't include any letter in the button's text property?

I faced this situation, and did what I always do: I queried Uncle Google. After a few false trails, I eventually stumbled across a MSDN article discussing how to bind keystrokes in a datagrid. Cool. But after reading it, I thought it was overkill for what I wanted to do.

Then I thought about how easy it is to bind a keystroke to a menu item through the menu item's ShortcutKey property. And how easy it is to make a menu and/or individual items invisible with the Visible property... Voila!

The KISS (Keep It Simple Steve) solution was to create a new, hidden menu that only contains items for actions I wanted to have a button triggered by a keystroke (e.g., HiddenAdd, HiddenEdit, etc.). I then bound the menu actions to keystrokes through each menu item's ShortcutKey property.

Then, I created the necessary events in the code behind the form to make both events (menu item click event triggered by the keystroke combination and the button click event) point to a common Sub (or function) to perform whatever actions are needed.

Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click
Call doAddButtonClick()
End Sub

Sub doAddButtonClick()
'Put whatever code needs to occur here. It's now called from button click and whatever keyboard shortcut you assigned to the hidden menu item.
End Sub

Private Sub HiddenMenuAdd_Click(sender As System.Object, e As System.EventArgs) Handles HiddenMenuAdd.Click
Call doAddButtonClick()
End Sub

The end result is the equivalent of a lazy-man's binding a keystroke to a button, without a lot of extra effort and code overhead.

0 Comments

SQL Query Tool

11/20/2012

1 Comment

As promised, I now present the SQL Query tool I built for my users. Dubbed "Comstock," it is an ad hoc query tool that enables the user to use drop-down lists to create data queries against our databases. In this way, someone with limited or no SQL experience can perform searches without the necessity of having the suite of SQL tools installed on the computer.

Additionally, the queries generated can be saved to be run later. Also, more complicated queries can be generated by someone with more SQL experience, and those queries saved for others’ use. Since Comstock isn't tied to any particular application or set of tables, it's more flexible for the user to jump between retrieving data from different databases easier and more nimble.

For example, one query can be searching for particular information from Database 1, immediately followed by a query for other information against Database 2, and then a query against a third database. Sure, each could be created as a report in their respective applications, but this doesn't require a report to be built, or the individual applications to be opened.

The query string in the Query field is built on the fly as the selections from the drop-downs are made. The contents of each drop down is dynamically created based on the previous selection, i.e., select a database, and the "From Table" drop down is populated with tables from that database, select a table, and the "Select" and "Where" drop downs reflect the columns of the selected table.

If more than the single field in the original drop down “Select” is wanted, selecting another field will append each to the Select portion of the query. That way, if the user wants multiple fields, selecting each from the drop down will add them. The query field is also editable, so additional mods can be made to the query as well.

Dealing with the saved queries is pretty straightforward. Click the Open Query button gives the user this:

From this screen the user selects a query they want to run, or delete one. The only queries they can delete are ones they originally authored. When a saved query is open, the information about that query is shown on the screen:

Saving a query involves naming, providing a description and identifying any additional users that are to have access to the query.

When a query is executed a small validation routine is run to ensure something drastic like Drop Table isn't in there. This app is designed to be a data extractor, not a data terminator!

When the results are displayed in the datagrid at the bottom, the user can sort by any column. Also, using the output buttons, the data can be extracted to a text file or the clipboard for repurposing. One bit of trickery there is that if the user has selected any rows, only those rows are exported/copied, providing a bit of selectivity.

I think it's pretty simple and straightforward for the end user, which means it might get used, and enable some "can you find this data for me?" questions to be answered by the users themselves.

Thoughts? Comments welcome.

1 Comment

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 "-".

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!

codeoutput.dotm
File Size:	62 kb
File Type:	dotm

Download File

0 Comments

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

When 4% is a lot

10/25/2010

0 Comments

The code for the bill drafting and other legal aspects of the Kansas Legislature project I have been working on for the last 3 years (design and implementation) went into code-freeze/change control last week. This is A Good Thing.
This means the client can no longer decide to change the color of a button, or the look of a dialog without going through their own change control process. Or, at least that is the theoretical upswing of the deal.
What this also means is that I have personally - and solely - designed and coded the legislative drafting process for 4% of the legislatures in the United States. Woo! Go me! I have now done all the design and implementation code for Nevada and Kansas to create bill drafts and amendments.
What made it a more interesting and challenging process is that the word processor of choice was different for both states. Nevada was using MS Word 95 when I started the process there. I "completed" it when they were using Word 2003. I did that project as an employee, so the time constraints were not so particular.
In Kansas, the word processor of choice was OpenOffice.org's Writer. Of course, being a different word processor, all the tricks I had learned for Word were no longer effective. Incorporating critical processes from keyboard control to linking to a database for information extraction was significantly different working from Writer than it was working from Word. Each had both benefits and caveats, and at this point, I'd be hard pressed to really go out on a limb defending the use of one over the other. From the coding perspective, they both had elements that were easy to work with and abysmal patches that no amount of coding would make smooth.

What would be interesting would be to find a private business enterprise that could benefit from the kind of granular control I can exercise over a word processor. Offhand, I can see benefits to any industry that requires a rigorous and auditable trail of changes in a document: pharma, biotech, legal (obviously), environmental... Coupling my coding base within a document (the innerds), with SVN-type control over a document repository (the outerds) and the result is a far more robust and cost-effective alternative to legacy document control systems.

0 Comments

Counting Occurrences of One String in Another Using VBA

Binding a keystroke to a button action in VB.Net

SQL Query Tool

VBA Code Wrangling

VBA, ClickOnce Applications and Parameters - oh my!

When 4% is a lot

Author

Archives

Categories