Evaluate ActiveCell’s Table Row

Structured table references are pretty easy to generate in Excel. You know, the ones that look like:

Structured Reference

I recently realized you can use those structured references in a VBA Evaluate function to build strings based on the active row of a table. This makes for shorter code and avoids the use of Intersect, ListRow and ListColumn functions.

For example, let’s pretend you have a table of Oscar winners by year and you want to build a short sentence for each row of the table. Maybe you get a lot of emails from people asking what movie won the award in a given year. With a table like the one below you can simply choose the applicable row of column C and copy and paste it into your reply.

Per Row Output

This works okay, but it’s clunky. Instead, I’d rather generate the phrase in VBA based on the row of the ActiveCell. It was while writing this I had the happy thought to use VBA’s Evaluate function.

The Evaluate function basically returns the value of whatever you are evaluating as if it was entered in the active cell. At least that’s how I think of it for this instance. So, in the usage below, the structured reference’s @ symbol, e.g., [@Year] means “the value of the Year field in the active row of the table.”

It has two forms, one where you spell out Evaluate and one where you use square brackets, like I’ve done below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim BestPicString As String

'only proceed if the ActiveCell is in the table body
On Error Resume Next
If Intersect(ActiveCell, ActiveCell.ListObject.DataBodyRange) Is Nothing Then
   Exit Sub
End If
On Error GoTo 0

BestPicString = _
   ["Best Picture for " & tblBestPics[@Year] & CHAR(10) & "was " & tblBestPics[@Film]]
wsBestPics.Range("cellBestPic").Value = BestPicString
End Sub

Active Row Output to Cell

I did this because when I use the brackets I don’t have to double the quotes. I’m not absolutely sure of this, but here are the two versions of Evaluate that work for me:

BestPicString = Application.Evaluate _
   ("=""Best Picture for "" & tblBestPics[@Year] & CHAR(10) & ""was "" & tblBestPics[@Film]")

and

BestPicString = _
   ["Best Picture for " & tblBestPics[@Year] & CHAR(10) & "was " & tblBestPics[@Film]]

One more thing to note is that both versions error when I don’t include the table name – tblBestPics – although that’s not necessary in an actual Excel formula where you could just refer to [@Year].

Below is the code that I would write if I wasn’t using the Evaluate function. It’s not bad, but requires more variables and logic:

Sub Alternative()
Dim lo As Excel.ListObject
Dim ActiveTableRow As Long
Dim BestPic As String
Dim BestPicYear As String
Dim BestPicString As String

On Error Resume Next
If Intersect(ActiveCell, ActiveCell.ListObject.DataBodyRange) Is Nothing Then
   Exit Sub
End If
On Error GoTo 0

Set lo = ActiveCell.ListObject
'header row is DatabodyRange.Rows(0)
ActiveTableRow = ActiveCell.Row - lo.DataBodyRange.Rows(0).Row
BestPicYear = lo.ListColumns("Year").DataBodyRange.Rows(ActiveTableRow)
BestPic = lo.ListColumns("Film").DataBodyRange.Rows(ActiveTableRow)
BestPicString = "Best Picture for " & BestPicYear & vbCrLf & "was " & BestPic
wsBestPics.Range("cellBestPic").Value = BestPicString
End Sub

Password Form With CapsLock Warning and Show Password

This post covers a password form I’ve developed developed and am adding to my database-connected utilities.

As I’ve mentioned, I use Excel mostly as a development environment for database queries. So, along with tools like SQL Developer and SQL Server Management Studio, I run and test a lot of my SQL right in Excel. This lets me use all of its filtering, pivoting and other data features to explore and validate query results. I recently even developed a data dictionary in Excel which queries database metadata for helpful information about the tables, fields, foreign keys, etc.

We adhere to an old-school coding style in my workplace. We don’t alias our tables with meaningless one-letter names and we type our SQL in ALL CAPS. I like this, but it means that I often typo case-sensitive passwords because CapsLock is on. So I’m updating my password entry to include a CapsLock check. And while I’m at it I’m adding a “show text” button. And, as often happens when I make something I like, I’m sharing my password form with you.

The Form

