Skip to content

Add comments/help to your Office VBA code

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.

Understand Office VBA code with ChatGPT
More adventures with Office VBA and ChatGPT
Easily learn and make Office VBA with ChatGPT

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

    ' 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.

Designer gets a big AI image boost

Understand Office VBA code with ChatGPT
More adventures with Office VBA and ChatGPT
Easily learn and make Office VBA with ChatGPT

About this author

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.