Beyond Excel: VBA and Database Manipulation

May 28, 2013  1:23 PM

SQL, UDFs, and *DTAARA (Data Areas)

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Getting data from IBM’s iSeries into XL is normally no different than other platforms.  There are exceptions.  The iSeries’ multi-member files, such as source files, are problematic for SQL.  So are data areas.  This post deals with simple Data Areas.

To read simple Data Areas from SQL we need a little help from IBM’s iSeries.  We need a “stored procedure” which we must create, which means we must program on the iSeries. This post assumes you have access to your iSeries via a “green screen”.  If you don’t know what I’m talking about, bale now because this won’t make any sense to you.



I’m going to use an example to help explain things. In this example imagine we have a data area containing a numeric ID.  The steps to create this stored procedure are:

  • Code CL source to pass parameters and retrive data area’s contents
  • Create a CL Module from the code source
  • Create a Service Program from the CL Module.
  • Code an SQL Script to create a stored procdure using the Service Program
  • Run the SQL script

The first step is to write the CL source.  Let’s put it in member GetMyData in QGPL/QCLSRC.  Here is the example:

    PGM        PARM(&ID)                                                                                      
    DCL        VAR(&ID) TYPE(*DEC) LEN(10 0)                                                                                
    RTVDTAARA  DTAARA(MYDATA) RTNVAR(&ID)                                                                                                       

The next step is to create the CL Module.  At a command prompt type:


After the above executes succesfully, type this command:


Next we write our SQL Script.  Create source member GetMyData in QGPL/QDDSSRC:

  Create Function
    Returns DECIMAL(10, 0)
    Language            CL
    Specific            GetMyData
    No SQL
    Returns Null on Null Input
    No External Action
    Not Fenced
    External Name       'QGPL/GetMyData(GetMyData)'
    Parameter Style     SQL;

Lastly, we run the SQL script from a command line:


Now all that is left to do is test it.  Using command STRSQL to start an SQL session, run this SQL statement:

  Select GetMyData() From SYSIBM/SYSDUMMY1

SYSIBM/SYSDUMMY1 is a special one record file we can use for this sort of thing.



As you can see, there’s not a lot of work involved once you know how.  And if you look carefully, you can see many possibilities for doing things far beyond reading simple data areas!



April 18, 2011  11:47 AM

“Can there be Dynamic Ranges for VLOOKUP and Data Validation List for an Invoice?”.

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

This question was posed and answered by “Mr. Excel” and “Excel is Fun” in a recent YouTube post by the same title.  Their solutions work well.  I’m going to answer with what I believe is a better approach.  It uses Tables. Tables have some great characteristics:

Dynamic Tables grow/shrink as rows/columns are add/deleted.
Nieghbor Aware Tables automatically move other tables as they grow (with some restrictions)
Structured References In Excel 2007 and higher (XL’07+), tables create special names so we can address a table’s column by its heading. Ex: =Table[Column]
Stylish Table Styles  (in XL’07+) dramatically improve data formatting.
Links They can link to external data.
Duplicate Removal XL’07+ includes a menu option to remove duplicate rows.

Creating tables is easy.  If you have a range that contains data, simply select any cell in the range and use the keyboard shortcut: CTRL-L (in XL2007 and later you can also use the more intuitive CTRL-T).   Once a table has been created, you can change its name by selecting any cell in the table and using the menu tab Table Tools.  The Table Name input box is on the left in the Properties grouping.    

Tables have some drawbacks.

Not Supported Prior to XL’03 So if you’re using old versions of Excel, use the “Mr.Excel” or “Excel is Fun” approach – or upgrade and start using Excel’s Tables.
External Data Source Restriction MS Query and Data Validation can’t see tables without a Named Range Wrapper. 