In the GIF below, CapsLock was on when the form was called. Once the user clicks in the password textbox the CapsLock warning is displayed. The CapsLock check is called from the textbox’s Enter and KeyUp events, in case it’s pressed while typing the password. The warning is turned off in the Exit event.

You can also see the Show Password feature in action. It’s just a toggle button that switches the textbox’s PasswordChar from “*” to “”.

password form in action

The code includes the GetKeyState API code I found on the web and modified. In the downloadable example below the password form is called from a main module and the username and password are passed back from the form, as in my flexible chooser form post.

Features I Didn’t Add

For a while I made it so that clicking the Show Textbox button put the focus back into the password textbox. This was cool, but harder to code with a predictable circular event between the textbox and the togglebutton. Also, it seemed like overkill for a password form that you won’t be spending much time with. The other thing I looked at was having the Show Textbox control engage only while it was pressed, which seems more secure from shoulder-hackers.

Do you have a password form or suggestions for this one?

Download

Here’s a sample workbook with the form.

Workbook-level Listobject QueryTable Properties Manager

Here’s a utility I’ve used for years. It lets you manage True/False properties for structured tables with data connections – for example BackgroundQuery and PreserveColumnInfo. You can change properties for all tables in a workbook at once, or just one or a few tables.

ListObject QueryTable Properties Form

I only added some properties to the form. One of these, MaintainConnection, can’t be found in Excel’s front end, and the others appear in two different dialog boxes. So being able to change them for multiple tables in one dialog is really nice. For example, above I’d select all three tables using Ctrl-A and uncheck AdjustColumnWidth and MaintainConnection. You can see that currently both highlighted tables have AdjustColumnWidth turned off. Also, a gray checkmark, like the one for MaintainConnection, means that some selected tables have it turned on but others don’t.

Features

  • Select which tables to modify. Ctrl-A selects all tables in the workbook. The Choose Current Table button selects the table where the active cell is located.
  • The current settings for the selected tables are shown by the state of the checkboxes. The checkboxes are triple-state, so if the property is true for only some of of the selected tables, the checkmark will be gray, as with MaintainConnection in picture.
  • To change properties for selected tables, set them with the checkboxes, then click Apply.
  • Double-clicking a table activates the sheet it’s on.
  • To add another property to the VBA form, simply add a checkbox inside the grpProperties frame of the form. The checkbox caption must be the exact name of a Boolean property.

The VBA

I hadn’t looked at the VBA for this for a while, except to add MaintainConnection a year or so ago. The code was clunky in that it referred to each property individually in a couple of places, something like:

lo.QueryTable.MaintainConnection = me.chkMaintainConnection.Value
lo.QueryTable.BackgroundQuery = me.chkBackgroundQuery.Value

… and so on for each property/checkbox assignment.

I realized that CallByName would work great here. If you don’t know it, CallByName is kind of like an INDIRECT function for object properties and methods. So instead of the list above, I used something like this inside of a loop:

CallByName lo.QueryTable, ctl.Caption, VbLet, chk.Value
  • lo.QueryTable is the QueryTable object
  • ctl.Caption contains the property name, e.g., MaintainConnection
  • chk.Value is the value of the checkbox, i.e. True or False

To see the actual code and the working form go to the download link below.

A note about MaintainConnection

I’ve mentioned MaintainConnection a couple of times now. It’s the key to an issue that comes up once a year or so, and adding this property to the form helps me remember the solution.

The issue occurs when connecting a structured table to another workbook. If MaintainConnection is True and I refresh the table, it locks the source workbook and it can’t be edited. I tried changing the connection to read only and fiddling with other connection settings, but setting MaintainConnection to false solves the issue. And this form makes it easy to do for every table in the workbook.

Download

Here’s a sample workbook with the form and some tables to test it on. Let me know what you think!

Creating a History Navigation System

I’m working on an Excel workbook that’s a database dictionary. It has a data-validation list of all the tables in the database. Picking a table in the list updates a query that returns info about that selection. I’d like to keep a history of tables I’ve already looked at and get back to them quickly by clicking buttons or picking them from a history list. So I came up with a history navigation system.

The simple example for this post lets you quickly return to years of National League home run stats that you’ve already viewed.

History Navigator overview

What Kind of History do I want?

