Utility Library (VB/VBA)

Images

2007-12-31T12:59:00.000-08:00

(See the full text with images at http://www.chrisgreaves.com/BlogUtils/)
Available in my UW.DOT library.

"edit undo" for a GUI form

2007-09-18T04:32:00.000-07:00

http://www.chrisgreaves.com/Downloads/GUIundo.zip is a link to a ZIPped file you can download, inspect and adapt.
It represents a simple model for providing an Edit-Undo facility on GUI forms.

In my real-world situation we had a GUI form with several interlocking options; changing this and that meant that those were modified.

I decided to permit the user to reverse successive decisions back to a valid restart point.

New Releases

2007-08-20T06:45:00.001-07:00

I have uploaded new versions of both UW.dot and UX.xla on the downloads page http://www.chrisgreaves.com/Downloads/

As well, both zip files contain an upgraded version of my Setup.dot which now farms out XLS to your XLStart folder and XLA to your Addins folder. Neat!

Frames

2007-01-23T03:13:00.000-08:00

Word does not provide a frame-management facility. Frames were orphaned with the release of Word97.

Legacy documents and legacy habits continue to provoke problems with frames.

These procedures attempt to reduce the frustration associated with frames.

As a minimum, we wish that Word’s Edit-Replace dialogue provided an option to replace frames in the sense that we can replace section breaks and other non-text items.

We will provide ability to locate and select the next frame, to clear all frames, to count the frames in a document.

Replacement of a frame prompts us to ask what makes sense as a replacement?

The procedures supplied allow the developer to:

Count frames within a range
Select the first frame in a range (GoTo)
Select the last frame in a range (GoTo)
Move forward one frame
Move backward one frame
Delete all frames within a range (except those frames that lie within tables).

These procedures could form the basis of a GUI form similar to Word's Edit, Replace.

Public Function DeleteFrames(rng As Range)

Public Function lngCountFrames(rng As Range) As Long

Public Function SelectBackwardFrame(ByVal rng As Range)

Public Function SelectFirstFrame(rng As Range)

Public Function SelectForwardFrame(ByVal rng As Range)

Public Function SelectLastFrame(rng As Range)

Here is a crude attempt to circumvent the frame/table problem:

Public Function DeleteFrames(rng As Range)
Dim lng As Long
For lng = rng.Frames.Count To 1 Step -1
If rng.Frames(lng).Range.Information(wdWithInTable) Then
' MsgBox "I cannot delete frames within tables"
Selection.Rows.ConvertToText Separator:=wdSeparateByTabs, NestedTables:=True
rng.Frames(lng).Delete
Selection.ConvertToTable Separator:=wdSeparateByTabs, Format:=wdTableFormatNone
Else
rng.Frames(lng).Delete
End If
Next lng
End Function
Sub TESTDeleteFrames()
Call DeleteFrames(Selection.Range)
End Sub

New release

2007-01-09T04:00:00.000-08:00

I have uploaded UW350.dot.
You can download it from my downloads page at http://www.chrisgreaves.com/Downloads/

New release

2006-11-22T16:46:00.000-08:00

I have uploaded UW345.dot.
You can download it from my downloads page at http://www.chrisgreaves.com/Downloads/

New release

2006-11-17T18:48:00.000-08:00

I have uploaded UW345.dot.
You can download it from my downloads page at http://www.chrisgreaves.com/Downloads/

Sectioning an INI file

2006-11-02T13:02:00.000-08:00

Nothing complex about it. (But see also Using An INI File).

We want to store data that reflects our run-time in a file quite separate from the program. INI files have been around for a long time. They are text files, and a reasonably competent user can edit them. A reasonably competent designer (like you!) can build a GUI form for a user interface.

Our application has two distinct phases the CORE and the RIM, and we would like to park INI keys and values in separate sections, one section to be called [CORE] and the other section to be called [RIM].

In the sample code below I have used literal character strings; you will, of course, prefer to use string constants.

References

Set a reference to the utility library UW.DOT from within your project.

Constants

In your constants module, declare two public constants:
Public Const strcApplication As String = "MyTest"

Program code

In a module write a very simple test macro:

Sub test()
Dim strEnv As String
strEnv = UW.strEnvironmentFactual(strcApplication)
MsgBox UW.strprofilein(strEnv, "CORE", "1stKey", "1stValue")
MsgBox UW.strprofilein(strEnv, "RIM", "1stKey", "2ndValue")
End Sub

Testing the code

Run the little test macro.

You will observe pop-up message boxes bearing the strings "1stValue" and "2ndValue".
The INI file
In your Documents and settings folder locate the file Utils.INI.

Its contents should look like this:
[CORE]
1stKey=1stValue
[RIM]
1stKey=2ndValue

Browsers

2006-10-20T06:06:00.000-07:00

So called because they browse for files; or more correctly, they allow the end-user to browse for files and folders.

strBrowseFile

Return the Drive, Path, Name and Extent of a chosen file.

Sub TESTstrBrowseFile()
MsgBox UW.Browsers.strBrowseFile
End Sub

StrBrowseFolder

Return the Drive and Path of a chosen folder

Sub TESTstrBrowseFolder()
MsgBox UW.Browsers.strBrowseFolder("Please choose a folder")
End Sub

Return the Drive and Path of a chosen folder starting from a specific root folder.

Sub TESTstrBrowseFolderNewRoot()
MsgBox UW.Browsers.strBrowseFolder("Please choose a folder", "c:\Greaves")
End Sub

DPNE

2006-09-14T07:03:00.000-07:00

Short for Drive, Path, Name, Extent.

This module holds several low-level and high-level functions that parse a string reported to be that of a file name under Windows/DOS Operating systems.

Here I will describe the high-level functions, the ones you are most likely to use. If you would like to know more about the low-level functions which return pointers to a parsed string, please contact me.

strGetPath
Given a string, Return the Path Item.

Sub TESTstrGetPath()
MsgBox strGetPath("c:\autoexec.bat")
MsgBox strGetPath("D:\Greaves\Admin\Web\Driving\BRAKING.DOC")
End Sub

strGetNameExtent
Given a string, return the name and extent portion

strGetName
Given a string, return the name portion

strGetExtent
Given a string, return the extent portion

strGetDrivePath
Given a string, return the drive and path portions

strGetDrive
Given a string, return the drive portions

strFixPath
Ensure that the result has one and only one trailing, right-hand separator.

A Little Problem ….

2006-09-09T08:03:00.000-07:00

A colleague attempted to download and install three applications form my download page (http://www.chrisgreaves.com/Downloads/) yesterday and ran into two sorts of problems. The first was a rogue virus-checker, but the second was caused by me.

I use a batch file to PKZip25 the zip file for download. The batch file obtains the current production version of UW.DOT from my word startup folder.

Where an application hasn't been updated for a long time, it will have a stale version of UW.DOT within the zip file, and I think some unzippers will timestamp this file with today's date as the file is unzipped and written to a folder. This version of UW.DOT now being "more recent" than any other version is now installed, and so a current application is running with a stale version of UW.DOT.

My solution to this problem is tedious, but seems to work. When I release a new version of any application, I will re-zip each downloadable file with the latest UW.DOT.

In consequence you will see the timestamp on the files seem awfully current, but at least every download will have a current copy of UW.DOT.

DelayMe

2006-09-03T08:09:00.000-07:00

This not a clever function. It will loop, consuming CPU time, until the specified time is elapsed. None the less is does provide a primitive one-time, short-time pause while your user contemplates the status of, say, a document thrown on the screen for a short interval.

If you want to have the CPU carry on processing during your brief delay, you should use a function other than this.

Sub test()
Call UW.WD_Miscellaneous.DelayMe(6)
MsgBox "here"
End Sub

Excel

2006-08-30T02:47:00.000-07:00

The Excel utility library is available as an add-in file with an extension of XLA. Store the file in an appropriate folder, then from Excel choose Tools, Add-Ins, Browse, locate the file, make sure it is checked ON, confirm and use.

How use?

From an empty cell, start a formula with the "equals" sign, use the function-picker ("fx" button), Function category "User defined", and scroll to "strDateTime".

Confirm out of there and observe he dynamic date and time, formatted as a 14-character string, in your cell. OK, Big deal, you have the Now() function in Excel.

Try the strAlphaDigitsOnly function; point it to a cell containing a mixed bag of characters:

Try obtaining your run-time parameters from an INI file:

Knock, as we say, yourself our!

You can now use Excel to test if a file is a Word Perfect DOS 5.1 file!

Two new releases

2006-08-23T03:39:00.000-07:00

I have added links to my Downloads page.

You can now download the latest revision of the stand-alone files that contain my Microsoft Word utility library and Microsoft Excel utility library.

Constants

2006-08-15T10:33:00.000-07:00

Good programming practices suggest the use of compile-time constants rather than literal strings.

Instead of writing:
strWorkFile = strWorkFile & ".TXT"

We write:

strWorkFile = strWorkFile & strcExtentTxt

And include a declaration:

Public Const strcExtentTxt As String = ".TXT"

This may seem like extra work - replacing one line of code with two lines, but think again.

By placing the character string in a constant and using the identifier for reference, we are assured of a common and consistent definition of the string, and have avoided the costly maintenance problem of a typo in one of perhaps twenty occurrences of the string throughout or program.

As well, we can convert the compile-time constant to an installation or a run-time constant with a small modification, and will not be required to search through the program looking for occurrences of the literal string.

You can draw on many years of established consistency by using the string constants defined in UW.DOT.

Use Intellisense to draw out identifiers that are prefaced with "strc" (as in "str"ing "c"onstant)
Sub test()
MsgBox UW.strcAlpha
MsgBox UW.strcAlphaDigits
MsgBox UW.StrcDigits
MsgBox UW.strcDriveSeparator
End Sub

ClipBoard

2006-07-28T06:43:00.000-07:00

Five clipboard functions are provided. Two of them duplicate the action performed by the keyboard sequence PrtScr and Alt-PrtScr. Three of them deal with text data.

ActiveScreenToClipboard

Takes a snapshot of the current screen. Equivalent to tapping the PrtScr key.

You can use this in reporting errors or the status of a running program.

Once you have trapped the run-time error, issue a call to ActiveScreenToClipboard and at least have the user paste the results into Paintbrush. Better yet, use a routine to capture the clipboard image as a bitmap or other graphic file.

Sub TESTActiveScreenToClipboard()
Call ActiveScreenToClipboard
End Sub

ActiveWindowToClipboard

Takes a snapshot of the current window. Equivalent to tapping the Alt-PrtScr key combination.

You can use this in reporting errors or the status of a running program.

Once you have trapped the run-time error, issue a call to ActiveWindowToClipboard and at least have the user paste the results into Paintbrush. Better yet, use a routine to capture the clipboard image as a bitmap or other graphic file.

I have had little success with this function, the equivalent of Alt-PrtScr, under Windows XP

Sub TESTActiveWindowToClipboard()
Call ActiveWindowToClipboard
End Sub

ClearClipboard

Returns any text stored in the clipboard before emptying the clipboard.

Sub TESTstrClearClipboard()
MsgBox strClearClipboard
End Sub

StrAppendToClipboard

Returns any text stored in the clipboard before appending the supplied string to the clipboard.

Sub TESTstrAppendToClipboard()
MsgBox strAppendToClipboard("v")
MsgBox strAppendToClipboard("w")
MsgBox strAppendToClipboard("x")
MsgBox strClearClipboard
MsgBox strAppendToClipboard("y")
MsgBox strAppendToClipboard("z")
End Sub

strLoadToClipboard

Returns any text stored in the clipboard before loading the supplied string to the clipboard.

Sub TESTstrLoadToClipboard()
MsgBox strLoadToClipboard("b")
MsgBox strLoadToClipboard("c")
MsgBox strLoadToClipboard("d")
End Sub

Fun 4 X

2006-07-23T16:09:00.000-07:00

Also known as “Fun For All”.

I had reason to demonstrate my ability to switch languages (English, French, Spanish etc.) on the Control Tip Text on controls on a GUI form.

I decided to demonstrate by changing the established English-language tips to whimsical bumper stickers.

At the same time I was lecturing to classes in a sunny room, and students couldn’t see the GUI forms.

I decided to introduce random colour schemes.

Then I got carried away and decided that the fonts and mouse pointers could do with a touch-up.

The result is Fun4X.

To see how it works, introduce a new User Form and place four command controls on the form.

I haven’t bothered to rename or captioon them, for reasons which will appear below.

Set a refernce to UW.DOT.

Double-click on a command button and write the code for that button, close, double-click on the next, write, close and so on until all 4 command buttons have code and then write the userform initialization code, as set out below.

Private Sub CommandButton1_Click()
Call UW.cmdColoursClick(Me)
End Sub
Private Sub CommandButton2_Click()
Call UW.cmdFontsClick(Me)
End Sub
Private Sub CommandButton3_Click()
Call UW.cmdIconsClick(Me)
End Sub
Private Sub CommandButton4_Click()
Call UW.cmdCaptionsClick(Me)
End Sub
Private Sub UserForm_Initialize()
Call UW.UserFormInitialize(Me)
End Sub

Run the user form (F5 function key), and you’ll see this:

I had hooked the top-left control to “Colours”, and the bottom left control to “Captions”.

Click the Colours control repeatedly and watch the pretty random colours.

Click the Captions control once, then inspect your Control Tip Text.

If you have icon files (*.ICO) on your root drive C:, you can switch mouse pointers.

You can change font face and size, too.

Once I am sure the controls are working, I resize them to be 4-pixel square “rivets” in each corner of the GUI form.

That’s why I don’t need captions!

Using an INI file

2006-07-14T04:46:00.000-07:00

Nothing complex about it.

We want to store data that reflects our run-time in a file quite separate from the program. INI files have been around for a long time. They are text files, and a reasonably competent user can edit them. A reasonably competent designer (like you!) can build a GUI form for a user interface.

Let us start by writing a small chunk of application code to demonstrate using an INI file.

References

Set a reference to the utility library UW.DOT from within your project.

Constants

In your constants module, declare two public constants:

Public Const strcDriveLetter As String = "DriveLetter"
Public Const strcDriveLetterDefault As String = "C"

Program code

In a module write a very simple test macro:

Sub test()
MsgBox UW.UT_ProfileStrings.strGPA(Publics.strcDriveLetter, Publics.strcDriveLetterDefault)
End Sub

You can, of course, omit the explicit module names:

Sub test()
MsgBox strGPA(strcDriveLetter, strcDriveLetterDefault)
End Sub

Testing the code

Run the little test macro.

You will observe a pop-up message box bearing the letter "C".
The INI file

In your Documents and settings folder locate the file Utils.INI.

Its contents should look like this:
[Utils]
DriveLetter=C

Changing the INI file

While you have the INI file open in Notepad, change the drive letter to be "D".

Re run the little test macro and observe the pop-up message box bearing the letter "D"
How it works

The procedure strGPA looks for a suitable INI file. If it doesn't find one, it builds one!

Within that file it looks for a key as stated in the first parameter of the call to strGPA; in our case the first parameter is "strcDriveLetter", so strGPA looks for a key "DriveLetter".

If the given parameter can not be found, strGPA creates a key and value using the second parameter of the call.; in our case the second parameter was "strcDriveLetterDefault" so the value defaults to "C", and that value is written to the INI file.

What is so good about all this?

I am glad you asked.

First off, you don't need to worry about the INI file. Just define your key names and default values, and strGPA does the rest.

Next, your user interface can include a Âpanic" button; resetting an INI file to default values is as simple as deleting the INI file. Your panic button merely KILLs the INI file, and each subsequent call will store and return the default values.

There's more Â a strPPA function for Putting parameters to an INI file, section management, and the use of value strings much longer than the Microsoft limit of 255 characters.

As well the source code is switchable between registry, regular INI and long-value INI file methods.

More later

Logging events

2006-06-25T08:01:00.000-07:00

Many of my applications involve processing huge (20,000+) numbers of document or template files.

Often some of these documents will baulk the process - especially documents with forms, ASK fields, or templates with stale references. For these applications I maintin an Exclusion file - a list of files I ought not to process; more on that in the next post.

For now I'll explain one method of dealing with awkward files.

I maintain a log file of events:
If os.strLogFile = strcTrue Then
Call Logger("ProcessProjectsOfFolder with " & strProjectList)
Else
End If

The slave function Logger obtains a valid name that will point into my Documents And settings folder:
Public Function Logger(strMsg As String)
Dim strLogFile As String
strLogFile = UW.STRFIXPATH(UW.strEnvironmentFactual(strcApplication))
strLogFile = strLogFile & "LogFile.txt"
Call logfile(strLogFile, strMsg)
End Function

The slave function LogFile prints to a file:
Public Function LogFile(strFile As String, strMsg As String)
' Procedure : LogFile
' Description: Time stamp a message string to a file.
' Copyright: Christopher Greaves
' Inputs: The local or full file name, a string.
' Returns: None.
' Assumes: None.
' Side Effects: None.
' Tested: By the calls shown below.
Call PrintFile(strFile, strDateTime & vbTab & strMsg)
'Sub TESTLogFile()
' Call LogFile("erase.txt", "Here is a first message") ' stored in macroContainer folder
' Call LogFile("c:\erase.txt", "Here is a second message") ' stored in boot root folder
' Call LogFile(MacroContainer.Path & Application.PathSeparator & "erase.txt", "Here is a third message")
'End Sub
End Function

While printFile merely opens, appends and closes a text file:
Public Function PrintFile(strFile As String, strMsg As String)
' Procedure : PrintFile
' Description: Print a message to a file.
' Copyright: Christopher Greaves
' Inputs: The local or full file name, a string.
' Returns: None.
' Assumes: None.
' Side Effects: None.
' Tested: By the calls shown below.
Dim intFile As Integer
intFile = FreeFile
Dim strWorkFile As String
strWorkFile = strFile
' add an extent if there is not one yet.
If InStr(1, strFile, strcExtentSeparatorDefaultValue) > 0 Then
Else
strWorkFile = strWorkFile & strcExtentTxt
End If
' add a path if there is not one yet.
If InStr(1, strFile, Application.PathSeparator) > 0 Then
Else
strWorkFile = strMacroContainerPathValue & strWorkFile
End If
Open strWorkFile For Append As #intFile
Print #intFile, strMsg
Close #intFile
End Function

That's an awful lot of code to say "I've started!", but note that PrintFile, and hence Logger, will have closed the file before the next statement is executed, so that no matter how the application crashes, I'll have a record of the file that triggered the failure, and can deal with it.

Parsing Strings

2006-06-13T09:40:00.000-07:00

Available in my UW.DOT and UX.XLS libraries.
Nothing complex about it. We are often asked to split or parse strings into sub-strings.
Typically we receive a series of words separated by spaces or the comma character; sometimes separated by the tab character (vbTab in VBA)
We can peel off consecutive substrings in this manner:
Sub ParseStrings()
Dim strMyString As String
strMyString = "Nothing complex about it."
While Len(strMyString) > 0
MsgBox UW.strSplitAt(strMyString, " ")
Wend
End Sub
Of course, we need to know in advance what character to use as a delimiter.
Smart programming suggests that we adopt a convention of building a string of substrings by placing the delimiter as the first or left-most character:
strMyString = " Nothing complex about it."
Now we can write a parsing routine that is independent of the delimiter:
Sub ParseStrings()
Dim strMyString As String
strMyString = " Nothing complex about it."
Dim strDelimiter As String
strDelimiter = Left$(strMyString, 1)
While Len(strMyString) > 0
MsgBox UW.strSplitAt(strMyString, strDelimiter)
Wend
End Sub

Read more at http://www.chrisgreaves.com/BlogUtils/ParsingStrings.html)

Network Paths

2006-05-26T06:46:00.000-07:00

Available in my UW.DOT and UX.XLS libraries.

In developing an archiving tool, I find a need to manipulate paths. Specifically I want to “spray” several copies of each file to a set of locations, like this:

MsgBox strArchiveToSites("b:\ShellLnk\Module1.bas", _
",b:\archive,\\BBB\Boot80GB (C),\\BBB\A,\\BBB\d", 0)

“BBB” is my Big beige Box (as distinct from LapTop).
Drive C is my R&D drive.
Drive D is the removable 400 GB backup drive on rails.
Drive A is the floppy disk on the BBB; I’m testing, OK? (grin!)

The procedure strArchiveToSites is to copy the Module1.BAS file to each of those areas, and so it may need to create folders (MkDir) on networked drives. VBA can’t cope with this, so I need to assign a drive letter temporarily while I do my stuff.

Because I’ll be issuing the “copy” command repeatedly for the same set of network paths, it makes sense to map each network path to a unique drive letter, copy all the files using the drive letter assignments, and then disconnect the drive letters.

In the example below I’ve shown how I obtain a drive letter for a single path, do My Stuff, and then disconnect.

Sub TESTstrPathToDriveMap()
Dim strTempDriveLetter As String
strTempDriveLetter = strPathToDriveMap("\\Bigbeigebox\Boot80GB (C)", "")
''' do stuff below here
MkDir strTempDriveLetter & "\" & Format(Time(), "hhmmss")
''' do stuff above here
strTempDriveLetter = strPathToDriveUnMap(strTempDriveLetter)
End Sub

In the real world, the pseudo-code would look like this:

Get drive letters for each networked path
For each file to be processed
Call the strArchiveToSites procedure using the mapped drives.
Release the mapped drive letters.

To see how these two functions work for you, paste the macro TESTstrPathToDriveMap into a module, change the literal network path, run the macro, and check that a new folder has been created on your network drive.

Available in my UW.DOT and UX.XLS libraries.

About This Blog

2006-05-25T10:37:00.000-07:00

I develop applications, primarily in Visual Basic for Applications (VBA), and primarily using Microsoft Word as a vehicle, but extending to Excel-VBA, PowerPoint-VBA and Access-VBA.

My background in application development has taught me to use libraries of utility procedures to speed up my development.

This blog documents the new and the more commonly-used existing functions that can be found in my UW.DOT and UX.XLS libraries.

You can download the most current copy of those files by clicking on the links.

In some examples you will see a small subroutine “TEST”, commented out. I usually drag this commented test out of the procedure body and de-comment it as a built-in self-testing procedure.

[ Home ] [ Contact Me ]