A Named Range Wrapper (NRW) is simply a name assigned to a range.  To create an NRW for a table just select the table’s entire range and assign a name by typing it into Excel’s Name Box or using the menu path Formulas >  Define Name.   If you are working with a .xls, you’re done.  But if you’re working with a any of the new formats (.xlsx, .xlsm) you must change the name’s  Refers to: reference from a table reference to A1 notation.  That’s it.  The new range is every bit as dynamic as the best dynamic named range formula – without the formula.   


“Can there be Dynamic Ranges for VLOOKUP?”
In XL’07+ VLOOKUP works with tables already.  If you have a table called “Products” you can use it in VLOOKUP like this:

=VLOOKUP(“ABC”, Products, 2, 0) 

Where “ABC” is the value in the first column of Products you’re searching for.  In XL’03 VLOOKUP needs an NRW.  Once the NRW is created, we use it as we did above. 


“Can there be Dynamic Ranges for Data Validation?”
Data Validation does not recognize tables but we can still use them. We can either apply a “Named Range Wrapper” over the table and use the name in Data Validation or we can exploit a quirk of using tables.

QUIRK! Any reference placed over a table becomes dynamic, even absolute cell addresses.

So if we have a table in cells $A$4:$C$10 and we want our Data Validation rule to use the first column’s values (excluding the header) as a list, we can put =$A$5:$A$10 in Data Validation’s Source: box. Now if we add entries to our table, the Source box’s values will change automagically! How cool is that!

Clearly, tables are far simpler than the “Mr.Excel” and “Excel is Fun” approaches (both good approaches, both compatible with older XL, neither requiring VBA).  Note that we will never have to worry about other tables on the worksheet like we would with the traditional “COUNT” approaches.  We don’t have to worry about the table’s location – ever. We don’t have to worry about blanks, or numeric data, or character data or any combinations thereof that can throw off COUNT methods.

April 7, 2011  3:41 PM

Getting User Names from Outlook Into Excel

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

The company president’s mother died.   The staff wanted to express their sympathy.  Cards were purchased and now we needed to make sure everyone got a chance to sign them.  We needed names on a check list to circulate with the cards so when someone signed, they could check their name and hand the card to someone whose name remained unchecked.  So how do we make a list with everyone’s name and make sure no one is left off?

Everyone in our office has a computer.  Everyone uses Outlook.  All we needed was a quick way to get all user names into Excel to edit, format and print.  This is different from the contact list.  The contact list may, or may not have all users in it and it definitely has far more names than users. 

NOTE! Requires Excel 2003 or later because of ListObject

  1. Open Excel and get to the VBE (Alt-F11)
  2. Use Ctrl-R to bring up the Project Explorer
  3. Double click ThisWorkbook to bring up its code window
  4. Enter this code then run it. 

Sub Network_Users()

     Date   Ini Modification
‘   04/10/11 CWH Initial Programming
‘   04/03/14 CWH Added email addresses

    Dim olA     As Object       ‘Outlook.Application    Start Outlook (OL)
    Dim olNS    As Object       ‘Namespace              OL identifiers context
    Dim olAL    As Object       ‘AddressList            An OL address list
    Dim olAE    As Object       ‘AddressEntry           An Address List entry
    Dim lo      As ListObject   ‘An Excel Table

    On Err GoTo ErrHandler

‘   Create a ListObject/Table in the spreadsheet
    With ActiveSheet
        .Cells.ClearContents                    ‘Clear worksheet completely
        .Cells.ClearFormats                     ‘Clear formats as well
        [A4:B4] = Array(“Names”,”Email”)        ‘Add a column headings
        Set lo = .ListObjects.Add(1, [A4].CurrentRegion, , xlYes)
        lo.Name = “Names”
    End With 

‘   Open Outlook, set context, open “All Users” address list
    Set olA = CreateObject(“Outlook.Application”)
    Set olNS = olA.GetNamespace(“MAPI”)
    Set olAL = olNS.AddressLists(“All Users”) 

