Wednesday, August 1, 2012

How do you create hatching in Excel?

The pattern fill is a great tool to create hatching in Excel 2010 (hatching is when you add fine lines to graphics to represent shading or other factors). However, for some reason the option of filling a series with a pattern was taken out of the user interface in Excel 2007.  The good news is the Excel pattern fill was not taken out of the 2007 object model so you can use VBA to apply a pattern to a series. Andy Pope has done just that by creating an add-on to return the tools for hatching in Excel 2007. We recently had the opportunity to talk to Andy about creating the Excel fill tool. Thanks to Andy for taking the time to answer a few of our questions.

ESH: How long have you been using Microsoft Excel?
A: I started using Excel 5.0 around 1998.

ESH: Could you please explain why the hatching/pattern fill feature can be found in Excel 2010 but was left out of 2007?
A: That is really a question for the people at Microsoft. For Excel 2007 the functionality was there, for backward compatibility, but they did not include any UI for users to employ it. This was either an over-site or a deliberate move to depreciate the feature. The negative feedback to the removal of this feature made Microsoft reinstate the UI for Office 2010.

how to fill excel
Excel 2007

ESH: What is an Excel add-on? Why are they needed?
A: Add-ins are hidden workbooks that contain code to extended the functionality of Excel. Normally the functionality they provide is not specific to any one workbook or data set. As with the Pattern Fill add-in it provides the functionality to fill any shape or chart element.

ESH: Could you describe the process of creating the fill add-on? What were the steps involved?
A: First identify a problem or task that can be made easier and or quicker by using VBA code. Create the code required to perform the task. You need to bear in mind the following,
  • References should be to the active workbook
  • You need to provide UI elements so the user can interact with your code
  • You need to handle errors that your code may encounter as you cannot
  • control how the user will attempt to use your add-in
how to pattern excel
Excel 2010

ESH: Great information! Thanks again to Andy for taking the time to answer our questions and for creating such a useful plugin. To download the pattern fill plugin visit Andy’s page here.