AI systems have got a lot better at making and fixing Microsoft Office VBA code. We compare Copilot and ChatGPT to see which is best choice for coping with the complexities of Office VBA.
Early in 2024 we tried asking ChatGPT for Office VBA code in some simple examples, the results weren’t great. A lot has happened in the last 12-18 months so we’re having another look to see if there’s been any improvement.
Copilot was very new when we did our initial tests and ChatGPT (the basis of Microsoft’s Copilot) has improved enormously.
Both AI systems give correct results, but one is far better than the other. AI now understands the differences between code for Word, Excel or PowerPoint. AI in 2025 has no mixing of object models.
For these tests we’ve used a paid Copilot plan with the “Think Deeper” option. Paid ChatGPT Plus using the o3 model.
Use ChatGPT for better Office VBA code
Copilot gives right answers but very narrowly defined answers. It’s adequate working code but little more.
ChatGPT’s responses are more comprehensive, and the sample code given is far better. It’s more like the type of code you’d expect from a thoughtful and experienced coder.
The AI responses can help a lot in getting a head-start on coding jobs but a long way from the fanciful sales notion of removing the need for human programmers.
“Write Word VBA macro to open a document”
This simple example is the one we started with last time. Copilot and ChatGPT give very different answers to the same question.
Both code examples work as given, unlike our earlier tests
Copilot
Copilot gives code that strictly answers the question and nothing more. The document path and name has to be hard-coded. It’s very similar to the answer from ChatGPT over a year ago.
ChatGPT
ChatGPT’s response goes beyond what was asked. Its code is more practical, opening a File | Open dialog to select a document with appropriate filters and default location. Elegant error and clean exit blocks. The complete code is at the bottom of this article.
Finding the bug in code
Modern AI can not only write code, it can also do a decent job at debugging code or suggesting improvements.
To test this we took some faulty code, suggested by ChatGPT in our previous tests.
Prompt: ”Why doesn’t this VBA code work in Excel?” followed by this code which only worked in Word, but not Excel as requested.
Copilot
Copilot correctly identifies the main problem (wrong object model) and the mismatched variable type from the Application.GetOpenFilename method.
And it offers some corrected code.
ChatGPT
Again, ChatGPT did a great and comprehensive job. It correctly identified the key problem (the wrong object model)
And it suggested a better way to trap an error than the string variable comparison.
Unasked, ChatGPT gave ‘cleaned-up’ code which is much better than the original and goes beyond simply fixing the errors. (complete code is below)
ChatGPT’s code to select and open Word document
'===========================
' Macro: OpenSelectedDocument
' Purpose: Let the user browse for a Word file and open it.
'===========================
Sub OpenSelectedDocument()
Dim fd As FileDialog
Dim chosenPath As String
On Error GoTo ErrHandler '--- basic error-handling
'--- create a File Open dialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Title = "Select a Word document to open"
.Filters.Clear
.Filters.Add "Word documents", "*.doc; *.docx; *.docm"
.AllowMultiSelect = False
.InitialFileName = Options.DefaultFilePath(wdDocumentsPath) 'starts in Documents folder
If .Show = -1 Then 'user clicked Open
chosenPath = .SelectedItems(1)
Application.Documents.Open FileName:=chosenPath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
AddToRecentFiles:=True
Else 'user clicked Cancel
MsgBox "No document selected.", vbInformation, "Open Document"
End If
End With
CleanExit:
Set fd = Nothing
Exit Sub
ErrHandler:
MsgBox "Sorry—something went wrong: " & Err.Description, vbExclamation, "Open Document"
Resume CleanExit
End Sub
ChatGPT code to select and open an Excel workbook
This code is better than the original but needs a little human tweaking.
Line 5: the routine is still called ‘OpenDocument’ though it’s for Excel workbooks. Could be misleading
Lines 15-17: the File Open filter includes Word and PowerPoint files which can’t be opened by the rest of the code (ie. Line 31)
Line 19 : the File Open title should remove the ‘document’ reference.
### A cleaned-up Excel version
```vb
Sub OpenDocument()
Dim vFile As Variant 'Use Variant for GetOpenFilename
Dim wb As Workbook 'Workbook object to hold the opened file
'Let user pick a file (optional file filter & title shown)
vFile = Application.GetOpenFilename( _
FileFilter:="Office files,*.doc*;*.xlsx;*.ppt*," & _
"All files,*.*", _
Title:="Select a document or spreadsheet")
If vFile = False Then
MsgBox "No file was selected. Please select a file to open.", vbExclamation
Exit Sub
End If
'Open the chosen workbook
Set wb = Workbooks.Open(FileName:=vFile, ReadOnly:=False)
'Do something with wb here if you wish…
End Sub
Understand Office VBA code with ChatGPT
More adventures with Office VBA and AI
Copilot Pro brings AI into Microsoft Office
