It all began with my Excel Dashboard Tutorial – Making a dashboard with player statistics. I have used bar charts with axis whose minimum is not zero, to create a dramatic effect in the charts. See below:
Jon Peltier commented saying that,
Rule #1: Bar and column chart value axes should start at zero. Since the lengths of bars and columns represent their values, starting at a position other than zero will lead to a misleading display.
As always Jon raised an excellent point. I liked it so much that I wanted to get your opinions on this axis scaling issue.
What do I think about this visualization rule ?
First the positive points about the rule:
- If you start axis at non-zero values, it gives a wrong impression about the data. In my example above you might end up thinking the top value is actually 10 times that of bottom value.
- The rule helps easy interpretation of data, since everything starts at zero.
Now the negative points about the rule:
- Not all data starts at zero (like the above with values ranging from 8000 to 12000). So when you create a bar chart for the data (why only bar charts, because they are easy to make and read) often you end up creating charts from which making out any analysis could be slightly difficult
- Breaking this rule helps in creating dramatic effect on your charts, one of the reasons we use them. Often when you use data with narrow range, creating dramatic effect could be difficult. Axis adjustments can help (you may want to try logarithmic axis scale as well)
Finally, I think this rule can be broken as long as we have right data and make alternative arrangements (like adding data labels) to ensure correct interpretation. Since this is a standard people have come to expect, it is better to make it clear to your audience about the axis range.
What do you think about the charting rule: Bar and column chart value axes should start at zero.
6 Responses to “Make VBA String Comparisons Case In-sensitive [Quick Tip]”
Another way to test if Target.Value equal a string constant without regard to letter casing is to use the StrCmp function...
If StrComp("yes", Target.Value, vbTextCompare) = 0 Then
' Do something
End If
That's a cool way to compare. i just converted my values to strings and used the above code to compare. worked nicely
Thanks!
In case that option just needs to be used for a single comparison, you could use
If InStr(1, "yes", Target.Value, vbTextCompare) Then
'do something
End If
as well.
Nice tip, thanks! I never even thought to think there might be an easier way.
Regarding Chronology of VB in general, the Option Compare pragma appears at the very beginning of VB, way before classes and objects arrive (with VB6 - around 2000).
Today StrComp() and InStr() function offers a more local way to compare, fully object, thus more consistent with object programming (even if VB is still interpreted).
My only question here is : "what if you want to binary compare locally with re-entering functions or concurrency (with events) ?". This will lead to a real nightmare and probably a big nasty mess to debug.
By the way, congrats for you Millions/month visits 🙂
This is nice article.
I used these examples to help my understanding. Even Instr is similar to Find but it can be case sensitive and also case insensitive.
Hope the examples below help.
Public Sub CaseSensitive2()
If InStr(1, "Look in this string", "look", vbBinaryCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub CaseSensitive()
If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub NotCaseSensitive()
'doing alot of case insensitive searching and whatnot, you can put Option Compare Text
If InStr(1, "Look in this string", "look", vbTextCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub