Land Of Oorang

    I have added an entry in the codearchive. It is a program to sort an array of strings. It is tweaked to run asfast as possible on VBA, while still offering few commonly needed features. Itwill allow for sorting ascending/descending and a choice to compare casesensitive or case insensitively. Take a look a let me know if there is room forimprovement.

It's often useful to document, either in a module or in a post, the referencs needed to make the module (or code snippet work). Unfortunately you can't copy the path's and names from the references window. Here is a nice and easy way to get the information you need.

1. Press Ctrl-G to open the Immediate Window.
2. Paste this: 
   For Each r in Application.VBE.ActiveVBProject.References:?"'"&r.Name,r.FullPath:Next
3. Press Enter, and enjoy:)

Some people (me) like this format better, it's a little longer though:
For Each r in Application.VBE.ActiveVBProject.References:?"'"& vbTab+"-"&r.Description:?"'"&vbTab+" "&r.FullPath:Next

Every now and again I will run across a post like this:

I have created (a) form with check boxes. I am trying program the form so that when a particular box is checked the user cannot check another box in that row. For example, the user should not be able to check "yes", "no" and/or "don't know" at the same time. This is the code I an using, but I keep recieving error messages. What is the problem?

Often such posts are followed by solutions that involve catching the click event of every single CheckBox control, then manually checking each CheckBox that shouldn't be selected and flipping the value: 

Private Sub CheckBox1_Click()
    If Me.CheckBox1.Value Then
        If Me.CheckBox2.Value Then Me.CheckBox2.Value = False
        If Me.CheckBox3.Value Then Me.CheckBox3.Value = False
    End If
End Sub

Private Sub CheckBox2_Click()
    If Me.CheckBox2.Value Then
        If Me.CheckBox1.Value Then Me.CheckBox1.Value = False
        If Me.CheckBox3.Value Then Me.CheckBox3.Value = False
    End If
End Sub

Private Sub CheckBox3_Click()
    If Me.CheckBox3.Value Then
        If Me.CheckBox1.Value Then Me.CheckBox1.Value = False
        If Me.CheckBox2.Value Then Me.CheckBox2.Value = False
    End If
End Sub

    If this code made you cringe, you are not alone. As you can imagine, when you (or the poor soul following you) has to add/rename/delete a CheckBox in future this is going to be real maintenance nightmare. Especially if you, like the user above have a lot of controls. Fortunately there is an easy solution to this problem.

    The easiest solution is, of course, to just use Option Groups. No code needed. But some people just really hate radio buttons and want the look of a CheckBox. And since some of those people might be employing you, I will direct your attention to the GroupName property of the CheckBox control. At first glance you might think all your problems are solved. It looks just like the OptionButton.GroupName property! Yay! Sadly, for whatever reason, in VBA this property will hold a value, but the mutual exclusion of an Option Button Group is just not implemented automatically. But don't despair! It's still pretty easy to work around this. With a Class Based approach you can reduce the code in your Form to this:

Private m_cogGroup1 As New CheckOptionGroup
Private m_cogGroup2 As New CheckOptionGroup

Private Sub UserForm_Initialize()
    m_cogGroup1.InitializeGroup "Group1", Me.Controls
    m_cogGroup2.InitializeGroup "Group2", Me.Controls
End Sub

To do this:

1. Import the two classes posted below.
2. Go ahead and use the GroupName property to specify your groups of checkboxes (just like you would an option group).
3. Implement as shown above, using one array of CheckOptionGroup for each group.

To make life a little easier, I have created an Example Workbook.

If you just want to copy/paste the code use these:

CheckOption.cls

CheckOptionGroup.cls

These are also stored in the Code Archive.

I have seen a lot of different ways to get a GUID, from randomly generating each character to using API calls. And every time I do, I think "Gosh that didn't have to be so hard." So for the record... Here is the easy way:

Private Function GetGUID() As String
    GetGUID = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36)
End Function

Of course if you only need one, you could just go here.

Migrating... I will be moving this site into the blog section slowly but surely. 

On to the show:

On occasion you need to open a form in Access, and have that form already know something special. (Say a filter.) The generally accepted way istp pass the form the information with an "Open Argument":

DoCmd.OpenForm "Example", OpenArgs:="Table1.[MyField]='1'"

But what if you want to pass more than one argument? Well the bad news is you can pass one, and only one string to the form. So you must cram all of your data into one string and parse the string as needed. If you have ever had to maintain someone else's work you know that if the original Author was in a hurry they probably just tacked this and that on as they went, and left you with a 5 or 6 custom string parsing procedures across 2 or 3 databases. This can be, in a word, painful.

One way you can spare your successors  this agony, is to use a (somewhat)self-documenting design pattern. A structured way to approach this problem is select a universal argument delimiter, and a common way to access the arguments.

Example follows:

On some rare occasion you may have a need to conditionally change the sign of a number. The most obvious way of doing this is

If x then y = -y



However there are a few occasions where you might want to do this without using a conditional, either in an Excel formula or in code. I wouldn't call this a best practice, merely a convenient trick to know about. 

y = y * (x * 2 + 1)



Why does it work? Well in VB (and Excel) boolean true implicitly converts to integer -1 and boolean false converts to integer 0. So when True you have:y = y * (x * 2 + 1)

y = y * (-1 * 2 + 1)

y = y * (-2 + 1)

y = y * -1

Whereas when False you end up with:
y = y * (x * 2 + 1)

y = y * (0 * 2 + 1)

y = y * (0 + 1)

y = y * 1

So there you have it. It's not always going to be the best way, but it's good to know about.

    As some of you know, I am one of the Knowledge Base Approvers for VBA Express.The VBA Express Knowledge Base is a compendium of how-to articles forcode written in VBA (Visual basic for Applications) geared towardsteaching beginners.

    The staff and membership have begundiscussions of implementing an agreed upon standard for the approval ofsuch articles. By popular request I am making available the firstproposed Draft. Please remember this is just a rough first copy, andwill likely change many time before being finalized.

I am currently soliciting feedback; please contact me via the board with your thoughts.

Download