'   Add each address entry name to the Excel Table
    For Each olAE In olAL.AddressEntries
        With lo.ListRows.Add
            .Range(1) = olAE.Name
            .Range(2) = olAE.GetExchangeUser.PrimarySmtpAddress
        End With

‘   Format Results
    lo.HeaderRowRange.Style = ActiveWorkbook.Styles(“Heading 1”)
    ActiveWindow.FreezePanes = True

‘   Do this ONLY if you want to close Outlook

ErrHandler:    If Err.Number <> 0 Then MsgBox _
         “Network_Users - Error#" & Err.Number & vbCrLf & _
         Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
     On Error GoTo 0   

End Sub

March 3, 2011  1:12 PM

My Top 10 “Freebies” for the Midsize Enterprise IT Director

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

I’m departing from my usual post to share these gems with our community.  Here are my top “10” freebies:

#1 XenServer – Virtualize your physical servers with this free hypervisor.

#2 MySQL and MS SQL Server Express – Manage your data with these free professional class databases.

#3a Microsoft Visual Studio ExpressDevelop fully functional, professional windows based applications with this little brother to Microsoft’s enterprise platform.     

#3b Visual Web Developer Express – Create dynamic professional websites with this free version of Microsoft’s enterprise web development platform.

#3c VBA – You may not even know that if you have Excel, Word, Access, PowerPoint, Visio, Project, or Outlook … you also have a powerful development platform that can even (gasp) update mainframe databases.  And that VBA you use in Office products is almost identical to the VB code in Visual Web Developer Express’ Active Server Pages.

#3d Microsoft Visual Studio Express for Phone – Need to create mobile apps?  Start here.

#3e Microsoft Excel – Well, Excel isn’t free, but all you need for a roll-your-own Business Intelligence/Business Analysis solution is there.  Many BI/BA solutions either use Excel directly as their front end, or their human interface looks a lot like it.  All you have to do to make Excel emulate the big boy BI/BAs is some VBA code and a link to the data.

#3f MS Query – Bundled in every version of Excel, MS Query can link to any database and provide a graphical view of your company’s information. 

#4 Skype – Free video calls to anywhere on the planet. Need to support remote PCs? Chat and share desktops.  And if a picture is worth a thousand words, use Skype on a webcam laptop to show “Peggy” that dead server in your rack so you don’t have to repeat yourself a thousand times.

#5 Dropbox – Automatically sync local documents to cloud storage and access them from any computer connected to the web.  Share them with anyone you choose or make them available to the public.  No FTP skills required.

#6 Youtube – Broadcast training videos, product demonstrations, how-tos, president’s message, whatever to a select few or the entire world for nothing.

#7 Linked In – Network with other professionals in your field.  No dues required.

#8 Google/Bing/Yahoo/  – Okay, I know.  These are not new.  But how much are you paying for answers to tech questions available free from these web search engines? 

#9 Facebook – Facebook?  For the IT Director?  Really?  Yep.  Throw that paper newsletter in the recycle bin and join the social media revolution.  Put you company’s events, anniversaries, employee recognition, and more on Facebook and lock it down to employees only.  Save paper.  Save trees.  Save time.  Save money.

#10 SurveyMonkey – Need to know what people think of your product, your company, YOU?  Collect results via weblink, email, or Facebook .  Click here to take my 3 question survey.

#11 Craig’s List – I had to add this one.  We’ve been using Craig’s list to advertise job openings and the responses from it far outnumber paid media advertisements.  Some of the applicants are even quite good.


February 24, 2011  6:53 PM

Solving Dynamic Ranges with Tables/ListObjects

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Dynamic named ranges have been a subject of interest on Excel boards for sometime.  Lots of people want to know what’s the best way to create a named range that expands (or shrinks) according to the data entered, and can be used in formulas like VLOOKUP without change.  For some background on this subject see: Dynamic Ranges – Overcoming Shortcomings.  In that post I promised to talk about what I believe is truly, the one best way to handle this kind of thing, Excel’s Tables.


