Listing New Shareholders

I have a list of several hundred stock transactions that consist of purchases, redemptions, transfers, and reissues.

  1. To get a list of new shareholders, I start by creating a pivot table from the data (Alt+N+V+Enter)

  2. Put the ShareholderName in Row Labels and the Date in Values

  3. Right click on any of the date numbers and choose Summarize Values By Min to get the earliest date for every shareholder.

  4. Right click on any of the dates (that probably don’t look like dates) and choose Value Field Settings. Then click on Number Format and format the field as a date.

  5. While you still have a date selected, choose Sort from the PivotTable Tools – Options Ribbon and sort largest to smallest

  6. Now you can copy the 10 new shareholder names from 2015.

Now try it yourself. You can download StockRegister.zip

Retrieving Data from Add-in Worksheets

Add-ins have worksheets. You just can’t see them. But you can store information on them and it’s a good way to store settings, preferences, and other data. When you want to get to that data, you can go to the Properties for ThisWorkbook and change the IsAddin property to False. Now you can see the worksheets and change the data if necessary.

When you’re done, go back to the VBE and change the IsAddin property back to True before you save your changes. Don’t forget that part; it’s important.

I have a list of vendor codes stored on a worksheet in an addin. I need to see the list, but not change it. I didn’t want to go through all the IsAddin rigmarole, so I did this in the Immediate Window.

wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.value

That part returns a two-dimensional array of all the values in the first column

application.transpose(...)

That turns a two-dimensional array into a one-dimensional array.

?join(...,",")

That turns an array into a string with commas between the values. In retrospect, I should have used

?join(application.transpose(wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.value),vbnewline)

to get each code on its own line. Here’s a way to put it into a range, if that’s where you’re going with it anyway.

wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.Copy workbooks.Add.Worksheets(1).cells(1,1)

Structured Table Referencing and Double Brackets in Column Headers

If you use certain characters in the column names of your table, then you have to use double brackets around those names when you refer to them in formulas. I don’t care for that, so I don’t use those characters. I was converting some imported data into a table and, of course, it had spaces in the column names (and a few other naughty characters). I needed to clean up those characters before converting to a table. The formula below shows what the double brackets look like.

This office web page provides a list of characters that cause this behavior. I don’t know where they got that list, but I did notice that there was no underscore on it. I like to use underscores to separate words, but I can’t use them in tables because of the double bracket thing. I figured I’d better make my own list if I’m going to clean up data.

I wrote this code to list out all of the bad characters:

Public Sub TestColHeaders()
   
    Dim i As Long
   
    For i = 1 To 255
        Range("G8").Value = "One" & Chr$(i) & "Two"
        If Left(Range("h9").Formula, 4) = "=[@[" Or InStr(1, Range("h9").Formula, "'") > 0 Then
            Debug.Print i, Range("h9").Formula
        End If
    Next i
   
End Sub