Two familiar examples of navigation history are those in browsers and file explorers. I’m in an out of Windows File Explorer all day long and use the history a lot. I like the way it works, but it seems silly that it includes the same folder multiple times.

File Explorer history

Firefox history doesn’t do that and just lists each visited page once. The thing that I don’t necessarily like is that if you hit the back button and then do a new search you lose the page that you “backed” from.

Firefox history

My Philosophy of History

After thinking about the above patterns for at least five minutes, I decided how I want history to work:

  • All previous choices made are included in the history for as long as the workbook is open.
  • Each previous choice is only included once.
  • If it’s already in the list, it’s moved to the most recent position when it’s chosen again.

The Code

It’s pretty simple, so I’m not going to go into it. You can see it by downloading the sample below. I used Form buttons so that I could assign the same subroutine to both of them without having to use WithEvents. Most of the logic has to do with updating the data validation list that has the history and updating the Prev and Next buttons. I’ve implemented this code in my data dictionary and it’s working well.

navigation history

Download

Here’s a sample workbook with the setup and code. Let me know what you think!

Some Things I Learned Lately

Seems like every day I pick up something new. So I thought I’d make a quick list of stuff I’ve learned lately.

1) XMLAGG in Teradata
Teradata’s handy XMLAGG function lets lets you flatten a column of data into a delimited list, thereby avoiding recursion. Oracle, to which I’m migrating, also has an XMLAGG function but the closer, and better-named, equivalent seems to be LISTAGG. The Teradata documentation is consistently terrible, so instead I’ll link to this Stack Overflow answer from the worthy dnoeth.

2) 64-bit Office Text File Connection Strings
While updating a fancy data-sucking addin I got an error message that my data connection was broke. Turns out MS changed the ODBC connection string for 64-bit ever so slightly from:

Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

to

Driver=Microsoft Access Text Driver (*.txt, *.csv);Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

There’s two differences. The addition of the word “Access” was quickly apparent when looking at this site. The second one took me some time to spot. Can you see it? Yup, they changed the semicolon after “*.txt” to a comma. I think it looks a lot better.

3) Format vs WorksheetFunction.Text in VBA to Mimic Cell Formats
I’ve done a couple of posts that attempt to give you a sneak preview of how different formats will look when applied to cells. I was using my ActiveCell Viewer to preview different date formats for a cell. The Viewer used the VBA Format function. I noticed that in some cases it returned text that isn’t what you get in a cell with that formatting.

For instance, below I’ve applied a formatting of an increasing number of “d”s to a date. With formatting of two to four “d”s the two outputs match:

format differences 1

However with 5 or 6 “d”s the VBA function begins to return a weird result that doesn’t match what you’d see in a cell with that formatting:

format differences 2

You can see below that a cell formatting of “dddddd” still returns “Friday,” just like WorksheetFunction.Text. In fact if you close the Format Cells dialog and re-open it, you’ll see that the formatting has been reset to “dddd”.

format differences 3

I’ve since fixed my Activecell Viewer and added some features. I’ll try to remember to post the improved version sometime.

4) You Can Undo a Pivot Table Refresh
Who knew? All these years I assumed you couldn’t. And then I tried it.

pivot table unrefresh

5) Pivot Table Grouped Number Ranges, Once Sorted, Can’t Be Forced Back to Numeric Order
At least I can’t figure out how.

pivot number ranges grouped and sorted

Can you?

Locating PivotItem Subtotals

I’m either on a roll or in a rut: here’s one more post about pivot field stuff. Last time I posted about determining whether a give pivot field has visible subtotals. This time I’ll tell you how to find them. The solution again relies on my new friend, the PivotCell object. My main function actually locates PivotItem subtotals, not a PivotField’s. I then wrap that function in another routine to deal with all of a PivotField’s PivotItems.

Here’s the VBA:

Function GetPivotItemSubtotalRanges(pvtItem As Excel.PivotItem) As Excel.Range()
Dim pvt As Excel.PivotTable
Dim pvtField As Excel.PivotField
Dim cell As Excel.Range
Dim ItemTester As Excel.PivotItem
Dim PivotItemSubtotalRanges() As Excel.Range

If Not pvtItem.Visible Then
   Exit Function
End If

