Navigating Outlook Favorites

Posted on March 23, 2023 by Dick Kusleika

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:

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

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.

AutoHotkey in VBA

Posted on January 11, 2023March 23, 2023 by Dick Kusleika

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

::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

::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

Macros with 32-bit API Calls

Posted on May 30, 2021 by Dick Kusleika

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.

Name Manager updated

Posted on December 14, 2020December 14, 2020 by jkpieterse

Do you use Range names in Excel? Today I have updated my 100% free Excel Name Manager add-in with two new features:

– 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

RefTreeAnalyser: Two updates

Posted on December 4, 2020 by jkpieterse

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

Posted on September 9, 2020 by Dick Kusleika

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.

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

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

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.

Excel Virtually Global

Posted on July 17, 2020July 17, 2020 by Charles Williams

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)