The hard part of code reuse has always been finding that elusive snippet of code that's buried somewhere among hundreds of modules in different databases. This article introduces an easy-to-use library add-in that allows you to quickly access your favorite procedures, plus a look at using the Windows clipboard.
Programmer's déjà vu: You know that you've already written the code that you're about to write. The problem is that you wrote it for some other application somewhere, and you don't know where it is. You could track down the code snippet that you want to reuse with the Windows 95 Find utility (if you can remember the database name). But, while this solution allows you to find the desired MDB file, you're still left with the task of opening the MDB file, carrying out a second search from within the module window, and then copying and pasting the code to your application. As a more elegant solution, I decided to write an add-in to store my library code.
I'm sure that you have certain procedures that you use in the majority of your development projects, and there are many more procedures or pet snippets of code that you'd like to keep on hand. Code that you're going to reuse has to have some characteristics. The most important thing about library code that you're going to be using over and over again is that you want it to be of a high standard. At the very least, you expect the code to compile without error, but beyond this you also want the code to be well laid-out, include a consistent approach to error handling, and contain a comprehensive header that documents the code.
If your procedure header does include useful formation about each procedure, it would be helpful if this information could be used to catalog the code automatically and support the process of finding it again. For this reason, I decided to design a library to work closely with the Top&Tail add-in that I introduced in the January 1999 issue of Smart Access (see "Keeping up Appearances").
Using the add-in
The library is an MDA file that you can install using the Add-In Manager from the Tools | Add-Ins | Add-In Manager menu. The add-in contains a few demonstration procedures that allow you to see how the library works. When you run the add-in, you're presented with an overview form listing the details of each library entry (see Figure 1).
The library stores archived code as text in a table, which allows you to store code snippets, procedures, or even whole modules as a single library item. You can sort the library overview by clicking on any of the column headings to quickly find a piece of code, or press the Find button to access a simple search form (see Figure 2).
Once you've found the code that you want to use, clicking on the Details button will take you to a detailed view of the library entry that you've selected. This form allows you to browse through the code, as well as giving you some more catalog data on the routine (see Figure 3).
If you're happy with the selected code, all you need to do is click on the Copy to Clipboard button to move the library entry to the Windows clipboard. You can then return to your Module window to paste the code into your application.
Adding your own code
The first step in adding code to the library is to ensure that you aren't propagating buggy code! Once you're sure your routine compiles, you should also make sure that it contains error-handling code and a header block. If you're using my Top&Tail add-in mentioned previously, these two items will already have been done for you.
Once the code is ready to be added, just select the code and copy it to the clipboard. You can select a section of a routine (maybe a series of API or constant declarations), a complete function or sub-procedure, or an entire module if you want to keep several procedures together. Once the code has been copied to the clipboard, just start the add-in and press the Paste button to add the code to the library. The next, and most important, part is to comprehensively catalog the entry so that you can find it again!
For those of you who are using Top&Tail, the process is a lot simpler. Instead of pressing the Paste button, you should press the Paste-Special button. The Paste-Special button assumes that the routine contains the Top&Tail standard header and will use the information in it to automatically populate the fields displayed in the library's Code Details form. If you want to use the Paste-Special button but don't want to use the Top&Tail add-in, you should include the following header categories in your routines (these strings are recognized by the parsing code behind the Paste-Special button). Note that each string ends with a colon and must begin on a new line:
'Date Created :
Behind the scenes
The core code behind both this add-in and the Top&Tail add-in is a series of procedures that take programmatic control of Windows. There are three ways that this can be achieved:
|•||the Send Keys macro action -- the easiest way, but might not always be reliable|
|•||the DoMenuItem macro action, using a command like this: |
DoCmd.DoMenuItem acFormBar, acEditMenu, acPaste, , acMenuVer70
|•||taking programmatic control of the clipboard using 32-bit API calls|
The method you choose will depend upon the context of the application -- API calls require the most code but offer the highest level of control and so are appropriate for an add-in such as Top&Tail. The Code Library add-in, on the other hand, uses the clipboard at a much more basic level, and in this context, DoMenuItem does just fine. As a number of people have asked me about controlling the clipboard using the API, I thought it would be useful to revisit the code that I use to do this.
The two functions that I wrote to perform these operations are pas_GetClipboard and pas_SetClipboard. The functions depend on a number of declarations that you can get from the Top&Tail.MDA, which is included in the accompanying Download file along with the library itself.
The code that retrieves the contents of the clipboard runs as follows (I've removed the error handling to make the code easier to read):
'first, open the clipboard
If pas_OpenClipboard(0&) <> 0 Then
'Obtain the handle to the global memory
'block that's referencing the text
hClipMemory = pas_GetClipboardData(pas_CF_TEXT)
'Lock Clipboard memory so we can reference
'the actual data string
lpClipMemory = pas_GlobalLock(hClipMemory)
MyString = Space$(pas_MAXSIZE)
lngRetVal = pas_lstrcpy(MyString, lpClipMemory)
lngRetVal = pas_GlobalUnlock(hClipMemory)
'Trim the null-terminating character
MyString = Mid(MyString, 1, InStr(1, _
MyString, Chr$(0), 0) - 1)
To put your own content into the clipboard, you'd use the following code (again, I've removed all the error handling):
' Allocate moveable global memory
lngGlobalMemory = pas_GlobalAlloc(pas_GHND, _
Len(MyString) + 1)
' Lock the block to get a far pointer to this memory
lngGlobalMemoryFP = pas_GlobalLock(lngGlobalMemory)
' Copy the string to this global memory
lngGlobalMemoryFP = _
' Unlock the memory
If pas_GlobalUnlock(lngGlobalMemory) = 0 Then
' Open the clipboard to copy data to
If pas_OpenClipboard(0&) <> 0 Then
' Clear the clipboard
lngRetVal = pas_EmptyClipboard()
' Copy the data to the clipboard
There are a great number of possible applications of these procedures, and if used judiciously they can add a great deal to your projects. You'll need to be aware of the unwritten rules that govern the use of the clipboard, which are discussed in the sidebar, "Clipboard Etiquette."
This add-in reduces each library addition to a single entry in a table. The simplicity of this structure lends itself to easy scalability. The add-in could, with a little revision, be modified to allow a development team to share standard code libraries. Beyond this, the next progression of this concept would be for organizations to share code through Active Server Pages that would allow the code to be reused and standardized on a much wider scale.
Your download file is called ACCLIB.ZIP in the file SA199903_down.zip
This is found in theon this page
Sidebar: Clipboard Etiquette
One of the fundamental factors of good programming practice is to know what's yours to control and, more importantly, what isn't yours to take control of in the first place. As a VBA programmer, you're caught between the user-controlled environment and Windows itself. Access and VBA handle most of the Windows interface for you, but as soon as you start to use the Windows API calls, you're starting to interface directly with Windows itself. While poorly written VBA code might cause an error message, a poorly defined API call can cause your computer to crash.
Less dramatic -- but equally irritating for your users -- are aspects of a program that take away their perceived control of the application. One of the classic mistakes of Access and VB programmers is over-using modal forms. When there are multiple windows on the screen, the user expects, and should be given, full control of each window concurrently. Modal forms, by locking all other windows, prevent this from happening and should be used only where necessary.
The clipboard is a tool that's used across many windows while being controlled by the user. When using the clipboard API functions, you're overwriting the user's clipboard contents and so committing the cardinal sin of causing data loss. Does this mean that you shouldn't use such API calls? My feeling is that a correct and courteous warning to the user that the clipboard is going to be overwritten is an essential part of using the calls. The user must also be given a chance to accept (or decline) the use of the clipboard. Alternatively, the user should directly control the process (by pressing a button explicitly marked Copy to Clipboard, for instance). With these rules in mind, the clipboard can offer programmers a useful and flexible tool.