I have a fairly slow running macro (~20 seconds) that works fine on one computer, but never finishes on another. If finally decided to figure out why. (Big Daddy is the nickname of the guy who started this golf league way before my time. I am not Big Daddy, although maybe I should be.)

First I noted that Windows Defender was using a lot of CPU and Excel almost none.

I disabled Windows Defender realtime under Windows Security. Then I got a green leaf next to Excel that says “This UWP process is suspended to improve system performance.”

I don’t know what that means, but the internet told me to increase the priority of Excel.

That got rid of the green leaf, but the macro never finished. I read some stuff about 32-bit APIs and Win 10 trying to prevent malicious code. I use the CopyMemory API referenced in Rob Bruce’s comment to Terminating Dependent Classes

I rewrote all the dependencies to simply point to the other classes rather than use CopyMemory and all the problems vanished.

In Excel 2019 16.0.10361.20002 32-bit, when I open a file from a userform (which for me is always), the hotkeys on the Ribbon don’t work. Here’s how I reproduce:

In a new workbook, add a userform named UserForm1. Add a commandbutton to the userform. Paste this code in the userform’s code module.

Add a standard module to the workbook and past this code into it.

Close the VBE and return to Excel. Press Ctrl+F8 and run the macro and click the button. Now press the Alt key. I don’t get these guys:

If I Alt+Tab away and back, they show up again. If you launch the form from the VBE, you have to Alt+Tab to get to Excel so the test doesn’t work.

Also, I hadn’t tried this until just now, but I can’t even click on the Ribbon with mouse. Nothing happens. My next tack is to close and unload the userform before opening the file and see if that takes care of it. It’s odd though. It’s the only odd thing in 2019 and building up a pretty nice rant about it. Two posts in one year? Yep, it could happen.

https://excelvirtuallyglobal.com/

Tue 21 July to Thu 23 July 2020

Microsoft’s Most Valuable Professionals, or MVPs, are technology experts
who passionately share their knowledge with the community.  They are
always on the “bleeding edge” and have an unstoppable urge to get their
hands on new, exciting technologies. This virtual conference, now in its
fifth year, presents Excel, Data Platform and PowerPoint MVPs, together
with other acknowledged experts from Microsoft and / or around the globe
to present, answer questions and demonstrate the future of Excel and how
it will make your life easier, personally and professionally.
Topics include: Auditing Spreadsheets, Charts, Dashboards, Data
Analysis, Data Types, Dynamic Arrays, Excel Tricks & Tips, Financial
Modelling, Forecasting, Maps, Microsoft 365, New Functions and Features,
PivotTables, Power BI, Power Pivot, Power Query, Presenting Your Data,
Spilled Formulae, Testing, Timelines and developing solutions with DNA
.Net, VBA, Python, R, OfficeScript and Office-JS.  And note well – it’s
not all in English, with sessions in Mandarin, Portuguese, Spanish and
Telugu, as well.

Most sessions will be recorded so you may watch them later with
downloads aplenty – there are no medals for staying up to watch the
entire event live!

 From your own favourite chair, bring a laptop, an inquisitive mind and
your sense of humour.  Be prepared to learn heaps.  And remember, it’s
for charity – all profits will be donated to a global COVID-19 research
charity.

Price only US$23 Tickets at
https://www.eventbrite.com/e/excel-virtually-global-a-virtual-excel-summit-tickets-106319837496
(or just click ‘Register’ on the first site)

Why is it that I can do this from here…

…but it doesn’t let me do this from there?

When I want to edit a PowerPivot measure, I generally want to edit a measure I’m *already* using.  I don’t want to scroll through a whole bunch of tables in the fields list or the Manage Measures dialog trying to find it. I just want to right-click on that Measure right there, that’s staring back at me from the PivotTable Fields Values Pane.

Yes, I’ve added an idea on UserVoice for this. It languishes somewhat behind Ken Puls’ great idea to radically improve the layout of this part of the PivotTable Fields List.  Which languishes in turn behind Zac Barresse’s no-brainer suggestion that Microsoft allow users to use Tables that are on otherwise-protected sheets.

Honestly, I think the Excel UserVoice site is the Microsoft equivalent of this ‘nifty’ feature in my other most widely used application:

I did tweet this from my new social media experiment@InsightsMachine but I think all 8 of my followers are my mother. If you have followers that aren’t your mother, please retweet this.

Excel occupies a special place in my heart. It also occupies a special place in the Power universe thanks to the superpowers (DAX and PowerQuery) it shares with its alter-ego PowerBI. Unfortunately this Power Universe is a lot like the Marvel one: An ever-expanding cast of new players with sometimes overlapping powers constantly has me saying “Wait, what?” Characters like Flow and PowerApps among others. Where did they come from? What are their powers? How do they fit into this picture?

