Microsoft Access Combo Box Search While Typing

If you start typing in a Microsoft Access combo box, it might not search as you type. I eventually figured out the trick to making the search work. The problem occurs when the combo box is based on a query. For the query to work, it must be a DISTINCT query. In other words, if you go to SQL View (select View SQL from the ribbon), and the query is:

SELECT tblCategory.pcCategoryId, tblCategory.strCategory
FROM tblCategory
ORDER BY tblCategory.strCategory;

Change it to:

SELECT DISTINCT tblCategory.pcCategoryId, tblCategory.strCategory
FROM tblCategory
ORDER BY tblCategory.strCategory;

After that the search while typing should work.

Open a URL using VBA

This is just a quick tip on opening a hyperlink using the default browser in VBA. It uses the ShellExecute Windows API call. Call the OpenHyperlink function shown below with the URL that you want to open.

Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hWnd As Long, _
  ByVal lpOperation As String, _
  ByVal lpFile As String, _
  ByVal lpParameters As String, _
  ByVal lpDirectory As String, _
  ByVal nShowCmd As Long _
  ) As Long

Public Function OpenHyperlink(ByRef strHyperlink As String) As Long
    OpenHyperlink = ShellExecute(0, "Open", strHyperlink, vbNullString, vbNullString, vbNormalFocus)
End Function

An example of calling it is:

Call OpenHyperlink("http://blog.xoc.net")

Calling a .NET 5 (.NET Core) COM DLL from Microsoft Office (Excel, Word, Outlook, etc.)

I have finally got a .NET 5 DLL written in C# able to be called from Microsoft Office! This process actually works for any application to call the DLL through COM.This has been a bear to figure out. It is not easy, but it can be done. There are some mentions of the process on the web, but I have seen no examples. I am going to post the code on GitHub with a sample project, and the full description over there.

So before launching into the process, here is my most basic recommendation. If possible, for the time being don't use .NET 5 or .NET Core at all. Use the .NET Framework 4.x, because it does much of the work for you. For my current project, that wasn't possible, because a library it needed to call was only going to be maintained on .NET 5, as it was also being called from at .NET 5 front end. This recommendation will likely change as the .NET core does the work of providing a type library in the comhost file they create in some future release.

But here is the basic process: 1) Create a project with a class and interface. 2) Decorate them with appropriate attributes. 3) Add the appropriate method and the commands to create the correct registry entries to the project. 4) Add the appropriate settings to project file. 5) Build the DLL. 6) Create a parallel IDL file that describes the interface. 7) Compile it with the MIDL compiler. 8) Register the DLL. 9) In office, add a reference to the DLL.

There are various gotchas in this process. One of the things that is tricky is making sure that your DLL, the comhost wrapper, and the version of Microsoft Office you are calling it from all have the same bitness (32 bits or 64 bits).

So given all that, you can find the test project with more detailed documentation here: https://github.com/GregReddick/ComTestLibrary/tree/master/ComTestLibrary1

Getting the Fonts in Use in a Word Document

Word does not provide an easy way to get a list of the fonts in use inside the document. Here is a VBA macro that will provide that list. In word, press Alt+F11, Select Insert Module from the menu, and paste the code below. Then from the View ribbon, click Macros, select XocListFonts from the list, then click Run. It will show a MessageBox with the names of all the typefaces in use inside the document. When you save the document, you will need to save it as a Macro Enabled Document, otherwise the code will be removed.

Public Sub XocListFonts()
    On Error GoTo ErrorHandler
    Dim rngStory As Range
    Dim rngChar As Range
    Dim strsFontNames As Collection
    Dim strFontOld As String
    Dim strFontCur As String
    Dim strFontName As String
    Dim objFontName As Variant
    Dim Number As Integer
    Dim strFonts As String
    
    Set strsFontNames = New Collection
    For Each rngStory In ActiveDocument.StoryRanges
        strFontOld = ""
        If rngStory.End > 1 Then
            Set rngChar = rngStory.Characters(1)
            Do
                strFontCur = rngChar.Font.Name
                If strFontCur <> strFontOld Then
                    strFontOld = strFontCur
                    
                    ' This causes a runtime error if the font doesn't exist
                    objFontName = strsFontNames.Item(strFontCur)
                End If
                rngChar.MoveStart wdCharacter, 1
                rngChar.MoveEnd wdCharacter, 1
            Loop Until rngChar.End = rngStory.End
        End If
    Next rngStory
    
    For Each objFontName In strsFontNames
        strFonts = strFonts & objFontName & vbCrLf
    Next objFontName
    MsgBox strFonts
