Getting Started: Thinking Outside the Cell
In this chapter, I’ll talk about some misconceptions concerning Excel development. It’s quite often heard that Visual Basic for Applications (VBA) is where all “advanced” Excel takes place. The thought is VBA can do everything, and if you want your data to be sorted, manipulated, and so forth, you’ll need VBA. But you should think of Excel formulas and VBA not as separate entities but as tools that should work in tandem to present the best result.
It’s true that many of the capabilities presented in this book will require knowledge and use of VBA, but it would inaccurate to describe everything as macro coding. This misconception, I believe, stems from the idea that a certain level of programming mastery is required to make Excel do fancy things. The macro recorder has played a strong role in this misconception because many believe that macros are seemingly the only way to make Excel perform automated tasks or create dynamic capabilities.
That this misconception is widely believed is not hard to understand when you consider Excel’s spectrum of users, from accountants who probably don’t think of their work as being too technical to engineers who might view their work as being a lot closer to coding. But, as Chapter 1 established, using Excel in any way is a type of unstructured computer programming. Perhaps you never thought of yourself as a coder, but the way you use Excel to construct solutions is similar to the processes that computer programmers use when they develop their work.
Consider this example. I could describe Rory McIlroy as a good putter, which is true, but it’s only part of the story. Rory McIlroy is a terrific golfer. VBA can help you do a lot in Excel, but knowing all the features of Excel—and knowing when they should be used—will make you terrific with Excel. You can be good at just VBA, but there’s more to developing terrific programs than just VBA. Don’t get me wrong, I love VBA. And if VBA is something you’ve always wanted to master, I say go ahead. But when it comes to Excel development, VBA is only half the story.
The other half of the story is all of the Excel capabilities at your disposal. In particular, there are formulas, custom formatting, conditional formatting, and form controls, among others. Indeed, you could create a capable, dynamic dashboard with all of these things without ever having touched any VBA.
In my opinion, the distinction between VBA and everything else isn’t very useful for what we want to do. If you rely purely on VBA for everything, your spreadsheets might not be as great as they could be. As you’ll see in this chapter, choosing VBA, formulas, or a mixture of both brings its own set of challenges and advantages.
In the following sections, I’ll go through some common scenarios and talk about the different ways to solve the problems presented.
HOW MY WORKSHOP SUDDENLY CHANGED ITS NAME
On one occasion, I was invited to give a workshop on developing dashboards with Excel. I had planned to deliver a presentation on all the cool things you could do without VBA since I had only an hour set aside for instruction. When I arrived to the presentation room, the organizers had changed the name of my class from Advanced Excel Development to Advanced VBA. It’s not uncommon for people to assume doing anything outside the normal use of Excel must be Visual Basic for Applications at work.
House Hunters: Excel Edition
In this section, I’ll go through three different ways to build a simple application. The application allows the user to switch between several different properties. When the user moves to the next property, a new property is shown. In all, you’re dealing with only three properties, but you can extend the underlying functionality to more complex dashboards and Excel applications. There will be instances in which users must switch between pictures and information. As I go through three different ways to solve this problem, you should consider the benefits and costs for each. Figure 5-1 shows one such attempt at solving this problem in Excel.
Figure 5-1. Implementing the image display for the rotating picture example
The following are the three methods you’ll consider in this section:
- A purely VBA method: This method largely relies on coding.
- The semi-VBA method: This method uses some spreadsheet functionality in conjunction with code.
- The no-code method: This method uses no code at all but instead relies solely on formulas and features.
In the next subsections, I’ll go through these methods and talk about the different ways to attack this problem. You can follow along on your own by downloading Chapter5HouseHunters.xlsm from the chapter files.
In this section, I’ll discuss the purely VBA method. You can get a copy of this method implemented in full from Properties List Example 1 in the Chapter5HouseHunters.xlsm chapter file.
In this first method, most of the mechanics are performed through code. I present this method as a lesson in what not to do. The code may seem innocuous at first—and to a certain extent it is—but in many ways, it’s also unnecessary (and annoying, as you’ll see in Chapter 6). It represents the rut many Excel developers find themselves in, offloading important features to VBA when it needn’t be this way.
Figure 5-2 shows this implementation in action.
Figure 5-2. Clicking the Next button moves to the next house available to the program. The mechanics are largely driven by VBA
Figure 5-2 features a simple Excel application. Users can switch between properties by using the Previous and Next buttons. Both the Previous and Next buttons are simply text boxes with macros assigned to execute on a click. When the Next button is clicked, GoNext is called; when the Previous button is clicked, GoPrevious is called. Listing 5-1 displays the code for both of these methods.
I say this method is “largely driven by VBA” because VBA is used to upload, store, and ultimately present each picture of the house. The spreadsheet is used as a place to lay out the application, but few spreadsheet features (for example, formulas and functions, conditional formatting, and cells) are used.
Notice how this code works. There are three files stored in the chapter directory that can be displayed in this program. They’re House1.png, House2.png, and House3.png. A private variable, CurrentHouseIndex, keeps track of the current index of the house to be displayed. Both procedures call InsertNewImage, as shown in Listing 5-2.
The InsertNewImage uses the current index to decide which picture file to show. It then replaces the shape shown on the spreadsheet with the desired uploaded image. It also sets the height and width to ensure uniformity across all presented images.
It may not be so obvious, but there are several problems with this code. First, take note of the .Delete and .Insert actions in the code. There is, in fact, no method to simply select the picture as an object and link it to a new source image. You have to first delete the old image and then insert the new one. In addition, that also means you have to set the picture height, width, left, and top properties every time you change pictures.
The code also relies on the pictures being exactly where you expect them to be. In practice, you can’t always be so confident this spreadsheet won’t be run from a different folder.
Finally, you might also have noticed IsEmpty(CurrentHouseIndex) in the IF conditions of both the GoNext and GoPrevious subroutines. You have to do this because the variable, CurrentHouseIndex, is a variable created in the code. These types of variables created outside procedures are a huge pet peeve of mine (and I hope of yours by the time you’re finished with this book). The problem is that these variables are essentially cleared out when Excel runs into errors (or when you open the spreadsheet for the first time). They must be initialized, and you test whether it’s empty in an attempt to initialize it if hasn’t already been initialized. Placing this variable in the code also prevents you from immediately seeing what the current index is at any time. In the next method, you’ll see a much better alternative.
Declaring variables outside of procedures isn’t the only way to keep track of important data while still allowing for it to be exposed to other procedures. In this section, you’ll see an alternative to the one presented previously, which relied largely on code.
In this section, you’ll be working on the Semi-VBA tab from within the Chapter5HouseHunters.xlsm chapter file.
Figure 5-3 shows one of the major changes employed by this method. If you select cell A1 in the example image, you’ll see that it isn’t actually blank. (Sneaky, right?) The formula bar shows that it contains a value. If you move your eyes to the left of the formula bar just a few pixels, you’ll see that I’ve given cell A1 the name Example2.HouseIndex. Example2.HouseIndex actually does what the coded variable did in the previous example: it tracks the current index of the selected property.
Figure 5-3. You now use a named range to store the variable instead of a variable in the code
This method also replaces another annoying constraint from the previous method. As you might recall, the previous method required separate images stored in a folder. But there’s a way around that in Excel. And to take advantage of that workaround, you’ll need to employ a rather old yet overlooked Excel feature—the Camera tool.
Say Cheese: Introduction to the Camera Tool
The Camera tool is one of the coolest features in Excel that many have not heard of. For some reason, Microsoft has tucked this little gem away from its standard toolbars. To gain access to the Camera tool button, you’ll have to add it manually. First, you’ll add it to your Quick Access Toolbar.
Here’s what you need to do:
- Click the File tab at the top right of the screen and select Options.
- Click Quick Access Toolbar in the tab list.
- In the “Choose commands from” drop-down, select Commands Not in the Ribbon.
- Scroll down the list until you find the Camera tool, as shown in Figure 5-4. It might help your search to click the top of the list and type C to skip to where items starting with C begin.
Figure 5-4. The Camera tool shown among the list of Excel features available
- Click the Add
button to add it to your Quick Access Toolbar list. Then click OK.
The Camera tool allows you to view any part of your workbooks as a picture. In Figure 5-5, I have a series of random numbers.
Figure 5-5. A series of random numbers
With the Camera tool, you can view this section of numbers anywhere else on the spreadsheet. Figure 5-6 shows a section of this series of numbers as a picture. You simply select the desired cells, click the Camera tool to take a snapshot (the desired cells will now have a border around them similar to what happens when you copy a range), and then click anywhere on the spreadsheet where you would like the desired picture of your range to appear.
Figure 5-6. The series of numbers shown as a picture
Figure 5-7 shows how this works behind the scenes. The Camera tool creates a picture that’s connected to the spreadsheet in a way similar to that of other formulas. The picture, as shown in Figure 5-7, is actually connected to a cell range. If you were to make changes to the numbers in that cell range, the connected picture would update immediately, similar to what would happen if the data were summed and you changed a number.
Figure 5-7. The Camera tool allows you to create pictures of anything on the spreadsheet and works like Excel formulas
MORE ABOUT THE CAMERA TOOL
Incredibly, the Camera tool always provides a real-time view. If you modify anything in the viewed region, the Camera will update its view automatically. You don’t need to reset the connection or perform a recalculation. This makes it a versatile, plug-and-play tool.
Actually, I’ll let you in on a little secret about the Camera tool, but don’t tell anyone. The Camera tool and an Excel image object are actually…the same object. If you insert an image into your spreadsheet, you can use it just like the Camera tool! Simply select the image object and then use the formula bar to set its reference.
Using the Camera Tool
In this section, you’ll take what you just learned about the Camera tool and apply it your spreadsheet. In Chapter5HouseHunters.xlsm, there is a worksheet tab called Pictures (Figure 5-8). Figure 5-8 shows borders around these images so that you can see their clearly defined cell regions, which represent defined named ranges. (The borders won’t be in your download file because they’re here for demonstration purposes only.) Figure 5-9 shows the first house and its defined region.
Figure 5-8. A modified image of the Pictures worksheet tab
Figure 5-9. The defined region for the first house is Pictures.House1. You can see it as a named region in the drop-down box
To make life easier, I’ve gone through and named each range surrounding a house. In fact, you can also see these named ranges in the named range drop-down box (Figure 5-10). The pictures are named Pictues.House1, Pictures.House2, and Pictures.House3, respectively.
Figure 5-10. A listing of each named range
If you select the picture, you’ll see in the formula bar it points to a named range mentioned previously (Figure 5-11).
Figure 5-11. The selected image points to Pictures.House1
Notice the picture points to the ranges created around each house. In fact, this picture is simply the result of using the Camera tool. To create this effect, you add the Camera tool to this worksheet tab and then replace its formula with a desired named range. You can do this manually by typing it by hand, as shown in Figure 5-12, or you can set the range via VBA.
Figure 5-12. Setting the Camera tool formula manually by typing in the desired range
In fact, if you click Next, you can see the image update to Pictures.House2 (Figure 5-13). This update happens using VBA. The VBA code replaces the formula used by the picture tool—you’ll see this in a moment.
Figure 5-13. Upon hitting Next, the image now points to Pictures.House2
Let’s jump to the code (which is considerably smaller than the previous example). Listing 5-3 shows the GoNext procedure; Listing 5-4 shows the GoPrevious procedure.
These two procedures are fairly similar. They both pull in the value stored in the cell named Example2.HouseIndex. Like I talked about previously, this stores the current index of the house shown. The value is stored in a spreadsheet cell rather than as a private variable. This allows you to see the value at any given time—and, if the spreadsheet hits a runtime error, the value won’t be cleared should you have to restart everything.
The most significant changes from the previous implementation is the InsertNewImage procedure (Listing 5-5). Let’s take a look.
Compare this code listing with that of the pure-VBA method. Here I’ve replaced several lines of code with just one. You form a string representing the desired range by concatenating the supplied index number with the string "Example2.HouseIndex" to form one of three named ranges defined earlier. The field DrawingObject.Formula is actually what you’re setting when you manually assign an image to a specified range.
This implementation definitely addresses disadvantages presented by the first method. For one, you no longer need to keep track of where the required images are stored. Since you’ve loaded them on to your spreadsheet, you can feel safe knowing that they will always be within reach where you left them. However, you did have to carve out another part of the spreadsheet to store them. Specifically, because the Camera tool will show exactly what’s within the defined cell region, ideally the images would be stored beyond the reach of someone who might accidentally edit or delete them.
Moreover, up until now, you haven’t really considered the impact of using code on your spreadsheets. But this is a real problem to consider because some organizations prevent the transmission (and downloading) of Excel spreadsheets that contain code. At one of my previous jobs, we weren’t able to send workbooks containing macros through our organization’s firewall.
Might there be a way to create the desired functionality without using any VBA code? The next implementation allows for just that.
The No-Code Method
In the no-code method, you’ll build upon your previous work; however, as you will see, this method may actually be the easiest to implement compared to two previous examples.
To get started, click the No-Code tab in the Chapter5HouseHunters.xlsm chapter file. At the top, you’ll see I’ve replaced the Next and Previous buttons with a form control scroll bar (Figure 5-14). The scroll bar isn’t the most elegant replacement for the sharp-looking buttons of the previous examples, but those buttons work only when they are connected to macros. (I’ll go into form controls in more detail in Chapter 12.) The scroll bar, as you will see, doesn’t require you to connect it to any macro.
Figure 5-14. The Next and Previous buttons have been replaced by a form control scroll bar
Right-click the scroll bar and select Format Control. The Format Control dialog box appears, as shown in Figure 5-15.
Figure 5-15. The Format Control dialog box
Here you can set the properties of your form control scroll bar. Minimum Value is set to 1, and Maximum Value is set to 3. This makes sense because you have only three houses to choose from. Incremental Change is set to 1 because you want the scroll bar to increase or decrease by 1 when the left and right paddles are clicked. For now, let’s skip Page Change and focus on “Cell link.” This is similar to a reference used with the Camera object. Just as the Camera object allows you to view the part of a spreadsheet to which it refers, likewise the form control scroll bar can “present” the value given by the named range.
Unlike the Camera tool, however, you can actually modify the value given in “Cell link” using the left and right paddles of the form control scroll bar. When you click the paddles, Example3.HouseIndex will actually automatically increment and decrement. Remember how you used VBA for that before?
Click OK to exit the dialog box, but don’t deselect the scroll bar quite yet. Take a look at what’s in the formula bar (see Figure 5-16).
Figure 5-16. The form control scroll bar connects to the spreadsheet the same way the pictures from the Camera tool and formulas do
Hey now, that looks familiar! Like I said, the way the Camera tool references other areas of your Excel workbook is no different from the cell link mechanism found within the form control scroll bar. In fact, if you want to set the cell link quickly, simply select the scroll bar with a right-mouse click and then set the reference from within the formula bar. Nifty right? As you’ll see, many Excel components work by allowing you to supply references to them.
But now you’re probably wondering how you connect changes to Example3.HouseIndex to changing the picture of the house shown.
The answer comes in two parts. The first part is that I used the following formula to test the value of Example3.HouseIndex and return the correct range.
=CHOOSE(Example3.HouseIndex, Pictures.House1, Pictures.House2 , Pictures.House3 )
I’ll go over the CHOOSE formula in a little more detail in Chapter 6. For now, here’s the short and sweet version. When Example3.HouseIndex is 1, starting from Example3.HouseIndex you count one region over. In this case, you see Pictures.House1 between the commas, so that’s what’s returned. When Example3.HouseIndex is 2, you count two regions over; Pictures.House2 is returned. When Example3.HouseIndexis 3, you count three regions over…and so forth.
If you click any of the unused cells in the Example 3 tab and type the previous formula, the result will be a #VALUE error. So, where on the spreadsheet did I write this formula? Well, that’s the thing—it’s actually not stored anywhere on the sheet. Instead, I’ve stored it as a named range. That’s the second part of the answer.
If you open the Name Manager from the Formulas tab, you can find it listed under SelectedHouse (see Figure 5-17).
Figure 5-17. The Name Manager shows all the named ranges on the sheet. It’s where you store the CHOOSE formula
So, now that you know the formula is stored as the named range SelectedHouse, guess how you change the reference for the image object? You simply tell the image to reference SelectedHouse (Figure 5-18). You do this by selecting the image with your mouse, going up to the formula, and typing =SelectedHouse.
Figure 5-18. The image now references the named range SelectedHouse, which has the CHOOSE formula telling the image which house picture to return
To recap, these are the steps:
- You link a form control scroll bar to the named range Example3.HouseIndex. Because the scroll bar is “scrolled,” the change in value is reflected in Example3.HouseIndex.
- You use a formula that references Example3.HouseIndex. A reference to a named range is returned in the formula dependent upon the value of Example3.HouseIndex.
- The formula is stored as a named range, but it’s not linked to any cell. Instead, the formula itself is the value of the named range. You call this named range SelectedHouse.
- Finally, the image is set to reference SelectedHouse. Because the formula returns different ranges, the ranges are fed into the image.
The “no-code” method successfully delivers the capability you need at the expense of the cleaner, button-based aesthetic. Still, you should prefer the functional to the flashy; in the grand scheme, I don’t believe the buttons will be missed so long as the use of the scroll bar is adequately explained. Some might even prefer the scroll bar because it effectively doubles as a progress meter from the house at the beginning of the list to the one at the end.
The “no-code” method may appear confusing at first, but in truth, you can turn it around much more quickly than that of the coding methods demonstrated prior. There are, in fact, fewer moving parts, and it won’t take long to get them working. However, there isn’t a good method for inline documentation when strictly using formulas. Excel’s Visual Basic editor allows you to include documentation right next to your code. The no-code method does not include a mechanism by which you can annotate what’s going on for yourself or for others. But, with more practice, reading exactly what a formula is doing will come easily. As well, you can still create documentation independently of your work. I don’t have a specific opinion on the best way to document formulas, but sometimes I create separate file with notes like the recap previously to document what my formulas do.
The no-code method is also technically faster functionally than the other methods. If you go to one of the pervious examples, you might notice your mouse momentarily turning into a busy icon as each new picture is processed. In the no-code method, there isn’t really any processing required.
Consider this example. If there were a larger list of houses, the difference would become clear as you move down the list. In the code-based methods, each mouse click on a button fires a macro to move forward. If you need to move forward five houses, you will inevitably fire the GoNext and InsertNewImage procedures five times. With a scroll bar, when you hold down one of the paddles, the value will increment and decrement as you hold it, but it won’t fire any changes until you release your mouse. So, if you hold down the increase-paddle and let go once you’ve increased the house index by five, Excel will be given only one direction to return the range given by the index when you mouse is released.
Effectively, you’ve optimized the process. If there is n number of houses to choose from, in the worst-case scenario you’ll have to fire n number of procedures to view the desired house using the code-based method. With the no-code method, no matter how many houses there are, there will always be one direction called. Finding ways to optimize how often Excel must process a direction or calculate a formula is essential to thinking outside the cell.
In the next section, I’ll discuss novel ways to deal with sorting data on a spreadsheet.
Sorting
There are occasions where you may need to pull from a sorted list whose values are subject to change at any given moment. For example, you might have the range shown in Figure 5-19. The problem is that you need to be able to change any of these values but require that the list automatically sort each value upon a change.
Figure 5-19. An example of data that would need to be sorted
Sorting isn’t a hard problem for Excel, and VBA can easily accommodate your needs. For example, to sort the previous list upon a value change, you could write something like Listing 5-6 into the Worksheet_Change method of the sheet.
This method basically addresses the issue, but there are caveats. For one, you need to set the ScreenUpdating properties to false to keep Excel from moving the selector back to the sorted range every time there is a change to the sheet. As well, you may need to use the Worksheet_Change event to handle other spreadsheet interactivity—do you really want your list to be sorted every time a cell is changed for the entire workbook?
Alternatively, you could employ a sorting mechanism that uses no code at all and instead relies purely on formulas. You would do this by using the SMALL() formula to sort ascending and the LARGE() formula to sort descending. The SMALL and LARGE formulas are basically the same mechanism but in reverse. You’ll be using SMALL for this example (more on both formulas in later chapters), so for reference, the formula works like this:
=Small(Array, k)
- Array: An array or range of numerical data for which you want to determine the kth smallest value
- k: The position (from the smallest) in the array or range of data to return
So for the previous range, you could write =SMALL(B1:B13, 2), and the result would be as follows since 3 is the second smallest value in the array:
3Let’s take a look at a new set of values, which you can find in Chapter5SortingExample.xlsm from the chapter files. In Figure 5-19, I’ve sorted the list of values in column B into column E by simply using the SMALL formula. For the array, I’ve supplied the array of values (highlighted in Figure 5-20) B2:B13. For the kth values to pull out, I’ve used ROW() -1. ROW() pulls back the current row number in which the formula resides. Since you start on row 2, you subtract 1 from it. When dragged down, the ROW() – 1 becomes the numbers 1 through 12. Therefore, SMALL($B$2:$B$13, ROW() – 1) returns the first smallest number in the series in the first row and the 12th smallest—or the largest value in the series—in the last row.
Figure 5-20. Using SMALL to help create a sorted listed
However, that takes care of only the sorted values—you still need the associated item labels. To do this, you’ll have to look up the locations of those values with yet more formulas. First, you’ll need to find where the values in the sorted series (Column E) match up to their original placement in the unsorted series (Column B). You can do this using the MATCH() formula. For example, the formula MATCH(E3,$B$2:$B$13,FALSE) will return a location of 9. If you take a look at Figure 5-21 and start counting down nine rows from the start of B2, you’ll arrive at the location of where the 2 resides. If you look to the column to the left, you’ll see the item label you want.
Figure 5-21. Using INDEX and MATCH to create a formula-based sorted list
To grab this label, you’ll use the INDEX formula, which allows you to return a value form within an array by supplying a specific location—a perfect partner to the MATCH formula. I’ve done just that in Figure 5-20.
If you’re not used to using formulas in this way, this second method might appear daunting. In the first example of rotating pictures, the formula-based method had fewer moving parts—here, the formula-based method has more parts than its code-based counterpart. As well, it employs the MATCH formula, which is a type of lookup formula and can become costly over time to use.
There are, however, occasions in which you might prefer using only formulas. For example, by using formulas, the resulting sorted lists are separate from the initial raw data. Conversely, the first, code-based sorting method makes direct changes to the series list. Sometimes, it’s a good development practice to not make direct changes to the raw data. Formulas, then, can provide the buffer to transform the original raw data without modifying it.
Now that you’ve seen the power of formulas, let’s take a look at a novel use of both formulas and VBA code together. Enter the “Rollover Method.”
The Rollover Method
I don’t claim a lot of originality in my life, but as far as I know, I was the first to have discovered and written about the mechanism that serves for the basis of the Rollover Method (which I’ve also uncreatively titled). The Rollover Method allows for what had been previously thought impossible in Excel: the ability to run macro code upon a mouse rolling over a cell.
As far as I can tell, the Rollover Method may just be a bug, an unintended consequence that I’ve figured out how to exploit (which, admittedly, was entirely accidental on my part). Yet, with the Rollover Method, you can create a lot of powerful stuff. For my blog, I created the periodic table of elements shown in Figure 5-22 that allows users to roll over certain elements to see information about them. As well, users can roll over the selections on the side to highlight element classifications.
Figure 5-22. An interactive periodic table of elements in Excel that uses the Rollover Method
My friend Robert Mundigl of ClearlyAndSimply.com used the Rollover Method to create a dashboard that creates a pop-up bubble when a user rolls their mouse over different regions of a map of Oktoberfest (see Figure 5-23).
Figure 5-23. When the user hovers over certain parts of the map, a pop-up is displayed with relevant details
Rollover Method Basics
The Rollover Method uses two key components: user-defined functions (UDFs) and the hyperlink formula.
Here’s a refresher on the hyperlink formula:
=Hyperlink(location, [Friendly Text])
- location : Here you write the intended address of your hyperlink. This can be a web page (for example, 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.
As you might recall, user-defined functions are the functions that you can write within VBA modules and then use in your spreadsheet. For example, you could use the user-defined function in Listing 5-7 in your spreadsheet like in Figure 5-24.
Figure 5-24. User-defined functions can be used much like standard Excel functions
Rollovers work by treating user-defined functions as macros that fire when your mouse moves over a cell; it’s similar to how you can assign a macro to a button or shape to be fired when the user clicks it. To use one, you place the user-defined function within the HYPERLINK formula , like this:
=HYPERLINK(MyMouseOverEvent(),"Click here")
Although, as you will see, this is still only half the picture.
Implementing the Rollover Method
To implement the method, complete the following steps:
- Open a new instance of Excel.
- You’re going to write a user-defined function as described earlier. So, open the Visual Basic editor from the Development tab.
- Create or find a free module and insert a UDF that looks like this:
Public Function MyMouseOverEvent()
Sheet1.Range("A1").Value = "Event Fired!"
End Function - Now, go to Sheet1 of your Excel spreadsheet and, in cell A2, type the following:
=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 voila! —cell A1 should now say “Event Fired!”
WAIT, WHAT’S THE DEAL WITH THE #VALUE! ERROR?
Well, this is where life gets tricky. You see, using a UDF you were able to change the value of another cell. Technically, that’s not allowed; in fact, Excel shouldn’t have even let you do it. Most experts thought using a UDF to change the value of another cell was impossible. For example, in Professional Excel Development, the authors wrote the following:
One of the most common beliefs about UDFs is that they can change the value of cells other than the one they’ve been programmed into. This is not the case....A UDF cannot change any properties of the cell it has been entered into other than the value of that cell. Attempting to set the pattern or border of a cell from within a UDF, for example, will not work.
And yet, that’s exactly what the Rollover Method allows you to do! I suspect, however, that Excel knows that it wasn’t supposed to let you do it. That’s why you get that pesky #VALUE! error. Luckily, you can handle that error easily, using the IFERROR formula.
- So, in cell A2, rewrite the formula to look like this:
=IFERROR(HYPERLINK(MyMouseOverEvent(),"Click here"), "Click here")
The Rollover Method turns cells into hotspots that can fire macros when your mouse rolls over them. The prototype for it is as follows:
=IFERROR(HYPERLINK(UserDefinedFunction, text_to_display), text_to_display)
The Rollover Method is useful for implementing functionality that can provide the user with details on demand. For instance, when the user hovers over a certain cell, the Rollover Method can signal a macro that will display specific information. You’ll implement the Rollover Method into a dashboard later in this book.
The Last Word
I’ll expound upon the previous examples at length throughout this book. Ideally, this chapter has whet your appetite for what you can build in Excel. In Chapter 1, I talked about Excel development as a journey, so let’s reflect on how far you’ve come.
Remember the three pillars to making an awesome Excel application? I discussed optimization and using formulas to separate your work from raw data. These items fall into the area of good development practice. In the chapters previous, I talked about makes for quality data visualization. In this chapter, and throughout the book so far, I’ve talked about thinking outside the cell. But the journey has only just began and there’s still so much more to learn.
In the next chapter, I’ll delve deeper into VBA coding, once again challenging long-held conventions.