I’ve probably said everything I need to on the subject, so no long diatribes here. However, I decided that I was going to experiment with coding without Hungarian and see what I thought. A little experimentation can’t be bad. I have a choice. I can be stubborn and not learn anything or I can force myself to try something different and maybe I’ll be the better for it. There was a time when I never coded custom class modules. Now you can’t get me to shut about them.
Here is the first procedure I coded in my experiment:
Dim Cell As Range
Dim Found As Range
For Each Cell In Sheet1.Range("A2:A133").Cells
Set Found = Sheet2.ListObjects(1).DataBodyRange.Find(Cell.Value, , xlValues, xlWhole)
If Not Found Is Nothing Then
Cell.Offset(0, 1).Value = Sheet2.Cells(Found.Row, 2).Value
Cell.Offset(0, 2).Value = "'" & Sheet2.Cells(Found.Row, 1).Value
Cell.Offset(0, 3).Value = Found.Offset(0, 1).Value
Not exactly a barn burner, I’m sure you’ll agree. I hated every minute of it. I hate reading it right now. I’ve struggled to pinpoint why it displeases me so, but I have a theory.
It’s hard to tell the difference between keywords and variables.
For Each Cell are all one syllable words with the first letter capitalized. The color distinction actually shows up better on this blog than it does in the IDE. I could change my color options in VBA so it stands out a little better.
There’s no requirement to make my variables proper case and thus hard to distinguish. I could code
For Each cell and make my variables stand out because they’re lower case. But there is a substantial advantage to using capital letters – the IDE fixes your caps and tells you if you have a typo. So I want to have at least one capital letter to get that benefit. I could use camel case for two syllable words, like fileName. Do I have to always avoid one syllable words?
As I’ve mentioned in the past, I don’t use data type prefixes in other languages. But like this experiment, I don’t really like the variables I use when I code Ruby and I think it’s for the same reason. The difference is that my Ruby IDE doesn’t fix caps and, maybe more importantly, everything about Ruby is new and novel and foreign so on the scale of strangeness, all lower-case variables don’t really rate.
Another advantage of data type prefixing is being able to use reserved words. For my experiments, if I want to use a reserve word I’m going to tack on an underscore. When I want to code
Dim lEnd As Long, I will instead use
Dim End_ As Long.
I haven’t made a userform yet, but there is a problem that I’m not sure how to solve. Most of my controls have labels and any control with a label is named the same as the label. The textbox tbxSearch has lblSearch. The combobox cbxCustomer has lblCustomer. There’s real value in that and I’m not sure how to get away from it. Another problem with userforms are class properties. When I start typing
Me.tbx I know I’m getting a textbox. But if my textbox is called CustomerName and I have a property of the userform class to hold a customer name, how do I distinguish them without the tbx? That’s not a rhetorical question, I really want to know how people do it.
I’ll keep writing new code without data type prefixing until I can’t take it anymore. And, of course, I’ll keep bitching about right here.