2

More people are interested in Excel mouse rollovers, which I think is great. I'm especially enthusiastic about Chandoo's latest dashboard contest where I found out that several contestants used the technique! By the way, there are many great dashboards showcased in the contest. Take a look and make sure to vote!

So here's the tip. If you're familiar with the rollover method - and you should be by now, it's like all I ever blog about these days - you may have noticed that while your mouse is over a cell, Excel is continuously firing the rollover method. For example, let's take a look at this snippet from Chandoo's blog:


Public Function highlightSeries(seriesName As Range)
Range("valSelOption") = seriesName.Value
End Function

If you place your mouse over the cell with the HYPERLINK formula that calls this function, the range valSelOption will be continuously written to. This becomes a problem when there's a lot of complex interaction on your spreadsheet. If, for example, you've used a lot of volatile functions (like OFFSET or VLOOKUP), continuously writing to the sheet will mean a recalculation for each cell with the volatile function. Nobody likes a slow spreadsheet. 

The incredibly simple fix to our problem comes from the wonderful book, Professional Excel Development, by  Bullen, Bovey, and Green in their chapter on spreadsheet optimization and speed tricks. Simply test if you're rewriting the same value over and over again:

Public Function highlightSeries(seriesName As Range)
If Range("valSelOption") <> seriesName.Value Then Range("valSelOption") = seriesName.Value
End Function

In the above code, I test if valSelOption already equals seriesName.Value. If it does, then we do nothing; if not, we write to it so that the next time the function is called with the same value in its argument (which is unpreventable if your mouse is over a cell for even a brief moment second), we can again, relax and do nothing.

Thats it! You'll likely see an immediate speed improvement, especially if you're doing lots of complex stuff with your rollover, like a this.

Author: Unknown Labels: Advanced Excel, Excel Development, Excel Tips, Rollovers, VBA

0

Today, I found this journal article, The role of OR [Operations Research] specialists in ‘do it yourself’ spreadsheet development, from the European Journal of Operational Research, by John S Edwards, Paul N Finlay, John M Wilson. The article outlines 20 Guidelines for Spreadsheet Development and 21 Best Practices Points, which I excerpt below.

Guidelines for Spreadsheet Development

Fig. 2. Guidelines for spreadsheet development.

Best Practices for Spreadsheet Development

Fig. 3. Best Practices Points. 

My favorite point is #14 from the list of Best Practices: 

Never input the same piece of data more than once.

This rule reminds me of the 17th rule of composition from the Elements of Style: "Omit needless words." Perhaps Best Practice-point #14 can be rewritten as "Omit needless data." 

_____

Figure 2 and Figure 3, from John S Edwards, Paul N Finlay, John M Wilson, The role of OR specialists in ‘do it yourself’ spreadsheet development, European Journal of Operational Research, Volume 127, Issue 1, 16 November 2000, Pages 14-27, ISSN 0377-2217, 10.1016/S0377-2217(99)00331-8.

Author: Unknown Labels: Advanced Excel, Excel Development, Excel Standards, Excel Tips

0

Reader, Bert van Zandbergen, from Beekbergen, The Netherlands (Holland) sent me his own cool creation based on the Rollover technique I created. I asked if I could post his work to this blog to which he graciously agreed.

Below is his write up. Some of the Excel functions below are in Dutch (which I think is really cool). If you're confused, download the file first - your version of Excel will show the functions in your language of choice. And, of course, if this technique is new to you, read the the tutorial on how to do Excel rollovers. 

***

Dynamic Funnel Chart

by

Bert van Zandbergen

The Netherlands

Based on this file: 

presentatietechniek-25-pipeline-2012.xlsm

Figure 1

Start with the 4 columns. The dummy had to be 100 or more for space to unfilled dummy bars. Delete the lines. Now you can change the value from “100” to “1”. Further information is visible in Figure 1.

Hyperlink:
Read also the information and explanation on the website of Chandoo.org
http://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/ 

Figure 2

Place a hyperlinks in the cells of the hotspot – see above. 

