Archive for the ‘Uncategorized’ Category.

New Excel and PowerBI user groups in Wellington

Do you live in Wellington, practically *live* in Micosoft Excel, day in, day out, and want an opportunity to pick up tips, tricks, hacks, and code from other Excellers? Have some tricks of your own to pass on? Need a second opinion on an approach you’re taking with a spreadsheet, or help untangling the monster mission-critical minefield of an XLSX file you just inherited?

If so, then boy do I have a group for you: I’ve just set up a dedicated Excel-focused meetup group at http://www.meetup.com/Wellington-Microsoft-Excel-Meetup/ that is just itching for folks like you to join. This Excel-centric group will pretty much cover the spectrum in terms of Excel’s incredibly deep functionality: Formulas, Tables, PivotTables, Charting, PowerPivot, PowerQuery, VBA, spreadsheet auditing and optimisation, dashboarding tips, information visualisation , the works.

I’m in the process of securing a venue for the first meetup, so expect to see more details soon. (And if you can help out with a venue, let me know).

At the same time I’ve come across Phil Seamark’s PowerBI group that has its first meetup on Wednesday at TradeMe. See http://www.meetup.com/Power-BI-User-Group-Wellington-NZ/ for details). No matter where your interest lies on the Excel/PowerPivot/PowerBI spectrum, between us we’ll have you covered. (I’ll be at the PowerBI event on Wednesday night at Trademe, and would love to see you there.)

So regardless of whether you’re a VLOOKUP virgin, or a VBA Virtuoso, a PivotTable pariah, or a PowerBI Professional, between these two groups we’ll have community led content, comment, and camaraderie relevant to you, your job, and your career.

So what are you waiting for? Sign up TODAY()

Why I’m going to Excel Summit South. (And why you should too).

At first I wasn’t going to. I live in Wellington, which is 636 km to the south. (395 miles, to those of you in the dark ages). And this being Middle Earth, to get there by road I would have had to run the gauntlet of Goblins, Orcs, and cliche tourists like Zack Barrasse likely driving on the wrong side of the road as he heads to Hobbiton behind a grin bigger than one face could safely hold:
Zack

Here’s a picture of me imagining I’m speeding around a corner only to find Zack heading the other way:
Jeff

And then even if I manage to avoid Zack, there’s no avoiding the famous Auckland traffic. I still haven’t gotten over how bad it was last time I was up there:
Auckland traffiic

(Don’t get me wrong…the traffic south of Auckland is not without its own challenges):

And then there’s the whole “Paying for Excel feels a little like the concept of paying for sex…I’m good enough at it these days that I really shouldn’t have to” thing. (Excel, that is.)

But then I read the program. And there is truly something for everyone. Even me, who’s a virtual demigod at it compared to the poor suckers around me who don’t use it at all and have no desire to start now. Not to mention the outstanding cast. These guys and gals are like the Dirty Dozen:
speakers2

Or at least, they would be, if there were one more of them. Oh wait, look: there’s two more of them below. Okay, so they’re like the Dirty Baker’s Dozen, then.
yigaledery_2 ben_rampsonThose two extras are a couple of guys called Yigel Edery and Ben Rampson from the Excel Project Team. These guys think they’re here to discuss the future of Excel. But I still live in the past – along with 99% of the rest of us – so I’ll make sure they find some time to answer all my tricky questions, like “How ’bout refreshing the Conditional Formatting Dialog. Have you ever had to actually use it yourselves? Huh? Huh?” …and… Where the hell are my Dynamic PivotTable References already? We’ve got ’em for Tables, but not for PivotTables. Have you ever tried to integrate PivotTables into a formula-driven spreadsheet? Huh? Huh? …and… Why does every ‘new’ Excel feature have the prefix ‘Formally Known As’? Have you ever tried to write a book about characters that kept changing their names half-way through? Huh? Huh?

Boy do I feel sorry for those guys: They’ll be dead keen to talk about the new extensions, while I’ll be dead keen to point out that the stairs that lead to them still are a little unsafe to use.  Let’s hope they’re still smiling like that when they get back on the plane.


I’ll also get to meet this guy, Ken Puls, Ken…who wrote the most useful Excel blogpost ever in the history of most useful blogposts ever on the strength of it’s excellent existential first line alone: Do you know why you are here? He’s moving on to something more future focused in his presentation: Do you know where you are going? (A little place called PowerQuery apparently, located in the hip new suburb of Get and Transform.) Can’t wait, because I know zipcode about it.


