Easily learn and make Office VBA with AI

ChatGPT or Copilot can be used to learn the basics of Microsoft Office VBA coding with examples made to order.  Here’s some VBA made by ChatGPT for Word, Excel, PowerPoint and Outlook and some of its mistakes.

ChatGPT is a trial site which take a simple request and returns some text.  People have used it to write whole paragraphs and even poetry!  To find out more or signup to ChatGPT, go to https://openai.com/blog/chatgpt/

One geeky use is to produce simple chunks of computer code. Ask a question like “How to replace text in a string” and AI can make sample code in the language you choose. We wondered how it would cope with making some Microsoft Office VBA. Turns out, it does a reasonable but hardly perfect job.

Here’s some examples, we tried for Word, Excel, PowerPoint and Outlook.  The VBA code was NOT always correct with one notable error (see below).

We’re obliged to the Office Watch readers who shared their Office VBA expertise with us to improve and fix the original article.

While it might need some enhancement, ChatGPT or Copilot gives you a starting point and shows you the methods and properties to explore.  That especially applies to Outlook VBA which can seem like an impenetrable mystery to novices.

All the code made by ChatGPT is at the bottom of this article as text you can copy to use yourself.  In ChatGPT itself there’s a “Copy Code” button at top-right of the black code box.

Write Word VBA to open a document

A basic starting point for any Office VBA is selecting a document then opening it. ChatGPT gives a simple way to do that. Ask “Write Word VBA macro to open a document” and it shows some code to do that …

Note the line starting “Documents.Open…” which works, but only in Word. ChatGPT will use this code line again in places where it does NOT apply.

Write Excel VBA to choose sheet then open it, with an error message if no sheet selected

Better code will include a ‘trap’ in case no document is selected.  ChatGPT can handle that too.

ChatGPT isn’t perfect – part 1

That code works for Excel only because “Application.GetOpenFilename” is a method in Excel VBA only (don’t you love “consistency” across the MS Office apps?). Try that code in Word or PowerPoint and you’ll hit a “Compile Error” “Method or data member not found”.

Unfortunately, ChatGPT’s system can’t make a distinction between code for Excel, Word or PowerPoint – at least not for selecting a file.

Asking “Word VBA to choose document then open it”, “PowerPoint VBA to choose document then open it,” “Microsoft Office VBA to choose document then open it” and other variations all suggested the same incorrect code line.

We’re very obliged to David Peel for pointing out where we and ChatGPT had gone off the rails.

ChatGPT isn’t perfect – part 2

Did you notice the line “Documents.Open FileName:=strFile“?

It’s the same code to open a document that ChatGPT used in the first example – except that it does NOT work in Excel. As we’ve noted above, that object and method only applies to Word.

The correct lines for Excel or PowerPoint are:

Workbooks.Open Filename:=strFile
Presentations.Open Filename:=strFile

We’re not blaming ChatGPT (which is in trial at the moment), after all any smart AI or reasonable human would expect that the same code to select or open a file would work for all Office apps.

It’s just a shame that the system has picked up the code that only works for one app (Excel or Word) and uses that for other Office apps.

File Open dialog VBA code that works for all Office apps

BTW, The correct code for to open a File Open selector for Word, PowerPoint AND Excel is:

' Setup the File Open dialog as part of a larger function ...

Dim fsDialog As FileDialog

Set fsDialog = Application.FileDialog(msoFileDialogFilePicker)

'.Show the dialog. -1 means a document was selected.

If fsDialog.Show = -1 Then

   MsgBox fsDialog.SelectedItems(1) ' do something, in this case display the file path and name.

End If

PowerPoint VBA to insert a new slide then add today’s date

This code does the job in a basic way.  From this base you can add ‘frills’ like changing the default slide layout (“ppLayoutBlank”) and controlling the date format and positioning more precisely.

It’s NOT necessary to add ‘Write’ or ‘Make’ to the start of a ChatGPT request. Just the name of the Office app plus ‘VBA’ was enough, followed by the specific request.