Figure 3
Formula: Define the hotspot with the name: “ valSelOption” 

Figure 4

Go to VBA and insert a module. For more information about the instruction – see on this website, the Chandoo website and the module above. 

Figure 5 -- Chart with hotspot

The hotspot is based on 10 columns combined with 42 rows. – see figure 5/6. The hotspots are linked by hyperlinks with corresponding cells in columns AI:AR.  For a special effect and an easy crossover the values are placed in a diagonal figure

See: Figure 5/6  

Figure 5 -- The "hotspot"

Above the hotspots and the linked cells. Special formed to make an easy crossover.

***

A big thank you to Bert - hopefully there will be more contributions in the future!

Do you have something interesting to share? Send me an email or drop me a line on LinkedIn. 

Author: Unknown Labels: Advanced Excel, Excel Development, Excel Tricks, Interactive Dashboards, Spreadsheet Tricks, VBA

2

So you've got a project that requires VBA – you’re ready and excited – and you jump right in! But here come the headaches! With every syntax error the visual basic environment interrupts your programming mojo with an annoying popup. You remembered to place comments in your code – but where are the ones you’re looking for? When did Excel programming become so frustrating?

Below, I’ll describe how I've customized my editor to minimize annoyance and maximize efficiency. It might not help you become a better programmer…but then again, it really might! Your best work probably comes from a space you can call your own. So season your VBA developer environment to taste.

Tip #1: Change the Font
The problem is that the default font, Courier New, does a poor job differentiating between colors - especially when your eyes quickly scan the screen. Don’t believe me? See the difference for yourself with my personal font choice: Consolas, 9pt.

Go to Tools > Options, then click on the “Editor Format” tab to try out a new font. Whatever you choose, you’ll need to keep these things in mind:

• You want a font that makes a significant contrast between colors.
• You want a fixed-width font. If you choose a font that is not fixed-width, you’ll have trouble using Tab to align your code. Some well known fixed-width fonts are: Consolas and Lucida Console.
• Pick something readable. Stay away from Mistrel.

Tip #2: Change your Comment Formatting
Make a statement with bold comments! Seriously though, the default comment formatting blends right in with the rest of the code. And that’s a pain – especially, when you’re on the hunt for some code you had commented to save for later.  I like my comments with a light-blue highlight in the background and a dark-blue foreground. They really stand out.

Go to Tools > Options, then click on the “Editor Format.” Select “Comment Text” from the list to get started.

Tip #3: Use the Immediate window, immediately
The Immediate window should be your best friend, but some folks didn’t even know it exists. Go to View > Immediate window if it’s not already open.

Why is it so great? Well, the immediate window allows you to print essential information to the screen while your program is running. Let’s say you need to iterate through tons of data and would like to know your intermediate progress but only temporarily. You can print to the immediate window by using Debug.Print(). Go ahead, try Debug.Print “foo!” in a Sub.

But wait, there’s more: you can also gain information even when you’re not running anything. Go place a shape onto an empty spread sheet and make sure you’ve selected it. Now go to your Immediate window and type Msgbox Selection.Width. Hit Enter.

Is your UserForm stuck in an endless loop? (it happens sometimes) Do a CTRL+Break and type “Unload Me” into the Immediate window to return everything to normal.

Tip #4: No more syntax error pop-ups
Sometimes you’re typing an IF/THEN but see some code above it that needs fixing. You click-off to go fix the code but you’re stopped by an annoying popup message. I mean, it’s nice of VBA to let you know there’s an error, but it didn’t need to ruin your flow.

So get rid of those nasty pop-ups by going to Tools > Options and unchecking Auto Syntax Check. The environment will still tell you that you have an error by highlighting the offending script in red - but the pesky popup box will bother you no more.

Tip #5: Opt for Option Explicit By putting Option Explicit at the top of your code, you are helping yourself so much. Seriously, I’m such an advocate of Option Explicit that I named my blog after it.

What does it do? It requires that you declare your variables. If you don’t write "Dim i as integer,” you will not be allowed to use i for anything. Without explicit declaration, VBA assumes that any new variable introduced is a variant type.