In 2003, Excel introduced something called a “ListObject” also known as a “Table”.  There are several features packed into Tables, but the main feature of importance in this discussion is their ability to grow or shrink dynamically.  Out of the box, a table is a dynamic named range.  So why agonize over what formula best creates one?

Creating tables is easy, simply select any cell within the desired range of data and press Ctrl-L (in Excel 2007 or 2010 you can also use Ctrl-T).  Excel will probably guess correctly what range you meant but gives you the opportunity to specify exactly what you want.  Whenever your selected cell is in a table Excel adds the Table Tools tab to its ribbon. There you can change the table’s name using the convenient “Table Name:” box in the upper left corner.

Unlike formula approaches, tables don’t mind other tables on a worksheet.  And Tables come with right click menu functions for adding/inserting/deleting rows/columns.  As you add rows or columns, the table automatically encompasses the new data, extends formats and formulas, and when rows are inserted, moves tables below to make room. 

Tables are superior to named ranges in every way except one: they can’t be used as datasources.   That’s easily overcome.  Simply select the range you want to use as a datasource (that can be the entire table, just the data, a column…) and give it a name using the name box in the upper left corner of each worksheet, or using the name manager.  The new name is every bit as dynamic as the best dynamic range formula – without the formula.

It’s Time to Move Ahead

At the time of this blog post, I still meet Excel fans enthusiastic over dynamic ranges.  When asked “why not tables?” I usually get a blank stare.  Some point out tables don’t work with all versions of Excel.  True enough.  But it’s been almost a decade now since XL 2003 came out.  I’ve long since upgraded every user in the company I work for to Office 2003 or higher.  I’ll bet your company has too.  So put your favorite dynamic named range formula in a frame and hang it on the wall with other momentous from your past.  It’s time to move ahead with tables.

January 27, 2011  9:50 AM

Animating Your Company’s Logo in Excel

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

This post shows how to animate a logo in Excel using VBA.  (Click here to download code)

[kml_flashembed movie="" width="480" height="292" wmode="transparent" /]

Logos are an easy way to add a sense of professionalism to your work. Splash screens and logos are part of every software product purchased and should be part of every application you provide you customers. Besides, animating logos is fun. It entertains users and sets your work apart from the average Excel programmer.  I use them when the workbook first opens.  Here’s how it’s done.

Open a blank workbook. Rename the first tab “Data”. This will be the worksheet displayed when our workbook opens.  Now get a copy of your company’s logo. Your company’s website probably has one about the right size. Paste it onto the Data worksheet.  We need to name it “Logo”. To do that, select it, then use Alt-F11 to get to the Visual Basic Editor. Use Ctrl-G to display the Immediate Window. Type “Selection.Name = “Logo” and hit enter. Now we’re ready to code.

Use Ctrl-R to bring up the Project Explorer. Double click “ThisWorkBook.” That brings up the workbook’s code window.  Our first routine must run when the workbook opens so we will code it in the Workbook_Open event. This routine activates the worksheet we renamed “Data”, and passes our “Logo” shape to two routines. The first “Grows” the logo into view. The second spins it once.

Private Sub Workbook_Open()
    ActiveSheet.Shapes("Logo").LockAspectRatio = False
    GrowShape ActiveSheet.Shapes("Logo"), 10
    SpinShape ActiveSheet.Shapes("Logo"), 10
End Sub

The “Grow” routine expands any shape into view. It first turns off screen updating. This allows us to make changes behind the scenes and display the results when we’re ready. Next, the routine remembers the shape’s dimensions. Then the routine changes the shape’s size, displays it, freezes the screen, and loops back until finished. Before returning, the Grow routine makes sure the shape is exactly as we found it.

The Spin routine is nearly identical. It freezes the screen, remembers the shape’s original dimensions, then alters the shapes size in a loop that simulates the shape spinning around its Y axis. When finished, the routine restores the shape to its original size.