Mythbusters jon And following straight on from that, I’ll either get to meet the Myth-Buster shown left, or the Chart-Buster shown right. (Apparently speakers are provisional and may change.) At least one of those guys needs no introduction: He’s Jon Peltier, and he’s a regular addition to my Google Search Terms whenever I try to squeeze out a good chart.  And I owe him an entire beer of gratitude thanks to the best macro I never paid for. He’s going to pick up where Ken left off: Using Ken’s Powerquery data to build a Dashboard.


jelen I also finally get to meet Bill Jelen. I’m gonna ask him nicely to autograph my stack of Excel books…even the ones he didn’t write. (He didn’t write like 0.01% of them, so it won’t take him long).

The only problem is that Bill’s talk coincides with another from Ken on PowerQuery. Damn. But maybe I’ll just have to skip Ken’s sequel, because Bill’s gonna dive deep into Data Visualisations (yes, it’s spelt with an s down here), Conditional Formatting, and PivotTables.


ZackAnd of course, Zack isn’t here merely to look down Hobbits’ holes. He’s also here to talk about Tables, and perhaps to drink me under one afterwards.


charles And I’m super-excited to be meeting Charles Williams, who’s like the Phar Lap of Fast Excel. The Edmund Hillary of Excel’s capillaries. The Ernest Rutherford of Excel under-the-hood. If you’ve got spreadsheets that take about as long to open as the running time of your average installment of The Hobbit, then you need to read this, and then you need to come meet this guy.


And that’s just a select few of the wizened, Excel-scarred faces that I’ll see there. There’s a whole bunch of other international stars and local heroes coming too (including from that sleepy continent-sized Island that lies to the West of New Zealand) that I just can’t wait to INDEX and MATCH.

So yes, I’ve got my ticket. And I suggest you get yours pronto, because – as we say down here – it’s going to be O for Owesome.

You going? Give me a shout out in the comments, and we’ll CONCATENATE. You thinking of going, but haven’t quite committed? Shout out below anyway, and perhaps one of the Dirty Baker’s Dozen will drop by and talk you around.

Problems with Apple’s sandbox requirements and Mac Office 2016 with VBA code

Hi all. long time ago I post here but i like to share this page with you all. After reading it you are happy that you only use Excel for windows <g> .

Problems with Apple’s sandbox requirements and Mac Office 2016 with VBA

http://www.rondebruin.nl/mac/mac034.htm

Have a nice day

Stack Cred

Ooh, look how special I am.

As a reward I bought myself some new monitors.

The real story is that one of my 22″ monitors died last weekend. It would display normally for about a second before going black. The consensus on the internet was to throw it away and buy a new one. It’s been a while since I bought monitors and I was expecting that 30″ monitors would be in my price range by now. I was disappointed that I they’ve only fallen in price enough that I could get 24s. But I’m pretty happy with monitors that are identical. And the boy is pretty happy to get my hand-me-down, working 22″ monitor.

My next problem was that my video card was too old to drive these beasts. I solved that problem by allowing the boy to open an early Christmas present.


EVGA GeForce GTX 970

He gets a better gaming card and I get his old video card. Easy-peasy. Except that it took me four days to get the cards swapped out as am I not the hardware genius I sometimes make myself out to be.

Have a lovely holiday if your religion or culture celebrates one this week. I’m going to see Star Wars on Christmas Eve, so if you see me walking around Denver this week, don’t spoil it for me (but be sure to say ‘hi’).

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.

Excel User Voice and Workbook Calculate

Excel User Voice

At the recent Excel Global Summit the Excel team were keen to explain how they have started using Excel User Voice to ask for and prioritise product improvement suggestions.

https://excel.uservoice.com/

There are 2 important things to note about this:

  1. The Excel Dev Team actually read your suggestions on User Voice!
  2. Starting with Excel 2016 the development and ship cycle is much much faster than the traditional 3 years.

So it really is worthwhile making your suggestions to improve the product.
You get 10 votes on suggestions, and the suggestions are categorised by end-point and product area. So if you want to make a performance suggestion for Excel on Android phones you can focus down, see what other suggestions have been made in this area, and either cast a vote for an existing suggestion or make a new suggestion.