Explicit declaration might sound like more work, but trust me you’ll save yourself some headache. You’ll find that you might not always spell your variables correctly, for example, “RecordCount” might accidently be spelled as “RecrodCount.” When your program doesn’t give the correct output (because it assumes “RecrodCount” is actually a new variable), you’ll be forced to scour through your code looking for the problem, which you might not even realize is a typo. And if you missed the typo when you first typed it out, it’s likely that your quick scanning will miss it again. By requiring explicit declaration, undeclared variables will result in a runtime error, so you can fix your variables before they become hidden within your code.

Don’t make Option Explicit optional. Go to Tools > Options and proudly check “Require Variable Declaration.”


Your developing environment is just as important and personal as any other. Make sure you use the features and options within the Visual Basic Environment to customize to the fullest.

Author: Unknown Labels: Excel Development, Excel Tips, Excel Tricks, Programming, Spreadsheet Tricks, VBA

362

"Rollover B8 OV1" (like Rollover Beethoven...? get it?)

Update 17 July 2011: This blog entry was featured in Chandoo's latest post. So if you're coming here from his site -- welcome!

original post:

This post was inspired by Chandoo's post on hyperlinks in Excel. If you haven't checked his website out yet - you should. It's excellent.

In this post, I suggest a method for creating a "Rollover" effect for your mouse on an Excel spreadsheet. Rollover techniques (also called "Mouseovers") are useful to (1) display quick information to the user; (2) to execute a function when a user puts their mouse over a specific region; and (3) to make neat graphical effects.

For item 1, my technique isn't really necessary. If you want to display brief information to the user when he or she rolls over a cell, the best way to do this is to insert a comment.

But comments are pretty limited. You can only write so much information in a comment before it becomes too cluttered to be useful. Moreover, comments are static. What if you want the information concerning the "target" cell (that is, the cell to be rolled over) to change based on different items in your spreadsheet? We can use the in-cell Hyperlink() formula to accomplish this.

The Hyperlink() formula looks like this:

=Hyperlink(location, [Friendly Text])
location - Here you write the intended address of your hyperlink. This can be a webpage (i.e. "http://www.google.com") or a file on your computer.
[Friendly Text] - This is an optional field that provides a caption to your hyperlink. If you leave it blank, it will simply display the address in the previous parameter.

To begin our example, you'll need to create a User Defined Function (UDF) to be executed when the rollover event is initiated. So go into the Visual Basic Environment, create or find a free module and create a UDF that looks something like this.

Public Function MyMouseOverEvent()

Sheet1.Range("A1").Value = "Event Fired!"

End Function

I can understand if you're puzzled at this point. UDFs are not really allowed to change values in other cells. However, since we'll be calling this function from within the Hyperlink() formula, you'll see this limitation no longer exists.

So now go to Sheet1 and click on cell A2. In that cell, type in the following formula:

=HYPERLINK(MyMouseOverEvent(),"Click here")

Upon hitting Enter, you should get a #VALUE! error, but ignore it for a second. Roll your mouse over cell A2 - and viola! - Cell A1 should now say "Event Fired!"

But what about that pesky #VALUE! error? Remember the limitation that UDFs can't change other cells? Well, even though Excel let's you change a value from within your UDF, it still recognizes that it wasn't supposed to! To get rid of that error is pretty simple. Just rewrite the function in your cell as follows:

=IFERROR(HYPERLINK(MyMouseOverEvent(),"Click here"), "Click here")

And that's it. If you like this technique and find some cool uses for it, drop me an email and show me.

Here's some graphical fun I created using this technique. Putting the mouse inside the black rectangle creates a fading snake-like pattern based on your mouse movements. Check out the screen capture:

Make sure to check out my latest (and greatest) example of this technique, here:

Periodic Table of Elements in Excel: Rollover Edition!

Author: Unknown Labels: Dashboards, Dynamic Charts, Excel Development, Interactive Dashboards, Spreadsheet Tricks, VBA