That’s all there is to animating a logo. 

Function GrowShape(ByRef Shape As Shape, Step As Integer) As Boolean
'   Description:Expands a shape into view
'   Parameters: Shape       The shape to animate
'               Step        Larger #s animate faster
'   Example:    GrowShape ActiveSheet.Shapes("Logo"), 10
'   Note:       For best results, shape should be hidden before calling
'               this routine
'     Date   Init Modification
'   01/10/11 CWH  Initial Programming
    Dim lCenterX As Long    'Shape's center X coordinate
    Dim lCenterY As Long    'Shape's center Y coordiante
    Dim lWidth   As Long    'Shape's width
    Dim lHeight  As Long    'Shape's height
    Dim l        As Long    'Generic Counter for the loop
    Application.ScreenUpdating = False
    With Shape
       'Remember shape's original dimensions
        lCenterX = .Width / 2 + .Left
        lCenterY = .Height / 2 + .Top
        lWidth = .Width
        lHeight = .Height
       'Animation Loop
        For l = 0 To lWidth Step Step
            .Width = l
            .Height = l * lHeight / lWidth
            .Left = lCenterX - .Width / 2
            .Top = lCenterY - .Height / 2
            Shape.Visible = True
            Application.ScreenUpdating = True
            Application.ScreenUpdating = False
        Next l
       'Restore shape's original dimensions
        .Width = lWidth
        .Height = lHeight
        .Left = lCenterX - .Width / 2
        .Top = lCenterY - .Height / 2
    End With
    Application.ScreenUpdating = True
End Function
Function SpinShape(ByRef Shape As Shape, Step As Integer) As Boolean
'   Description:Expands a shape into view
'   Parameters: Shape       The shape to animate
'               Step        Larger #s animate faster
'                           Steps should divide 90 evenly
'   Example:    SpinShape ActiveSheet.Shapes("Logo"), 10
'     Date   Init Modification
'   01/10/11 CWH  Initial Programming
    Const Pi = 3.14159265358979
    Dim sng01 As Single     '1 Degree in Radians
        sng01 = Pi / 180
    Dim lCenterX As Long    'Shape's center X coordinate
    Dim lCenterY As Long    'Shape's center Y coordiante
    Dim lWidth   As Long    'Shape's width
    Dim lHeight  As Long    'Shape's height
    Dim l        As Long    'Generic Counter for the loop
    Application.ScreenUpdating = False
    With Shape
        .LockAspectRatio = False
       'Remember shape's original dimensions
        lCenterX = .Width / 2 + .Left
        lCenterY = .Height / 2 + .Top
        lWidth = .Width
        lHeight = .Height
       'Animation Loop
        For l = 0 To 360 Step Step
            .Width = lWidth * Abs(Cos(l * sng01))
            .Left = lCenterX - .Width / 2
            If l = 90 Or l = 270 Then .Flip msoFlipHorizontal
            Shape.Visible = True
            Application.ScreenUpdating = True
            Application.ScreenUpdating = False
        Next l
       'Restore shape's original dimensions
        .Width = lWidth
        .Height = lHeight
        .Left = lCenterX - .Width / 2
        .Top = lCenterY - .Height / 2
    End With
    Application.ScreenUpdating = True
End Function

January 18, 2011  5:28 PM

Debug Your Data with Conditional Formatting

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

(By Guest blogger Yoav Ezer)

Many times when a workbook is crammed full of numbers, your data can be difficult to read. This is bad enough when you are sure the data is correct, but If you are supplied a spreadsheet that contains potential errors you really want to be able to detect them so they can be fixed.

A common error in inputting is where your work contains duplicate records. There are a couple of ways we can delete duplicates, but what if you only want to see them rather than delete them? Here is when conditional formatting can help. With this little technique you can make the duplicates jump out at you!

Check out this screen grab below.

