Random Tips & Tricks for Access and/or VBA

[Banana]

I figured it would be fun to have a thread on list some various tips or tricks that wasn't quite worth a thread on its own, but could be useful in sharing, even if not particularly relevant to our needs. I like to hear about others' clever tricks as it helps me take a new look at how we can do things.

So anyway:

Creating a read-only "constant" array

VB normally cannot hold a constant array (at least one with values filled in), and some snippets use class modules just to be able to initialize the array. This is how I would create a constant array without having to worry about whether it was already initialized before calling it, or writing values to it. This can be used in a standard module:

Static Property Get foo() As Variant

Dim init As Boolean
Dim bite(2) As Byte

If Not init Then
    bite(0) = &H1
    bite(1) = &H10
    bite(2) = &H11
    init = True
    foo = bite()
Else
    foo = bite()
End If

End Property

Private Sub bar()

Dim i As Integer

For i = 0 To 2
    Debug.Print foo(i)
Next i

End Sub

EDIT:

I discovered that Constants can be in fact stored in a procedure, so here is a much more robust way, and I assume faster because we aren't swapping in and out with an array and passing a variant anymore:

Static Property Get foo(iSelect As Integer) As String

Select Case iSelect
      Case 1
           Const foo1 As String = "A"
           foo=foo1
     Case 2
           Const foo2 As String = "B"
           foo=foo2
     Case 3
           Const foo3 As String = "C"
           foo=foo3
     Case Else
          'Shouldn't be here!
          foo=""
          Err.Raise 0
End Select

End Property

 

[Banana]

Using Implements as a form of versioning control

Because VBA isn't truly object-oriented, I don't blame majority of programmers for feeling that class module is basically an overkill for most of functions they write. It did annoy me to no end when I first read about how it supports interfaces, but not inheritance, which sound very half-assed way to do it.

But there's a good use for scenarios where you need to do basically same thing in X different ways. An example would be import/export specifications for clients that may use different tools or formats across the company. One could just write out a bunch of functions for each import/export specifications, but how to keep track of whether they're all consistent?

That's where Implements can be useful. By defining an abstract class and creating class, you can streamline the work of maintaining functionality for all different specifications by using VBE to remind you whenever you add new functionality to update each class.

An abstract class would be basically a blank template with list of public procedures, but no actual codes within. Something like this:

Option Compare Database
Option Explicit

Public Property Let foo(bar as Variant) As Variant

End Property

Public Property Get foo()

End Property

Public Function Bar(foo As Variant) as Variant

End Function

Note: Don't bother with privates- no point in putting them in abstract class.

When you create a new class module to represent one import/export specifications, add "Implements [NameOfAbstractClass]" at the Declarations, then VBE will add all the public procedures to this class based on what you specified in the abstract class. Voila! Instant consistency!

Of course, it's still your responsibility to write out actual codes for each of procedures as MS hasn't quite perfected their mind-reading program (scheduled to come out sometime in next century). Therefore, whenever you want to introduce new functionality or strip away unwanted functionality, you just modify the abstract class then VBE will lead you to all functions that needs to be edited.

 

[Banana]

Implementing a .NET library for VBA

There's so many stuff out there that are ripe for taking and using. Maybe we want something to enable advanced math operations or perform cryptographic operations or interface with a service. It just happens that .NET is the current paradigm and COM is on the way out, so some cool library may not be available in COM model.

However, implementing a .NET library is fairly easy and can be done with express version of Visual Studio, provided you have the source code for the library. Here's two good primer on how to do this:

Exposing .NET to COM
Creating COM interfaces for .NET library

Now, the thing is that they require a full version of VS (at least I assume so as I don't see any templates to create a COM class) and checking the COM Interop isn't always enough to get what you need. One reason for this is that COM doesn't seem to like classes that aren't based on objects. So if a class inherited from different base type, it isn't really useable in VBA even though it may be exposed. CreateObject() or GetObject() won't work on those class.

For those who do not have full version of VS, this can be sidestepped by creating a wrapper class which just call all the methods of the inherited class, then boom! It's available in VBA for your use! Just add the type library file (extension .tlb) (Tools->References) and instantiate it just like any other object.

Finally, take a look at this, if you want to be able to use early binding and intellisense for the class called from the library. (Thanks to Mearle for this link)

 

[Banana]

Using virtual selection to slim your functions parameters

I had this idea when I wrote a collection of functions that would pertain to same object. I wanted it to be available for various objects of same type, but didn't want to insert the object reference every time I called a function from the collection, so I came up with this idea of using what I call "SelectedObject" which I set once then thereafter call functions with one less parameter.

Example:

Option Compare Database
Option Explicit

Public SelectedForm As Form

Private Sub ChangeRecordSource(strSQL as String)

SelectedForm.Recordsource = strSQL

End Sub

Private Function CountOfControls() As Integer

CountOfControls = SelectedForm.Controls.Count

End Function

This is useful only if the collections of functions you have in mind will refer to same objects repeatedly. You also may find it preferable to use Property routines for setting/identifying the "Selection" object.



BTW, do feel free to post random doodads you like to use for developing.