ChatGPT is a powerful tool that can enhance your Office VBA code with comments and help. It or Copilot can analyze your code and generate explanations, tips, and examples to make it easier to read and debug
One of the modern AI tricks is to help write and understand programming code. We’ve already looked at uses for ChatGPT with Office VBA.
This time we’ll show a common situation. You’re faced with some code written by someone else (or you) which doesn’t have a lot of explanation.
ChatGPT can add comments, do a little code reformatting at the same time and do it all very well. Microsoft’s Copilot can do a similar job but not as well.
Here’s a simple example from our article about Automatic worksheet/tabs list in Excel here’s the original code with some comments.
Add comments to this Office VBA code
In ChatGPT, type
Add comments to this Office VBA code " then paste in the VBA code and close the quotes
Here’s the ChatGPT response. It’s left any existing comments intact but added a lot more. The single line of DIM setups has been broken up into one line for each variable. It’s even worked out what each variable is for and added a line of explanation.
' Function to create a list of all worksheet names in the active workbook Function SheetList() As Variant ' Makes the function recalculate every time there is a change in the worksheet Application.Volatile ' Declare variables Dim sheet As Worksheet ' Variable to hold each worksheet during loop Dim strSheetList() As String ' Dynamic array to store worksheet names Dim lngNumSheets As Long ' Counter for number of worksheets ' Initialize lngNumSheets based on Option Base setting If LBound(Array()) = 1 Then lngNumSheets = 1 ' Initialize to 1 if Option Base 1 is set ' Loop through each worksheet in the active workbook For Each sheet In ActiveWorkbook.Worksheets ' Resize the array to hold an additional worksheet name ReDim Preserve strSheetList(lngNumSheets) ' Assign the worksheet name to the current position in the array strSheetList(lngNumSheets) = sheet.Name ' Increment the worksheet counter lngNumSheets = lngNumSheets + 1 Next sheet ' Assign the array of worksheet names to the function's return value. ' Returns a dynamic array along columns, i.e., 1 row x nn columns. ' Use the Transpose() function in Excel to convert it into a list down a single column, i.e., nn rows x 1 column. SheetList = strSheetList() End Function
Copilot in Windows
Copilot in Windows does a similar job, as it should because Copilot is based on ChatGPT. However it’s code narration isn’t as good. Here’s just the first few lines.
Function SheetList() As Variant Application.Volatile ' This makes the function recalculate whenever any cell in the workbook changes Dim sheet As Worksheet, strSheetList() As String, lngNumSheets As Long ' This declares the variables for the sheet object, the array of sheet names, and the number of sheets If LBound(Array()) = 1 Then lngNumSheets = 1 ' This checks if the lower bound of the array is 1, which means the Option Base setting is 1, and sets the initial value of lngNumSheets accordingly
The comments aren’t as clear, there’s no code reformatting or overall comment to explain what the function does.
One small advantage are the suggestions at the end of Copilot’s response:
Generate page summary does NOT refer to the VBA code just made. Instead it’s offering a summary of the currently open web page in the Edge browser. So it’s not really a relevant follow-on from the code.
The other three suggestions do apply to the previous code-comment request.