This code assumes the slice deck is already open.

Outlook VBA to find the most recent email from “???” and open it

VBA programming is a lot more complicated for Outlook, but ChatGPT handles this request OK … up to a point.  It could be a great starter for Outlook VBA because it includes the, often assumed, declarations and objects needed before anything is done in Outlook.

Unfortunately the code does NOT always work in practice because of the messy nature of Outlook.

The suggested code has this block …

' Loop through the items in the Inbox folder 
For Each objItem In objFolder.Items   
' Check if the sender is "Fred"   
If objItem.SenderName = "Fred" Then     
' Open the email     
objItem.Display     
' Exit the loop     
Exit For   
End If 
Next

That code might work for you but, more likely, it’ll stop with a ‘Type Mismatch’ error on the final Next line. Why? Even though you’re searching the Inbox, it might contain a mixture of Outlook items, not just emails. When Outlook reaches a non-email item, it fails.

Proper code needs to filter the folder to only the type of item wanted, then do the search.

This code assumes the Inbox is sorted as ‘Most Recent’ first (which is normally true). As the code stands, it will open the first message it finds based on the ordering of the Inbox. 

No problem fixing that, we just asked ChatGPT again …

Outlook VBA change sort order of Inbox to Most Recent first

“Outlook VBA change sort order of Inbox to Most Recent first” gave us similar code to above but with the key line after getting the Inbox folder.

' Get the Inbox folder
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)
' Set the sort order to "Date (Descending)"
|objFolder.Sort "[ReceivedTime]", True

VBA code examples from ChatGPT in full

Here are plain text versions of all the ChatGPT examples we tried.

Word VBA to open a document

A very simple example with hard-coded file path and name.

Sub OpenDocument()

' Declare variables
Dim strFile As String

' Set the file path
strFile = "C:\Documents\MyDocument.docx"

' Open the document
Documents.Open FileName:=strFile

End Sub

Excel VBA to choose a sheet with error if nothing selected

Sub OpenDocument()

' Declare variables

Dim strFile As String

' Show the file picker dialog - only works in Excel - see above

strFile = Application.GetOpenFilename

' Check if a file was selected

If strFile = "False" Then

  ' No file was selected, show an error message

  MsgBox "No file was selected. Please select a file to open.", vbExclamation

Else

  ' Open the selected document

' Documents.Open FileName:=strFile
' above ChatGPT line does NOT work, change for Excel to ...
Workbooks.Open FileName:=strFile

End If

End Sub

PowerPoint VBA to insert a slide then add todays date

Sub InsertSlideWithDate()

' Declare variables

Dim objSlide As PowerPoint.Slide

' Insert a new slide at the end of the presentation

Set objSlide = ActivePresentation.Slides.Add(ActivePresentation.Slides.Count + 1, ppLayoutBlank)

' Add today's date to the slide

objSlide.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 200, 50).TextFrame.TextRange.Text = Date

End Sub

Find the most recent Outlook email from “Fred” and open it

Sub OpenMostRecentEmailFromFred()

' Declare variables
Dim objOutlook As Outlook.Application
Dim objNamespace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim objItem As Outlook.MailItem

' Get the Outlook application object
Set objOutlook = Outlook.Application

' Get the MAPI namespace
Set objNamespace = objOutlook.GetNamespace("MAPI")

' Get the Inbox folder
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)

' Loop through the items in the Inbox folder
For Each objItem In objFolder.Items
  ' Check if the sender is "Fred"
  If objItem.SenderName = "Fred" Then
    ' Open the email
    objItem.Display
    ' Exit the loop
    Exit For
  End If
' NOTE - code will probably give 'Type Mismatch' on this line.
Next

End Sub 

More adventures with Office VBA and ChatGPT

Excel merge arrays into a single array with VBA
Inside the Office VBA/MOTW changes
More VBA macro blocks coming to Office, details