This is a guest post by Paresh Shah
In his latest book, Now You See It
On pages 165 and 166 of the book, Few discusses how grouping related time intervals can facilitate analysis of data. As an illustration he explains that when viewing data of daily website visits, it helps in separating weekdays and weekends to differentiate expected traffic during these periods. The use of this technique would make it easier for the analyst to identify any anomalous movement in ether the weekend or the week day.
Fortunately excel combo charts can help you do that.
Given below is a combination chart of daily visits to a web site [ hypothetical ] where in the days of the week are shaded. The website visit data has been plotted as a line chart.
The website visit data has been plotted as a line chart. The shading has been achieved by using column chart – the data for the secondary series has been plotted on a second axis. A constant data value for Monday to Friday, 3 and a second constant value for Saturday and Sunday, 0 has been assigned for each date of the month. The secondary axis has thereafter been hidden. The maximum value for the second axis has been manually set at 3 to get the columns to run from the top to bottom and gap between the columns has been set to zero [ Format data series ->Gap width->No Gap ]. The secondary axis has thereafter been hidden.
The concept can be used for other groupings too, months grouped by year, by quarter etc without too much effort.
Download this excel combo chart and play with it to learn more
Click here to download the tutorial workbook and learn by changing things.
Added by PHD
Thank you Paresh. That is an innovative way to achieve zebra lines / bands on the charts to group related events.
Hello there, my dear reader, if you have enjoyed this charting trick, say thanks to Paresh.
Further Resources on Excel Combo Charts
- Excel combo charts – What are they and how to make one?
- Make a combination chart in Excel in 15 seconds
- Using combination charts to make a timeline to show project milestones [project management using excel]
PS: the link to Now You See It
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