It loops through all the characters in the basic ASCII character set, inserts the character into a the column header, and reads the formula that references that column. I check for =[@[ or an apostrophe. It turns out that all the characters you have to escape with an apostrophe also cause double brackets, so I only needed to check for the double brackets.

With my list, I created a function to clean the data before I use it as a column header. I don’t check for Chr(13) because I don’t think you can have that in a cell.

Public Function CleanTableColumnHeader(ByVal sHeader As String) As String
   
    Dim i As Long
    Dim sReturn As String
   
    sReturn = sHeader
   
    For i = 32 To 47
        sReturn = Replace$(sReturn, Chr$(i), vbNullString)
    Next i
   
    For i = 58 To 64
        sReturn = Replace$(sReturn, Chr$(i), vbNullString)
    Next i
   
    For i = 91 To 96
        sReturn = Replace$(sReturn, Chr$(i), vbNullString)
    Next i
   
    sReturn = Replace$(sReturn, Chr$(123), vbNullString)
    sReturn = Replace$(sReturn, Chr$(125), vbNullString)
    sReturn = Replace$(sReturn, Chr$(126), vbNullString)
    sReturn = Replace$(sReturn, Chr$(9), vbNullString)
    sReturn = Replace$(sReturn, Chr$(10), vbNullString)
   
    CleanTableColumnHeader = sReturn
   
End Function

Now, to make sure my Excel workbooks are utterly un-editable by anyone else, I’m going to write an event handler that converts all my underscores to something else as I type them. The only question, is should I use an elipsis (chr$(133))

One…Two

or a macron (chr$(175))

One¯Two

That’s a tough one.

Pivot Table Videos

If you don’t have an ad blocker installed, you may have noticed we have a new sponsor: myexcelonline.com. If you do have an ad blocker installed, you should whitelist this page because I only show relevant, unobtrusive ads.

myexceloneline.com offers a free pivot table webinar when you click on the add in the sidebar. I didn’t sign up for the webinar, but I did watch a few of the free videos on that site. They’re very well made, professional videos. I really like the pace – they move along without feeling rushed.

If you have an interest in pivot table or dashboards, give myexcelonline.com a try.

And the Winner Is

The winner of the Access 2016 Bible is:

Jef

who commented

Does it help my chances at all if I casually mention that Daily Dose of Excel is my browser’s home page?

No, it doesn’t help your chances. But it did help that the RAND() function next to your name returned the lowest value between 0 and 1. So good work!

ChipG wins the most clever comment prize for this comment:

SELECT Name, Email
INTO Winners
FROM Commenters
WHERE Name = 'ChipG'

The prize for most clever comment is nothing. But I’m sending him a consolation prize anyway. Let’s see… What do I have handy… I guess all I have is another copy of Access 2016 Bible. That shouldn’t set a bad precedent for future contests.

Thanks to everyone who commented and retweeted. When you do go buy the book, I promise I’ll spend my share wisely.


Access 2016 Bible

Announcing Excel Summit South: Auckland Sydney Melbourne in March 2016

We_Header

It has taken me 2 years to put this series of Excel conferences in Australia and New Zealand together.

Now please help me spread the word!

 

 

MVP_horizontal_Smallpwclogo

MS

Dev-Team

 

 

For the first time ever some of the world’s leading authorities on Excel and spreadsheet models are coming together to share their knowledge.

 

Speakers

 

 

 

If you use, rely on, tell stories with, worry about, or operate in the advanced areas of Excel, then there’s a track designed just for you.

EXTEND YOUR SKILLS

This is a unique opportunity to:

  • Learn from six of the world’s leading Excel MVP’s as they discuss the Excel topics most useful to you.
  • Hear industry leading speakers from around the world give you the latest views on Financial Modelling best practices, standards and spreadsheet risk.
  • Shape the future of Excel: Interact with members of the Microsoft Excel Dev Team as you explore with them the future of Excel.
  • Choose the sessions that best suit your needs from 23 masterclass sessions over two days of twin tracks for modellers and analysts.

INTERACT WITH THE EXPERTS AND MEMBERS OF THE EXCEL DEV TEAM

Use your opportunities, including two Panel discussions and Q&A sessions, throughout the two days to ask questions and discuss with the Excel MVPs and the industry experts.
The Excel Dev Team members will use this opportunity to learn from their customers, understand how you use Excel and get feedback on your Excel experience.

EXCEL SUMMIT SOUTH PLACES ARE LIMITED: REGISTER NOW

EarlyBird 20% discount available for registrations before December 31 2015.
Don’t miss out out on this unique Excel opportunity.

ModeloffSmallNETWORK AND ENJOY AT THE MODELOFF EVENING MEETUP EVENT

The ModelOff Meetup event  delivers plenty of opportunities to mingle, learn from your peers, talk to the speakers and have fun.

PROUDLY SUPPORTED BY

Sponsor_Logos

Access 2016 Bible

They’re here.

Who wants one? Leave a comment on this post and I will select one lucky recipient at random. Leave your comment by midnight CST this Friday. And for you international types, I reserve the right to not spend a lot of money shipping this book, so you might be out even if you’re picked.

For the rest of you, you’ll have to pony up the dough for the “#2 in Books > Computers & Technology > Software > Databases” by clicking here:


Access 2016 Bible

Let’s get that to #1.

Filtering PivotTables with VBA? Deselect Slicers first!

I’m in the final stages of coding up a commercial add-in that gives you lot more filtering options for PivotTables at your fingertips than you get out of the box. Here’s how it looks, along with the PivotTable its connected to and a native Slicer for comparison:
 
Pre Invert
 
 

As you can see, it offers you a lot more tricks than a native Slicer – including a nifty ‘Invert Filter’ function. It also lets you see a lot more items displayed in it compared to a Slicer: 22 items are visible in mine, vs just 17 in the correspondingly sized Slicer. And that’s one of the many beefs I have with Slicers…they take up far too much screen real estate for the scant options they offer. In fact, my version takes up no screen real-estate most of the time: it launches simply by double-clicking the PivotTable field header, and you can dismiss it when you’re done to free up space if you want:
 
Slicer Dismissed
 
 
So about that Invert Filter function. I love clicking on that sucker over and over again. It takes about 7 seconds to invert my sample PivotTable that has 1000 items in it (12 items of which are selected in this example). That’s actually pretty fast as far as inverting a PivotTable, because you need to change the .visible status of all 1000 PivotItems, and as per a previous post that’s very slow to do unless you get tricky. And 7 seconds is a vast improvement on the method Microsoft gave you…none. I can’t comprehend why simple options like these are not built in to Slicers and Tables, but your filtering pain is (hopefully) my financial gain. (Yep, this works on Tables too.)

If I click that Invert Filter label, then here’s the after-effect:
 
Inverted
 
 

Beautiful: 7 seconds, and she’s turned completely inside out. Unless that is, that Slicer happens to be selected. Then it takes a full minute. Why? No idea. Moral of the story: if you’re writing code to filter PivotTables, then you probably want to make sure any Slicers for that field are deselected first. Not disconnected…you can leave ’em in place quite happily, and the code won’t suffer.

Anyways, that’s the first look at my new baby. It does a lot more than just this inverting trick, too. Among other tricks, it lets you filter PivotTables based on external ranges, and gives you some absolutely fantastic new tools for filtering PivotFields based on just about any tricky search conditions you might want to string together – but that’s a subject for my next post.