'I can't figure a better way to get the containing pivot table
Set pvt = pvtItem.DataRange.Cells(1).PivotTable
Set pvtField = pvtItem.Parent
'Cells with subtotal PivotCellType are in ColumnRange or RowRange
For Each cell In Union(pvt.ColumnRange, pvt.RowRange)
   Set ItemTester = Nothing
   On Error Resume Next
   'Only test cells with an associated PivotItem
   Set ItemTester = cell.PivotItem
   On Error GoTo 0
   With cell.PivotCell
      If Not ItemTester Is Nothing Then
         If (.PivotCellType = xlPivotCellSubtotal Or .PivotCellType = xlPivotCellCustomSubtotal) And cell.PivotField.DataRange.Address = pvtField.DataRange.Address And cell.PivotItem.DataRange.Address = pvtItem.DataRange.Address Then
            RedimRanges PivotItemSubtotalRanges
            If pvtField.Orientation = xlColumnField Then
               Set PivotItemSubtotalRanges(UBound(PivotItemSubtotalRanges)) = Intersect(cell.EntireColumn, pvt.DataBodyRange)
            ElseIf pvtField.Orientation = xlRowField Then
               Set PivotItemSubtotalRanges(UBound(PivotItemSubtotalRanges)) = Intersect(cell.EntireRow, pvt.DataBodyRange)
            End If
         End If
      End If
   End With
Next cell

GetPivotItemSubtotalRanges = PivotItemSubtotalRanges
End Function

How It Works

Be sure to read the previous post on this topic for background of how I got here.

With that background in hand, what the function above does is fairly simple. It loops through the RowRange and ColumnRange of a PivotItem’s pivot table. It looks for cells with a Range.PivotItem property that matches the PivotItem passed to the function, and which have a Range.PivotCellType of Subtotal or CustomSubtotal. If so then that PivotItem subtotal range is set to the intersection of the pivot table’s DataBodyRange and the row or column of the cell being tested. The subtotal range is added to the array of subtotal ranges returned by the function.

The PivotField Routine

Here’s an example of using the GetPivotItemSubtotalRanges function. This Sub takes a PivotField as its argument and selects all of it’s subtotals.

Sub SelectPivotFieldSubtotals(pvtField As Excel.PivotField)
   Dim pvtItem As Excel.PivotItem
   Dim PivotItemSubtotalRanges() As Excel.Range
   Dim PivotFieldSubtotals As Excel.Range
   Dim i As Long

   If Not PivotFieldSubtotalsVisible(pvtField) Then
      MsgBox "No Visible Subtotals"
      GoTo exit_point
   End If
   For Each pvtItem In pvtField.PivotItems
      If pvtItem.RecordCount > 0 Then
         PivotItemSubtotalRanges = GetPivotItemSubtotalRanges(pvtItem)
         For i = LBound(PivotItemSubtotalRanges) To UBound(PivotItemSubtotalRanges)
            If PivotFieldSubtotals Is Nothing Then
               Set PivotFieldSubtotals = PivotItemSubtotalRanges(i)
            Else
               Set PivotFieldSubtotals = Union(PivotFieldSubtotals, PivotItemSubtotalRanges(i))
            End If
         Next i
      End If
   Next pvtItem
   If i > 0 Then
      PivotFieldSubtotals.Select
   End If

exit_point:
End Sub

Stray Code Bits You’ll Need to Run the Above

This is the function that checks whether a PivotField has visible subtotals, and that I posted about previously:

Function PivotFieldSubtotalsVisible(pvtFieldToCheck As Excel.PivotField) As Boolean
Dim pvt As Excel.PivotTable
Dim cell As Excel.Range

With pvtFieldToCheck
   'Only row and column fields can show subtotals,
   If Not (.Orientation = xlColumnField Or .Orientation = xlRowField) Then
      GoTo exit_point
   End If
   Set pvt = .Parent
   For Each cell In Union(pvt.ColumnRange, pvt.RowRange)
      If cell.PivotCell.PivotCellType = xlPivotCellSubtotal Or cell.PivotCell.PivotCellType = xlPivotCellCustomSubtotal Then
         If cell.PivotCell.PivotField.Name = .Name Then
            PivotFieldSubtotalsVisible = True
            GoTo exit_point
         End If
      End If
   Next cell
