Today I want to introduce a new excel feature to you, called as Picture link.
Well, picture links are not really new, they are called as camera snapshots in earlier versions. They provide a live snapshot of a range of cells to you in an image. So that you can move the image, resize it, position it wherever you want and when the source cells change, the picture gets updated, immediately.
What is the use of Picture Links or Camera Snapshots?
At the outset picture links may seem like a useless feature. But they are pretty powerful. Here are few sample uses for picture links:
- In dashboards & reports: Usually in dashboards, we need to combine charts, tables of data, conditional formatting etc., all in one sheet. When the size of these are not uniform, aligning them on output sheet could be a huge pain. This is when you can use picture links. First create the individual portions of dashboard in separate worksheets. Then, embed picture links to these portions in final dashboard. Re-size them and align as you see fit.
See this in action: Project management dashboard in excel
. - In micro-charts & sparklines: While Excel 2010 has native support for sparklines and other micro-charts, if you want to create a micro-chart in earlier versions of excel you have to use trickery. This is where picture links can help. You can make a regular chart and take a picture link of that. Then resize the picture so that it fits in to a small area.
See this in action: Micro-charts using camera tool.
- In Dynamic Charts: Since picture links are nothing but images with a formula assigned to them, you can easily construct dynamic charts & dynamic dashboards using these.
See this in action: Dynamic chart using camera tool, Dynamic dashboard using camera tool
- In shared workbooks: When you share a workbook with a colleague or boss, a common worry is what if they change formulas or edit something. This is where picture links can be of great use. You can embed a picture link of actual data so that no one can edit it.
How to insert a picture link in Excel – 3 step tutorial:
To insert a picture link to your data, just follow these 3 steps:
- Select the cells. Press CTRL+C
- Go to a target cell. From home ribbon select Paste > As picture > Picture link option (see image below)
- That is all. Your picture link is live. Move it or play with it by changing source cells.
Do you use Picture Link / Camera Snapshot ?
I have been a fan of picture links / camera snapshots ever since I learned about them. I have used them in various dashboards, reports, workbooks to wow my clients, bosses and colleagues. However, one problem with picture links / camera snapshots is that, they do not print well. So I avoid using them for workbooks that get printed alot.
What about you? Do you use picture links often? Share your experience, tips and ideas using comments.
Read more quick tips to become awesome in excel, in less than a minute.
PS: Donut to Hui for telling me about Picture links feature.
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