Exit Sub
ErrorHandler:
    Number = Err.Number
    Select Case Number
        Case 5 'Invalid Procedure Call
            strsFontNames.Add strFontCur, strFontCur
            Resume Next
        Case Else
            MsgBox "Unexpected Error #" & Number & vbCrLf & Err.Description
    End Select
End Sub

An alternate way to get a list is to save the document as a PDF file. Then open the PDF file in Adobe Reader. From the File menu, select Properties. Then click the fonts tab. Adobe is a little more explicit about the fonts, differentiating Bold and Italic fonts from the normal font.

Forward All Headers from Outlook from a Button

There are times that you want to forward all headers an from Outlook email message. Usually this would be to report Spam. In the headers are the information necessary to block the offending email message. Since things like a return address can be (and frequently are) spoofed, the headers are the only definitive way of tracking these offenders.

You can get the headers by opening the message, then selecting File/Info/Properties from the menu. However, if the message is spam, I usually prefer not opening the message as there have been vulnerabilities in Outlook that allow malicious code to run. It's paranoia, but just because I'm paranoid doesn't mean they aren't out to get me. It is also tedious to copy the headers from the dialog and paste them into a message.

I have created a little code that allows creating an email message with just the headers from a press of a button. With a little more work, I could add the body of the message. It is just a little bit tricky, because an email message can contain really two bodies: a plain text body, and a HTML mail body. You could also add the attachments, if any. This code doesn't do either of those...it just creates an email message with the headers.

It opens the message in the editor so you can add a recipient and subject line. These could be automated if you knew the recipient, and then the message could be automatically saved and sent, which would place it in the outbox.

The code appears below. To add this code to Outlook, you need to bring up the VBA editor. The easiest way to do that is to press Alt+F11 on the keyboard. Then Insert Module, add the code, and save it.

Then assign the code to a Quick Access Toolbar button. Click on the little button at the end of the Toolbar called Customize Quick Access Toolbar. Select "More Commands" from the menu. Select "Macros" from the "Choose commands from" drop-down list. Then pick "ForwardHeaders" from the list, and click the Add button. You can modify the icon, if you like, by pressing the "Modify..." button.

Select one or more email messages. Click the button.

Here is the code:

Option Explicit

Public Sub ForwardHeaders()
    'From blog.xoc.net, written by Greg Reddick
    Dim selection As Outlook.selection
    Dim mail As Outlook.MailItem
    Dim i As Long
    Const PR_TRANSPORT_MESSAGE_HEADERS = "http://schemas.microsoft.com/mapi/proptag/0x007D001E"
    Dim headers As String
    Dim sendMessage As Outlook.MailItem
    
    Set selection = Application.ActiveExplorer.selection
    For i = 1 To selection.Count
        If selection.Item(i).Class = OlObjectClass.olMail Then
            Set mail = selection.Item(i)
            headers = mail.PropertyAccessor.GetProperty(PR_TRANSPORT_MESSAGE_HEADERS)
            Set sendMessage = Application.CreateItem(olMailItem)
            With sendMessage
                .Body = headers
                .display
            End With
            Set sendMessage = Nothing
            Set mail = Nothing
        End If
    Next i
    Set selection = Nothing
End Sub

The Four VBA Options You Should Always Change

I have been teaching and using Visual Basic and VBA for almost 20 years. Although the classic Visual Basic is dead as a programming language, VBA is still being used for macros in Microsoft Office. There are four settings in the options that you should always change:

1. Auto Syntax Check. Uncheck this option. If this option is checked, it brings up a syntax error message box every time that you leave a line with a syntax error. This gets in the way of programming, as you frequently want to move to other lines of code before completing the current line. When this option is off, the line will turn red if there is a syntax error. If you want to know why it is red, you can press F5 and then the message box will appear.
2. Require Variable Declarations. Check this option. This option is actually misnamed. It should be named "Insert Option Explicit at the top of every new module you create". It has no impact on existing modules and it is the Option Explicit that actually makes variable declarations required.
3. Notify Before State Loss. Check this option. This will give you a message box before committing any changes to a line that would require you to exit the debugger while debugging. This saves effort if you are doing a long debugging session, and make some minor change that would cause you to exit the debugger.
4. Compile On Demand. Uncheck this option. This will compile all of your code, rather than just the next procedure that it needs to execute. This will find syntax errors in code that you are not executing. Machines are 1000 times faster than when this feature was put in place.

This creates the best environment for programming in VBA.