End With

exit_point:
End Function

This one is because I want to hide the fact that I’m Redimming a lot:

Sub RedimRanges(ByRef SubtotalDataRanges() As Excel.Range)
If IsArrayEmpty(SubtotalDataRanges) Then
    ReDim SubtotalDataRanges(1 To 1)
Else
    ReDim Preserve SubtotalDataRanges(LBound(SubtotalDataRanges) To UBound(SubtotalDataRanges) + 1)
End If
End Sub

This is Chip Pearson’s array check:

Public Function IsArrayEmpty(Arr As Variant) As Boolean
'Chip Pearson
Dim LB As Long
Dim UB As Long

Err.Clear
On Error Resume Next
If IsArray(Arr) = False Then
    ' we weren't passed an array, return True
    IsArrayEmpty = True
End If
UB = UBound(Arr, 1)
If (Err.Number <> 0) Then
    IsArrayEmpty = True
Else
    Err.Clear
    LB = LBound(Arr)
    If LB > UB Then
        IsArrayEmpty = True
    Else
        IsArrayEmpty = False
    End If
End If
End Function

And this is what I attached to a button. Select a cell in a pivot table and if that cell’s PivotField has subtotals they will be highlighted:

Sub test()
SelectPivotFieldSubtotals ActiveCell.PivotField
End Sub

Subtotals Selected

In Conclusion

Whew! That feels like a lot of code with maybe not enough explanation. I plan to wrap up all this pivot field selection stuff soon with a post about my new-and-improved Per-Item Conditional Formatting tool.

Determining if a Pivot Field Has Visible Subtotals

In my last post I talked about identifying pivot table’s Values field, if it had one. That function plays a part in this post, which is shows two functions for determining if a pivot field has visible subtotals. As with the last post, I didn’t find much about this on the web. I even asked my first Excel question on Stack Overflow. After a bunch of experimentation I came up with a function that seems to always work. And then, whaddaya know I came up with a better one. I use the second function in my improved per-item conditional formatting utility, which I will post about soon.

What Do I Mean by “Visible Subtotals?”

In the picture below the pivot table is set to show subtotals for every field. However subtotals are actually visible only for the Region field. There’s none for the Items field, which makes sense since Items is the rightmost field, and its subtotals would just be a repeat of the individual item values:

All Subtotals at Bottom

The VBA Subtotals Property Does Half the Job

The first thing you might try in VBA is checking the pivot fields Subtotals property. However below you can see that it returns True for both fields. The issue is the same as above: Subtotals are turned on but they don’t show for the rightmost field:

Subtotals in Immediate Window

My First Attempt

So, I wrote some code that:
1. Checks if a field’s subtotals are turned on. If not, the function returns False.
2. Checks if any fields with the same orientation as the field we’re checking is a Values field
3. Tests if the field we’re checking is in the last position for its orientation (including the Values field). If not, then subtotals are on and the function returns True.

Function PivotFieldSubtotalsVisible_OLD(pvtFieldToCheck As Excel.PivotField) As Boolean
Dim i As Long
Dim SubtotalsOn As Boolean
Dim pvt As Excel.PivotTable
Dim ValueField As Excel.PivotField
Dim FieldPosition As Long

With pvtFieldToCheck
   'Only row and column fields can show subtotals,
   If Not (.Orientation <> xlColumnField Or .Orientation <> xlRowField) Then
      GoTo exit_point
   End If
   Set pvt = .Parent
   
   'Get the pivot tables ValuesField
   Set ValueField = GetValuesField(pvt)
   'The Value field is a column or row field,
   'but won't have subtotals
   If ValueField Is pvtFieldToCheck Then
      GoTo exit_point
   End If
   
   'There are 12 possible types of Subtotals (at least XL 2003 on)
   'If any of them are TRUE then Subtotals are on.
   For i = LBound(.Subtotals) To UBound(.Subtotals)
      If .Subtotals(i) = True Then
         SubtotalsOn = True
         Exit For
      End If
   Next i

   'No need to proceed if they aren't even on
   If Not SubtotalsOn Then
      GoTo exit_point
   End If
   
   FieldPosition = .Position
   'This is confusing, but
   'if the Values field's position is greater than the field-to-check's position
   'we want to ignore the Values field, as it won't affect the field-to_check's visibility
   If Not ValueField Is Nothing Then
      If ValueField.Orientation = .Orientation And ValueField.Position > FieldPosition Then
         FieldPosition = FieldPosition + 1
      End If
   End If
   'If the field-to-check isn't in the last position
   '(taking into account the Values field)
   'then it's Subtotals will be visible
   If (.Orientation = xlColumnField And pvt.ColumnFields.Count > FieldPosition) Or _
      (.Orientation = xlRowField And pvt.RowFields.Count > FieldPosition) Then
      PivotFieldSubtotalsVisible_OLD = True
   End If
