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:

Sub NavigateFavorites()
    
    Dim ae As Explorer
    Dim oMod As MailModule
    Dim oGrp As NavigationGroup
    Dim oFldrs As NavigationFolders
    Dim i As Long
    
    Set ae = Application.ActiveExplorer
    Set oMod = ae.NavigationPane.Modules.GetNavigationModule(olModuleMail)
    Set oGrp = oMod.NavigationGroups.GetDefaultNavigationGroup(olFavoriteFoldersGroup)
    Set oFldrs = oGrp.NavigationFolders
    
    For i = 1 To oFldrs.Count
        If oFldrs(i).DisplayName = ae.CurrentFolder.Name Then
            If i = oFldrs.Count Then
                Set ae.CurrentFolder = oFldrs.Item(1).Folder
            Else
                Set ae.CurrentFolder = oFldrs.Item(i + 1).Folder
            End If
            Exit Sub
        End If
    Next i
    
End Sub

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.

::ThisPath::ThisWorkbook.Path & Application.PathSeparator

::ppg::Public Property Get

::with::With {End}{Enter}End With{up}{end}

:*:then`n::Then{Enter}{Enter}End If{up 1}`t

::forarray::For i = LBound(arr) to UBound(arr){Enter}{Enter}Next i{up}`t

:*:for ::
	;when you type for{space}, replace it with caps so you know you're in AHK mode
	SendInput FOR{Space}
	;wait for the next word and store it in counter
	Input, counter,I V T10,{Space}{Escape}
	;finish with ESC and you thwart AHK
	;but finish with a space and more stuff happens
	if (ErrorLevel = "EndKey:Space")
		{
		;if the next word is each, it's a for each loop
		if (counter = "each")
			{
			;wait for the next word and store it in eachctr
			Input, eachctr, I V T10,{Space}{Escape}
			if (ErrorLevel = "EndKey:Space")
				{
				;Once you know eachctr, fill in the Next line and go back up to the For line
				SendInput +{HOME}{DELETE}{Enter}Next %eachctr%{Up}For Each %eachctr%{Space}
				}
			}
		;if the next word is one of these, you're opening a text file
		else if (counter = "Append" or counter = "Binary" or counter = "Input" or counter = "Output" or counter = "Random")
			{
			;get the next word - it really should only be 'As'
			Input, askeyword, I V T10,{Space}{Escape}
			if (ErrorLevel = "EndKey:Space")
				{
				if (askeyword = "As")
					{
					;the word after 'As' is the file number
					Input, filenum, I V T10,{Enter}{Escape}
					if (ErrorLevel = "EndKey:Enter")
						{
						;complete the close statement, because I always forget that.
						SendInput {Enter}Close{Space}
						;you got to send this part raw because there may be a # in there and that's special
						SendRaw %filenum%
						SendInput {Up}
						}
					}
				}
			}
		else
			{
			;and finally if it's not all that special stuff, it's just a for next
			SendInput +{HOME}{DELETE}{Enter}Next %counter%{Up}For %counter%{Space}
			}
		}
	else
		{
		;thwarted, so get rid of caps
		SendInput {bs 4}for{Space}
		}
	Return

:*:do`n::
    sendinput Do{Enter 2}Loop{Up}{Tab}{Down}{End}{Space}
	Return
 
 :*:do until ::
    sendinput DO UNTIL{Enter}Loop{Up}{End}{Space}
	Return
 
 :*:do while ::
    sendinput DO WHILE{Enter}Loop{Up}{End}{Space}
	Return	

:*:.eof::
	sendinput .EOF{Enter}{Enter}{Tab}.MoveNext{Up}{Tab}
	Return
	
:*:Input(::
	sendinput Input$(lof(lfile),lfile)
	Return

:*:Input$(::
	sendinput Input$(lof(lfile),lfile)
	Return

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.

– Edit the name’s comment

– Filter Lambda names

https://jkp-ads.com/officemarketplacenm-en.asp

Here’s a screen-shot:

Regards,

Jan Karel Pieterse

jkp-ads.com

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

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

Private Sub CommandButton1_Click()
    
    Application.RecentFiles(1).Open
    
    Unload Me
    
End Sub

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

Public Sub OpenRecent()
    
    UserForm1.Show
    
End Sub

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.

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)

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

“WASD V2 88-Key ISO Custom” by Bitboxer is licensed under CC BY-NC-SA 2.0

This year I’ll be starting all of my programs with the keyboard. First, I’m going to hide the Cortana search bar because that’s just a waste of real estate. Ironically, the best way to do this is with your mouse. Right-click on the taskbar, choose Cortana, and Hidden.

Now let’s pin some frequently used applications to the taskbar. Start the application however you normally would. Find the icon in your taskbar, right-click on it, and choose Pin to Taskbar.

I have eight applications pinned to my taskbar. I can open these using the Windows key and the number representing that applications position in the taskbar. For me Outlook is first, so I would use Windows+1 to open Outlook.

Every morning, I unlock my computer, hold down the Windows key, and press 2, 3, 5, and 7. This opens all the programs that I leave running all day. I never close Outlook because of all the client side rules I have, but I include Win+1 to that process after a reboot. I used to open #4 every day, but that’s fallen out of favor. But I don’t want to remove it and change the positions or all the other icons.

In addition to opening the program, Win+number switches to that application if it’s already running. If that application already has the focus, it gets moved to the back of the line and hidden. Just press the shortcut again to bring it back to the front. If you have multiple instances running, like multiple Word docs or Excel files, continue holding down the Windows key and pressing the number to cycle through all the documents. When the one you want is at the front, release the Win key.

One more thing about the taskbar: let’s say you have a bunch of File Explorer windows open, which you no doubt opened using Win+E. To close them all at once, press Win+Alt+number to get a context menu and choose Close All Windows. I don’t have File Explorer pinned to my taskbar, so its position in the list isn’t the same every time. But it’s usually ninth or tenth. In this example, it’s tenth so I use Win+Alt+0 to bring up the context menu.

You might have also noticed that you could have used the Win+Alt context menu to pin the application to the taskbar instead of right clicking. If you did, you get an IKSD bonus.

OK, last tip before you’re on your way to a great keyboarding day. If you want to open a program that’s not pinned to the task bar, press and release the windows key and start typing the application’s name. For instance, if I want to open Powerpoint, I press and release Win and type power.

Windows shows you everything that has power in the name or description. The “best match” is usually what I’m looking for, but you can see that you can also get to PowerShell or the power settings control panel using this method. If you have some recently used documents, those will also appear. When I type ues to open Ultra Edit Studio, it shows me a few documents I used recently.

I hope you use these techniques on Wednesday to open your applications using your keyboard and become a little more productive. Happy keyboarding.

Check out my new Excel challenges page to find out which solutions I received and who won the prize for best solution!

I hope you enjoyed this challenge. If you have more ideas for challenges, let me know!

Regards,

Jan Karel Pieterse