Which is why I’m planning on attending the Australia Summit for Microsoft Business Applications in Melbourne from 21st-23rd August. Even though it’s 2,571 km from Wellington. Even though I have to get up at 4am to catch a 6am flight to it. Even though I won’t get to go to work for a few days.

If you live in Australia or New Zealand, you should check out the content on offer for the ‘Power Platform’ track: There is a tonne of presentations that are directly relevant to DAX and PowerQuery for starters. Which means they are directly relevant to Excel Developers too. Particularly so if you’ve come to the conclusion that the only good formula is a DAX formula; and that the argument between VLOOKUP and INDEX/MATCH – or even between Option Explicit vs “ ” – are now pretty much irrelevant, given what we should instead be fighting about is “Should I handle this with PowerQuery, or PowerPivot?”

There’s also a lot of content pitched at Citizen Developers. Citizen what? Well, the Power universe is expanding so fast that Microsoft have decided to let anybody who knows how to drive a mouse program these tools. Granted, not all of us can. Yes, maybe they’re overselling it. But if so, organizations are certainly overbuying, meaning there is a ton of opportunity out there for anyone who can talk the talk, even if they’re still learning to walk the walk.

This conference is a great place to get up to speed on what’s going on in the wider realm that Excel inhabits. But the problem with any conference is scheduling clashes. Sticking with my movie analogy a little longer, it’s like the annual Wellington International Film Festival: It comes around once per year; I always have great intentions to map out my viewing ahead of time when flicking through the glossy programme, but everything I’m interested in seems to be on at the exact same time. Meaning paralyzing indecision and crushing FOMO.

But not this time. This time I have a plan. This time I have carefully studied every presentation outline, and scored each based on a highly scientific method in order to optimize my learning given the inevitable scheduling clashes. And I’m gonna share it with you, in case you want to tag along and sit beside me right at the front center. (Best place to sit. Most of these presenters can’t throw a free T-shirt for crap).

Wednesday 21 August

Creating and Managing a Power BI Enterprise Deployment (Craig Bryden). Why: Craig came to my house last time he was in Wellington. I liked his music. He liked my beer. I need to find out where he’s staying so he can like my music and I can like his beer.

Many to Many and Weak Relationships in Power BI (Matt Allington). Why: I’ve had many weak relationships over the years, and a complete absence of many to many ones. I’ve simply got to find out where I’m going wrong.

Build Flows Like a Coder Without Knowing How to Code (Leon Tribe). Why: I’m clueless. And lazy. And want to embellish my CV.

Thursday 22 August

Using Microsoft Flow as a middleware to turbo-boost PowerBI (John Liu). Why: The title reminds me of Knight Rider. That said, I like the sound of The Shortfall of Microsoft Flow and the Solution No One Talks About (Aung Khaing). So tough choice, but I’ll probably just go with whatever presenter looks most like David Hasselhoff.

Fifteen Examples how Power BI is enabling Local Government to become data-driven (Warren Dean). Why: It’s about the city of Casey. I went to school with Casey. He could hardly find his own house. I never dreamed he would go on to found a city.

Top 5 DAX Tricks for Super Effective Power BI Dashboards (Andrej Lapajne) Why: Zebra BI take their dashboard visualisation principles seriously. And I’m serious about DataViz. That said, I’m also keen on Solving Business Problems with DAX (Darren Gosbell), because I live for examples and problems, having been one and/or the other many times over.

Friday 23 August

Debugging Power BI Performance Issues (Darren Gosbell) Why: I’ve had performance issues in the past. Ask anyone. Mind you, Darren is on at the same time as Bhavik Merchant from Microsoft, who’s giving us Power BI Performance Tuning: What, Why and When? They should make these guys do a performance-off.

Unleash Row Level Security Patterns in Power BI (Reza Rad) Why: Because I’m insecure and prone to oversharing.

Learning M from the UI (Matt Allington) Why: I want to see what M stands for, and what it has to do with Unemployment Insurance.

Advanced Analytics Methods To Uncover Hidden Gems in Your Data (Martin Kratky) Why: Because I’m constantly regressing (I’d go to see Phil Seamark talk about The Art of Data Modelling with Analysis Services, but he runs the Wellington PowerBI User Group with me, so I’ll just make him give the presentation there, while I scarf free Pizza).

So, that’s my highly scientific method. And I think it’s as good a method as any.

Mind you, there’s a hell of a lot of other great looking presentations with less funny titles that I’m bypassing. Again, check out the Power Platform track on the website, or alternately click here for a handy Word doc I compiled contains the presentation outlines of a whole bunch of sessions that I’d recommend might be of interest to Excel folk.

Love to see you there.