Workbook Calculate

To try this out I made a suggestion (about calculation of course).

At the moment from the UI you can either calculate all open workbooks (F9 or Automatic) or a worksheet (Shift F9).
From VBA you can also calculate a range (Range.Calculate and Range.CalculateRowMajorOrder).

But you cannot calculate a single workbook.

This is really annoying when you have 2 versions of a large slow workbook open, or you have a small rapidly changing workbook open that links to a large mostly static workbook.

My suggestion is to add an additional calculation setting: Calculate Active Workbook Only and from VBA Workbook.Calculate.

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10601079-workbook-level-calculation

So please vote for my suggestion if you think it’s a good idea!

Listbox bulk refresh won’t redraw if called by Listbox click.

I’ve been playing around with ListBoxes recently. Or more to the point, they’ve been playing around with me. After much staring and swearing, I’ve found that you can’t get a ListBox to repaint itself when feeding it an entire list in one go in response to a Listbox_Click event in that same ListBox. It does however repaint correctly if you run the exact same code from somewhere else…say a CommandButton-initiated action.

By new list, I’m talking about populating the ListBox with an array via the Listbox.list=SomeArray approach. The .AddItem method works fine. So there’s always that: you can simply remove all items one by one, then add all the new items one by one. But that’s really slow: on my laptop it takes something like 15 seconds to add 10,000 items to that listbox. And it gets slower the more you add. And you can probably double that, if you need to replace each and every one of those items with something else. Whereas the Listbox.list=SomeArray approach takes less than a second to add ten times as many items. It’s fast.

So why would you want to add tens of thousands of items to a ListBox? And why would you want to perform a wholesale update that list by clicking in the Listbox itself? Here’s why: I’m designing a UserForm to help filter PivotTables. It’s a cross between the existing Pivot Filter functionality and a Slicer. Here’s what you see if you double-click on a PivotField header:
 
Initial State
 
 
Note the Search field at the top, and the three buttons immediately below that search field. If you type something into that field, then instead of displaying everything that’s currently filtered, you instead get a list of any matches, and you can then apply those search results to the underlying PivotTable via those three CommandButtons. The first cb simply filters the PivotTable to reflect the search, and the other two let you add or remove any search result from an existing filter. (The native filter lets you add, but not remove).

I want to do away with those three command buttons, and instead (in the event that a search is performed) simply list those three options at the top of the ListBox above any search results returned. Clicking on those three options will then trigger the exact same code as currently triggered by the Command Button.

Here’s how that looks currently if I actually type something in that Search box (Note I haven’t yet removed the three command buttons this new functionality will make redundant from that Search frame):
 
test search
 
 

I’ve added a simple bit of code to the lbResults_Change() event handler that checks if a user clicks any of those first three options. All that code does is trigger the exact same routines as would be triggered if they’d simply clicked on the actual command buttons themselves:

For i = 0 To 4
    If Me.lbResults.Selected(i) Then Exit For
Next i
Select Case i
    Case 0: cmdApplySearch_Click
    Case 1: cmdAddToFilter_Click
    Case 2: cmdSubtractFromFilter_Click
    Case 3: Me.lbResults.Selected(3) = False
End Select

The listbox gets updated just fine if I click on the command buttons, as you can see from the below. The Pivot has been filtered accordingly, and those contextual search options have been removed from the top of the listbox (and the search field cleared):
 
CB press
 
 
But watch what happens if I trigger the exact same code by clicking that first option in the ListBox itself:
 
ListItem
 
 
As you can see from the above screenshot, it still shows those three options at the top, even though they are NOT in the array that I assigned to the listbox, as evidenced by the screenshot below:
 
Watch
 
 
The Listbox DID get updated, mind:
? .ListCount 3
? .List(0) 263213: ICT Systems Test Engineer
? .List(1) 263299: ICT Support and Test Engineers nec
? .List(2) 839313: Product Tester

…It just didn’t get redrawn. And it won’t get redrawn unless I refill the ListBox via one of those CommandButtons I’m trying to do away with. And it doesn’t seem to matter if I set the focus to the CommandButton before I try to refresh the Listbox, or even clear the ListBox entirely before I try to refresh it.

You can see this behavior for yourself in this sample file I’ve prepared.