End With

exit_point:
End Function

A Better Way – PivotCell to the Rescue

The above seems to work fine, but it’s got kind of a feel-your-way-in-the-dark aspect to it. I would much rather just have some code that examines the actual pivot table and figures out whether a given field is currently showing any subtotals. Happily, I have found a way to do this.

It’s based on the Range.PivotCell object and its PivofField and PivotCellType properties, all of which go back to Excel 2003, according to this MSDN page. They allow you to cycle through a pivot table’s cells checking for ones with a PivotCellType of xlPivotCellSubtotal (or xlPivotCellCustomSubtotal ) and, if so, checking what PivotField the subtotals belong to. I’ll discuss this some more after the VBA.

The Code

Function PivotFieldSubtotalsVisible(pvtFieldToCheck As Excel.PivotField) As Boolean
Dim pvt As Excel.PivotTable
Dim cell As Excel.Range

With pvtFieldToCheck
   'Only row and column fields can show subtotals,
   If Not (.Orientation = xlColumnField Or .Orientation = xlRowField) Then
      GoTo exit_point
   End If
   Set pvt = .Parent
   For Each cell In Union(pvt.ColumnRange, pvt.RowRange)
      If cell.PivotCell.PivotCellType = xlPivotCellSubtotal Or cell.PivotCell.PivotCellType = xlPivotCellCustomSubtotal Then
         If cell.PivotCell.PivotField.Name = .Name Then
            PivotFieldSubtotalsVisible = True
            GoTo exit_point
         End If
      End If
   Next cell
End With

exit_point:
End Function

How it Works

