ChatGPT 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 it’s 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 ChatGPT can make sample code in the language you choose.
Naturally, 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 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 “
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.
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.
' Declare variables
Dim strFile As String
' Set the file path
strFile = "C:\Documents\MyDocument.docx"
' Open the document
Excel VBA to choose a sheet with error if nothing selected
' 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
' Open the selected document
' Documents.Open FileName:=strFile ' above ChatGPT line does NOT work, change for Excel to ... Workbooks.Open FileName:=strFile
PowerPoint VBA to insert a slide then add todays date
' 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
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