When the userform opens, you’ll see this:
 
Userform1
 
 
Clicking the CommandButton correctly loads a new array into the ListBox (and increases the size of the ListBox accordingly):
 
Userform2
 
 
…but if you click in the ListBox itself – which triggers the exact same code – then while you’ll see that the ListBox got expanded, it did not get redrawn:
 
Userform3
 
 
Just as weird: you can see that the ListBox got expanded by two lines. That’s because the ListBox_Click event got executed twice…even though I have the requisite event suppression code in place (and I’m not talking about application.enable events here, because that doesn’t work for UserForm events). Putting a breakpoint in the code also shows that the 2nd time it runs occurs immediately after the previous run finishes, and not as a response to the .list = SomeList bit that normally triggers such repeat runs.

And if I now click that CommandButton, you can see that the missing numbers from the double ListBox_Click pass are in fact there, as well as the extra addition from the latest CommandButton_Click:
 
Userform4
 
 
If I click that Use .AddItem Approach radio button and then click in the ListBox, things go according to plan:
 
Userform5
 
 
So there’s always that approach. But that approach sucks. Maybe I’ll be forced to keep those CommandButtons in the UserForm after all. Anyone else experience this issue, or have a workaround up their sleeve?

Happy Gerbitz Day

This is the first year you can sing “Happy Birthday” to Excel without having to pay a royalty, so that’s nice.

Here’s my uninteresting Excel story: My first spreadsheet program was SuperCalc. I remember we had orange screens on our PCs. Eventually we graduated to VGA monitors and Lotus 1-2-3 v.1A. I stuck with that version for a long time. I had tons of keystroke macros – whatever the heck those were called in Lotus – and I wasn’t giving them up. Excel burst onto the scene and I barely blinked. I don’t need that fancy new stuff. I used v.1A until 1992 and I think 1-2-3 was on v4 by then.

In 1992, I miraculously got a job at KPMG (nee Peat Marwick). Apple was a client of KPMG, so everyone got a Mac and used Office. It was like living in hell. I was a PC, Lotus 1-2-3 guy and I was forced to use these toys in business. Over time, I got used to my Mac Plus, then my SE30, then my PowerBook. And, of course, I got used to Excel and its obviously superior features to the version of 1-2-3 I was using. I don’t remember what version of Excel that was, I just know that most people in my office sucked at using it. Thankfully 25 years later, every office worker is, at a minimum, competent at Excel. What? That’s not true, you say? There are still people who work with Excel and aren’t competent? What the hell have they been doing for the last 25 years? It’s not like learning Excel is exactly cutting edge. End rant.

Being forced to use Office was probably a pivotal point in my life. (Using Macs in an accounting firm in the early ’90s was just stupid.) Pivotal though it was, I think what really turned me into an Excel geek was an intranet message board that KPMG had. I think they called it the KPMG Knowledgebase, but my memory isn’t so good. I could go on the message board and answer people’s questions about Excel. And I was hooked. Then it was on to newsgroups (nntp), a blog, and the crazy post-Microsoft newsgroup period that has mostly meant StackOverflow.com for me. What the heck did I do after MS closed the newsgroups but before SO? I don’t remember. I know I visited answers.microsoft.com once, saw a terrible answer from a moderator, and saw that the moderator had selected his own answer as “the” answer. I haven’t been back.

When I first started on the newsgroups, I was more of an Access guy than an Excel guy. I was surely answering more Access questions than Excel at the beginning. It was when I started reading Chip Pearson, Rob Bovey, Stephen Bullen, and others posting about VBA that the tables turned. I realize that Access has VBA, but the Excel object model was, and is, a thing of a beauty. I still do plenty of Access work, but it pales in comparison to the time I spend in Excel.

Other random memories:

  • At my first MVP Summit, everyone thought I was going to be a 60-year-old guy and I was in my mid-thirties. I guess I came off as cantankerous mature in my newsgroup postings.
  • I remember after a year of DDoE, a bunch of fellow Excellers joined as authors. There were some great posts back then.
  • I remember applying for a job and taking an Excel and an Access test. I aced them both. The secretary was looking at me like I was a witch. (If you’re reading this blog, you could ace them too.)
  • I remember planning an Australian Excel conference over beers and actually going through with it. If I had a nickel for every plan I made over beers that came to fruition, I’d have a nickel.