See how the duplicate rows are highlighted? In this sheet, the highlighting helps us indentify duplicate invoices.

The Solution

Here is how it was done.

First you need to go to the conditional formatting dialog as you normally would.

Then in the “Edit the Rule Description” box:

Enter this formula:


The formula might look complicated, and it kind of is. It relies on a function you might not have seen much called SUMPRODUCT. If you are curious about the function, this article is a great introduction to the topic.

In this formula, SUMPRODUCT will return the sum of rows from rows 2 through 16 where columns A,B and C are equal to the current row. If the result is greater than 1 then the format is implemented on that row.


As you can see, when you are given a spreadsheet containing problems, you don’t always want to nuke error rows, some times you need to know about them so you can deal with the issues at source. Conditional formatting can raise your awareness without changing the content of your spreadsheet. Give it a try!

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.

For more Excel tips from Yoav, join him on Facebook or Twitter

January 13, 2011  2:00 PM

Dynamic Named Ranges in Excel – Overcoming Shortcomings

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

A couple of months ago, Guest blogger Yoav Ezer posted a piece including the concept of Dynamic Named Ranges (see Strategies for Speeding Spreadsheets).  Dynamic Named Ranges are ranges within Excel that have been named using “Name Manager” or “Define Name” and that can be expanded or contracted without having to change what the name refers to.  Once named, you can use the range’s name in formulas and data validation instead of the common “$A$1:$D$50” cell references.  This makes your formulas simpler to read.  And with the dynamic nature of Dynamic Named Ranges, you don’t have to change the formula when rows of data are added. 

Here is a typical formula that could be added in Name Manger for a named range that starts in cell $A$1, has two columns, and can grow to as many rows as entries:


Translating this formula for us humans, it says:

  • From $A$1
  • Go down 0 rows
  • Go right 0 columns
  • Expand to down by the number of cells in column A that contain characters
  • Expand to the right 2 columns 


I “Googled” the subject and found many articles on Dynamic Named Ranges.  Obviously there is a lot of interest and many examples of their use; however, in every article I read, including those in Microsoft’s MSDN, I kept coming across the same shortcomings:

  • If your range contains empty cells, especially in the first column, you’re likely to get bad results
  • If your range contains spaces after the last row, you’re likely to get bad results.
  • If your range contains columns of different lengths, the suggested approaches are very cumbersome.
  • If your range starts somewhere other than row 1, the formula gets more complex.
  • If another range exists below or to the right of the first named range, you’re likely to get bad results.

Most of these limitations are because almost everyone seems to want to use COUNT or COUNTA to determine how many rows should be contained in the range.  I found one blogger who used MATCH instead of COUNT.  This had the advantage of skipping over empty cells, but still worked only for numbers, or for characters, but not for both (unless you double the formula and the MAX function). 

=OFFSET($A$1,0,0,MATCH("",$A:$A,-1),2)		'Finds last character cell
=OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),2)	'Finds last numeric cell

Seeking A Better Approach

The above approaches all work.  But no one of them works for all circumstances by itself.  And none of the approaches dealt with the stray space after the table’s last row.  That’s not good enough.  I want one compact formula that requires as little thought as possble that works for as many situations as possible.  In researching and experimenting, I accidently stumbled on a quirk regarding formulas stored in names that makes overcoming these problems much simpler.  Chip explains this quirk very well.

Defined Name Formulas And Array Formulas by Charles H. Pearson

If you use a formula in a Defined Name, that formula is evaluated as if it were an array formula. There is no way to force a formula in a Defined Name to be evaluated as a non-array formula.

Brilliant!  With this bit of knowledge, we can use logical functions that are insensitive to the type of data used (Numbers vs Characters).  Here is an array formula that finds the last row in column A containing anything at all (NOTE! The curly brackets are the result of Shift-Ctrl-Enter.  For more information on how to enter array formulas see Array Formulas by Charles H. Pearson).

