Land Of Oorang

Or "Things Only I Will Think Are Important"



This is where I throw out code tidbits, minutia, bad artwork, obscure jokes and various other things that no one will care about but me.



view:  full / summary

New Entry in Code Archive

Posted by Oorang on April 1, 2010 at 9:28 PM Comments comments (0)


    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.

Funny and Disturbing

Posted by Oorang on January 21, 2010 at 11:52 PM Comments comments (0)

OK, maybe it's just me, but when this recommendation from ITunes Genius popped up, I was both amused and disturbed.

Yes this really happened.

How to Output all Project References Using the Immediate Window

Posted by Oorang on November 25, 2009 at 2:50 PM Comments comments (0)

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


Microsoft Office 2010 Beta Released

Posted by Oorang on November 20, 2009 at 11:23 PM Comments comments (0)

Thought of the Day

Posted by Oorang on November 9, 2009 at 3:04 AM Comments comments (2)

You should always speak the truth. But you shouldn't always speak.

Using Excel Checkboxes as Option Groups

Posted by Oorang on November 7, 2009 at 3:10 PM Comments comments (0)

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:




These are also stored in the Code Archive.

All Caps

Posted by Oorang on August 16, 2009 at 12:44 AM Comments comments (0)

Something I've just noticed recently: Advice given in all caps is almost always wrong.

I can Haz StackOverflow?

Posted by Oorang on June 28, 2009 at 9:45 AM Comments comments (0)

I have been participating in the development question and answer site StackOverflow. If you haven't been there yet, you really should take a look. StackOverflow is a great place to get an answer. One of the reasons it is such a great place to get an answer is that it's pretty no-nonsense. Any response to a question that is not a correct answer will be voted down, and on some occasions submitted to some less than tactful critique. This is not a bad thing, because it contributes to an enviroment that encourages correct answers. But it is also why the correct answer to this question had me laughing so hard.

A Simple Way to Get a GUID

Posted by Oorang on June 28, 2009 at 7:41 AM Comments comments (1)

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.

The Strangest Thing I Have Seen Today

Posted by Oorang on June 11, 2009 at 4:15 PM Comments comments (0)

Thanks to BraveTerry for pointing this out: The Manga Guide to Databases.

I'm not a manga fan, but that is just... just... well words fail me:)