The code above actually only checks the pivot table’s ColumnRange and RowRange. These ranges are highligthed in the picture below. The code checks this area for cells with a PivotCellType of subtotal or custom subtotal. There are 10 PivotCellTypes, nine of which can be found in the ColumnRange or RowRange areas (the data area of the pivot table consists just of the xlPivotCellValue type.

ColumnRange and RowRange

The picture below highlights the cells with a PivotCellType of either xlPivotCellSubtotal or xlPivotCellCustomSubtotal. The custom subtotals are ones such as Min, Max and Average. These can be set in the field options menu. If the code finds a cell whose PivotCell.PivotCellType property is one of these two it then checks the cell’s PivotCell.PivotField object for a match with the field passed to the function.

PivotCell Subtotal types

I Like PivotCells

I’ve used the Range.PivotTable object quite a bit over the years. But it’s just recently that I’ve delved into the Range.PivotCell property. Hopefully I’ve given you some ideas for how you could use it to poke around in pivot tables.

Have you used the Range.PivotCell property? If so, leave a comment (I also love comments, especially the ones that add to my knowledge and don’t require me to do anything but say “thanks”).

PivotItem.DataRange Off By One Row Bug

This week I ran into a pivot table VBA issue I’ve never noticed before. When a pivot table has more than one data field, referring to a PivotItem.DataRange returns a range one row down from where it should be. Below you can see that the PivotItem.DataRange address is one row off and that the selection is below the pivot table:

PivotIItem.DataRange Offby One

If the pivot table has only one data field, e.g., if I get rid of “Sum of Total” above, the issue goes away.

I found one reference to this by Macro Marc on SO, but nothing else on the web. It seems like it would be a well-known thing though, especially if it’s been around for a while.

I’m curious if anybody knows whether this has been reported as a bug. I noticed it on my home computer running Office 365 Pro Plus. I’d be interested to hear if it’s on other versions.

My Workaround

In my very limited testing it seems like there isn’t a similar issue for PivotFields. So one idea is to compare the first row of a pivot field against the first row of its first pivot item and use the difference, if any, to offset the PivotItem.DataRange back to where it should be. However, I’m not sure that my concept of “first” will always be the same as Excel’s. Anyways I’m using this function:

Function GetPivotItemOffsetBugCorrection(pvt As Excel.PivotTable) As Long
'Only occurs if the pivot table has more than one data field
If pvt.DataFields.Count = 1 Then
   Exit Function
End If

GetPivotItemOffsetBugCorrection = pvt.VisibleFields(1).DataRange.Row - _
    pvt.VisibleFields(1).VisibleItems(1).DataRange.Row
End Function

Then I use it like this in places where I refer to a pivot item’s data range:

Set pvt = pvtItem.Parent.Parent
PivotItemOffsetBugCorrection = GetPivotItemOffsetBugCorrection(pvt)
For Each cell In pvtItem.DataRange.Offset(PivotItemOffsetBugCorrection)

Yuck!

If you’ve got a good solution for dealing with this, or any info, please leave a comment.

Identify a Pivot Table’s Values Field

Over the next few posts I plan to delve into a couple of functions I’ve written to identify areas in a pivot table. I also want to do a quick post on a pivot quirk I noticed recently. I then plan to roll it up into a post on my new-and-improved per-pivot-item conditional formatting tool. It’s good to have plans, right? Anyways, let’s get started with a function to identify a pivot table’s Values field.

I deduced the following just by messing around – I couldn’t find anything on the web about identifying a Values field. If I got something wrong, or if you have a better way to do this, please leave a comment.

What is a Values Field?

The Values field is the one that appears when you have more than one data field. Its location in the Rows or Columns area of the pivot table dialogs controls the grouping of those data fields. In the following example, I’ve grouped the data area by data fields within years. In other words, the two summing data fields appear side-by-side for each year:

Values Field by years then values

In the next example I’ve dragged the Value field up and now the data area grouping is for years within data fields:

Values Field by values then years

Some pivot table layouts, such as the one below, don’t show the word “Values” anywhere in the pivot table, but it still shows in the pivot table dialog:

Values Field Column Labels

Like all pivot fields, the Values field can be renamed. Note that though I changed it to “Frodo” in the pivot table, it still says “Values” in the dialog:

Values Field by values called Frodo

Everything I’ve said about the Columns area of the pivot dialog applies to the Rows area. The Values field behaves the same way there.

Identifying the Values Field in VBA

EDIT:

In the comments below Petra identified a much faster way using PivotTable.DataPivotField. DataPivotField contains the Values field, whether or not it’s visible. So,

If DataPivotField.Orientation <> 0

tests whether the Values field is present.

So, anyways, I wanted a VBA function that returns a pivot table’s Values field if it has one. When figuring out how to do this I asked myself:

Is the Values field a PivotTable.DataField or a PivotTable.ColumnField/RowField?

The answer is both, kind of. So, for instance, in the examples pictured above typing the following into the immediate window returns “Values”:

? ActiveCell.PivotTable.ColumnFields("Values").Name

And so does this:

? ActiveCell.PivotTable.DataFields("Values").Name

So it looks like the Values field is both a data and column (or row) field. To further confirm this, note that this statement returns True:

? ActiveCell.PivotTable.DataFields("Values").Orientation = xlColumnField

So, even though it’s both a Data and Column (or Row) field it looks like it’s a bit more of a Column field (I’m going to stop saying “or Row” now). This is backed up by the fact that you can’t refer to it’s Data personality using an index. In other words, the following returns an error:

? ActiveCell.PivotTable.DataFields(3).Name

(1 and 2 return the two other data fields)

Furthermore, if you check the

DataFields.Count

for the example above the count is only two.

Cutting to the Chase

In addition to the above, I’ve got one more informational tidbit: if you change the name of the Values field to “Frodo,” both its Data and Column selves refer to themselves as “Frodo.” So even though, as we’ve seen above, the dialog box continues to use the word “Values” to refer to this field,

? ActiveCell.PivotTable.DataFields("Values").Name

gets you a runtime error 1004.

This means that you can’t just refer to the values field using “Values” in either its DataField or ColumnField version. If you do and a user changes its name you’re out of luck.

Fortunately, this has an upside, and it’s not just that I have something to blog about. It means that a Values field name is the only field name in the pivot table that can be repeated for a Data field and a Column field. Usually two fields can’t have the same name. For example, in the examples above if you try to rename “Year” or “Values” to “Sum of Unit Cost” you’ll get a “Field name already exists” error. But in the case of a Values field both its Data and Columm/Row references will be the same name.

This means you can identify a pivot table’s Value field by finding a row or column field that has the same name as a data field. Cool, eh?

The Function

Function GetValueField(pvt As Excel.PivotTable) As Excel.PivotField
Dim pvtField As Excel.PivotField
Dim TestField As Excel.PivotField
Dim ValueField As Excel.PivotField
 
'If there's only one data field then there won't be a Values field
If pvt.DataFields.Count = 1 Then
    GoTo exit_point
End If
 
For Each pvtField In pvt.PivotFields
    On Error Resume Next
    'test each non-data field for a data field with a matching name
    Set TestField = pvt.DataFields(pvtField.Name)
    On Error GoTo 0
    If Not TestField Is Nothing Then
        'if there's a match then you've got the Values field
         Set ValueField = pvtField
        Exit For
    End If
Next pvtField
Set GetValueField = ValueField
 
exit_point:
End Function

Boom! Let me know if you’ve got a better way, anything to add, etc. And, as always, thanks for dropping by.

Force Userform Textbox Scrolling to Top

I use my Edit Table Query utility every day to easily modify and test SQL in Excel. The main textbox contains the SQL code, which often fills more than the textbox. The problem is when I click into the textbox it always scrolls to the bottom. Even though this has been happening for months this always catches me off guard. I’m surprised, then annoyed. I finally decided to take action, and came up with some code to force userform textbox scrolling to the top.

The Issue
Here’s an example of what I’m talking about. When I click the New Data button the textbox content looks good, in that the numbers start at one. But as soon as I click into it the content scrolls to the bottom. (To add to my annoyance, the scrollwheel doesn’t work in the textbox.)
textbox scrolling issue

My solutions uses the Textbox’s SelStart and SelLength properties. I set both to 0, meaning that the selection starts before the first character. That’s what the “Force Start at Top” checkbox in the form does. (Download below!)

However, when I added those two lines of code another issue appeared. There was no scrollbar. In fact in the animation above you can see that there’s no scrollbar until I click into the textbox. And below you can see that with the scrolling fix applied there is no scrollbar:

no scrollbar after fix

You can force the scrollbar to appear by arrowing down past the bottom of the visible content. An internet search came up with the solution of setting focus on the textbox. I do this before applying the SelStart/SelLength code. That’s what the “Make scrollbars visible” checkbox does:

textbox scrolling fixed

VBA
Here’s a basic subroutine that takes some text and a button object as parameters. It sets a textbox’s text, sets the focus on the textbox, sets the selection start to zero and sets the focus back to the calling button.

Sub FillTextboxText(TextboxText As String, CallingButton As MSForms.CommandButton)

Me.TextBox1.Text = TextboxText
Me.TextBox1.SetFocus
Me.TextBox1.SelStart = 0
Me.TextBox1.SelLength = 0
CallingButton.SetFocus
End Sub

Other Stuff

Note that the issue with the scrollbar not appearing only occurs once in the life in the userform. In other words, once it has appeared it will always appear. I think.

You might have noticed that the form also has a Same Data button, this button simply saves the textbox contents to a string variable and then set the textbox’s text to that variable. Oddly, when you do this and then click into the textbox no scrolling happens at all, even before the checkboxes are checked. To see this, leave the checkboxes unchecked, click Restart, then click New Data, then scroll halfway up and then click Same Data. There’s no scrolling, even though I’ve done almost the same thing as was done with the New Data button.

This all makes me wonder how MS programmed textbox behavior. It seems almost like it forces the textbox to the bottom to make the scrollbar appear, and that it somehow checks the contents before it changes the scrolling position.

Download

Here’s a workbook with the Userform shown in this post.