{=MAX(IF($A:$A<>"", ROW($A:$A),0))}

Building on this, we can find the last row within the first four columns that contains anything at all, regardless of which column is longest.

{=MAX(IF($A:$D<>"", ROW($A:$A),0))}

Because logical operators in Excel return 0 for FALSE and 1 for TRUE, we can shorten the formula up a bit, if that’s your preference.


But one problem remains.  In my opinion, entries containing only spaces are the same as totally empty cells and as I said before, the above formula finds cells that contain anything at all, including those with just spaces.  No worries, this is simple enough to overcome by trimming cells.


This formula finds the last cell that contains anything other than just spaces.  It doesn’t matter if any of the cells in between are empty.  It doesn’t matter if any of the cells are numbers or characters.  It doesn’t matter which column is longest. To put it all together, you need to adjust for one more thing, the starting row.  I often want my ranges to start in row 4 with totals in row 2.  So if you want your range to start somewhere other than row 1, you need to subtract the starting row number and add 1 back for good measure.   Here is the final formula that you would enter into Name Manager for a range that starts in $A:$4 and has 5 columns (NOTE! Name Manager does not need the curly brackets since it treats ALL formulas as array formulas no matter what).

=OFFSET($A$4,0,0,MAX((TRIM($A:$E)<>"")*(ROW($A:$A)))-3, 5) 

This works.  It’s also slow.  It’s cumbersome.   

Solving with VBA

Formula approaches just don’t work well. There are just too many ways they can fail and too many limitations.  So I looked to VBA.  VBA has always had a very simple and elegant way of dealing with dynamic ranges:

Set DynamicRange = Range("A4").CurrentRegion

CurrentRegion finds all adjacent non-empty cells.  So if “A4” is anywhere inside a table, CurrentRegion will identify the entire table.  One minor problem is it will also pick up any adjacent cells with stray spaces.  But a bigger problem is that when used in a UDF (User Defined Function intended to be used within an Excel formula), CurrentRegion returns only one cell. 

A different approach (copied from Andy Pope) can be encapsulated into a VBA routine and used in a UDF context:

Set DynamicRange = Range("A4").Resize(Range("A4").End(xlDown).Row - 1, _
                   Range("A4").End(xlToRight).column - 1)

End() works as long as there is more than one row and column in the range.  If, for example, only one row is in the table, the End() method will find the last cell in the worksheet or the next list.

The “One Best Way”

Going through this exercise was interesting, but utlimately, neither VBA, nor complex formulas are required to create dynamic ranges without ANY of the shortcommings of either.  We’ll discuss that after another great Excel tip from Yoav Ezer.

October 4, 2010  12:58 PM

Chart Drill Down

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

PivotChart Drill DownThis seems so basic to me that I was not surprised Googling “Drilldown Excel Chart” shows lots of interest out there.  But the implementation is so simple that I was very surprised Googling “Drill Down Excel Chart” yielded almost no good suggestions.  That ends today.

[kml_flashembed movie="" width="480" height="292" wmode="transparent" /]

For beginners: What is “Drilldown?” 
Drilldown is displaying underlying details for a total.  This is important because we hope charts and summaries show something we didn’t know and/or expect.  When that happens, we want to know why.  Displaying what makes up a total helps answer that question.

Doesn’t Excel Support Drilldown Automatically?
In PivotTables and Outline Reports – yes.  You can double click any calculated number in these Excel objects and Excel displays the associated rows from their source data range.  But if you double click on a Chart/Graph element, the “Format Data Point” dialog box appears.  That’s not what my users want.  The good news, though, is the very same mechanism that reveals detail beneath PivotTables makes coding drilldown for PivotCharts a snap.

