Navigating Outlook Favorites

I have these four favorites defined in Outlook:

From the inbox, I could hit Shift+F6 to get into the Favorites area but sometimes I would end up in no man’s land and couldn’t figure out how to get back into the inbox. So, god forbid, I had to use my mouse. I wrote this macro:

And added it to the fourth position on my QAT. Now I can press Alt+4 to cycle through my favorites. And I’ve only hit Alt+F4 accidentally about a dozen times.

AutoHotkey in VBA

In response to https://nolongerset.com/all-about-indenting/

Macros with 32-bit API Calls

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.

RefTreeAnalyser: Two updates

Hi There,

It’s been a while since I last posted here. Today I have released an update of my RefTreeAnalyser utility.

The tool now allows you to add a Table Of Contents to your workbook which contains lists of all sheets, all charts and all Pivottables:

In addition I have added a Feedback button to enable my users to send their complaints -I mean compliments- directly to me! So as of now, if you have ideas for improvements or suggestions for additions or a bug to report, use that feedback button!

Be safe!

Jan Karel Pieterse

Opening a File from a Userform Disables Ribbon

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.

Excel Virtually Global

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)

Excel VBA Masterclass

Hi there. Hope you and yours are well in these difficult times!

This is just a very short announcement that I’ll be doing an on-line version of my Excel VBA Masterclass.

The training is scheduled for May 18, 20, 26, 28, June 2, 4 and I’ll be using Microsoft Teams to deliver it to your homes!

Register now!

Regards,

Jan Karel Pieterse