What are PivotCharts?
A PivotChart is a chart over a PivotTable.  In the templates provided in this blog, we use PivotTables to summarize data in our extracted rows.  PivotTables are extremely flexible and allow the user to slice and dice data in many, many useful ways.  The only draw back to PivotTables is they show numbers, not graphs.  This is easily overcome by simply creating a chart over the PivotTable.  Charts made from PivotTable data, as opposed to simple rows of data, also allow users to slice and dice the underlying data just like the user can with a PivotTable.  The only drawback to the PivotChart is that it lacks Drill Down. 

Adding Drilldown to PivotTable Charts
This “trick” only works with PivotCharts because it relies on the PivotTable’s ShowDetail property.  As mentioned before, you can double click on any calculated result in a PivotTable and it will automatically show the associated detail rows.  If you start the Macro Recorder, double click on a PivotTable cell, stop the recorder, and then view the recorded code, you’ll see something like this:

Selection.ShowDetail = True

…where “B9” is the cell you double clicked.  The Selection.ShowDetail = True is what causes the detail to display.  Now when you create a PivotChart from a PivotTable, each PivotTable cell becomes a chart element.  So what we have to do is figure out which chart element the user double clicked and which PivotTable cell that represents.  Then all that’s left to do is use that cell’s ShowDetail property to display the data.  As it turns out, this is almost easier done than said.

Determining Which Chart Element was Clicked
Excel provides a simple routine that makes this easy – ActiveChart.GetChartElementActiveChart.GetChartElement is a method attached to every chart in Excel.  You pass to it the mouse pointer’s X and Y coordinates and it returns the Chart Element Type and two of that Chart Element Type’s properties.  Chart Element Types can be the chart’s Title, Legend, Axis, … or a Graph Element.  We are only interested in Graph Elements such as a slice in a Pie Chart, a line in a Line Chart, a bar in a Bar Chart, etc.  So if ActiveChart.GetChartElement returns anything other than a Chart Element Type of 3 (Graph Element), we know to ignore things and move on.  On the other hand, if the user clicked a Graph Element, we want to show the detail.  When Chart Element Type is 3, Arg1 is the associated PivotTable row and Arg2 is the column.  So to show the detail beneath we use:

ActiveChart.PivotLayout.PivotTable.DataBodyRange. _
            Cells(Arg2, Arg1).ShowDetail = True 

Capturing Chart Double Click and Mouse Pointer’s X and Y
Every Chart also has a Chart_MouseUp event.  Chart_MouseUp fires whenever the user clicks (and releases) the mouse on a chart.  Excel also passes a few properties to this event.  Two are important to us: X and Y.

We now have all of the pieces to the puzzle.  All that’s left to do is put it together.  Place this code in the Chart Sheet object:


Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
                          ByVal x As Long, ByVal y As Long)
'   Description:Drill Down into Pivot Chart's data
'   Parameters: Button  Mouse botton that was released
'               Shift   State of SHIFT, CTRL, and ALT keys
'               x       Mouse pointer X coordinate within Chart
'               y       Mouse pointer Y coordinate within Chart
'   Example:    *none - This is an event handler
'     Date   Init Modification
'   10/04/10 CWH  Initial Programming 
    On Error GoTo ErrHandler
    Dim ElementID As Long
    Dim Arg1 As Long
    Dim Arg2 As Long
'   Pass: x, y. Receive: ElementID, Arg1, Arg2
    ActiveChart.GetChartElement x, y, ElementID, Arg1, Arg2
'   If data element clicked, show detail
    If ElementID = 3 Then
        ActiveChart.PivotLayout.PivotTable.DataBodyRange. _
            Cells(Arg2, Arg1).ShowDetail = True
        ActiveSheet.Cells(2, 2).Select
        ActiveWindow.FreezePanes = True
    End If
    If Err.Number <> 0 Then MsgBox _
        "Chart_MouseUp - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error Resume Next
    On Error GoTo 0
End Sub

September 23, 2010  7:45 PM

Controlling the Cursor: Find_Unlocked_Cell

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

This post has been superseded. Please see:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: